Not really sure of why you are choosing this as a solution to getting your
data. Why not just open a cursor (a recordset). I know a lot of people
code result sets in MS SQL server becasue it is the only effiecient way to
do queries. But in Oracle you don't have to; you want use host variables
(which the majorityof RDBMS's support) and that will allow your queries to
scale much better. (difference is the speed of a query won't be a lot
faster, but the backend will be able to handle many more of them at a time -
scale better). Oracle's OLE objects allow you to use host variables (the
documentation calls them parameters) and you just open a record set. It is
very very simple. As for being backend independent - if you are returning
result sets via a stored procedure you are already highly backend and
platform dependent. Alas if you want any type of performance and
scalability you are going to have to be somewhat backend dependent (what OS
it is is somewhat irrelivant). They all have their quirks and are more
efficient in certain ways and have different subtle interpretations of
"standards"
Jim
rjkdev_at_rocketmail.com wrote in message <717gv3$i0m$1_at_nnrp1.dejanews.com>...
>I would rather stay platform (and DB) independant.
>
>the only examples I found of passing back a recordset from a stored
procedure
>look like this:
>
>Create or Replace Package Body MISKMA3
>
> as
>
> procedure getrcds
> (first_name out tfirst_name,
> last_name out tlast_name)
>
> IS
>
> CURSOR kma3 IS SELECT FIRST_NAME,LAST_NAME FROM NAME_TABLE;
>
> rc NUMBER DEFAULT 1;
>
> BEGIN
> for se in kma3
> loop
> first_name(rc) := se.FIRST_NAME;
> last_name(rc) := se.LAST_NAME;
> rc := rc + 1;
> end loop;
> end;
> end;
>
>Create or Replace Package MISKMA3
> as
>
> TYPE tfirst_name IS TABLE OF VARCHAR2(50)
> Index by BINARY_INTEGER;
> TYPE tlast_name IS TABLE OF VARCHAR2(50)
> Index by BINARY_INTEGER;
>
> Procedure GETRCDS
> (first_Name out tfirst_name,
> last_name out tlast_name);
> end;
>
>
>
>Is there a simpler way of doing the above. I don't like the fact I have to
>tell the server the maximum number of records to return. Do you avoid this
>problem if you use the Oracle Objects. And even if you do, I would think
the
>stored procedure could be a lot simpler.
>
>
>Thanks,
>
>Robbie K
>
>In article <s2RY1.115$Dm.3031723_at_news.ipass.net>,
> "David Sisk" <davesisk_at_ipass.net> wrote:
>> Hi:
>>
>> Try using ORACLE Objects for OLE rather than ODBC.
>>
>> Good luck,
>>
>> --
>>
>> David C. Sisk
>> http://www.ipass.net/~davesisk/ORACLEonNT.htm
>>
>> rjkdev_at_rocketmail.com wrote in message
<70qmdm$vc$1_at_nnrp1.dejanews.com>...
>> >I am new to the oracle world, i have worked with MS SQL Server though.
>> >
>> >In MS SQL I would create the following Stored Proc
>> >
>> >CREATE PROCEDURE My_info
>> > @lastname varchar(40),
>> >AS
>> > SELECT au_lname, au_fname
>> > FROM authors
>> > WHERE au_lname = @lastname
>> >
>> >
>> >Then I just use VB and ADO to execute the stored procedure and put the
>> results
>> >in a recordset.
>> >
>> >
>> >
>> >But in oracle, the same does not seem to work. I have some examples of
>> >creating oracle stored procedures, but they all declare a cursor and
loop
>> >through the records.
>> >
>> >Also, in the example i have, my VB program needs to tell oracle how many
>> >records are going to be returned.
>> >
>> >Am i missing the big picture here, or do you really have to:
>> >1. Declare a cursor in all oracle stored procedures, and
>> >2. always tell it how many records will be returned.
>> >
>> >Thanks in advance!
>> >
>> >- Robbie K -
>> >
>> >-----------== Posted via Deja News, The Discussion Network ==----------
>> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>>
>>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Oct 28 1998 - 23:51:34 CST
