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

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 - 06:43:10 CST