Home » Infrastructure » Windows » ODP.net passing grid data to SP UDT parameter - C# (Oracle 11.0.2.10, Window Server 2008 R2 Enterprise, Visual Studio 2010, IIS 6)
ODP.net passing grid data to SP UDT parameter - C# [message #666130] Tue, 17 October 2017 03:00
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I am new to using ODP.net and shifting from Java to C# and have some questions.
In the following example
from DB side: I have an Oracle object type, a defined table, and a SP that takes a UDT as an input to insert it in a table. from Application side: I have a simple app with a button to fill data from DB to a grid and another button that should get this data and pass it to the SP that accepts ODT.

DB code:
create table test_MTable 
  (
   Mtype_id number(10) ,
   MType_Name VARCHAR2(50)
);

create or replace TYPE My_type AS OBJECT
(
   Mtype_id number (10),
   MType_Name VARCHAR2 (50)
);

create or replace TYPE table_Mtype AS TABLE OF My_type;

create or replace PROCEDURE test_Insert_MType (
                            MType_items table_Mtype,
                            confirmation_num OUT VARCHAR2)
   IS
  confirmation_num1 varchar2(50);
begin

     for i in 1..MType_items.count loop
      INSERT INTO test_MTable (Mtype_id, Mtype_Name) 
      SELECT MType_items(i).Mtype_id, MType_items(i).Mtype_Name
        FROM DUAL ;    
     end loop;

  confirmation_num1:= '1';
 end;

-- The following SP is just for testing test_Insert_MType  
create or replace PROCEDURE test_call_ins_MType (confirmation_num OUT VARCHAR2)
   IS
  confirmation_num1 varchar2(50);
  rec_MyType SYS_REFCURSOR;
  tab_MyType table_Mtype;
begin
  open rec_MyType for
    select My_type(number0, name) from employee;
 
  fetch rec_MyType bulk collect into tab_MyType;

  test_Insert_MType(tab_MyType,confirmation_num1);
 
  confirmation_num1:= '1';
 end;


Application code:
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


namespace AcquisitionMain
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 

        }

        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code, 
            // you can retrieve it from a configuration file. 
            return "Data Source=xxx;Persist Security Info=True;" +
                   "User ID=xxxxxx;Password=xxxxxxxx";
        }


        protected void Button_Ora_Click(object sender, EventArgs e)
        {
            try
            {
                OracleConnection con;
                con = new OracleConnection();

                con.ConnectionString = GetConnectionString();

                con.Open();

                string sql = "SELECT NUMBER0, NAME FROM employee";
                OracleCommand cmd = new OracleCommand(sql, con);
                cmd.CommandType = CommandType.Text;

                using (OracleDataReader oReader = cmd.ExecuteReader())
                {
                    DataTable dtQuery = new DataTable();
                    dtQuery.Load(oReader);
                    grdMainGrid.DataSource = dtQuery;
                    grdMainGrid.DataBind();
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine("Oracle Exception Message");
                Console.WriteLine("Exception Message: " + ex.Message);
                Console.WriteLine("Exception Source: " + ex.Source);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception Message");
                Console.WriteLine("Exception Message: " + ex.Message);
                Console.WriteLine("Exception Source: " + ex.Source);
            }
        }

        protected void Button_Insert_Click(object sender, EventArgs e)
        {
            DataTable dt_Employee = grdMainGrid.DataSource as DataTable;
            
            OracleConnection con = new OracleConnection();
            con.ConnectionString = GetConnectionString();
            con.Open();
            
            OracleCommand cmd = con.CreateCommand();

            cmd.CommandText = "test_Insert_MType";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            OracleParameter Oparameter1 = cmd.CreateParameter();
            Oparameter1.Direction = ParameterDirection.Input;
            Oparameter1.ParameterName = "MType_items";
            Oparameter1.OracleDbType = OracleDbType.Array;
            Oparameter1.UdtTypeName = "SCHEMA.TABLE_MTYPE";  --<----------Fails to locate the type 
                                                               -----------reporting TABLE_MTYPE can not be found
            Oparameter1.Value = dt_Employee;
            cmd.Parameters.Add(Oparameter1);

            OracleParameter Oparameter2= cmd.CreateParameter();
            Oparameter2.Direction = ParameterDirection.Output;
            Oparameter2.ParameterName = "confirmation_num";
            Oparameter2.OracleDbType = OracleDbType.Varchar2;
            cmd.Parameters.Add(Oparameter2);

            cmd.ExecuteNonQuery();

        }

    }
}

