How to execute this Oracle statement on JDBC

问题:

I have trouble executing the following using a JDBC prepared statement:

CREATE OR REPLACE TRIGGER Time_trg BEFORE INSERT ON Time FOR EACH ROW 
BEGIN 
  SELECT Time_seq.NEXTVAL INTO :new.id FROM dual; 
END;

The code:

try {
   PreparedStatement statement = connection.prepareStatement( sql );
   preparedStatement.executeUpdate();
} finally {
   statement.close();
}

I'm getting this error:

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

I'm working on a database agnostic solution so I need something that is portable. So what is oracle's problem?

回答1:

Use oracle.jdbc.OraclePreparedStatement

OraclePreparedStatement statement = (OraclePreparedStatement)connection.prepareStatement( sql );

As this is much specific to Oracle, regular PrepareStatement doesn't help. Oracle provides a wrapper for the same, with additional functionalities as well.

Similarly, Oracle provides OracleCallableStatement similar to CallableStatement

WorkAround: (When PreparedStatement has to be used - Risk of being misused

 CREATE PROCEDURE EXECUTE_MY_DDL(INSTRING VARCHAR2)
 AS
 BEGIN
    EXECUTE IMMEDIATE INSTRING;
 END;

Reference JavaDoc

回答2:

There is no need to write our own stored procedure to do this. Oracle provides a built-in stored procedure we can use: DBMS_UTILITY.EXEC_DDL_STATEMENT:

 DBMS_UTILITY.EXEC_DDL_STATEMENT('create table t1 (id number)');

In fact this is safer than the workaround procedure suggested in the accepted answer as it doesn't allow the execution of DML and so is protected against SQL injection