Re: Stored Procedures and VB

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 - 10:32:35 CST