Procedure Call from ASP caused PLS-00306 error

From: cschang <cschang_at_maxinter.net>
Date: Sat, 30 Aug 2003 00:47:49 -0400
Message-ID: <3F502C75.B794ED9A@maxinter.net>

I wrote an ASP page where it tried to query a set of records back from a 8.1.7 ( on NT 4 w/SP6a). I used a procedure call to do the job (see below). However, I got an error when doing this. :

   [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETITEMINFO' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

from my asp page, which has codes:

   Set objCon = Server.CreateObject("ADODB.Connection")

objCon.CursorLocation = adUseClient
objCon.ConnectionTimeout = Session("D2_ConnectionTimeout")
objCon.open "Driver={Microsoft ODBC for Oracle};

Server=myDB",Session("UserID"),Session("Password")

itemCall = "{call D2$catalog.getItemInfo(?,?, {resultset 100, o_vendor_id, "& _

             " o_vendor_um, o_vendor_price, o_count})}"

Set itemInfo = Server.CreateObject("ADODB.Command")

itemInfo.ActiveConnection = objCon
itemInfo.CommandText = itemCall
itemInfo.CommandType = adCmdText
itemInfo.Parameters.Refresh
itemInfo.Parameters(0) = siteID
itemInfo.Parameters(1) = oldNiin

set itemList = Server.CreateObject("ADODB.Recordset") itemList.CursorType = adOpenStatic

itemList.Open itemInfo

Where the getItemInfo is a procedure of the catalog package that uses synonym D2$catalog
and the procedure is defined as
CREATE OR REPLACE PACKAGE vd2_catalog AS

   TYPE tbl_vendorID IS TABLE OF catalog_items.vendor_ID%TYPE    INDEX BY BINARY_INTEGER;    TYPE tbl_qtyUnit IS TABLE OF catalog_items.um%TYPE    INDEX BY BINARY_INTEGER;    TYPE tbl_priceCost IS TABLE OF catalog_items.vendor_price%TYPE    INDEX BY BINARY_INTEGER;    TYPE tbl_counter IS TABLE OF NUMBER
   INDEX BY BINARY_INTEGER;

  PROCEDURE getItemInfo ( p_siteID        IN  VARCHAR2,
                        p_niin        IN  VARCHAR2,
                        o_vendor_id    IN OUT   tbl_vendorID,
                        o_vendor_um    IN OUT  tbl_qtyUnit,
                        o_vendor_price  IN OUT  tbl_priceCost,
                        o_count        IN OUT  tbl_counter);

END vd2_catalog;
/
I am not sure whether here is right place to post this. Execuse me to post here if I post the wrong place. however, I hope someone may experienced this before such error can help me out. I have searched and tried veries tips on the web, but none of it works. I believe the cause was from the declariation of the ?, input parameter. Does anyone have a good clue how to solve this?

C Chang Received on Fri Aug 29 2003 - 23:47:49 CDT