Executing SQL file from another SQL file

Community Answers
1

Hi everyone,

Could you anyone please help me clarify below scenario in liquibase.

I have an existing application where one sql file is called from another sql file.

“temp_script.sql” contains

start C:\scripts\temp_func.sql

“temp_func.sql” contains

create or replace function temp_func
return date
as
l_date date;
BEGIN
select sysdate into l_date from dual;
return l_date;
end;
/

i have added temp_script in one of the changeset, but i am getting error during execution

<changeSet author="user" id="4" runAlways="true">
 <sqlFile dbms="oracle"
    encoding="UTF-8"
    endDelimiter="\nGO"
    path="../scripts/temp_script.sql"
    relativeToChangelogFile="true"
    splitStatements="true"
    stripComments="true"/>
</changeSet>

And the error is

Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
    ... 72 more

Caused by: Error : 900, Position : 0, Sql = start C:\scripts\temp_func.sql, OriginalSql = start C:\scripts\temp_func.sql, Error Msg = ORA-00900: invalid SQL statement

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
    ... 85 more
2

The problem is your end delimiter. You used a “/” in your .sql, but you defined endDelimiter="\nGO" in your changeset. One or the other needs to be changed so they match.

3

Hi Daryl,

here is the modified code

Change Set

<changeSet author="author" id="4" runAlways="true">
 <sqlFile dbms="oracle"
	relativeToChangelogFile="true"
    path="../scripts/temp_script.sql"
	splitStatements="true"
    stripComments="true"/>
</changeSet>

temp_script.sql

start C:\scripts\temp_func.sql;

temp_func.sql

create or replace function temp_func
return date
aS
l_date date;
BEGIN
select sysdate into l_date from dual;
return l_date;
end;
/

But sill i am getting the same error:

Starting Liquibase at 11:59:11 (version 4.11.0 #2708 built at 2022-05-23 15:17+0000)
Liquibase Version: 4.11.0
Liquibase Community 4.11.0 by Liquibase

Running Changeset: changelog/change_001.xml::4::author

Unexpected error running Liquibase: ORA-00900: invalid SQL statement

For more information, please use the --log-level flag

4

Since / is a default terminator on Oracle but the sql standard is still ; you can change your splitstatements to false. Otherwise it is trying to end the sql statement on the first ; which would be l_date date;

5

Hi,

This is the new changeset.

<changeSet author="author" id="4" runAlways="true">
 <sqlFile dbms="oracle"
	relativeToChangelogFile="true"
    path="../scripts/temp_script.sql"
	splitStatements="false"
    stripComments="true"/>
</changeSet>

Tried executing, but sorry, it still doesnt work.

Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
    ... 72 more

Caused by: Error : 900, Position : 0, Sql = start C:\scripts\temp_func.sql;, OriginalSql = start C:\scripts\temp_func.sql;, Error Msg = ORA-00900: invalid SQL statement

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
    ... 85 more

Thank you for all the help.

6

Since your are executing PL/SQL, and using “/” as the end-delimiter in temp_func.sql, your changeset needs to set endDelimiter="/". Liquibase is using the default semicolon, which is breaking your PL/SQL into invalid statements, resulting in the ORA-00900.