Oracle Update Stored Proc not working from C# code
I have the following code:
try
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "collikx.webadmin.usp_update_license";
cmd.Parameters.Add(new OracleParameter("licenseSeqNo", OracleDbType.Int32, 30, 228, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("thirdPartyId", OracleDbType.Int32, 30, 2, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("licenseNumber", OracleDbType.Varchar2, 12, "tttttttttttt", ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("licenseState", OracleDbType.Char, 2, "CT", ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("licenseLevel", OracleDbType.Varchar2, 2, "E1", ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("licenseType", OracleDbType.Char, 2, "QQ", ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("cutTap", OracleDbType.Char, 1, "Y", ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("status", OracleDbType.Varchar2, 30, "NOTVALID", ParameterDirection.Input));
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
throw;
}
finally { conn.Close(); }
The stored proc is:
PROCEDURE usp_update_license( thirdPartyId integer,
licenseSeqNo integer,
licenseNumber VARCHAR2,
开发者_如何学Python licenseState VARCHAR2,
licenseLevel VARCHAR2,
licenseType VARCHAR2,
cutTap VARCHAR2,
status VARCHAR2) AS
begin
update CNTRCTR_LCNS_INFO
set cntrctr_lcns_no = licenseNumber,
lcns_st_cd = licenseState,
certfn_level_type_cd = licenseLevel,
cntrctr_type_cd = licenseType,
cut_tap_authy_ind = cutTap,
stat_type_nm = status
where third_party_id = thirdPartyId and cntrctr_lcns_seq_no = licenseSeqNo
;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end usp_update_license;
When I execute this, nothing in the database changes. I know it's getting called from C# because if i comment out one of the parameter lines I get an error that there are not enout params. However, the value "QQ" for licenseType in the C# code should violate a FK contraint but I get no error telling me that. I have other stored procs called exactly the same way that work fine...
I should note that executing the stored proc directly through sql developer works.
What am i doing wrong?
You have the parameters out of order:
switch
cmd.Parameters.Add(new OracleParameter("licenseSeqNo", OracleDbType.Int32, 30, 228, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("thirdPartyId", OracleDbType.Int32, 30, 2, ParameterDirection.Input));
to
cmd.Parameters.Add(new OracleParameter("thirdPartyId", OracleDbType.Int32, 30, 2, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("licenseSeqNo", OracleDbType.Int32, 30, 228, ParameterDirection.Input));
You are not getting the where clause to match (binds by order by default) in ODP you can change this .BindByName = True to overwrite the default BindByName =false
EDIT
You will want to
cmd.BindByName = True;
(this defaults to FALSE and causes the confusion and problem!)
May be the Where Clause is not matching...
Try executing the store procedure from Oracle itself.
精彩评论