Print the ref cursor results in a grid when running oracle stored procedures

Answered

When I run oracle store procedure, the out ref cursor results are not showing. 

But the same thing is showing in sql developer and pl/sql developer. 

Is there any option in datagrip? 

I don't think datagrip features are not upto the market standard :(

2 comments
Comment actions Permalink

Sorry, could you please share a screenshot of what you mean by ref cursor from PL/SQL developer?

0
Comment actions Permalink

Most of my efforts in the db are spent creating procedures whose sole purpose is to provide data to a report which means the procedure returns an SYS_REFCURSOR.

In DG when you select to run a procedure it runs but for those that return an SYS_REFCURSOR you don't see the output.  The expectation is that the REF_CURSOR is captured and the contents are displayed in a grid or at least you offer that ability to the end users when they choose to run the procedure.

create or replace PROCEDURE SP_RPT_SALES (
    P_YEAR       IN NUMBER,
    P_REF_CURSOR IN OUT SYS_REFCURSOR
)
AS

BEGIN
    OPEN P_REF_CURSOR FOR
      SELECT A.FIRST_NAME, A.LAST_NAME, A.SALES, A.YEAR
      FROM (
         select 'JANE' AS FIRST_NAME, 'DOE'  AS LAST_NAME, 105999 AS SALES, 2023 AS YEAR FROM DUAL
         UNION ALL
         select 'JOHN', 'DOE', 13503, 2023 FROM DUAL
         UNION ALL
         select 'Fonzie', 'Liley', 29524, 2022 FROM DUAL
         UNION ALL
         select 'Nanni', 'McKew', 174050, 2022 FROM DUAL
      ) A
      WHERE A.YEAR = COALESCE(P_YEAR,EXTRACT(YEAR FROM SYSDATE));

END SP_RPT_SALES;

When executed in Oracle SQL Developer for 2023 you get the results below:

When you select to run in that tool and others the following anon block is executed:

DECLARE
  P_YEAR NUMBER;
  P_REF_CURSOR SYS_REFCURSOR;
BEGIN
  P_YEAR := 2023;
  P_REF_CURSOR := NULL;

  SP_RPT_SALES(
    P_YEAR => P_YEAR,
    P_REF_CURSOR => P_REF_CURSOR
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('P_REF_CURSOR = ' || P_REF_CURSOR);
*/ 
  :P_REF_CURSOR := P_REF_CURSOR; --<-- Cursor
--rollback; 
END;

 

You can vote on this issue:

DBE-6807 Database console should show results from PL/SQL functions (Oracle) that return cursors

 

0

Please sign in to leave a comment.