OracleUdt.SetValue(con, pUdt, "COUNTRY", Country) OracleUdt.SetValue(con, pUdt, "ZIP", Zip) OracleUdt.SetValue(con, pUdt, "STATE", State) OracleUdt.SetValue(con, pUdt, "CITY", City) OracleUdt.SetValue(con, pUdt, "ADDRESS2", Address2) OracleUdt.SetValue(con, pUdt, "ADDRESS1", Address1) OracleUdt.SetValue(con, pUdt, "COMPANYNAME", CompanyName) OracleUdt.SetValue(con, pUdt, "COMPANYID", CompanyId) Convert from the Custom Type to Oracle Object Public void FromCustomObject( con, IntPtr pUdt) Public class CompanyInfo : INullable, IOracleCustomType ** A custom type must implement INullable and IOracleCustomType interfaces ** An instance of a CompanyInfo class represents an CompanyInfo object This should also implement OracleCustomTypeFactory. CompanyInfoListFactory - This class is required by ODP.NET to create custom objects that represent oracle custom objects and collections.This class has an array of CompanyInfo property to capture the CompanyInfo object list. This class also implements INullable, IOracleCustomType interfaces. This type is used to pass a list of COMPANYINFO objects. CompanyInfoList -This class to represent Oracle type " COMPANYINFOLIST ".This should implement IOracleCustomTypeFactory. CompanyInfoFactory - This class is required by ODP.NET to create custom objects that represent oracle custom objects and collections.Implement the INullable method IsNull and IOracleCustomType methods FromCustomObject, ToCustomObject. This class needs to inherit following interfaces - INullable, IOracleCustomType. CompanyInfo - This class to represent Oracle type "COMPANYINFO".This dll is located in oracle home directory as shown. Open Visual Studio and create a new sample project " OracleUDTSample "Īdd reference to to the project. Select column_value as compName from table(cast (results as stringSet)) įollowing type is used by the procedure to add company names back to the return cursorĬREATE OR REPLACE TYPE "STRINGSET" is table of varchar(32000) Results(unt) := companyInfoList(i).companyName dbms_output.put_line(companyInfoList(i).companyId)
Procedure GetCompanyInfoArray(companyInfoList in COMPANYINFOLIST, resultCursor out sys_refcursor) isįor i in companyInfoList.first. Prerequisites: ODP.NET 11g, Visual Studio 2005 / 2008 / 2010įollowing are the UDT declarations in OracleĬreate or replace type "COMPANYINFO" AS OBJECTĬreate or replace type "COMPANYINFOLIST" is table of COMPANYINFO Net Object and another UDT as a table of these objects. I had to create a User Defined Type (UDT) / Custom Type in Oracle that represents my. And I found the following way to accomplish this.
Finale 2014 serial number blogspot update#
I did not want to loop through and update each record as I wanted to reduce the number of network round trips. Net custom objects and I want to pass this list to an Oracle procedure to process them. Introduction: I came across a situation where I have a list of.