Delete from TABLE using Comma Separted Values + ORACLE

[sonia.sardana]

I have three records in table with roll numbers 1,2,3.

I want to delete the records from the table using C#.

protected void Button1_Click(object sender, EventArgs e)
    {
        string sRollNos="";
        for (int i = 1; i <= 3; i++)
        {
            if (sRollNos == "")
            {
                sRollNos = i.ToString ();
            }
            else
                sRollNos = sRollNos + "," + i;
        }

        OracleCommand cmd = new OracleCommand("USP_DEL_TBL_STUDENT_DATA", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@p_ROLL",sRollNos );

        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        cmd.ExecuteNonQuery();
    }

I am getting the error-
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1.

Roll column datatype is Number.

Stored Procedure-

create or replace
PROCEDURE           "USP_DEL_TBL_STUDENT_DATA"
(
p_ROLL IN TBL_STUDENT.ROLL%TYPE
) AS
BEGIN
  DELETE FROM TBL_STUDENT where roll in (p_roll);
  end;

Can somebody help me out!!!

[gep13]

Hello,

First up, are you able to call the Stored Procedure directly against the database, as you are trying to call it in your code? Does it work?

When you debug your application, set a breakpoint on this line:

cmd.Parameters.Add("@p_ROLL",sRollNos );

What is the value of sRollNos? Is it what you expect it to be?

Gary

[sonia.sardana]

Hi Gep,I am able to call SP correctly . see in Fig wat I have in sRollNos.
Basically I want to Execute the query
Delete from TBL_STUDENT where roll in (1,2,3) FROM ASP.Net.
But as Roll is of type Number, & in sRollNos I have string value,dats Y error is dere.

[gep13]

So, to be clear, if you only add one number as a parameter, does the stored procedure work?

Gary

[sonia.sardana]

Yes, If I add one number as parameter,RECORD IS DELETED FROM THE DATABASE.
In mY case I my passing 1,2 more than two number..I want to delete two records usin Comma Separated Values.

[gep13]

Ah, I see what you mean.

The problem here is that your stored procedure seems to be expecting a number, and when you give it "1,2" it can't do the conversion.

I am not sure even that what you are asking is possible.

I think this question might be better answered in the Database Development Forum.

Gary