How to create Ref Cursor type which can be stored in Oracle DB

Question

I want to create a ref cursor type in database, so that I can declare variables of this type in sql developer command window using following statement :

variable PO_Ref_Csr_Out RefCursorTyp;  

In the declaration RefCursorTyp is the stored ref cursor type in database

I want to use this PO_Ref_Csr_Out variable in Anonymous pl/sql block to test some procedures.

How to create this RefCursorTyp in database?

I am not aware about how to create type in database.

Answer 1

If you are asking how to make a strongly-typed cursor then create a package and define the type in that:

CREATE PACKAGE SCHEMA_NAME.PACKAGE_NAME
AS
  TYPE RefCursorTyp IS REF CURSOR
    RETURN SCHEMA_NAME.TABLE_NAME%ROWTYPE;

  PROCEDURE get_Strongly_Typed_Cursor (
    out_cursor OUT RefCursorTyp
  );
END;
/

CREATE PACKAGE BODY SCHEMA_NAME.PACKAGE_NAME
AS
  PROCEDURE get_Strongly_Typed_Cursor (
    out_cursor OUT RefCursorTyp
  )
  IS
  BEGIN
    OPEN out_cursor FOR
     SELECT * FROM SCHEMA_NAME.TABLE_NAME;
  END;
END;
/

Then when you want to use it in PL/SQL you can:

DECLARE
  v_cur SCHEMA_NAME.PACKAGE_NAME.RefCursorTyp;
BEGIN
  SCHEMA_NAME.PACKAGE_NAME.get_Strongly_Typed_Cursor(v_cur);
  -- Do something with the cursor.
END;
/

fiddle

Answer 2

To me, this is the simplest way to do what you described.

This is Oracle's command-line tool, SQL*Plus:

SQL> var rc refcursor
SQL>
SQL> begin
  2    open :rc for
  3      select * from dept;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print rc

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

---

Alternatively, using Oracle's (free) SQL Developer GUI tool (code is similar to previous, but it won't work with anonyomus PL/SQL block but uses stored procedure that returns refcursor):