Re: stored proc for select statement

Hi: Could you elaborate more? We've tried to do this before (from VB) and couldn't make it work. Please see below.....

Thanks,
Dave

bialik_at_wis.weizmann.ac.il wrote in message <6ge5kq$7mk$1_at_nnrp1.dejanews.com>...
>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:
> A. OpenCursor
> B. FetchCursor ( it must return foo%rowtype and return_code ).
> C. CloseCursor.

Do you have an example of B? I would be doubly thankful if you would email to me as well as posting here.

Thanks!

>
> 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