My two questions are:
1- Why cant I pass the UDT this way without having to create the below class and methods on the app side? why doesn't Oracle UDT work the same way as passing other parameter types? Is there another type (other than OracleUDT can be used to pass datatable type as an Oracle UDT)?:
    public class EmployeeInfo : INullable, IOracleCustomType
    {
        private bool objectIsNull;

        [OracleObjectMappingAttribute("NUMBER0")]
        public string EmployeeId { get; set; }

        [OracleObjectMappingAttribute("NAME")]
        public string EmployeeName { get; set; }

        public static EmployeeInfo Null
        {
            get
            {
                EmployeeInfo Employee = new EmployeeInfo();
                Employee.objectIsNull = true;
                return Employee;
            }
        }

        public bool IsNull
        {
            get { return objectIsNull; }
        }

        public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            // Convert from the Custom Type to Oracle Object
            if (!string.IsNullOrEmpty(EmployeeId))
            {
                OracleUdt.SetValue(con, pUdt, "NUMBER0", EmployeeId);
            }
            if (!string.IsNullOrEmpty(EmployeeName))
            {
                OracleUdt.SetValue(con, pUdt, "NAME", EmployeeName);
            }
        }

        public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            EmployeeId = (string)OracleUdt.GetValue(con, pUdt, "NUMBER0");
            EmployeeName = (string)OracleUdt.GetValue(con, pUdt, "NAME");

        }
    }

    [OracleCustomTypeMappingAttribute("AFESD2.My_type")]
    public class EmployeeInfoFactory : IOracleCustomTypeFactory
    {
        public IOracleCustomType CreateObject()
        {
            return new EmployeeInfo();
        }


    }

    --EmployeeInfoList Class  
    public class EmployeeInfoList : INullable, IOracleCustomType
    {
        [OracleArrayMapping()]
        public EmployeeInfo[] EmployeeInfoArray;

        private bool objectIsNull;


        public bool IsNull
        {
            get { return objectIsNull; }
        }

        public static EmployeeInfoList Null
        {
            get
            {
                EmployeeInfoList obj = new EmployeeInfoList();
                obj.objectIsNull = true;
                return obj;
            }
        }

        public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, EmployeeInfoArray);
        }

        public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            EmployeeInfoArray = (EmployeeInfo[])OracleUdt.GetValue(con, pUdt, 0);
        }

        [OracleCustomTypeMapping("AFESD2.TABLE_MTYPE")]
        public class EmployeeInfoListFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
        {
            #region IOracleCustomTypeFactory Members
            public IOracleCustomType CreateObject()
            {
                return new EmployeeInfoList();
            }

            public Array CreateArray(int numElems)
            {
                return new EmployeeInfo[numElems];
            }

            public Array CreateStatusArray(int numElems)
            {
                return null;
            }

        }
    }

2- Even after I add the class and method definition I still get an exception "ORA-06531: Reference to uninitialized collection" when executing
 cmd.ExecuteNonQuery();

Thanks,
Ferro

[Updated on: Tue, 17 October 2017 03:01]

Report message to a moderator

Previous Topic: Start using ODP.net
Next Topic: Drop/Delete an instance
Goto Forum:
  


Current Time: Thu Mar 28 06:59:11 CDT 2024