0

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)
Community
  • 1
  • 1
Kuldeep Jain
  • 8,409
  • 8
  • 48
  • 73

4 Answers4

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.

RGO
  • 4,586
  • 3
  • 26
  • 40
  • 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` http://ibatis.apache.org/docs/java/dev/com/ibatis/common/jdbc/ScriptRunner.html – Kuldeep Jain Dec 20 '13 at 14:58
  • You can call `setDelimiter()` to define another separator which doesn't interfere with anything; e.g. `|`. – RGO Dec 20 '13 at 15:02
  • Tried this `executer.setDelimiter("|");` and separated sql statements by `|` but still same error :( – Kuldeep Jain Dec 20 '13 at 15:27
  • Much probably because inside the source file, you are still using `;` as the delimiter. – RGO Dec 20 '13 at 16:06
  • 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 Dec 22 '13 at 04:21
  • Well, this is how this SQLEXEC class works, which is slightly different from SQL Developer in terms of defining the statements' boundaries (SQL Developer is also Java based). So, if you need to use it, you have to adapt to its model. i.e. it needs you clearly define for it how to distinguish one statement from another. Is this achieved through changing all your scripts? probably! unless you are ready to do some form of pre-processing on the files' contents before feeding them into this class. – RGO Dec 23 '13 at 06:07
  • Thanks for your answer, I think I will have to go ahead with separating each statement with some `delimiter` as you said and remove the statement terminating `semicolon`. And in that case I can execute(using jdbc) each statement at a time by parsing the file myself instead of using SQLExec or any other external api/library. – Kuldeep Jain Dec 24 '13 at 08:31
  • Thanks for your input @Reza +1, your input helped me to reach the solution which I opted (I put it in my own answer). – Kuldeep Jain Dec 26 '13 at 14:26
1

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:

  1. Each SQL statement (not part of PL/SQL block) and PL/SQL block must end with a forwarded slash (/) in a new line.

  2. SQL statement (not part of PL/SQL blocks) should not end with semicolon (;). I just removed semicolon from the end of statements.

  3. 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.

Kuldeep Jain
  • 8,409
  • 8
  • 48
  • 73
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
/
Andrew Paes
  • 1,940
  • 1
  • 15
  • 20
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.

Katesclau
  • 192
  • 1
  • 8