CODEWITHSUNDEEP
oracle-database
jaw
jaw

Reputation: 319

RETURN REF CURSOR FROM PROCEDURE BY SELECT

i need to return ref cursor in select statement . i'm use below code i need another type to return data . sorry i remove a code some out put variable (the true number of column return in ref cursor it's 14 ) but need any type to return data easily to can check it working fine or not. 

        DECLARE
           TYPE rc IS REF CURSOR;
           v_cur                      rc;                --declare ref cursor variable
           LIST_ID                    VARCHAR2 (250);
           PERSON_NO                  VARCHAR2 (250);

        BEGIN
           MOI_SERVICES.HCR_INQUIRIES_PKG.GET_SUSPECTED_LIST_PRC (148608947,
                                                                  NULL,
                                                                  NULL,
                                                                  NULL,
                                                                  NULL,
                                                                  NULL,
                                                                  NULL,
                                                                  NULL,
                                                                  NULL,
                                                                  v_cur,
                                                                  V_STAUS, -- 0 FAIL -- 1 SUCCESS
                                                                  V_MSG);


           LOOP
              FETCH v_cur
                  INTO LIST_ID,
                      PERSON_NO;
                EXIT WHEN v_cur%NOTFOUND;

         DBMS_OUTPUT.put_line (
                    'LIST_ID = '
                 ||LIST_ID 

                END LOOP;

              CLOSE v_cur;
             END;

Upvotes: 1

Views: 4447

Answers (1)

Stew Ashton
Stew Ashton

Reputation: 1529

The idea of a REF CURSOR is that you open the cursor, but whoever called you does the fetching and the closing. Using SQL*Plus or Oracle SQL Developer, here is a simple example using anonymous PL/SQL; the "print" command does the fetching, displaying and cursor closing.

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

PL/SQL procedure successfully completed.

SQL> print :rc

D
-
X

Calling a function would look like this:

SQL> create or replace function f return sys_refcursor is
  2    l_rc sys_refcursor;
  3  begin
  4    open l_rc for select * from dual;
  5    return l_rc;
  6  end f;
  7  /

Function F compiled

SQL> exec :rc := f;

PL/SQL procedure successfully completed.

SQL> print :rc;

D
-
X

Best regards, Stew Ashton

Upvotes: 1

Related Questions