CODEWITHSUNDEEP
javaoracle-databasejdbc
chubbsondubs
chubbsondubs

Reputation: 38706

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?

Upvotes: 3

Views: 2159

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220877

Since you cannot have any bind variables in Oracle DDL anyway, why use a PreparedStatement? You can use a static statement instead and shouldn't run into this problem:

try (Statement s = connection.createStatement()) {
    s.executeUpdate(sql);
}

Upvotes: 1

APC
APC

Reputation: 146239

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

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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

Upvotes: 0

Related Questions