Re: Returning Recordset with Oracle SP

You need to put 'PLSQLRSet=1' in your connection string.

<beeckm_at_my-deja.com> wrote in message news:8tup1m$gif$1_at_nnrp1.deja.com...
>
> > Is the problem as simple as the need to add parenthesis?
> > {call SP_LIS_APPGROUPACCESS() }
>
> Apparently not :-(
>
> > If not, can you explain the error(s) you're getting?
>
> OK, I'll tell you what Oracle says:
>
> "PLS-00306: Wrong number or type of arguments in call
> to 'SP_LIS_APPGROUPACCESS'..
> Line 1, Column 7: PL/SQL: statement ignored.
>
> I have changed some stuff however, I have user the Oracle Migration
> Workbench to migrate my procedure which existed on a SQL Server 7.0, to
> my Oracle 8i DB.
>
> My package now looks like this:
>
> CREATE PACKAGE SP_LIS_APPGROUPACCESSPKG
> AS
> TYPE RT1 IS RECORD (
> GAUSRGRP APPGRACC.GAUSRGRP%TYPE,
> GATAGXXX APPGRACC.GATAGXXX%TYPE,
> GAFLVIEW APPGRACC.GAFLVIEW%TYPE,
> GAFLUPDT APPGRACC.GAFLUPDT%TYPE,
> GAFLCREA APPGRACC.GAFLCREA%TYPE,
> GAFLDELE APPGRACC.GAFLDELE%TYPE,
> GAFLACC1 APPGRACC.GAFLACC1%TYPE,
> GAFLACC2 APPGRACC.GAFLACC2%TYPE,
> GAFLACC3 APPGRACC.GAFLACC3%TYPE,
> GAUSUPDT APPGRACC.GAUSUPDT%TYPE,
> GADTUPDT APPGRACC.GADTUPDT%TYPE,
> GATMUPDT APPGRACC.GATMUPDT%TYPE
> );
> TYPE RCT1 IS REF CURSOR RETURN RT1;
>
> END;
>
> A Procedure is created also by the Migration Workbench, which looks
> like:
>
> CREATE PROCEDURE SP_LIS_APPGROUPACCESS
> (
> RC1 IN OUT SP_LIS_APPGROUPACCESSPkg.RCT1)
> AS
> StoO_selcnt INTEGER;
> StoO_error INTEGER;
> StoO_rowcnt INTEGER;
> StoO_crowcnt INTEGER := 0;
> StoO_fetchstatus INTEGER := 0;
> StoO_errmsg VARCHAR2(255);
> StoO_sqlstatus INTEGER;
> BEGIN
> OPEN RC1 FOR
> SELECT GAUSRGRP, GATAGXXX, GAFLVIEW, GAFLUPDT, GAFLCREA,
> GAFLDELE, GAFLACC1, GAFLACC2, GAFLACC3, GAUSUPDT, GADTUPDT,
> GATMUPDT FROM APPGRACC;
> END sp_lis_APPGROUPACCESS;
>
> I have created a small testproject in VB that uses this connection
> string:
>
> m_Conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data
> source=LAB400;User ID=salogin;Password=sapassword;"
>
> The code to call this SP looks like this:
>
> comm.ActiveConnection = m_Conn
> comm.CommandType = adCmdStoredProc
> comm.CommandText = "SP_LIS_APPGROUPACCESS"
> Set RS = comm.Execute
>
> Connection to Oracle is established, but when the ADO Command is
> executed I get the error message. I have tried ADO 2.1 and ADO 2.5, no
> difference there...
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Nov 14 2000 - 05:35:16 CST