Missing IN or OUT parameter at index:: 1

Question

I am trying to execute a query in java code, the query is:

String triggerQuery = 
   "CREATE OR REPLACE TRIGGER global_archive_01
    AFTER INSERT ON archive_01 FOR EACH ROW
    BEGIN
    INSERT INTO archive 
    values (:NEW.id_01 , :NEW.id_02 , :NEW.id_03 , 'test' , :NEW.id_05);
    END;"

Query query = session.createSQLQuery(triggerQuery);
query.executeUpdate();

When I try to execute this query in SQL Developer it works fine, but in jdbc it is throwing an exception.

Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Database:  Oracle 10g
Hibernate: 3.0

Answer 1

It turns out that when you pass a query containing : the hibernate will translate it to a parameter in the query, so the solution was to execute the query on simple java statement.

Connection conn = session.connection();
Statement state = conn.createStatement();
state.execute(triggerQuery);
state.close();