[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Return a resultset in Oracle 9i without using cursor

Posted on 2007-07-19
5
Medium Priority
?
499 Views
Last Modified: 2013-12-19
create or replace procedure proc_check
as
begin
select * from tablename;
end;

I 'm trying to compile this procedure in oracle 9i, but oracle is giving me the error as into clause expected.
If i write query using into clause procedure gets compiled successfully.
Also if is use the cursor then also procedure gets compiled successfully.

Is it possible to return a result set through procedure in oracle ???
since into clause will store only one value and cursor will have only one record......

please suggest me solution so that i can return a resultset (i.e table as a output) in Oracle 9i

Thanks
0
Comment
Question by:techprocess
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 19528818
you cannot do that without a ref cursor, or alternatively by using a function with a PIPELINED method.
http://www.akadia.com/services/ora_pipe_functions.html
0
 
LVL 15

Expert Comment

by:ishando
ID: 19528870
I haven't used this before, but you could use:

create or replace procedure proc_check(oRec out tablename%rowtype)
as
begin
  select * into oRec from tablename;
end;

You would need to ensure that the select does only return one record.
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 400 total points
ID: 19528988
Two ways of doing this:

1. Using REF CURSORs
Return the result set as a REF CURSOR.

create or replace procedure test_proc(x_cur OUT sys_refcursor)
as
begin
 open x_cur for select * from a1;
end;
/


declare
 l_cur sys_refcursor;
 l_id number;
 l_dt date;
begin
 test_proc(l_cur);

 loop
  fetch l_cur into l_id, l_dt;
  exit when l_cur%notfound;
  dbms_output.put_line(l_id||'  '||l_dt);
 end loop;
 close l_cur;
end;
/

2. Returning a pl/sql array
Declare the array type and the procedure in a package. Return the array.

create or replace package test_pack
as
 type t1 is table of a1%rowtype index by binary_integer;
procedure test_proc(x_array out t1);
end test_pack;
/
create or replace package body test_pack
as
procedure test_proc(x_array out t1)
as
begin
 select * bulk collect into x_array
 from a1;
end;
end test_pack;
/

declare
 l_array test_pack.t1;
begin
 test_pack.test_Proc(l_array);

 for i in l_array.first..l_array.last loop
  dbms_output.put_line(l_array(i).id||'  '||l_array(i).dt);
 end loop;
end;
/

0
 
LVL 3

Author Comment

by:techprocess
ID: 19529017
thanks angellll
but for that should i d create type as table and the write the procedure .

can u gv me the example ???

thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19529075
the link shows full sample...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month19 days, 13 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question