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 Wed Aug 14 2002 - 03:04:36 CDT
