1

I'm trying to call a simple stored procedure on Oracle DB which takes a Udt (User defined type) as it's input and produces a simple varchar2 output.

However there is obviously something wrong in my code as I keep on getting following error:

Invalid parameter binding
   at Oracle.DataAccess.Client.OracleParameter.PreBind_OracleObject(OracleConnection conn)
   at Oracle.DataAccess.Client.OracleParameter.PreBind_Object(OracleConnection conn)
   at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
   at Oracle.DataAccess.Client.OracleCommand.ExecuteScalar()

I have been reading through Oracles documentation
https://docs.oracle.com/database/121/ODPNT/extenRest.htm#ODPNT453,
https://docs.oracle.com/cd/E14435_01/win.111/e10927/featUDTs.htm#CJAHJHGE and
https://docs.oracle.com/cd/E14435_01/win.111/e10927/IOracleCustomTypeInterface.htm#BABHGCIG and googling various sources:
https://www.codeproject.com/Articles/141728/Interaction-between-C-Application-and-Oracle-throu
Invalid parameter name binding oracle UDT
C# Call Oracle Stored Procedure with nested UDT
for clues what could be wrong but no luck.

My stored procedure has the the following declaration:

PROCEDURE Test2(TEST2_IN_o IN TEST2_IN, errortext_out OUT varchar2); 

and type:

create or replace TYPE TEST2_IN AS OBJECT (
    FIELD1 VARCHAR2 (50 Byte),      
    FIELD2 VARCHAR2 (50 Byte),     
    FIELD3 VARCHAR2 (20 Byte),   
    USER_ID VARCHAR2(20)           
) NOT FINAL

Here is my object mapping:

namespace OracleProcedureTest
{
    [OracleCustomTypeMapping("TEST2_IN")]
    public class TEST2_IN : IOracleCustomType, INullable
    {
        public TEST2_IN(string Field1, string Field2, string Field3, string UserID)
        {
            FIELD1 = Field1;
            FIELD2 = Field2;
            FIELD3 = Field3;
            USER_ID = UserID;
        }

        [OracleObjectMapping("FIELD1")]
        public string FIELD1 { get; set; }

        [OracleObjectMapping("FIELD2")]
        public string FIELD2 { get; set; }

        [OracleObjectMapping("FIELD3")]
        public string FIELD3 { get; set; }

        [OracleObjectMapping("USER_ID")]
        public string USER_ID { get; set; }

        public bool IsNull { get; set; }

        public static TEST2_IN Null => new TEST2_IN { IsNull = true };

        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, "FIELD1", FIELD1);
            OracleUdt.SetValue(con, pUdt, "FIELD2", FIELD2);
            OracleUdt.SetValue(con, pUdt, "FIELD3", FIELD3);
            OracleUdt.SetValue(con, pUdt, "USER_ID", USER_ID);
        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            FIELD1 = ((string)(OracleUdt.GetValue(con, pUdt, "FIELD1")));
            FIELD2 = ((string)(OracleUdt.GetValue(con, pUdt, "FIELD2")));
            FIELD3 = ((string)(OracleUdt.GetValue(con, pUdt, "FIELD3")));
            USER_ID = ((string)(OracleUdt.GetValue(con, pUdt, "USER_ID")));
        }
    }
}

And here is the code that makes the call:

public static void RunTestFunction2(OracleConnection connection)
        {
            OracleTransaction transaction = null;
            OracleCommand cmd = null;

            //Start transaction
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            //Create the command
            cmd = new OracleCommand();
            cmd.Connection = connection;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "MyPackage.Test2";

            //Create the input object
            TEST2_IN inputObject = new TEST2_IN("Hello", "world", "OracleDB ", "123456");

            //Create the input parameter
            OracleParameter parameter_in = cmd.CreateParameter();
            parameter_in.OracleDbType = OracleDbType.Object;
            parameter_in.Direction = ParameterDirection.Input;
            parameter_in.ParameterName = "TEST2_IN_o";
            parameter_in.UdtTypeName = "TEST2_IN";
            parameter_in.Value = inputObject;
            cmd.Parameters.Add(parameter_in);

            //Create output parameter
            OracleParameter parameter_out = cmd.Parameters.Add("errortext_out", OracleDbType.Varchar2, 200);
            parameter_out.Direction = ParameterDirection.Output;

            //Run command and print result.
            Oracle.DataAccess.Types.OracleString oString;

            cmd.ExecuteNonQuery();
            oString = (Oracle.DataAccess.Types.OracleString)parameter_out.Value;

            Console.WriteLine("Return message was '" + oString.Value + "'.");
        }

Any help is appreciated.

I am aware that Oracle tools for Visual Studio allows creation of these objects from the Server Explorer but I could not get this to work. I have developer tools installed but I can only see managed driver which does not allow creation Udt-classes.

  • Why are you using `ExecuteScalar`, that's only meant for Scalar operations like Min, Max, Average and more over for procedure execution you shall use ExecuteNonQuery for the DML and ExecuteReader for the data fetch operations. Also why the parameter for the UDT doesn't specify its name anywhere, which shall be `TEST2_IN_o` – Mrinal Kamboj Aug 30 '18 at 10:50
  • I changed the code to use 'ExecuteScalar' and I added the parameter name but the problem still persists. – Matti Lindroth Aug 30 '18 at 11:01
  • Please edit your code to show what you are doing and I mentioned not using `ExecuteScalar` – Mrinal Kamboj Aug 30 '18 at 11:13
  • Updated as requested – Matti Lindroth Aug 30 '18 at 11:35

1 Answers1

1

Found the problem, and like so many times before it was between my keyboard and my screen:

I had a struct in my solution named TEST2_IN. Plan was to test with struct as well as classes. However oracle's reflection programming caught that type as the type to be used instead of the class I presented above, even though I had not assigned any attributes to the struct. So after removing the struct the code above worked as planned !

  • It was only possible for you to figure out this issue, great – Mrinal Kamboj Aug 30 '18 at 13:32
  • I am trying to do the same but getting Invalid provider, I've added the references through the nuget. Any idea how can i fix my error? – Ali Umair Sep 15 '18 at 08:10
  • Ali I used [Oracle tools for visual studio 2017](http://www.oracle.com/technetwork/topics/dotnet/downloads/odacmsidownloadvs2017-3806459.html). I had some trouble with oracle nuget packages and also note that UDT's do not work with managed drivers (https://docs.oracle.com/database/121/ODPNT/featUDTs.htm#ODPNT379). – Matti Lindroth Sep 16 '18 at 18:10