Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> stored proc for select statement

stored proc for select statement

From: rsood <rsood_at_panix.com>
Date: 1998/04/07
Message-ID: <352A4453.44881C30@panix.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US