# Invoking oracle stored procedure package with sqlFile

**URL:** <https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873>\
**Category:** General Discussion\
**Created:** [May 19, 2022, 8:06pm UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873 "2022-05-19T20:06:28Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![spbendekar](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@spbendekar](https://forum.liquibase.org/u/spbendekar)\
**Post date:** [May 19, 2022, 8:06pm UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/1 "2022-05-19T20:06:28Z")

</div>

Hello Team,

How to call oracle stored procedure package with sqlFile tag of Liquibase  
**Example:**

```auto
<changeSet failOnError="true" author="ABC XYZ" id="TEST_sql_1" labels="create_objects.sql">

   <sqlFile splitStatements="false" endDelimiter=";" stripComments="true" encoding="utf8" path="/db/sql/create_objects.sql" /> 

  </changeSet>

```

**/db/sql/create\_objects.sql has contents:**  
`EXEC p_refresh('TABLENAME',NULL,NULL,NULL);`

**Error:**  
_Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement [Failed SQL: (900) EXEC p\_refresh(‘TABLENAME’,NULL,NULL,NULL);_

Appreciate your help!  
Thanks!

---

<div class="post-metadata">

**Author:** ![daryldoak](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/daryldoak/32/764_2.png) [@daryldoak](https://forum.liquibase.org/u/daryldoak)\
**Post date:** [May 20, 2022, 9:11pm UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/2 "2022-05-20T21:11:53Z")

</div>

Change endDelimiter to “/” and try:

```auto
BEGIN
  p_refresh('TABLENAME',NULL,NULL,NULL);
END;
/

```

---

<div class="post-metadata">

**Author:** ![rakhi](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/rakhi/32/288_2.png) [@rakhi](https://forum.liquibase.org/u/rakhi)\
**Post date:** [May 23, 2022, 5:28am UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/3 "2022-05-23T05:28:28Z")

</div>

Please refer liquibase documentation link [end-delimiter.sql](https://docs.liquibase.com/concepts/changelogs/attributes/end-delimiter-sql.html) for more details to Daryldoak’s reply.

Hope this helps. Please let us know if you need more help.

Thanks,  
Rakhi Agrawal

---

<div class="post-metadata">

**Author:** ![spbendekar](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@spbendekar](https://forum.liquibase.org/u/spbendekar)\
**Post date:** [May 25, 2022, 3:24pm UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/4 "2022-05-25T15:24:10Z")

</div>

Thanks a lot @daryldoak for the solution.  
Thanks @rakhi for documentation link

I’m able to execute SQLs (stored\_procedure.sql & create\_table.sql) separately with below xml changeset.

```auto
<changeSet failOnError="true" author="ABC XYZ" id="TEST_sql_1" labels="p_refresh TABLE" >
    <sqlFile dbms="oracle" splitStatements="true" endDelimiter="/" path="/db/sql/stored_procedure.sql" stripComments="true" encoding="utf8" /> 
  </changeSet>

 <changeSet failOnError="true" author="ABC XYZ" id="TEST_sql_2" labels="create TABLE">
    <sqlFile dbms="oracle" splitStatements="true" endDelimiter=";" path="/db/sql/create_table.sql" stripComments="true" encoding="utf8" />
 </changeSet>

```

But not working when I tried to combine then together (looks some issue with delimiter), getting error as ORA-00911: invalid character  
Please guide & help.  
Thanks!

```auto
 <changeSet failOnError="true" author="ABC XYZ" id="Combined_sql" labels="Combined PROC & TABLE">
    <sqlFile dbms="oracle" splitStatements="true" endDelimiter="/" path="/db/sql/execute_proc_create_table.sql" stripComments="true" encoding="utf8" />
 </changeSet>

```

**execute\_proc\_create\_table.sql**

BEGIN  
p\_refresh(‘TEST\_TABLE’,NULL,NULL,NULL);  
END;  
/  
CREATE TABLE TEST\_TABLE  
(  
KEY\_ID VARCHAR2(200 BYTE)  
, MODEL\_ID VARCHAR2(40 BYTE)  
, FNAME VARCHAR2(20 BYTE)  
, LNAME VARCHAR2(40 BYTE)  
);

/

**ERROR:**  
[2022-05-25 15:15:02] SEVERE [liquibase.integration] ORA-00911: invalid character  
liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: Unexpected error running Liquibase: Migration failed for change set db/sql/execute\_proc\_create\_table-2849.xml::Combined\_sql::ABC XYZ:  
Reason: liquibase.exception.DatabaseException: ORA-00911: invalid character  
[Failed SQL: (911) CREATE TABLE TEST\_TABLE  
(  
KEY\_ID VARCHAR2(200 BYTE)  
, MODEL\_ID VARCHAR2(40 BYTE)  
, FNAME VARCHAR2(20 BYTE)  
, LNAME VARCHAR2(40 BYTE)  
);]  
at liquibase.command.CommandScope.execute(CommandScope.java:163)  
at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:51)  
at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:21)  
at picocli.CommandLine.executeUserObject(CommandLine.java:1953)  
at picocli.CommandLine.access$1300(CommandLine.java:145)  
at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352)  
at picocli.CommandLine$RunLast.handle(CommandLine.java:2346)  
at picocli.CommandLine$RunLast.handle(CommandLine.java:2311)  
at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)  
at picocli.CommandLine.execute(CommandLine.java:2078)  
at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$1(LiquibaseCommandLine.java:325)  
at liquibase.Scope.child(Scope.java:189)  
at liquibase.Scope.child(Scope.java:165)  
at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:291)  
at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:80)

