Execute anonymous pl/sql block and get resultset in java

Question

I would like to execute the anonymous PL/SQL and need to get the resultset object. I got the code which can be done by using cursors inside the PL/SQL block.

But the PL/SQL block itself will come from the database as text. So I can't edit that PL/SQL block. And it will return only two values whose column names will be same always. It will return list of 2 column combination values.

Here I am giving sample PL/SQL.

BEGIN  RETURN 'select distinct fundname d, fundname r from <table> where condition order by 1';  EXCEPTION    WHEN OTHERS THEN     RETURN 'SELECT ''Not Available'' d, ''Not Available'' r FROM dual'; END; 

Any reply will be so helpful.

Answer 1

Here is a self contained example of how to "execute the anonymous PL/SQL and get the resultset object"

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Types;  import oracle.jdbc.OracleTypes;  public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameterAndOneOutputCursorParameter {      public static void main(String[] args) throws Exception {          DriverManager.registerDriver(new oracle.jdbc.OracleDriver());          // Warning: this is a simple example program : In a long running application,         // error handlers MUST clean up connections statements and result sets.          final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");         String plsql = "" +         " declare " +           "    p_id varchar2(20) := null; " +         "    l_rc sys_refcursor;" +         " begin " +         "    p_id := ?; " +         "    ? := 'input parameter was = ' || p_id;" +         "    open l_rc for " +         "        select 1 id, 'hello' name from dual " +         "        union " +         "        select 2, 'peter' from dual; " +         "    ? := l_rc;" +         " end;";          CallableStatement cs = c.prepareCall(plsql);         cs.setString(1, "12345");         cs.registerOutParameter(2, Types.VARCHAR);         cs.registerOutParameter(3, OracleTypes.CURSOR);          cs.execute();          System.out.println("Result = " + cs.getObject(2));          ResultSet cursorResultSet = (ResultSet) cs.getObject(3);         while (cursorResultSet.next ())         {             System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));         }          cs.close();         c.close();     } } 

The above example query "select 1 id, 'hello' name from dual union select 2, 'peter' from dual;" can be replaced by any query.