C # DataTable for Oracle Stored Procedure

Question

What I need to do is pass the C # DataTable to the Oracle stored procedure.

Here is what I did:

Oracle side:

• Type created:

  create or replace TYPE CUSTOM_TYPE AS OBJECT ( attribute1 VARCHAR(10), attribute2 VARCHAR(10) ); 

• Created a table

   create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE; 

• Stored procedure created

   create or replace PROCEDURE SP_TEST ( P_TABLE_IN IN CUSTOM_TYPE_ARRAY, P_RESULT_OUT OUT SYS_REFCURSOR ) AS --P_TABLE_IN CUSTOM_TYPE_ARRAY; BEGIN OPEN P_RESULT_OUT FOR SELECT attribute1, attribute2 FROM TABLE(P_TABLE_IN); END SP_TEST; 

C # side:

 void Run() { OracleConnection oraConn = new OracleConnection(); oraConn.ConnectionString = ConfigurationManager.ConnectionStrings["NafasV2ConnectionString"].ToString(); DataSet dataset = new DataSet(); DataTable Dt = new DataTable(); OracleDataAdapter da = new OracleDataAdapter(); OracleCommand cmd = new OracleCommand(); try { FormTVP(ref Dt); PopulateTVP(ref Dt); oraConn.Open(); cmd.Connection = oraConn; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "SP_TEST"; OracleParameter parm1 = new OracleParameter("P_TABLE_IN", OracleDbType.RefCursor,100,"xx"); parm1.Value = Dt; parm1.Direction = ParameterDirection.Input; cmd.Parameters.Add(parm1); OracleParameter parm2 = new OracleParameter("P_RESULT_OUT", OracleDbType.RefCursor); parm2.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm2); da.SelectCommand = cmd; da.Fill(dataset); ASPxLabel1.Text = "OK!!"; } catch (Exception ex) { ASPxLabel1.Text = "DIE. REASON: " + ex.Message; } finally { da.Dispose(); cmd.Dispose(); oraConn.Close(); oraConn.Dispose(); } } void FormTVP(ref DataTable Dt) { DataColumn attribute1 = Dt.Columns.Add("ATTRIBUTE1", typeof(String)); DataColumn attribute2 = Dt.Columns.Add("ATTRIBUTE2", typeof(String)); Dt.AcceptChanges(); } void PopulateTVP(ref DataTable Dt) { DataRow Dr = Dt.NewRow(); Dr["ATTRIBUTE1"] = "MK1"; Dr["ATTRIBUTE2"] = "MK2"; Dt.Rows.Add(Dr); DataRow Dr1 = Dt.NewRow(); Dr1["ATTRIBUTE1"] = "HH1"; Dr1["ATTRIBUTE2"] = "HH2"; Dt.Rows.Add(Dr1); Dt.AcceptChanges(); } 

But I get an error message:

Incorrect parameter binding Parameter name: P_TABLE_IN

HELP!

Answer 1

