CODEWITHSUNDEEP
javaoracle-databaseplsqlspring-jdbc
Bopsi
Bopsi

Reputation: 2436

Running a PL/SQL block using Java

Say I have following procedure - 

DECLARE
   enqueue_options     dbms_aq.enqueue_options_t;
   message_properties  dbms_aq.message_properties_t;
   message_handle      RAW(16);
   message             custom_payload_type;

BEGIN
   message := custom_payload_type('Subject','Body');

   dbms_aq.enqueue(queue_name => 'my_demo_queue',           
         enqueue_options      => enqueue_options,       
         message_properties   => message_properties,     
         payload              => message,               
         msgid                => message_handle);

   COMMIT;
END;

Using spring jdbc I already created a datasource> I Dont have this procedure stored in data base. These procedure hardcoded in java side.

How to run this SQL block using java?

Upvotes: 3

Views: 3346

Answers (2)

Alin Pandichi
Alin Pandichi

Reputation: 955

If you only have the stored procedure in your Java code as a String, you need to create the stored procedure with Connection.createStatement() and then execute it with something like prepareCall("{call your_procedure_name()}") and execute.

See Creating Stored Procedures in Java DB with SQL Scripts or JDBC API for more detailed steps. Or this page for the Oracle equivalent.

Rough code steps are below. For brevity, I ommited try catch blocks, setting input parameters for the call statement, registering output parameters, etc. Like I said, the full answer can be obtained by extracting relevant paragraphs from the documentation links I posted above.

Connection conn = dataSource.getConnection();
String createProcedure = "create procedure enque_message(" + ... ;
Statement stmt = conn.createStatement();
stmt.executeUpdate(createProcedure);

CallableStatement cStmt = conn.prepareCall("{call enque_message()}");
boolean hadResults = cStmt.execute();
while (hadResults) {
    ResultSet rs = cStmt.getResultSet();
    // process result set
}

Upvotes: 0

user330315
user330315

Reputation:

Use a CallableStatement: 

String plsql = "..... your code in here ...";
Connection conn = dataSource.getConnection();
CallableStatement cstmt = conn.prepareCall(plsql);
cstmt.execute();

Upvotes: 4

Related Questions