I try to execute this script in C# :
declare
NEW_PAP_OPERATOR_ID number;
cursor cu_records_a_traiter is
select *
from operator_dossier d,
pap_operator_verandering ov,
pap_verandering v
where d.operator_id = ov.operator_id
and v.cr_info = :v_cr_info;
begin
for rec_cu in cu_records_a_traiter loop
/* insert new record */
INSERT INTO BOOD.PAP_OPERATOR
(HOOFD_OF_NEVENACTIVITEIT)
VALUES
(rec_cu.HOOFD_OF_NEVENACTIVITEIT);
SELECT BOOD.SEQ_PAP_OPERATOR_ID.CURRVAL
into NEW_PAP_OPERATOR_ID
FROM dual;
/* update with new record*/
UPDATE Bood.Pap_Operator_Verandering
SET pap_operator_doel_id = NEW_PAP_OPERATOR_ID,
datum_wijziging = Sysdate,
gebruiker_wijziging_id = '-549'
WHERE pap_operator_verandering_id = rec_cu.PAP_OPERATOR_VERANDERING_ID;
end loop;
end;
No exception but the script is not running. If I execute sql script without C#, it works. I think there is a problem with currval NEW_PAP_OPERATOR_ID For the moment I don't anything special for it in c #.
C# code :
try
{
OracleCommand command = new OracleCommand("myScript", Connection);
command.Parameters.Add("v_cr_info", OracleDbType.VarChar, changeRequest, ParameterDirection.Input);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
Connection.Close();
}
You are not showing PROC parameter and also you didn't specify your command is PROC