Running a PL/SQL block using Java

Question

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?

Answer 1

Use a CallableStatement:

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