Delete from TABLE using Comma Separted Values

[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!!!

[leinad31]

p_roll is treated as ONE parameter. It may be a list of values to you but to the SQL parser it just one parameter, and an invalid one at that (it expects a number, not a string). What you are trying to accomplish (SQL string created at runtime, and is NOT precompiled) is called dynamic SQL. Read up on that but please note that this is prone to SQL injection.

Other ways of doing this (not via dynamic SQL and execute immediate):

- pass a collection (number array at front-end converted to corresponding collection type in oracle). Disadvantage is complexity from working with oracle collection type at front-end and additional handling for null arrays at front-end code.

- use working table or temp table. insert data there then rewrite SQL accordingly: DELETE FROM tbl_student WHERE roll IN (SELECT roll FROM wrk_table). Disadvantage is additional code to maintain wrk_table data.

- use a package, overload USP_DEL_TBL_STUDENT_DATA procedure. Each variant accepts different number of roll. At front-end call appropriate subprogram variant, e.g. if parameter values come from an array check size of array, if array size is 4 then call SAMPLE_PKG.USP_DEL_TBL_STUDENT_DATA(p_roll1 NUMBER, p_roll2 NUMBER, p_roll3 NUMBER, p_roll4 NUMBER). Subprograms can simply call variants with less number of parameters http://www.vbforums.com/showpost.php...63&postcount=4

- use XML or advance queuing technology of oracle. But this is overkill for such a simple process.