0

The below sql script executes successfully on SQL Developer but fails when trying to execute the script using Java class org.apache.tools.ant.taskdefs.SQLExec.execute() 

CREATE OR REPLACE TRIGGER <SCHEMA_NAME>."TR$BIR_TINFO" BEFORE INSERT
ON <SCHEMA_NAME>.TINFO
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
SELECT <SCHEMA_NAME>.SEQ_TINFO_CID.NEXTVAL
INTO :NEW.CID
FROM DUAL;
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')
INTO :NEW.CINSERT_TIME
FROM DUAL;
END;
/
ALTER TRIGGER <SCHEMA_NAME>."TR$BIR_TINFO" ENABLE;


The exception encountered is 
Caused by: java.sql.SQLException: ORA-01008: Not all variables are bound
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:766)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1718)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1678)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:332)
at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:799)
at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:769)
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:704)

Oracle driver used is oracle.jdbc.driver.OracleDriver.

Any suggestions on how to resolve this issue ?

Manasa
  • 72
  • 10
  • Usually it is a bad design if you store TIMESTAMP values as string. You could simply execute `:NEW.CID := .SEQ_TINFO_CID.NEXTVAL;` and `:NEW.CINSERT_TIME := CURRENT_TIMESTAMP;` – Wernfried Domscheit May 21 '19 at 19:34
  • Not sure if you have comments in the code that have been removed before posting but if you do have a look at https://stackoverflow.com/questions/7493028/ora-01008-not-all-variables-bound-they-are-bound. It is about .NET but one comment says they have seen it in JAVA as well. – Shaun Peterson May 21 '19 at 22:27
  • There are no comments in the code. But i have observed that there is some issue with the trigger code. When i execute the same code in SQL Developer and look into this table select * from all_triggers where trigger_name = 'TR$BIR_TINFO '. Then the TRIGGER_BODY column has the whole code from BEGIN - END. But when i execute through Java, it stores upto ONLY SELECT .SEQ_TINFO_CID.NEXTVAL INTO :NEW.CID FROM DUAL – Manasa May 22 '19 at 08:34
  • @Wernfried i have tried your suggestion. But now it throws Invalid SQL type: sqlKind = UNINITIALIZED error is shown – Manasa May 22 '19 at 08:38
  • Which statement do you execute? You cannot execute a trigger code manually. Most likely the trigger is not the problem. – Wernfried Domscheit May 22 '19 at 08:42
  • It is a huge sql script which has sequence and table creation followed by the trigger mentioned previously. So when i execute the sql script, upto the trigger execution all have been created successfully. It throws exception exactly at the trigger code. – Manasa May 22 '19 at 08:55
  • I have modified the trigger code as follows CREATE OR REPLACE TRIGGER ."TR$BIR_TINFO" BEFORE INSERT ON .TINFO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN :NEW.CID := .SEQ_TINFO_CID.NEXTVAL; :NEW.CINSERT_TIME := CURRENT_TIMESTAMP; END; / ALTER TRIGGER ."TR$BIR_TINFO" ENABLE; – Manasa May 22 '19 at 08:55

0 Answers0