Dynamically execute a stored procedure with reference cursor output

Question

I have a package like this:

CREATE OR REPLACE PACKAGE PKG_EXAMPLE
IS
  PROCEDURE SP_EXAMPLE_1 (inVal IN VARCHAR2, outCur OUT SYS_REFCURSOR);
  PROCEDURE SP_EXAMPLE_2 (inVal IN VARCHAR2, outCur OUT SYS_REFCURSOR);
  PROCEDURE SP_EXAMPLE_N (inVal IN VARCHAR2, outCur OUT SYS_REFCURSOR);
  PROCEDURE SP_NON_CURSOR_EXAMPLE (inVal IN VARCHAR2, outVal OUT VARCHAR2);
END PKG_EXAMPLE;

CREATE OR REPLACE PACKAGE BODY PKG_EXAMPLE
AS
  --for brevity, only showing one relevant procedure
  PROCEDURE SP_EXAMPLE_N (inVal IN VARCHAR2, outCur OUT SYS_REFCURSOR)
  IS
  BEGIN
    OPEN outCur FOR SELECT something FROM somewhere WHERE value = inVal;
  END SP_EXAMPLE_N;

END PKG_EXAMPLE;

At runtime I am looping through some records and I want to call procedures (only the ones with outCur OUT SYS_REFCURSOR cursor outputs) in this package dynamically. For demonstration purposes, I included SP_NON_CURSOR_EXAMPLE to show that I could call it like this:

DECLARE
  outVal  VARCHAR2(100);
BEGIN

  FOR rec IN (SELECT 'SP_NON_CURSOR_EXAMPLE' as spName, inData FROM table_name) LOOP

    EXECUTE IMMEDIATE 'CALL PKG_EXAMPLE.' || rec.spName || '(''' || rec.inData || ''', :myResult)'
    USING OUT outVal;

  END LOOP;

END;

This works. But how can I do this for the procedures that have outCur OUT SYS_REFCURSOR cursor outputs? I don't actually need the cursor results or care what they are, I just want to know whether the cursor returned > 0 results. I want something like this:

DECLARE
  crs  SYS_REFCURSOR;
  cnt  NUMBER;
BEGIN

  FOR rec IN (SELECT spName, inData FROM table_name) LOOP

    EXECUTE IMMEDIATE 'CALL PKG_EXAMPLE.' || rec.spName|| '(''' || rec.inData|| ''', :myResult)'
    USING OUT crs;

    cnt := crs%ROWCOUNT; -- this is what I need

  END LOOP;

END;

But the %ROWCOUNT is always zero. I believe that I have to FETCH the cursor before I can check whether it returned results, but I don't know what columns or types the cursor will return so I'm not sure what I can fetch into.

wweicker · Accepted Answer · 2016-01-22 19:57:02Z

2

Might not be the best approach, but I found a working solution...

DECLARE
  crs    SYS_REFCURSOR;
  crsID  NUMBER;
  cnt    NUMBER  :=  0;
BEGIN

  FOR rec IN (SELECT spName, inData FROM table_name) LOOP

    EXECUTE IMMEDIATE 'CALL PKG_EXAMPLE.' || rec.spName|| '(''' || rec.inData|| ''', :myResult)'
    USING OUT crs;

    crsID:= DBMS_SQL.TO_CURSOR_NUMBER(crs);
    cnt := 0;

    WHILE DBMS_SQL.FETCH_ROWS(crsID) > 0 
    LOOP
      cnt := cnt + 1;
    END LOOP;

  END LOOP;

END;

answered Jan 22, 2016 at 19:57

wweicker

4,9925 gold badges39 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

Dynamically execute a stored procedure with reference cursor output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related