PLS-00306: wrong number or types of arguments in call to.. ASP/Oracle problem

From: Phil Powell <soazine_at_erols.com>
Date: 13 Aug 2002 11:47:57 -0700
Message-ID: <1cdca2a7.0208131047.e90ede@posting.google.com>

I am getting the infamous PLS-00306 and I can't figure out why this time. I have every parameter present, in order, and spelled correctly, handling the correct datatype. It works in SQL Plus, even!

Here is the valid stored procedure:

CREATE OR REPLACE PROCEDURE SQLINSERTCOMPANYREP  (

	strCompanyRepType		IN VARCHAR2,
	strCompanyRepFirstName 	IN VARCHAR2,
	strCompanyRepLastName 	IN VARCHAR2,
	strCompanyRepAddress1 	IN VARCHAR2,
	strCompanyRepAddress2 	IN VARCHAR2,
	strCompanyRepCity 		IN VARCHAR2,
	strCompanyRepStateProvince IN VARCHAR2,
	strCompanyRepZip 		IN VARCHAR2,
	strCompanyRepCountry	IN VARCHAR2,
	strCompanyRepPhone1 	IN VARCHAR2,
	strCompanyRepPhone2 	IN VARCHAR2,
	strCompanyRepEmail 		IN VARCHAR2,
	strCompanyRepFax 		IN VARCHAR2,
	strCompanyRepLink 		IN VARCHAR2,
	strCompanyRepTerritory 	IN VARCHAR2,

    intCompanyRepID OUT VARCHAR2 /* Story Server requires char type OUT parameters */

        )

/********************************************************************
	Name: 		sqlInsertCompanyRep
	Function:	Insert a new row into tblCompanyRep.
				Return the generated sequence number, intCompanyRepID.
	Created:	09/01/2000	By:	Phil Serfass (Idea Integration)
	Changed:	10/31/2000	By: Phil Serfass
				Redesigned Company Rep tables.
********************************************************************/

AS

BEGIN
/****************************************************************

		Insert new CompanyRep row
	****************************************************************/
	INSERT 	INTO tblCompanyRep (
			intCompanyRepID,
			strCompanyRepType,
            strCompanyRepFirstName,
            strCompanyRepLastName,
            strCompanyRepAddress1,
            strCompanyRepAddress2,
            strCompanyRepCity,
            strCompanyRepStateProvince,
            strCompanyRepZip,
            strCompanyRepCountry,
            strCompanyRepPhone1,
            strCompanyRepPhone2,
            strCompanyRepEmail,
            strCompanyRepFax,
            strCompanyRepLink,
            strCompanyRepTerritory
        	)
	VALUES(
			seqCompanyRepID.NEXTVAL ,
			TRIM(sqlInsertCompanyRep.strCompanyRepType),
        	TRIM(sqlInsertCompanyRep.strCompanyRepFirstName),
        	TRIM(sqlInsertCompanyRep.strCompanyRepLastName),
        	TRIM(sqlInsertCompanyRep.strCompanyRepAddress1),
        	TRIM(sqlInsertCompanyRep.strCompanyRepAddress2),
        	TRIM(sqlInsertCompanyRep.strCompanyRepCity),
        	TRIM(sqlInsertCompanyRep.strCompanyRepStateProvince),
        	TRIM(sqlInsertCompanyRep.strCompanyRepZip),
        	TRIM(sqlInsertCompanyRep.strCompanyRepCountry),
        	TRIM(sqlInsertCompanyRep.strCompanyRepPhone1),
        	TRIM(sqlInsertCompanyRep.strCompanyRepPhone2),
        	TRIM(sqlInsertCompanyRep.strCompanyRepEmail),
        	TRIM(sqlInsertCompanyRep.strCompanyRepFax),
        	TRIM(sqlInsertCompanyRep.strCompanyRepLink),
        	TRIM(sqlInsertCompanyRep.strCompanyRepTerritory)
	       	)
	RETURNING intCompanyRepID INTO sqlInsertCompanyRep.intCompanyRepID;

	COMMIT;

