Facing error ‘invalid SQL statement’ while working with cursor variables in PL/SQL

cursorsoracleplsql

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

Best Answer

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

Related Solutions

Oracle strong ref cursor with dynamic sql

There are probably better ways to achieve your underlying goal, but if you really want values from dynamic SQL into a strongly typed refcursor I guess you could do something like this:

create table TESTTABLE(TESTID number not null);
create global temporary table TEMPTESTTABEL(TESTID number not null);

create or replace procedure testprocedure is 
  type testtablerefcursor is ref cursor return TESTTABLE%rowtype;
  type testtabletable is table of TESTTABLE%rowtype;
  mycursor testtablerefcursor;
  mysyscursor SYS_REFCURSOR;
  mytesttable testtabletable;
begin
  open mysyscursor for 'select * from TESTTABLE';
  fetch mysyscursor bulk collect into mytesttable;
  close mysyscursor;
  forall i in mytesttable.first..mytesttable.last
   insert into TEMPTESTTABEL values mytesttable(i);
  open mycursor for select testid from TEMPTESTTABEL;
end;
/

PL/SQL recursive lookup with cursor

The answer is really use Hierarchical query model from Oracle. http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

So in my case:

select * 
from tfinal 
start with request = &search 
       and type = 'CNAME' 
connect by prior response = request;

The solution is simpler than what I envisioned. Here is the real data and the query itself

select a.request,a.response,level from dnstest a  left join dnstest b on b.request=a.response||'.'  start with a.request='www.microsoft.com' connect by prior a.response=a.request||'.';

The data looks like

1,1431606892,www.microsoft.com,CNAME,toggle.www.ms.akadns.net.
2,1431606892,toggle.www.ms.akadns.net,CNAME,www.ms.akadns.net.
3,1431606892,www.ms.akadns.net,CNAME,www.microsoft.com-c.edgekey.net.globalredir
.akadns.net.
4,1431606892,www.microsoft.com-c.edgekey.net.globalredir.akadns.net,CNAME,c.edge
key.net.globalredir.akadns.net.
5,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.154
6,1431606892,www.google.com,A,173.194.121.48
7,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.194

Related Question