problem insert to Oracle

[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;
/

..

 

[David Browne]

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?

Hard to tell, but here's your next step.
CommandType=StoredProcedure is just a convenience for you, but it may be
hiding the problem here.

Change your commandType to Text, and change your command to a small PL/SQL
program.
Then you can test exactly the same thing in SQL Plus and your program.

OracleCommand oraCmd = new OracleCommand

("begin pkgbenefits.sp_InsertBenefits1,:2,:3,:4,:5,:6,:7,:8);
end;",this.Connection);

oraCmd.CommandType = CommandType.Text;

BTW are you using the MS oracle provider, or the ODP.NET driver from Oracle?

David

 

[anon]

Thanks

I am using the MS provider

I simplified it to run without parameters from the
webservices. Instead, I hardcoded the values into the
now parameterless data access method as shown below and
it worked.

I will play with it some more.

Thanks.


public int InsertBenefits()
{
OracleCommand oraCmd = new
OracleCommand("begin pkgbenefits.sp_InsertBenefits
('11111111', 'A', 'B', 5, 'D', 'E', 'F', 'G'); end;",
this.Connection);
oraCmd.CommandType =
CommandType.Text;
try
{
int recordsAffected =
oraCmd.ExecuteNonQuery();
return (recordsAffected);
}
catch (OracleException exep)
{
LogError
(exep.Message.ToString(),EventLogEntryType.Error,1001,10);
throw (exep);
}
}