EXCEPTION


	/****************************************************************

		Rollback the transaction and reraise the error
	****************************************************************/
	WHEN OTHERS	THEN
		ROLLBACK;

    	sqlInsertError(
            strErrorSource	 => 'sqlInsertCompanyRep',
            intErrorNumber   => SQLCODE,
            strErrorMessage  => SQLERRM,
            strErrorComments => 'strCompanyRepType = "' ||
strCompanyRepType ||'" ' ||
            					'strCompanyRepFirstName = "' ||
strCompanyRepFirstName ||'" ' ||
            					'strCompanyRepLastName = "' || strCompanyRepLastName
||'" ' ||
            					'strCompanyRepAddress1 = "' || strCompanyRepAddress1
||'" ' ||
            					'strCompanyRepAddress2 = "' || strCompanyRepAddress2
||'" ' ||
            					'strCompanyRepCity = "' || strCompanyRepCity ||'" '
||
            					'strCompanyRepStateProvince = "' ||
strCompanyRepStateProvince ||'" ' ||
            					'strCompanyRepZip = "' || strCompanyRepZip ||'" ' ||
            					'strCompanyRepCountry = "' || strCompanyRepCountry
||'" ' ||
            					'strCompanyRepPhone1 = "' || strCompanyRepPhone1 ||'"
' ||
            					'strCompanyRepPhone2 = "' || strCompanyRepPhone2 ||'"
' ||
            					'strCompanyRepEmail = "' || strCompanyRepEmail ||'" '
||
            					'strCompanyRepFax = "' || strCompanyRepFax ||'" ' ||
            					'strCompanyRepLink = "' || strCompanyRepLink ||'" '
||
            					'strCompanyRepTerritory = "' ||
strCompanyRepTerritory ||'" '
            );

		RAISE;

END; Here is my code snippet in ASP I wrote to handle the stored procedure.  It calls a working function, SearchProcedure (this function is used throughout the site I'm building and successfully works approx. 15-20 times per template). SearchProcedure takes the connection object, dbConn, and an array whose elements are:

1. string consisting of stored procedure name
2. dictionary object containing a parameter name string as key, and as value an array consisting of:
3. data type (e.g., adVarChar, adNumeric, etc.)
4. data size integer
5. value to pass into parameter
6. dictionary object containing a parameter name string as key, and as value an array consisting of:
7. data type
8. data size

(2) handles input while (3) handles output

Here is the code that will build the unusual array and call the function:

Dim hasSubmittedDB, companyRepObject, companyRepObjArray

   if hasSubmittedRep = 1 then
    ReDim companyRepObjArray(3)

        companyRepObjArray(0) = "SQLINSERTCOMPANYREP"    else
    ReDim companyRepObjArray(2)

        companyRepObjArray(0) = "SQLUPDATECOMPANYREP"    end if
   set companyRepObjArray(1) =
Server.CreateObject("Scripting.Dictionary")

   if hasSubmittedRep = 1 then set companyRepObjArray(2) = Server.CreateObject("Scripting.Dictionary")

   hasSubmittedDB = false
   if hasEditedRep = 1 then
    companyRepObjArray(1).Add "intCompanyRepID", Array(adNumeric, 38, CInt(Request.Form("companyRepID")))

   end if
   companyRepObjArray(1).Add "strCompanyRepType", Array(adVarChar, 255, Request.Form("repType"))

   companyRepObjArray(1).Add "strCompanyRepFirstName", Array(adVarChar, 255, Request.Form("firstname"))

   companyRepObjArray(1).Add "strCompanyRepLastName", Array(adVarChar, 255, Request.Form("lastname"))

   companyRepObjArray(1).Add "strCompanyRepAddress1", Array(adVarChar, 255, Request.Form("address1"))

   companyRepObjArray(1).Add "strCompanyRepAddress2", Array(adVarChar, 255, Request.Form("address2"))

   companyRepObjArray(1).Add "strCompanyRepCity", Array(adVarChar, 255, Request.Form("city"))

   companyRepObjArray(1).Add "strCompanyRepStateProvince", Array(adVarChar, 255, Request.Form("stateprovince"))

   companyRepObjArray(1).Add "strCompanyRepZip", Array(adVarChar, 255, Request.Form("zip"))

   companyRepObjArray(1).Add "strCompanyRepCountry", Array(adVarChar, 255, Request.Form("country"))

   companyRepObjArray(1).Add "strCompanyRepPhone1", Array(adVarChar, 255, Request.Form("phone1"))

   companyRepObjArray(1).Add "strCompanyRepPhone2", Array(adVarChar, 255, Request.Form("phone2"))

   companyRepObjArray(1).Add "strCompanyRepEmail", Array(adVarChar, 255, Request.Form("email"))

   companyRepObjArray(1).Add "strCompanyRepFax", Array(adVarChar, 255, Request.Form("fax"))

   companyRepObjArray(1).Add "strCompanyRepLink", Array(adVarChar, 255, Request.Form("website"))

   companyRepObjArray(1).Add "strCompanyRepTerritory", Array(adVarChar, 300, Request.Form("territory"))

   if hasSubmittedRep = 1 then
    companyRepObjArray(2).Add "intCompanyRepID", Array(adVarChar, 255)    end if    

   on error goto 0
    set companyRepObject = SearchProcedure(dbConn, companyRepObjArray)

This is beyond what I can deduce with my own eyes to figure out what I did wrong, maybe someone else can find something I could not.

Thanx
Phil Received on Tue Aug 13 2002 - 13:47:57 CDT