Re: stored proc for select statement

Hi,

with VB, I'd use Oracle Objects for OLE, which are shipped with Oracle in conjunction with ref_cursors. See the examples delivered by oracle. Using OO4O you would even be able to bind grids and so on to your result set.

On Tue, 07 Apr 1998 23:59:35 GMT, "David Sisk" <davesisk_at_ipass.net> wrote:

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

--

Regards

Matthias Gresz :-)

GreMa_at_T-online.de Received on Wed Apr 08 1998 - 07:11:32 CDT