Invoking oracle stored procedure package with sqlFile

General Discussion
1

Hello Team,

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

<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!

2

Change endDelimiter to “/” and try:

BEGIN
  p_refresh('TABLENAME',NULL,NULL,NULL);
END;
/
1 Like
3

Please refer liquibase documentation link end-delimiter.sql for more details to Daryldoak’s reply.

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

Thanks,
Rakhi Agrawal

1 Like
4

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.

<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!

 <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)

5

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.

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.

1 Like
6

Use

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

and .sql file should have

BEGIN
package.function();
END;
/

7

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