---

<div class="post-metadata">

**Author:** ![daryldoak](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/daryldoak/32/764_2.png) [@daryldoak](https://forum.liquibase.org/u/daryldoak)\
**Post date:** [May 25, 2022, 4:52pm UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/5 "2022-05-25T16:52:17Z")

</div>

Once you defined “/” as enddelimiter for the sqlfile you cannot use a semicolon to end any sql statements.

Your create table statement is still using semicolon as the delimiter, plus then you added the “/”. You cannot use semicolons for any SQL statements in that sqlfile.

```auto
CREATE TABLE TEST_TABLE
(
KEY_ID VARCHAR2(200 BYTE)
, MODEL_ID VARCHAR2(40 BYTE)
, FNAME VARCHAR2(20 BYTE)
, LNAME VARCHAR2(40 BYTE)
)
/

```

Note: PL/SQL and SQL are treated differently by Oracle when it comes to end delimiters. PL/SQL will contain many semicolons, but the entire block is one statement, so it ends with a “/”.

That being said, I would not recommend combining these into one changeset. Each DDL should be in it’s own changeset since DDL is not transactional in Oracle.

---

<div class="post-metadata">

**Author:** ![Abhay.Singh](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/abhay.singh/32/933_2.png) [@Abhay.Singh](https://forum.liquibase.org/u/Abhay.Singh)\
**Post date:** [February 28, 2023, 11:28am UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/6 "2023-02-28T11:28:30Z")

</div>

Use

sqlFile  
path=“xxx.sql”  
relativeToChangelogFile=“true”  
stripComments=“true”  
splitStatements=“false”  
endDelimiter=“/”  
dbms=“oracle”

and .sql file should have

BEGIN  
package.function();  
END;  
/

---

<div class="post-metadata">

**Author:** ![ppothuri](https://avatars.discourse-cdn.com/v4/letter/p/eb8c5e/32.png) [@ppothuri](https://forum.liquibase.org/u/ppothuri)\
**Post date:** [April 17, 2023, 3:27pm UTC](https://forum.liquibase.org/t/invoking-oracle-stored-procedure-package-with-sqlfile/6873/7 "2023-04-17T15:27:40Z")

</div>

Thanks Abhay Singh… Executing / Invoking Procedure with Begin and End statement works.