Calling SP with sys_refcursor as out parameter inside another procedure

Question

I have an SP

create or replace PROCEDURE         ALTERNATE_NAME_LOOKUP
 ( P_NAME IN VARCHAR2,
  P_TYPE IN VARCHAR2, retCursor OUT SYS_REFCURSOR
 )

I didn't paste the rest of its body; The above procedure works fine on its own (with the body of course)

Now I want to call it from another stored procedure, and I want to traverse over the refcursor.

What I am doing is declaring an_last_cur SYS_REFCURSOR; and calling ALTERNATE_NAME_LOOKUP procedure as ALTERNATE_NAME_LOOKUP(p_req.LASTNAMEEXP,c_LAST, an_last_cur); It compiles. but when I add following block -

 ALTERNATE_NAME_LOOKUP('Roman Reigns','LAST',an_last_cur);
      For alt in an_last_cur
      Loop
        DBMS_OUTPUT.PUT_LINE('ok');
      end loop;

It gives compilation error -

 PLS-00221: 'AN_LAST_CUR' is not a procedure or is undefined

What am I doing wrong?

Answer 1

Try this one. Hope this helps. I dont have workspace with me so pardon syntax erro r if any.

CREATE OR REPLACE PROCEDURE test_ref_prc
  ( p_ref_out  OUT sys_refcursor)
AS
BEGIN
OPEN p_ref_out FOR
  SELECT LEVEL  FROM DUAL CONNECT BY LEVEL < 10;
END;

CREATE OR REPLACE PROCEDURE test_ref2
AS
refc sys_refcursor;
num_ntt NUMBER_NTT;
BEGIN
test_ref_prc(refc);
FETCH refc BULK COLLECT INTO num_ntt;
FOR I IN num_ntt.FIRST..num_ntt.LAST LOOP
dbms_output.put_line(num_ntt(i));
END LOOP;
END;

exec test_ref2;