Execute PL/SQL block in Oracle 11g and Process the cursor in Java client

Question

We are trying to implement a solution that requires us to,

Poll a control table in frequent intervals to query for unprocessed transactions
Once we pull the transaction identifiers from Step 1 we have to query for details. This step is existing functionality, only that we do a full scan by joining the with the control table in step one.

The existing solution is starting to slow down the processing when there is a volume. So we decided to pull transactions that are not processed, from the status table and do a PK look up for querying the details.

I understand that this is not an ideal solution like Adapters,CDC or having the full details expressed in a view. We are restricted due to product contract that forbids us from creating any kind of objects on the source schema or anywhere in the source Oracle instance that leaves us polling the tables and this solution is required to be scallable and near real time(5 seconds delay).

DDL:

 CREATE TABLE "CONTROL_TABLE"
(   
"REF_NO" VARCHAR2(16 CHAR), 
"BRANCH" VARCHAR2(3 CHAR),
"INIT_DATE" DATE, 
"STATUS" VARCHAR2(1 CHAR), 
 CONSTRAINT "CONST_CONTROL_TABLE" PRIMARY KEY ("REF_NO")
)

Following is what I have tried as a POC, executing the below block,
1. Created a block to run a SELECT for UPDATE as follows. In the below block I am selecting for update with "SKIP LOCKED" and update the records in the cursor scope.

 DECLARE
   CURSOR tCURSOR IS
     SELECT REF_NO FROM CONTROL_TABLE 
        WHERE STATUS = 'U' FOR UPDATE OF STATUS SKIP LOCKED;
 BEGIN
  FOR tCURSORREC IN tCURSOR LOOP
    UPDATE CONTROL_TABLE SET STATUS='W' WHERE STATUS='U';    
  END LOOP; 
  COMMIT;  
 END;

The above block works just fine and I have tested by randomly inserting new records and updating status from different client sessions. My issue is I would like to get the cursor returned to the Java client for downstream processing. Referred to an earlier post where just a SELECT query returns a cursor bind var to Java. Any helpful pointer is greatly appreciated. Execute anonymous pl/sql block and get resultset in java

Java snippet that runs the block,

    public static void main(String args[]) throws Exception
    {
       final Connection c = DriverManager.getConnection("jdbc:oracle:thin:<<service_name>>:8888:<<schema>>", "user", "passwd");

     String plsql = "declare\r\n" + 
            "  cursor tCursor is\r\n" + 
            "    select ref_no from CONTROL_TABLE \r\n" + 
            "        where status = 'U' for update of REF_NO,STATUS skip locked;\r\n" + 
            "begin\r\n" +        
            "  for tCursorRec in tCursor loop\r\n" + 
            "    update CONTROL_TABLE set status='W' where status ='U';    \r\n" + 
            "  end loop;\r\n" +             
            "  commit;  \r\n" + 
            "? := tCursor" +
            "end;";     

            CallableStatement cs = c.prepareCall(plsql);
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            cs.execute();          

            ResultSet cursorResultSet = (ResultSet) cs.getObject(1);
            while (cursorResultSet.next ())
            {
               System.out.println (cursorResultSet.getString(1));
            } 
                cs.close();
                c.close();

   Exception:Exception in thread "main" java.sql.SQLException: ORA-06550: 
   line 10, column 8:
   PLS-00382: expression is of wrong type
   ORA-06550: line 10, column 1:

A PL/SQL explicit cursor isn't the same thing as a ref cursor, which is what the Java side is looking for. But you have a deeper problem - aside from you updating all 'U' rows in the table each time round the loop, whether you locked them or not - in that the cursor has been exhausted by the time you try to assign it; and you can't reopen it because you've changed the data so it won't find anything. Are you sure you want to update in the loop, and commit (freeing the locks), separately from doing further processing? Either way, you want to return the list of ref numbers to Java, right? — Alex Poole
– Alex Poole, Commented Dec 12, 2018 at 17:56
Alex, To get the REF_NO in Java is my goal. Also another thread should not pull the same ref numbers. Thats the reason to SELECT FOR ... UPDATE. Thanks.. — MAYBEMEDIC
– MAYBEMEDIC, Commented Dec 12, 2018 at 18:18

Alex Poole · Accepted Answer · 2018-12-12 18:57:56Z

0

Using a ref cursor is problematic because you've exhausted the existing PL/SQL cursor (which isn't the same type as a ref cursor anyway, hence the error), and you can't requery as you've already updated and commited the changes. Really the commit should probably be done from the Java side anyway, but that's a separate issue.

You could use a collection instead, e.g. using a built-in varray type:

    Connection c = ods.getConnection();

    String plsql = "declare\r\n" +
        "  cursor tCursor is\r\n" +
        "    select ref_no from CONTROL_TABLE\r\n" +
        "        where status = 'U' for update of STATUS skip locked;\r\n" +
        "  array SYS.ODCIVARCHAR2LIST := new SYS.ODCIVARCHAR2LIST();\r\n" +
        "begin\r\n" +
        "  for tCursorRec in tCursor loop\r\n" +
        "    update CONTROL_TABLE set status='W' where current of tCURSOR;\r\n" +
        "    array.extend();\r\n" +
        "    array(array.count) := tCURSORREC.REF_NO;\r\n" +
        "  end loop;\r\n" +
        "  commit;\r\n" +
        "  ? := array;\r\n" +
        "end;";

    CallableStatement cs = c.prepareCall(plsql);
    cs.registerOutParameter(1, java.sql.Types.ARRAY, "SYS.ODCIVARCHAR2LIST");
    cs.execute();

    String[] refNos = (String[]) cs.getArray(1).getArray();

    cs.close();
    c.close();

    for (String refNo : refNos)
    {
        // Whatever processing you want to do
        System.out.println(refNo);
    }

The generated PL/SQL block ends up as:

declare
  cursor tCursor is
    select ref_no from CONTROL_TABLE
        where status = 'U' for update of STATUS skip locked;
  array SYS.ODCIVARCHAR2LIST := new SYS.ODCIVARCHAR2LIST();
begin
  for tCursorRec in tCursor loop
    update CONTROL_TABLE set status='W' where current of tCURSOR;
    array.extend();
    array(array.count) := tCURSORREC.REF_NO;
  end loop;
  commit;
  ? := array;
end;

Inside the cursor loop, the array collection is extended (so it has an empty element at the end), and the ref_no for that cursor row is added to the array in that position.

I've also changed the update to only apply to one row at a time, using where current of - which targets the row locked by for update. In your original version you were doing an unrestricted update of all rows with 'U'; not just those that you've locked, and as they're all updated the first timr round, subsequent iterations of the loop didn't have anything left to update.

On the Java side the bind variable is an array too, and that can be cast to a local array of the appropriate type.

You might be able to avoid the explicit cursor with something like:

begin
  update CONTROL_TABLE set status='W' where status = 'U'
  returning REF_NO
  bulk collect into ?;
  commit;
end;
/

edited Dec 12, 2018 at 18:57

answered Dec 12, 2018 at 18:23

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MAYBEMEDIC Over a year ago

Alex, Thanks very much. Tested working, but to be honest, I am trying to wrap my head around this.. Will try to pull the commit out of the block.

Alex Poole Over a year ago

@MAYBEMEDIC - I've added a bit more explanation; but also simplified the Java assignment and processing a bit.

MAYBEMEDIC Over a year ago

Alex, Thanks much for the extended updates. I am experimenting with the solution.

Collectives™ on Stack Overflow

Execute PL/SQL block in Oracle 11g and Process the cursor in Java client

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related