CODEWITHSUNDEEP
oracle-databasefunctionplsqlcursor
mostafa63
mostafa63

Reputation: 5

return data as cursor from function in pl/sql wiithout create type oracle 11g

I wrote this code in pl/sql but I couldnt take answer.

  create or replace function mostafa.sbs_Topic_LedgerBalance8Column
   (BranchID number,DateFrom number,DateTo number) 
 RETURN SYS_REFCURSOR 
  IS  O_RESULT SYS_REFCURSOR;

  BEGIN
  open O_RESULT for
     Select s* From Mostafa.topic ;


      RETURN O_RESULT;

end sbs_Topic_LedgerBalance8Column;

and I called it this way:

DECLARE v_refcursor SYS_REFCURSOR;

BEGIN
v_refcursor :=mostafa.sbs_topic_ledgerbalance8column(12,12,12);

FOR employee_rec IN v_refcursor
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.ID);
   END LOOP;
end;

why did I get error when I retrieve result?

error is :v_refcursor is not a procedure or is undefined

Upvotes: 0

Views: 1646

Answers (1)

Armunin
Armunin

Reputation: 996

When you are using a refcursor, you can't access it by using the cursor for loop. Use something like the following instead (Untested):

DECLARE
    v_refcursor SYS_REFCURSOR;
    v_emp_rec topic%ROWTYPE;

BEGIN
    v_refcursor :=mostafa.sbs_topic_ledgerbalance8column(12,12,12);

    LOOP
        FETCH v_refcursor INTO v_emp_rec;
        EXIT WHEN v_refcursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.id);
    END LOOP;
    close v_refcursor;
END;

Upvotes: 1

Related Questions