problem inserting to Oracle with web services

[anon]

I am using web services to call a data access component
which inserts into a small Oracle 9i (table, stored
procedure shown below).

The data access component returns the following error:ORA-
06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call
to 'SP_INSERTBENEFITS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Here is my Data access component code:
I can insert the records by calling the Stored procedure
in SQL+, so I know the sproc works. I can also insert
records using the data access component when callled by a
console application.

What am I doing wrong?


public int InsertBenefits(string ssn,
string fName, string lName, int age, string address,
string city, string state, string zip)
{

OracleCommand oraCmd = new OracleCommand
("pkgbenefits.sp_InsertBenefits",this.Connection);

oraCmd.CommandType = CommandType.StoredProcedure;
oraCmd.Parameters.Add(new
OracleParameter("p_SSN",OracleType.VarChar)).Value = ssn;
oraCmd.Parameters.Add(new
OracleParameter("p_firstName",OracleType.VarChar)).Value
= fName;
oraCmd.Parameters.Add(new
OracleParameter("p_lastName",OracleType.VarChar)).Value =
lName;
oraCmd.Parameters.Add(new
OracleParameter("p_Age",OracleType.Int32)).Value = age;
oraCmd.Parameters.Add(new
OracleParameter("p_Address",OracleType.VarChar)).Value =
address;
oraCmd.Parameters.Add(new
OracleParameter("p_City",OracleType.VarChar)).Value =
city;
oraCmd.Parameters.Add(new
OracleParameter("p_State",OracleType.VarChar)).Value =
state;
oraCmd.Parameters.Add(new
OracleParameter("p_Zip",OracleType.VarChar)).Value = zip;
try
{
int recordsAffected =
oraCmd.ExecuteNonQuery();
return (recordsAffected);
}
catch (OracleException exep)
{
LogError
(exep.Message.ToString(),EventLogEntryType.Error,1001,10);
throw (exep);
}


CREATE TABLE Benefits
(
SSN Varchar2 (11) NOT NULL,
First_Name Varchar2 (20) ,
Last_Name Varchar2 (20),
Age Number (3),
Address Varchar2 (50),
City Varchar2 (15),
State Char (20),
Zip Varchar2 (10),
CONSTRAINT Benefits_PK PRIMARY KEY (SSN)
);

CREATE OR REPLACE PACKAGE pkgBenefits AS

PROCEDURE sp_InsertBenefits
(
p_SSN IN Varchar2,
p_firstName IN VARCHAR2,
p_lastName IN VARCHAR2,
p_Age IN Number,
p_Address IN VARCHAR2,
p_City IN VARCHAR2,
p_State IN Char,
p_Zip IN Varchar2
);
END pkgBenefits;
/

CREATE OR REPLACE PACKAGE BODY pkgBenefits AS

PROCEDURE sp_InsertBenefits
(
p_SSN IN Varchar2,
p_firstName IN VARCHAR2,
p_lastName IN VARCHAR2,
p_Age IN Number,
p_Address IN VARCHAR2,
p_City IN VARCHAR2,
p_State IN Char,
p_Zip IN Varchar2
)
AS

BEGIN
INSERT INTO Benefits
(
SSN,
First_Name,
Last_Name,
Age,
Address,
City,
State,
Zip
)
VALUES
(
p_SSN,
p_firstName,
p_lastName,
p_Age,
p_Address,
p_City,
p_State,
p_Zip
);

END sp_InsertBenefits;

END pkgBenefits;
/

 

[netnews.comcast.net]

My best guess having done a lot of SP calls to Oracle is that one of your
parameters is wrong. Most like the Int32. I can't remember in Oracle, but
it think you need to use a generic Number type (in VB6 if was adNumeric).

Mark Richards