CODEWITHSUNDEEP
c#oracle-databasestored-proceduresuser-defined-types
Matti Lindroth
Matti Lindroth

Reputation: 53

Why do I get "Invalid Parameter binding" when using Oracle stored procedure with Udt and C#

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.

Upvotes: 1

Views: 11057

Answers (1)

Matti Lindroth
Matti Lindroth

Reputation: 53

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 !

Upvotes: 1

Related Questions