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

The solution was the most bizarre, way-out thing to have happened.. it turns out I did not properly dimension the array that contains the 2 dictionary objects used to call the stored procedure!

Thanx!
Phil

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3d5a0f14$0$236$ed9e5944_at_reading.news.pipex.net>...
> Is your problem caused by intCompanyRepID being numeric in the asp code but
> (it looks like) character in PL/SQL.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Phil Powell" <soazine_at_erols.com> wrote in message
> news:1cdca2a7.0208131047.e90ede_at_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:
> > a) data type (e.g., adVarChar, adNumeric, etc.)
> > b) data size integer
> > c) value to pass into parameter
> > 3) dictionary object containing a parameter name string as key, and as
> > value an array consisting of:
> > a) data type
> > b) 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 Thu Aug 15 2002 - 14:32:21 CDT