1

Hi everyone hope you're doing well. I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not work. my first procedure is

  create or replace procedure p_get_student_detail(s_id  number,
     s_cur out sys_refcursor) AS

    Begin

     open s_cur for
     select student_name, age from student where student_id = s_id;

   end;

and my second procedure in which I'm using the output of the first procedure is :

Create or replace procedure s_print_data(s_id number)

   as

    type ref_cur is ref cursor;
    s_refcur       ref_cur;
    s_student_name student.student_name%type;
    s_student_age  student.age%type;

 begin

    p_get_student_detail(s_id, s_refcur);

     loop
        fetch s_refcur into s_student_name, s_student_age;
        exit when s_refcur%notfound;
        dbms_output.put_line(s_student_name || s_student_age);
     end loop;
 end;

when I try to execute the procedure exec s_print_data (1) I see this error :

ORA - 00900:invalid SQL statement 

I was wondering if you could help me with that. Thanks

  • 2
    It's working fine on 11gr2 (Sql*plus and Oracle Sql Developer) – Sam Jun 8 at 9:47
  • 1
    i'm using pl/sql developer and it shows the above mentioned error – Pantea Tourang Jun 8 at 10:16
  • 2
    You are missing ORA error number and line number. Can you please updatr your question with thst information please? – Michael Kutz Jun 8 at 11:05
1

OK I found where the problem is the syntax for calling the procedure is incorrect I have to call the procedure like this :

begin

   s_print_data(1);
  end;

and everything works fine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.