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
– Alex Poole, Commented Jun 21, 2012 at 10:28

Community · Accepted Answer · 2017-05-23 12:15:47Z

5

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?

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Aug 24, 2012 at 6:31

monha

511 silver badge3 bronze badges

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

Collectives™ on Stack Overflow

creating trigger in java app

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related