PL/SQL that returns a Recordset

[WebNickson]

Can anyone show me a simple sample as to how I can create a PL/SQL that returns a recordset.

E.g. The PL/SQL should return the result from
Select id, name from employee where name like 'A%'

rgds,
Nickson

 

[sem]

create package defs is

type rc is ref cursor;

end;

create procedure getEmps(c out defs.rc) is
begin

open c for 'Select id, name from employee where name like ''A%''';

end;

In sql*plus:

var c refcursor
exec getEmpsc)
print c

Regards, Dima

http://seminihin.narod.ru

 

[WebNickson]

One more qn sem... but if A% is an input parameter...how should I modify it?




rgds,
Nickson

 

[sem]

Is this so complex to be asked about? As you may see cursor is opened for a STRING LITERAL, so you may pass a part of it and then concatenate with the rest of the statement.

create or replace procedure
getEmps(c out defs.rc, par in varchar2)
is
begin

open c for 'Select id, name from employee where name like '''||par||'%''';

end;

Another, less obvious, way is to bind parameter with USING clause:

create or replace procedure
getEmps(c out defs.rc, par in varchar2)
is
begin

open c for 'Select id, name from employee where name like :1||''%''' using par;

end;

Regards, Dima

http://seminihin.narod.ru

 

[WebNickson]

Thx a lot... I am just bad with Oracle's PL/SQL.

rgds,
Nickson

 

[lktestting]

Hi sem /wick,

Regarding retriving resultset from oracle store procedure.

I have tried the same as you mention but when i execute this procedure getting err shown below

SP2-0552: Bind variable "C" not declared.

I wanted to know how to execute the procedure which used ref cursor.

 

[sem]

So declare it! var c refcursor is not a trash, but indeed a mandatory line


Regards, Dima

http://seminihin.narod.ru