SQL*Plus and SQL Developer

Question

I have just a simple question regarding executing a pl/sql script inside SQL*PLUS:

create or replace trigger CICD.S_ACCNT_POSTN_DELETE
   after delete on CICD.S_ACCNT_POSTN    
   FOR EACH ROW 
BEGIN
    INSERT INTO CICD.CX_PROCCHLDDEL (ROW_ID, OBJECT_TYPE, PARENT_ID,
          CHILD_ID,STATUS,CREATED,CREATED_BY,LAST_UPD,LAST_UPD_BY)
    VALUES (:old.ROW_ID,'AccountTeam', :old.OU_EXT_ID, :old.POSITION_ID
          ,'Not Synchronized', SYSDATE,:old.CREATED_BY
          ,SYSDATE,:old.LAST_UPD_BY);
END;

This script generates me the following error:

E- ORA-00900: invalid SQL statement

E- oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
E- oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
E- oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
E- oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
E- oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
E- oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
E- oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
E- oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
E- oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
E- oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
E- oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
E- oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
E- com.uc4.ara.feature.database.SQLFeature.executeSQLScript(SQLFeature.java:164)
E- com.uc4.ara.feature.database.SQLFeature.run(SQLFeature.java:53)
E- com.uc4.ara.feature.AraFileCmd.main(AraFileCmd.java:120)

I have also tried executing the script with DECLARE in the top. When doing this i get the following error:

 E- ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge

E- oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
E- oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
E- oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
E- oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
E- oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
E- oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
E- oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
E- oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
E- oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
E- oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
E- oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
E- oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
E- com.uc4.ara.feature.database.SQLFeature.executeSQLScript(SQLFeature.java:164)
E- com.uc4.ara.feature.database.SQLFeature.run(SQLFeature.java:53)
E- com.uc4.ara.feature.AraFileCmd.main(AraFileCmd.java:120)

The funny thing is when I paste either one of the two scripts in SQL Developer it executes the script perfectly fine. This is what confuses me.

Now I want to know if I somehow can transform these scripts into a valid PL/SQL script which can be executed in SQL*PLUS or is there another way ?

p.s. running Sun Solaris.

SQLPLUS: SQLPlus: Release 11.2.0.2.0

Thanks a lot.

SQL*Plus does not throw a Java error stack. So you must be using some other tool. Are you running the script using a build tool (Ant? Maven? Hand-rolled Groovy?)? — APC, Sep 02 '15 at 06:38
I think this comes from the previous statement that was not terminated with a `/` on its own line. See `line 2`: your CREATE keyword is at line 1. — Benoit, Sep 02 '15 at 06:38
If i use BEGIN on top ill get the second error message, if I use DECLARE (whcih is very onlogical to me) in the top instead of BEGIN I am just getting an E- ORA-00900: invalid SQL statement. Also if i put nothing in there im getting the invalid SQL statement. Yes i am running an automation tool to execute this command. — nassah, Sep 02 '15 at 06:51

APC · Accepted Answer · 2015-09-02T06:55:09.383

"The funny thing is when I paste either one of the two scripts in SQL Developer it executes the script perfectly fine."

You have shown only one script. However the one you have posted lacks a trailing /. SQLPlus uses a slash to indicate the end of an PL/SQL executable statement. So, assuming this is not just a posting error, you have two scripts without slashes at the end of the PL/SQL statement. This means the SQLPlus engine can't separate them, and that could cause SQL Plus to hurl an ora-00900 exception.

Of course if you were running these scripts manually you would see SQL Plus hang, waiting for you to type in the terminating slash. I presume the Java error stack means you're running the scripts through some form of build runner.

IDEs such as SQL Developer will run an individual script without a trailing slash because they handle it; by highlighting some code and pressing [F8] they can figure out the statement and simulate the trailing slash.

Thank you also for your reply, I did put a slash in the end and also tried to put one before BEGIN (the second part of the script) none of these are working. I need to somehow maybe refactor the codes to be valid for SQL*Plus..... — nassah, Sep 02 '15 at 07:29

SQL*Plus and SQL Developer

1 Answers1