CODEWITHSUNDEEP
c#asp.netoracle-databasestored-procedures
Pacchi
Pacchi

Reputation: 57

Issue in fetching data from SP (Oracle Database)

As I am new to this, I am facing some issue in executing stored procedure in oracle DB. Here is the SP which gives record as output parameter which is of type %rowtype and l_serno as input parameter which is of type Number.

Create OR Replace procedure get_product(l_serno in product.serno%type,record out product%rowtype)
is

begin

select * into record from product where serno=l_serno;

end get_product;

Using C#, I am trying to fetch the data from the SP and show it on the gridview.

OracleCommand cmd = new OracleCommand("get_product", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                Conn.Open();
                OracleParameter input = cmd.Parameters.Add("V_SERNO", OracleType.Number);
                OracleParameter output = cmd.Parameters.Add("ITEMS_CURSOR", OracleType.Cursor);
                input.Direction = ParameterDirection.Input;
                output.Direction = ParameterDirection.ReturnValue;
                input.Value = 2;
                OracleDataReader rd = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(rd);
                GridView1.DataSource = dt;
                GridView1.DataBind();
                Conn.Close();

Here I am getting error as

ORA-06550: line 1, column 24:

PLS-00306: wrong number or types of arguments in call to 'GET_PRODUCT'

ORA-06550: line 1, column 7:

Please let me know what is the wrong I am doing here. Thanks in Advance.

Upvotes: 0

Views: 420

Answers (1)

APC
APC

Reputation: 146239

Your procedure has this signature:

(l_serno in product.serno%type,record out product%rowtype)

But in your C# code you specify this:

 OracleParameter output = cmd.Parameters.Add("ITEMS_CURSOR", OracleType.Cursor);

A cursor is a pointer to a result set and is different from a variable. You could change your C# code: define a class whose attributes match the projection of the PRODUCT table. Alternatively, change the stored procedure to use a ref cursor.

The second approach is probably less work (not least because you can get us to do it for you)

create or replace procedure get_product
       (l_serno in product.serno%type,
            record out sys_refcursor)
is
begin
     open record for  
            select * from product
             where serno=l_serno; 
end get_product;

Upvotes: 1

Related Questions