Re: Returning Recordset with Oracle SP

From: <beeckm_at_my-deja.com>
Date: Fri, 03 Nov 2000 16:27:44 GMT
Message-ID: <8tup1m$gif$1@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 Fri Nov 03 2000 - 10:27:44 CST