Re: stored proc for select statement

In article <352A4453.44881C30_at_panix.com>,   rsood <rsood_at_panix.com> wrote:
>
> 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
>
>

Hello.
 You have, actually 2 different problems :

1. Explosion of output buffer. To solve it you have to increase output buffer size using command: SET SERVEROUTPUT ON SIZE 300000 It sets the size of output buffer to 300000 bytes instead of default of 2000.
2. You will NOT be able to get any data via ODBC because DBMS_OUTPUT is used for display of data on the SERVER only. You must rewrite your stored procedure as PACKAGE with 3 procedures:
3. OpenCursor
4. FetchCursor ( it must return foo%rowtype and return_code ).
5. CloseCursor.

 Good luck. Michael

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Apr 07 1998 - 00:00:00 CDT