CODEWITHSUNDEEP
oracle-databaseplsqltoad
ns102030
ns102030

Reputation: 1

How to get the output of a PL/SQL stored procedure to display in the Data Grid in Toad for Oracle?

My goal is to create a PL/SQL package that contains a stored procedure and output the results into the Data Grid in Toad for Oracle. The stored procedure takes in two parameters: new_entity and new_date. The stored procedure should select and output data from a table based on these parameters.

CREATE OR REPLACE PACKAGE test1 AS
  -- Declare the stored procedures
  PROCEDURE get_date_range(new_date VARCHAR2, new_entity VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY test1 AS
  -- Define the stored procedures
  PROCEDURE get_date_range(new_date VARCHAR2,new_entity VARCHAR2) IS
    -- Declare a ref cursor to hold the query result
    TYPE rc_entity_and_date IS REF CURSOR;
    c_entity_and_date rc_entity_and_date;
  BEGIN
    -- Open the ref cursor with the query
    OPEN c_entity_and_date FOR
      SELECT * FROM my_table WHERE entity = new_entity AND report_date = new_date;
    -- Output the ref cursor data to the Data Grid using dbms_sql.return_result
    dbms_sql.return_result(c_entity_and_date);
    -- Close the ref cursor
    CLOSE c_entity_and_date;
  END get_date_range;
END;
/

EXECUTE test1.get_date_range('6/12/2023', 'MLPFS');

I'm getting errors : ORA-01001, ORA-06512

Upvotes: 0

Views: 220

Answers (1)

Littlefoot
Littlefoot

Reputation: 143063

Just don't close the cursor (line #12 is commented); return_result implicitly does that, so - if you try to do it manually, Oracle complains that there's no such cursor which is open.

SQL> create or replace PROCEDURE get_date_range(new_date date,new_entity VARCHAR2) IS
  2      -- Declare a ref cursor to hold the query result
  3      TYPE rc_entity_and_date IS REF CURSOR;
  4      c_entity_and_date rc_entity_and_date;
  5    BEGIN
  6      -- Open the ref cursor with the query
  7      OPEN c_entity_and_date FOR
  8        SELECT * FROM emp WHERE job = new_entity AND hiredate >= new_date;
  9      -- Output the ref cursor data to the Data Grid using dbms_sql.return_result
 10      dbms_sql.return_result(c_entity_and_date);
 11      -- Close the ref cursor
 12      -- CLOSE c_entity_and_date;
 13    END get_date_range;
 14  /

Procedure created.

Testing:

SQL> exec get_Date_range(date '1980-01-01', 'CLERK');

PL/SQL procedure successfully completed.

ResultSet #1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL>

Upvotes: 0

Related Questions