Running Oracle sql script from java gives SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Question

I am using Oracle 11g, I am executing Oracle sql script through java code. My SQL script may contain SQL statements(DDL or DML) or PL/SQL blocks, so I don't want to parse the script in my java code but used This solution to execute complete script at once. Following is the sample code, where SQLExec class is in ant jar.

This solution worked for most cases except that if sql script contains create or replace trigger it fails with java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement. I have also specified snippet of sql script which fails.

Please note that if I run same script through SQL Developer, it runs fine.

Following is the Java code:

private void executeSql(String sqlFilePath) {
    final class SqlExecuter extends SQLExec {
        public SqlExecuter() {
            Project project = new Project();
            project.init();
            setProject(project);
            setTaskType("sql");
            setTaskName("sql");
        }
    }

    SqlExecuter executer = new SqlExecuter();
    executer.setSrc(new File(sqlFilePath));
    executer.setDriver(args.getDriver());
    executer.setPassword(args.getPwd());
    executer.setUserid(args.getUser());
    executer.setUrl(args.getUrl());
    executer.execute();
}

SQL Script snippet:

......
......
CREATE OR REPLACE TRIGGER MY_TRG
   BEFORE INSERT ON MY_TABLE
   FOR EACH ROW
   BEGIN
    :NEW.MYNUMBER := MY_SEQUENCENUM.NEXTVAL;
   END;

Following is the Exception trace:

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:398)
    at com.kuldeep.OracleConnectionTest.executeSql(OracleConnectionTest.java:160)
    at com.kuldeep.OracleConnectionTest.main(OracleConnectionTest.java:25)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
    at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:499)
    at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:470)
    at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction(SQLExec.java:664)
    at org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000(SQLExec.java:627)
    at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:370)

RGO · Accepted Answer · 2013-12-26 14:21:54Z

1

In the documentation it says:

Multiple statements can be provided, separated by semicolons (or the defined delimiter).

Therefore, using the semicolon character (;) as the default delimiter, SQLEXEC interprets the CREATE TRIGGER statement of your script as two statements, giving this error message as the result.

edited Dec 26, 2013 at 14:21

answered Dec 20, 2013 at 14:51

RGO

4,7774 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kuldeep Jain Over a year ago

Ok, the semicolon in that statement is valid, but the SQLExec api is treating it as statement separator, so does that mean I can not use SQLExec here? I am facing almost same issue in other approaches too like ScriptRunner ibatis.apache.org/docs/java/dev/com/ibatis/common/jdbc/…

RGO Over a year ago

You can call setDelimiter() to define another separator which doesn't interfere with anything; e.g. |.

Kuldeep Jain Over a year ago

Tried this executer.setDelimiter("|"); and separated sql statements by | but still same error :(

RGO Over a year ago

Much probably because inside the source file, you are still using ; as the delimiter.

Kuldeep Jain Over a year ago

I think I can not remove the semicolons, because they are valid (and probably required), as it executes from SQL Developer. Does this means all SQL scripts I want to run has to be modified? If that is the case, what's the point in running them through java code, someone can just run them through SQL Developer/ SQL Plus. :(

|

Kuldeep Jain · Accepted Answer · 2013-12-26 14:25:45Z

As @Reza Goodarzi mentioned the cause of invalid SQL statement is semicolon being used as the statement separator. So to solve my issue I am separating each statement with slash(/) as delimiter and followed these rules which I created myself:

Each SQL statement (not part of PL/SQL block) and PL/SQL block must end with a forwarded slash (/) in a new line.
SQL statement (not part of PL/SQL blocks) should not end with semicolon (;). I just removed semicolon from the end of statements.
For PL/SQL block do not remove the semicolon(;) from end of the block as well as from any statement contained within the block.

And by making these changes in my SQL Scripts I executed (using jdbc) each PL/SQL block and each SQL statement (not part of PL/SQL block) at a time by parsing the file myself instead of using SQLExec or any other external api/library.

Andrew Paes · Accepted Answer · 2013-12-20 15:07:17Z

0

I think you need to change your trigger to set your new ID

create or replace trigger MY_TRG
BEFORE insert MY_TABLE
for each row
begin
  if (:new.MYNUMBER  is null) then
    select MY_SEQUENCENUM.nextval
    into :new.MYNUMBER 
    from DUAL;
  end if;
end;
/

or this:

create or replace trigger TG_BIU_TABLE1
before insert or update on TABLE1
for each row
begin
    if (:new.ID1 is null) then
        select SQ_TABLE1.nextval
        into :new.ID1
        from DUAL
    end if
end
/

edited Dec 20, 2013 at 15:07

answered Dec 20, 2013 at 14:35

Andrew Paes

2,0221 gold badge16 silver badges23 bronze badges

6 Comments

Kuldeep Jain Over a year ago

does it mean my Trigger creation is incorrect? but it does work from SQL Developer. why so ?

Andrew Paes Over a year ago

What is your Oracle version?

Kuldeep Jain Over a year ago

My Oracle version is 11.2.0.3.0

Kuldeep Jain Over a year ago

I tried changing the trigger as you mentioned, but still getting same exception.

Andrew Paes Over a year ago

please execute this-> select object_name from dba_objects where object_type = 'TRIGGER' and status = 'INVALID';

|

Katesclau · Accepted Answer · 2014-07-10 18:22:52Z

0

You can also add a delimiter in the execute statement, as so:

......
......
DELIMITER $$ 

CREATE OR REPLACE TRIGGER MY_TRG
   BEFORE INSERT ON MY_TABLE
   FOR EACH ROW
   BEGIN
    :NEW.MYNUMBER := MY_SEQUENCENUM.NEXTVAL;
   END; $$
......

I also left the final part of the script just for the triggers and procedures, as the delimiter is used onward. That did the trick for me. Courtesy of SQL Developer´s Migration tool.

answered Jul 10, 2014 at 18:22

Katesclau

3222 silver badges8 bronze badges

Collectives™ on Stack Overflow

Running Oracle sql script from java gives SQLSyntaxErrorException: ORA-00900: invalid SQL statement

4 Answers 4

8 Comments

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related