Re: How to get a recordset into VB from Oracle Stored Procedure

Since posting my last reply, I have worked out what the problem is and its certainly worth knowing. In the connection string for the database you need to put 'PLSQLRSet=1'. I don't know what it does yet but I'm glad its working.

"Ian Carrick" <ian_at_rombus.co.uk> wrote in message news:974119257.1156.0.nnrp-13.c2de47d8_at_news.demon.co.uk...
> Thanks for confirming that. I have some more detail about what I'm trying
> to do. I'm using VB6, Oracle 8i on Linux and the Microsoft OLE DB for
> Oracle driver/ADO. Listed are a package and some VB code:-
>
> CREATE OR REPLACE PACKAGE ROMBUS.PKG_GETVAT
> AS
> TYPE curVAT IS REF CURSOR RETURN VAT%ROWTYPE;
> PROCEDURE usp_GetVat(VATID_in IN CHAR, VAT_out OUT curVAT);
> END PKG_GETVAT;
>
> CREATE OR REPLACE PACKAGE BODY ROMBUS.PKG_GETVAT
> AS
> PROCEDURE usp_GetVat(VATID_in IN CHAR, VAT_out OUT curVAT) IS
>
> BEGIN
> IF VATID_in = '' OR VATID_in IS NULL THEN
> OPEN VAT_out FOR
> SELECT * FROM VAT;
> ELSE
> OPEN VAT_out FOR
> SELECT * FROM VAT WHERE VATID = VATID_in;
> END IF;
>
> END usp_GetVat;
> END PKG_GETVAT;
>
> The above packages work fine in SQL Plus. The problem is getting VB to
> return the result set using:-
>
> cmd.CommandText = "PKG_GETVAT.usp_GetVat"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Append cmd.CreateParameter("VATID_in", adChar,
> adParamInput,1,"H")
> Set rs = cmd.Execute
>
> The Error 'PLS-00306 wrong number or types of arguments in call to
> USP_GETVAT' always occurs. If you have to declare 'VAT_out' as an output
> parameter then what datatype should it be. Are you allowed to pass a ref
> cursor back anyway. I am perplexed. Any help?
>
> <jocave_at_my-deja.com> wrote in message news:8uhqvj$nes$1_at_nnrp1.deja.com...
> > In article <973866755.26348.0.nnrp-01.c2de47d8_at_news.demon.co.uk>,
> > "Ian Carrick" <ian_at_rombus.co.uk> wrote:
> > > I've been used to SQL Server 7 and stored procedures. I've used VB to
> > > access stored procedures and return records into recordsets. I'm now
 having
> > > to do exactly the same but with Oracle 8i. I'm having trouble getting
 my
> > > head round how Oracle works and how you get a recordset from a stored
> > > procedure.
> > >
> > > Do you use packages to do such things and how do you get recordsets
 from a
> > > stored procedure. I read somewhere that 'ref cursors' cannot be used
 in
> > > Oracle to pass recordsets back. If that's the case how do you dot it?
> >
> > You can certainly get resultsets from stored procedures in Oracle.
> > You'll have your choice of API's-- ODBC, OLE DB, OO4O, RDO, DAO, ADO,
> > etc.
> >
> > In general, the way you do this is to create a procedure that returns a
> > REF CURSOR as an OUT parameter, then call that procedure from your VB
> > app using whichever API you selected.
> >
> > --
> > Justin Cave - Oracle ODBC Development
> >
> > Opinions expressed herein are my own and may not reflect those of
> > Oracle Corporation.
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Mon Nov 13 2000 - 09:17:04 CST