我正在通过Oracle数据访问非托管程序集将Oracle用户定义类型映射到.Net自定义类型。

我已关注文章https://www.codeproject.com/Articles/1261430/Calling-Oracles-Stored-Procedures-and-Mapping-User

我已经应用了简单的方案。

1. 即在架构级别创建Oracle UDT

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;

这是过程GET_ALL_STUDENTS的正文

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,null)
    FROM
    student s) ; 
END;

以上示例工作正常。上面的情况只是为了说明实现没有问题

1. 现在我在包中定义了类型，例如

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;

以下是STUDENT_PKG身体

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 OF GET_ALL_PHOnes
END;


--END OF PACKAGE BODY
END;

当我将Oracle参数与所有信息一起添加时，会出现Oracle异常

“ OCI-22303：键入\“ STUDENT_PKG \”。\“找不到PHONE_RC_TBL \”

我已经定义了名称为STUDENT_PKG.PHONE_RC_TBL的Oracle自定义映射。

我已经尝试使用以下名称

• STUDENT_PKG.PHONE_RC_TBL
• STUDENT_PKG
• PHONE_RC_TBL

执行OracleCommandBuilder.DeriveParameters(command);以获取命令文本参数时，将获得以下信息：

执行时，出现以下异常

OCI-22303：键入“ KAMI”。找不到“ STUDENT_PKG”

这意味着处理软件包到.Net类的udt映射有些棘手。

这是我对P_PHOnes_TBL参数的.Net自定义类的定义

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,Values);
        }

        public override void ToCustomObject(OracleConnection connection,IntPtr pointerUdt)
        {
            Values = (tvalue[])OracleUdt.Getvalue(connection,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,"PHONE_TYPE",PhoneTYpe);
            OracleUdt.Setvalue(con,"PHONE_NUMber",PhoneNumber);
        }

        public override void ToCustomObject(OracleConnection con,IntPtr pUdt)
        {
            PhoneId = (int)OracleUdt.Getvalue(con,"PHONE_ID");
            PhoneTYpe = OracleUdt.Getvalue(con,"PHONE_TYPE").ToString();
            PhoneNumber = OracleUdt.Getvalue(con,"PHONE_NUMber").ToString();
        }
}

[OracleCustomTypeMapping("STUDENT_PKG.PHONE_RC_TBL")]
public class Phonetable : CustomCollectionTypeBase<Phonetable,PhoneRecord>
{
}

这是存储过程调用：

OracleCommand command = new OracleCommand("KAMI.STUDENT_PKG.GET_ALL_PHOnes",connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

try
{
            //  OracleCommandBuilder.DeriveParameters(command);
            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)
{
}