creating trigger in java app

Question

I can run the following statement in SQL Plus

CREATE OR REPLACE TRIGGER TEST.RECORD_TABLE.BIR
BEFORE INSERT ON TEST.CURRENT_VACANCIES
FOR EACH ROW WHEN (new.VACANCY_ID IS NULL)
BEGIN 
   SELECT TEST.CURRENT_VACANCIES_SEQ.NEXTVAL INTO :new.VACANCY_ID FROM dual; 
END;

and it woks perfectly.

However, when I try to call it from a prepared statement in a Java App I get the following error

SQL Exception;java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Has anybody got any ideas?

Thanks

You're trying to create the trigger from within the Java application? Why would you want to do that? Without seeing your code, I would guess that it's interpreting `:new` as a bind variable, which seems odd as those are usually `?` in a prepared statement. — Alex Poole, Jun 21 '12 at 10:28

score 5 · Answer 1 · edited May 23 '17 at 12:15

Do not use the PreparedStatement interface to create a trigger that refers to a:NEW or :OLD column. Use Statement instead. Using PreparedStatement will cause execution to fail with the message java.sql.SQLException: Missing IN or OUT parameter at index:: 1.

see: http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#CHDIIDBE

use:

session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        //connection, finally!
    }
});

from: How to get jdbc connection from hibernate session?

creating trigger in java app

1 Answers1