12

If I have a function which returns a reference cursor for a query, how can I view the result set of this in SQL Developer? Toad has a special tab for viewing the results of a reference cursor, this is the functionality I would like to find.

Mark Harrison
  • 297,451
  • 125
  • 333
  • 465

4 Answers4

8
SET SERVEROUTPUT ON;
VARIABLE X REFCURSOR;
EXEC PROCEDURE_WITH_OUTPUT_SYS_REFCURSOR(:X);
PRINT X;
quillbreaker
  • 6,119
  • 3
  • 29
  • 47
  • Do you know how to set a variable to pass in as a parameter. (in conjunction with the cursor. In my code here...I'm trying to create and set a variable for "pin_customer_last_name_string". /* */ var v_customer_cursor refcursor; /* */ exec MYSCHEMA01.MYPACKAGE01.MYPROCEDURE01( pin_customer_last_name_string => 'SMITH' , pout_customer_cursor => :v_customer_cursor ); /* */ print v_customer_cursor; /* */ – granadaCoder Aug 10 '12 at 14:27
  • I can't even look into that... my work doesn't put me in front of an Oracle DB these days. – quillbreaker Oct 18 '13 at 19:28
7

Double click the cursor fields in your result record. On the right side there is a "..." icon. Click this and you'll see the contents

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
7

Hi I know this was asked a while ago but I've just figured this out and it might help someone else. Not sure if this is exactly what you're looking for but this is how I call a stored proc and view the output in SQL Developer.
In SQL Developer when viewing the proc, right click and choose 'Run' or select Ctrl+F11 to bring up the Run PL/SQL window. This creates a template with the input and output params which you need to modify. To return the results of a sys_refcursor you then need to declare a row type that is exactly equivalent to the select stmt / sys_refcursor being returned by the proc. Below I declare "type t_row" which matches my output fields, then loop through the returned sys_refcursor. If t_row matches my sys_refcursor then it gets populated with each row of the sys_refcursor:

DECLARE
  P_CAE_SEC_ID_N NUMBER;
  P_FM_SEC_CODE_C VARCHAR2(200);
  P_PAGE_INDEX NUMBER;
  P_PAGE_SIZE NUMBER;
  v_Return sys_refcursor;
  type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),rownum number, v_total_count number);
  v_rec t_row;

BEGIN
  P_CAE_SEC_ID_N := NULL;
  P_FM_SEC_CODE_C := NULL;
  P_PAGE_INDEX := 0;
  P_PAGE_SIZE := 25;

  CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
    P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
    P_FM_SEC_CODE_C => P_FM_SEC_CODE_C,
    P_PAGE_INDEX => P_PAGE_INDEX,
    P_PAGE_SIZE => P_PAGE_SIZE,
    P_FOF_SEC_REFCUR => v_Return
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = ');
  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('sec_id = ' || v_rec.CAE_SEC_ID || 'sec code = ' ||v_rec.FM_SEC_CODE);
  end loop;

END;
Ciarán Bruen
  • 5,221
  • 13
  • 59
  • 69
1

there are no way to display a refcursor in datagrid in sqldeveloper. we can define a refcursor,call SP,then print refcursor,then data will be printed in Script output window in a plane text mode,but not in Query Result window.

user982785
  • 19
  • 2