C# : Getting error ORA-06550 while updating BLOB using stored procedure & ODP.NET

Question

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'UPDATEPHOTO'
ORA-06550: line 1, column 7: PL/SQL: Statement ignoredOracle.ManagedDataAccess.Client.OracleException

I don't know what is wrong either with procedure or my code.

Here's my stored procedure

create or replace 
PROCEDURE UpdatePhoto
(
  v_ac_photo_fileName IN VARCHAR2 DEFAULT NULL ,
  v_ac_photo_contentType IN VARCHAR2 DEFAULT NULL ,
  v_ac_photo_Data IN BLOB DEFAULT NULL ,
  v_ac_uniqueID IN VARCHAR2 DEFAULT NULL 
)
AS

BEGIN
   UPDATE account_table
      SET ac_photo_fileName = v_ac_photo_fileName,
          ac_photo_contentType = v_ac_photo_contentType,
          ac_photo_Data = v_ac_photo_Data
      WHERE ac_uniqueID = v_ac_uniqueID;
END;

Here's my C# code:

public int UpdatePhoto(BO nBo)
{
    OracleConnection ocon = new OracleConnection(orastr);
    OracleCommand ocmd = new OracleCommand("UpdatePhoto", ocon);
    ocmd.CommandType = CommandType.StoredProcedure;

    ocon.Open();

    try
    {
        ocmd.Parameters.Add("ac_uniqueID", nBo.account_uniqueID);//String
        ocmd.Parameters.Add("ac_photo_fileName", nBo.account_photo_fileName);//string
        ocmd.Parameters.Add("ac_photo_contentType",   nBo.account_photo_contentType);//string
        ocmd.Parameters.Add("ac_photo_Data", nBo.account_photo_Data);// (Byte[] photo data)       

        // tried these also
        ocmd.Parameters.Add("ac_uniqueID", OracleDbType.Varchar2, ParameterDirection.Input).Value = nBo.account_uniqueID;
        ocmd.Parameters.Add("ac_photo_fileName", OracleDbType.Varchar2, ParameterDirection.Input).Value = nBo.account_photo_fileName;
        ocmd.Parameters.Add("ac_photo_contentType", OracleDbType.Varchar2, ParameterDirection.Input).Value = nBo.account_photo_contentType;
        ocmd.Parameters.Add("ac_photo_Data", OracleDbType.Blob, ParameterDirection.Input).Value = nBo.account_photo_Data;

        return ocmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        ocon.Dispose();
        ocon.Close();
        nBo = null;
    }
}

The parameter names in your Oracle stored procedure are v_ac_photo_fileName, v_ac_photo_contentType and so forth, but in your C# code, you use totally different names!! Those names must match! — marc_s
It didn't give me error while I was updating other fields which were not named as same as in Oracle Stored Procedure. — Rex Jones
You probably passed the parameters in the same order they were expected. Either make the names match EXACTLY, or at least make sure you add you parameters to the OracleCommand in the exact order they're expected by your stored procedure. — Eric Walker
Thanks @EricWalker, It only worked when passed in order and as the same name expected in procedure! — Rex Jones

Hambone Hambone · Accepted Answer · 2016-08-12T03:45:12

Check out this post:

How to Update a BLOB column, error ORA-00932, while Insert works

I can't say for sure this is your issue, but per this post if you have BLOBs as parameters, you need to list them first in the parameter list for this to work. I have no idea why, but at the time I tried it both ways, and sure enough it worked when the BLOB was first, and it did not when it wasn't. I know, it makes no sense.

Alternatively, if you set the BindByName property of your OracleCommand object to true, this should also work. I'd try both to be sure.

One caveat: the post I referenced is for an insert function, not a stored procedure, so it's possible there is a difference.

C# : Getting error ORA-06550 while updating BLOB using stored procedure & ODP.NET

1 Answers