I am wrote stored procedure to Insert data into database table but i am not getting how to pass datatable to stored procedure kindly tell how to use it.
below is my storedprocedure
CREATE OR REPLACE PROCEDURE PR_SREE_TEST(p_recordset In SYS_REFCURSOR) IS
Contrac_rc SREE_TEST%rowtype;
BEGIN
Loop
Fetch p_recordset Into Contrac_rc;
EXIT WHEN p_recordset%NOTFOUND;
Insert into SREE_TEST(CT,DESC,FLAG)
Values(Contrac_rc.CT,Contrac_rc.DESC,Contrac_rc.FLAG);
End Loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END PR_SREE_TEST;
/
and cs page
public DataSet sreetest(DataTable dt)
{
DataSet dsRegularIndentdtl = new DataSet();
try
{
OracleConnection OraConn = new OracleConnection(strDBConnection);
OraConn.Open();
OracleCommand OraCmd = new OracleCommand();
OraCmd.Connection = OraConn;
OraCmd.CommandText = "PR_SREE_TEST";
OraCmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter();
var recordSet1 = new DataTable();
recordSet1 = dt;
OraCmd.Parameters.Add("p_recordset", OracleDbType.RefCursor, recordSet1, ParameterDirection.Input);
OraCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
return dsRegularIndentdtl;
}
}
above is my code it is saying that p_recordset not valied. please tell me how to execute it.