Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> stored proc for select statement
To encapsulate a select query within a stored procedure I use the following:
create or replace procedure spFoo as
cursor c1 is select * from foo;
foorow foo%rowtype;
col1 varchar2(20);
col2 number;
begin
open c1;
loop
fetch c1 into foorow;
exit when c1%notfound;
col1:=foorow.fldchar;
col2:=foorow.fldint;
dbms_output.put_line col1;
end loop;
close c1;
end;
/
I can execute it from SQL Plus but after about 500 rows it generates an
error about a output buffer overflow.
So far I am unable to call this procedure from client code via an odbc
connection. No results are returned.
I don't want developers to write hand-coded sql. They should just call
the stored proc which will take care
of optimization, indexing etc. Will someone post about how to handle
this fairly common task, or is it in a FAQ
already?
-R. Sood
rsood_at_panix.com
Received on Tue Apr 07 1998 - 00:00:00 CDT