开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