CODEWITHSUNDEEP
c#vb.netoracle-databasestored-proceduresplsqldeveloper
Sesuraj
Sesuraj

Reputation: 29

Error on calling procedure from oracle to C#

Hi i am creating a application using stored procedure. I am not able to insert data into table.

My table is:

SQL> create table test (name varchar2(20), qualification varchar2(10), address v
archar2(100));

Table created.

My stored procedure is

SQL> create or replace procedure inserttest (
  2  p_name test.name%TYPE,
  3  p_qualification test.qualification%TYPE,
  4  p_address test.address%TYPE)
  5  IS
  6  BEGIN
  7  INSERT INTO test (name, qualification, address)
  8  VALUES (p_name, p_qualification, p_address);
  9  COMMIT;
 10  END;
 11  /

Procedure created.

My C# Code is:

protected void Button1_Click(object sender, EventArgs e)
        {
            con = new OleDbConnection("Provider=MSDAORA;Data Source=xe;Persist Security Info=True;Password=sesu;User ID=system");
            cmd.Parameters.Add("p_name", OleDbType.VarChar ).Value = TextBox1.Text;
            cmd.Parameters.Add("p_qualification", OleDbType.VarChar).Value = TextBox2.Text;
            cmd.Parameters.Add("p_address", OleDbType.VarChar).Value = TextBox3.Text;
            cmd = new OleDbCommand ("inserttest", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close(); 

        }

I am not able to insert data through C#. It shows:

One or more errors occurred during processing of command.
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

What is the problem.? How to solve it.?

Upvotes: 1

Views: 314

Answers (2)

Nagaraj S
Nagaraj S

Reputation: 13474

I think PLS-00306: wrong number or types of arguments in call to 'INSERTTEST' because of you have varchar2(20) in table .In codebehind you have mentioned OleDbType.VarChar.And also check your stored procedure datatype and use correct datatype

In stored peoedure try like this

   create or replace procedure inserttest (
   p_name varchar2(20),
   p_qualification varchar2(20),
   p_address varchar2(20))
IS
BEGIN
INSERT INTO test (name, qualification, address)
VALUES (p_name, p_qualification, p_address);
COMMIT;
END;

Upvotes: 1

Andrea
Andrea

Reputation: 12355

You could try to define static types for your parameters in your stored procedure:

create or replace procedure inserttest (
    p_name          in varchar2,
    p_qualification in varchar2,
    p_address       in varchar2
)
IS
BEGIN
    INSERT INTO test (name, qualification, address)
    VALUES (p_name, p_qualification, p_address);
    COMMIT;
END;

Upvotes: 0

Related Questions