DataTable cannot be bound directly. You need to create your own class for any UDT that you want to access with .NET. Here I made a simple example of how to map DataTable to UDT in general:

 void Main() { var dataTable = BuildSourceData(); using (var connection = new OracleConnection("DATA SOURCE=hq_pdb_tcp;PASSWORD=oracle;USER ID=HUSQVIK")) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = "BEGIN HUSQVIK.SP_TEST(:P_TABLE_IN, :P_RESULT_OUT); END;"; command.BindByName = true; var p1 = command.CreateParameter(); p1.ParameterName = "P_TABLE_IN"; p1.OracleDbType = OracleDbType.Array; p1.UdtTypeName = "HUSQVIK.CUSTOM_TYPE_ARRAY"; p1.Value = ConvertDataTableToUdt<CustomTypeArray, CustomType>(dataTable); command.Parameters.Add(p1); var p2 = command.CreateParameter(); p2.Direction = ParameterDirection.Output; p2.ParameterName = "P_RESULT_OUT"; p2.OracleDbType = OracleDbType.RefCursor; command.Parameters.Add(p2); command.ExecuteNonQuery(); using (var reader = ((OracleRefCursor)p2.Value).GetDataReader()) { var row = 1; while (reader.Read()) { Console.WriteLine($"Row {row++}: Attribute1 = {reader[0]}, Attribute1 = {reader[1]}"); } } } } } private DataTable BuildSourceData() { var dataTable = new DataTable("CustomTypeArray"); dataTable.Columns.Add(new DataColumn("Attribute1", typeof(string))); dataTable.Columns.Add(new DataColumn("Attribute2", typeof(string))); dataTable.Rows.Add("r1 c1", "r1 c2"); dataTable.Rows.Add("r2 c1", "r2 c2"); return dataTable; } public static object ConvertDataTableToUdt<TUdtTable, TUdtItem>(DataTable dataTable) where TUdtTable : CustomCollectionTypeBase<TUdtTable, TUdtItem>, new() where TUdtItem : CustomTypeBase<TUdtItem>, new() { var tableUdt = Activator.CreateInstance<TUdtTable>(); tableUdt.Values = (TUdtItem[])tableUdt.CreateArray(dataTable.Rows.Count); var fields = typeof(TUdtItem).GetFields(); for (var i = 0; i < dataTable.Rows.Count; i++) { var itemUdt = Activator.CreateInstance<TUdtItem>(); for (var j = 0; j < fields.Length; j++) { fields[j].SetValue(itemUdt, dataTable.Rows[i][j]); } tableUdt.Values[i] = itemUdt; } return tableUdt; } [OracleCustomTypeMapping("HUSQVIK.CUSTOM_TYPE_ARRAY")] public class CustomTypeArray : CustomCollectionTypeBase<CustomTypeArray, CustomType> { } [OracleCustomTypeMapping("HUSQVIK.CUSTOM_TYPE")] public class CustomType : CustomTypeBase<CustomType> { [OracleObjectMapping("ATTRIBUTE1")] public string Attribute1; [OracleObjectMapping("ATTRIBUTE2")] public string Attribute2; public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt) { OracleUdt.SetValue(connection, pointerUdt, "ATTRIBUTE1", Attribute1); OracleUdt.SetValue(connection, pointerUdt, "ATTRIBUTE2", Attribute2); } public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt) { Attribute1 = (string)OracleUdt.GetValue(connection, pointerUdt, "ATTRIBUTE1"); Attribute2 = (string)OracleUdt.GetValue(connection, pointerUdt, "ATTRIBUTE2"); } } public abstract class CustomCollectionTypeBase<TType, TValue> : CustomTypeBase<TType>, IOracleArrayTypeFactory where TType : CustomTypeBase<TType>, new() { [OracleArrayMapping()] public TValue[] Values; public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt) { OracleUdt.SetValue(connection, pointerUdt, 0, Values); } public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt) { Values = (TValue[])OracleUdt.GetValue(connection, pointerUdt, 0); } public Array CreateArray(int numElems) { return new TValue[numElems]; } public Array CreateStatusArray(int numElems) { return null; } } public abstract class CustomTypeBase<T> : IOracleCustomType, IOracleCustomTypeFactory, INullable where T : CustomTypeBase<T>, new() { private bool _isNull; public IOracleCustomType CreateObject() { return new T(); } public abstract void FromCustomObject(OracleConnection connection, IntPtr pointerUdt); public abstract void ToCustomObject(OracleConnection connection, IntPtr pointerUdt); public bool IsNull { get { return this._isNull; } } public static T Null { get { return new T { _isNull = true }; } } } 

The ConvertDataTypeToUdt function is common, and if you provide the appropriate class, it automatically matches the data table. The next step would be to automate the entire mapping so that the target data type is determined by the data table itself. "HUSQVIK" in custom type attributes is the name of the schema, and it must match your database if you are not connecting as the owner of the schema containing your custom types.