SYS_REFCURSOR out parameter - running stored procedure fails

Question

I come from a SQL Server background but I'm struggling with this in Oracle. All of the sprocs in the database that's been configured expect a parameter passed in of type 'sys_refcursor'. In a nutshell, they do this (the sprocs are far more complex, but the whole cursor thing is what I'm getting at) :

CREATE OR REPLACE PROCEDURE xxx_API_TEST 
(
  TESTCURSOR OUT SYS_REFCURSOR 
) AS 
BEGIN
  OPEN CURS FOR
  SELECT * FROM SOMETABLE;
END xxx_API_TEST;

So - in Oracle SQL Developer I can run this no problem - and view the output of the cursor in the Output Variables window.

However, rather than have to keep reloading the dialog each time I want to retest it - I figured it'd be possible to copy the PL/SQL in the dialog to a new worksheet, and then just run that as I needed to - adjusting parameters to suit - so in this case, I'd run :

    DECLARE
      TESTCURSOR SYS_REFCURSOR;
    BEGIN

      xxx_API_TEST(
        TESTCURSOR => TESTCURSOR 
      );
      /* Legacy output: 
    DBMS_OUTPUT.PUT_LINE('TESTCURSOR = ' || TESTCURSOR );
    */ 
      :TESTCURSOR := TESTCURSOR; --<-- Cursor
    --rollback; 
    END;

Which is exactly the same as that shown in the Run dialog.

However, if I do this - it just throws an error - stating:

Error starting at line : 1 in command -
<snip>
Error report -
ORA-06550: line 11, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I've no idea how to go about diagnosing this one. The fact it runs fine from the dialog but not the worksheet is puzzling - and inspecting what was sent to the DB I can't see any differences in the SQL it actually used.

Interestingly if I run this code from the worksheet - it pops up a dialog to ask for the value for TESTCURSOR - which the 'Run' dialog doesn't do. I just press OK - but I've tried both ticking and unticking the 'Null' checkbox to no avail.

Any help very much appreciated. It's Oracle 12c. If you need any more info just let me know.

Cheers, Tony

Update: 20/7/2016

I ended up pulling the values into variables from a fetch, and just dumping to dbms_output - which gives me what I needed to repeatedly run the same sproc. e.g.

loop 
    fetch testcursor into Res, ActDate<snip>;  

    exit when testcursor%notfound;  

    DBMS_OUTPUT.PUT_LINE(Res || ' | ' || ActDate etc etc);  

  end loop;  
  close testcursor;

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

The Run dialog is generated from the procedure spec and therefore knows the correct type for testcursor, but when you just paste it into a worksheet it doesn't, and it seems to define it as a text string. As of SQL Developer 4.0.3.16 the worksheet bind value prompt doesn't seem to have a way to change the datatype.

As for how to create a reusable test script with a cursor output in SQL Dev, I don't know. (I normally use PL/SQL Developer which can do this, but it's not free software.)

Edit: in this thread a solution was to write a wrapper function and select it from dual. I've just tried and you don't get a grid, but you get a kind of debug output without too much effort:

Oracle SQL Developer: Show REFCURSOR Results in Grid?

edited May 23 '17 at 11:52

Community

1
1

answered Jul 19 '16 at 17:24

William Robertson

15,273
4
38
44

Thanks William - at least I'm not going mad. Ended up just running it as above, but fetching into variables for each column e.g. `loop fetch testcursor into Res, ActDate; exit when testcursor%notfound; DBMS_OUTPUT.PUT_LINE(Res || ' | ' || ActDate etc etc); end loop; close testcursor; ` – PoorbandTony Jul 20 '16 at 08:49
I've updated my answer with a link to an earlier question that might help. The suggestion there was to wrap the procedure in a function and select it from dual. – William Robertson Jul 20 '16 at 09:17

SYS_REFCURSOR out parameter - running stored procedure fails

1 Answers1