I am mapping Oracle user defined types to .Net custom types by Oracle Data access unmanaged assembly.
I have followed the article
https://www.codeproject.com/Articles/1261430/Calling-Oracles-Stored-Procedures-and-Mapping-User
I had applied simple scenarios.
1. Created Oracle UDT at schema level i.e.
CREATE OR REPLACE TYPE STUDENT_TYP IS OBJECT (
STUDENT_ID NUMBER,
STUDENT_NAME VARCHAR(20),
ADDRESS VARCHAR(20),
AGE NUMBER,
BIRTH_DATE DATE
);
CREATE OR REPLACE TYPE STUDENT_TYP_TBL IS TABLE OF STUDENT_TYP;
and here is the Procedure GET_ALL_STUDENTS body
PROCEDURE GET_ALL_STUDENTS(P_STUDENTS OUT STUDENT_TYP_TBL)
AS
BEGIN
SELECT *
BULK COLLECT INTO P_STUDENTS
FROM
(
SELECT STUDENT_TYP(s.student_id,
s.student_name,
s.address,
s.age,
s.birth_date)
FROM
student s) ;
END;
Above example is working fine. Above scenario just to explain there is no issue in implementation
2. Now I defined types inside a package e.g.
CREATE OR REPLACE PACKAGE STUDENT_PKG
AS
TYPE PHONE_TYP_RC IS RECORD (
PHONE_ID STUDENT_PHONE.PHONE_ID%TYPE,
PHONE_TYPE STUDENT_PHONE.PHONE_TYPE%TYPE,
PHONE_NUMBER STUDENT_PHONE.PHONE_NUMBER%TYPE
);
TYPE PHONE_RC_TBL IS TABLE OF PHONE_TYP_RC;
PROCEDURE GET_ALL_PHONES(P_PHONES_TBL OUT STUDENT_PKG.PHONE_RC_TBL);
END;
Following is STUDENT_PKG body
create or replace PACKAGE BODY STUDENT_PKG
AS
PROCEDURE GET_ALL_PHONES(P_PHONES_TBL OUT STUDENT_PKG.PHONE_RC_TBL)
IS
BEGIN
SELECT PH.PHONE_ID, PH.PHONE_TYPE, PH.PHONE_NUMBER
BULK COLLECT INTO P_PHONES_TBL
FROM STUDENT_PHONE PH;
END;
END;
When I add Oracle parameter with all information. I received an Oracle Exception
"OCI-22303: type \"STUDENT_PKG\".\"PHONE_RC_TBL\" not found"
I had already defined the Oracle custom mapping with name STUDENT_PKG.PHONE_RC_TBL
I already tried with following names
- STUDENT_PKG.PHONE_RC_TBL
- STUDENT_PKG
- PHONE_RC_TBL
When I execute
OracleCommandBuilder.DeriveParameters(command);
to get the Command text parameters I am receiving the following information
Parameter Name: P_PHONES_TBL
OracleDbType: Array
OracleDbTypeEx: Array
UdtTypeName: KAMI.STUDENT_PKG
DbType: Object
and when i execute I get following exception
OCI-22303: type "KAMI"."STUDENT_PKG" not found
This means there is something tricky to handle the package's udt mapping to .Net class.
Here is my definition of .Net custom class for *P_PHONES_TBL* Parmeter
public abstract class CustomTypeBase<T> :
IOracleCustomType, IOracleCustomTypeFactory, INullable where T : CustomTypeBase<T>, new()
{
private bool _isNull;
public bool IsNull
{
get { return this._isNull; }
}
public static T Null
{
get { return new T { _isNull = true }; }
}
public IOracleCustomType CreateObject()
{
return new T();
}
public abstract void FromCustomObject(OracleConnection con, IntPtr pUdt);
public abstract void ToCustomObject(OracleConnection con, IntPtr pUdt);
}
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 elementCount)
{
return new TValue[elementCount];
}
public Array CreateStatusArray(int elementCount)
{
return new OracleUdtStatus[elementCount];
}
}
[OracleCustomTypeMapping("STUDENT_PKG.PHONE_TYP_RC")]
public class PhoneRecord : CustomTypeBase<PhoneRecord>
{
[OracleObjectMapping("PHONE_ID")]
public int PhoneId { get; set; }
[OracleObjectMapping("PHONE_TYPE")]
public string PhoneTYpe { get; set; }
[OracleObjectMapping("PHONE_NUMBER")]
public string PhoneNumber { get; set; }
public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "PHONE_ID", PhoneId);
OracleUdt.SetValue(con, pUdt, "PHONE_TYPE", PhoneTYpe);
OracleUdt.SetValue(con, pUdt, "PHONE_NUMBER", PhoneNumber);
}
public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
PhoneId = (int)OracleUdt.GetValue(con, pUdt, "PHONE_ID");
PhoneTYpe = OracleUdt.GetValue(con, pUdt, "PHONE_TYPE").ToString();
PhoneNumber = OracleUdt.GetValue(con, pUdt, "PHONE_NUMBER").ToString();
}
}
[OracleCustomTypeMapping("STUDENT_PKG.PHONE_RC_TBL")]
public class PhoneTable : CustomCollectionTypeBase<PhoneTable, PhoneRecord>
{
}
and here is Store procedure call
OracleCommand command = new OracleCommand("KAMI.STUDENT_PKG.GET_ALL_PHONES", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
try
{
OracleParameter param = new OracleParameter()
{
ParameterName = "P_PHONES_TBL",
Direction = System.Data.ParameterDirection.Output,
OracleDbType = OracleDbType.Object,
OracleDbTypeEx = OracleDbType.Object,
UdtTypeName = "STUDENT_PKG.PHONE_RC_TBL",
};
command.Parameters.Add(param);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
}
Please anyone experienced the same and how to resolve the issue
What I have tried:
1. I mapped Schema level UDT with custom types and it worked for me
2. I defined types inside Oracle package and tried to mapped with custom types.
I received following issues.
By adding parameter information to Oracle command here is the error.
"OCI-22303: type \"STUDENT_PKG\".\"PHONE_RC_TBL\" not found"
When using
OracleCommandBuilder.DeriveParameters(command);
Here is error
OCI-22303: type "KAMI"."STUDENT_PKG" not found
I tried to create OracleCustomTypeMapping with following names
- STUDENT_PKG.PHONE_RC_TBL
- STUDENT_PKG
- PHONE_RC_TBL