I am trying to pass UDT array to an Oracle SP from .NET but I am getting this strange error. PFB my code:
Oracle Code:
create or replace type Patient_Vascular_Access AS OBJECT
(
access_type_code number,
access_location_code number,
doctor_num number
);
create or replace PACKAGE patient_child_VA AS
type Patient_Vascular_Access_Array is table of Patient_Vascular_Access INDEX BY PLS_INTEGER;
PROCEDURE SavePatientVascularAccess (
personal_num_v number,
patient_VA IN Patient_Vascular_Access_Array,
USERNAME_v IN varchar2,
TIMESTAMP_v IN date,
paging_cursor out SYS_REFCURSOR,
error_msg out VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY patient_child_VA IS
procedure SavePatientVascularAccess (
personal_num_v number,
patient_VA IN Patient_Vascular_Access_Array,
USERNAME_v IN varchar2,
TIMESTAMP_v IN date,
paging_cursor out SYS_REFCURSOR,
error_msg out VARCHAR2
) is
v_sql varchar2(32767) := '';
access_code_exists_v varchar2(1) := null;
access_id_v number := 0;
begin
// Do whatever in SP. SP verified - its working fine, tested by calling directly from oracle sql developer
end;
end;
C# code:
[OracleCustomTypeMapping("patient_child_VA.Patient_Vascular_Access_Array")]
public class Patient_Vascular_Access_Array : CustomCollectionTypeBase<Patient_Vascular_Access_Array, Patient_Vascular_Access>
{
public override void FromCustomObject(OracleConnection connection, object pointerUdt)
{
}
public override void ToCustomObject(OracleConnection connection, object pointerUdt)
{
}
}
[OracleCustomTypeMapping("Patient_Vascular_Access")]
public class Patient_Vascular_Access : CustomTypeBase<Patient_Vascular_Access>
{
[OracleObjectMapping("access_type_code")]
public int access_type_code;
[OracleObjectMapping("access_location_code")]
public int access_location_code;
[OracleObjectMapping("doctor_num")]
public int doctor_num;
public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
{
OracleUdt.SetValue(connection, pointerUdt, "access_type_code", access_type_code);
OracleUdt.SetValue(connection, pointerUdt, "access_location_code", access_location_code);
OracleUdt.SetValue(connection, pointerUdt, "doctor_num", doctor_num);
}
public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
{
access_type_code = (int)OracleUdt.GetValue(connection, pointerUdt, "access_type_code");
access_location_code = (int)OracleUdt.GetValue(connection, pointerUdt, "access_location_code");
doctor_num = (int)OracleUdt.GetValue(connection, pointerUdt, "doctor_num");
}
public override void FromCustomObject(OracleConnection connection, object pointerUdt)
{
OracleUdt.SetValue(connection, pointerUdt, "access_type_code", access_type_code);
OracleUdt.SetValue(connection, pointerUdt, "access_location_code", access_location_code);
OracleUdt.SetValue(connection, pointerUdt, "doctor_num", doctor_num);
}
public override void ToCustomObject(OracleConnection connection, object pointerUdt)
{
access_type_code = (int)OracleUdt.GetValue(connection, pointerUdt, "access_type_code");
access_location_code = (int)OracleUdt.GetValue(connection, pointerUdt, "access_location_code");
doctor_num = (int)OracleUdt.GetValue(connection, pointerUdt, "doctor_num");
}
}
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];
}
}
public abstract class CustomTypeBase<T> : IOracleCustomType, IOracleCustomTypeFactory 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, object pointerUdt);
public abstract void FromCustomObject(OracleConnection connection, object 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 }; }
}
}
///////////////////////////// Passing parameters to Oracle SP //////////////
public static async Task<List<XYZ>> SavePatientVascularAccess(int personal_num, string userName)
{
try
{
PatientDBFactory db = new PatientDBFactory();
OracleParameter p1 = new OracleParameter("personal_num_v", OracleDbType.Int32, personal_num, ParameterDirection.Input);
OracleParameter p5 = new OracleParameter("USERNAME_v", OracleDbType.Varchar2, userName, ParameterDirection.Input);
OracleParameter p6 = new OracleParameter("TIMESTAMP_v", OracleDbType.Date, DateTime.Now, ParameterDirection.Input);
OracleParameter p3 = new OracleParameter("paging_cursor", OracleDbType.RefCursor, ParameterDirection.Output);
OracleParameter p4 = new OracleParameter();
p4.ParameterName = "error_msg";
p4.OracleDbType = OracleDbType.Varchar2;
p4.Size = 32767;
p4.Direction = ParameterDirection.Output;
OracleParameter p2 = new OracleParameter();
p2.ParameterName = "patient_VA";
p2.OracleDbType = OracleDbType.Array;
p2.UdtTypeName = "patient_child_VA.Patient_Vascular_Access_Array";
p2.Direction = ParameterDirection.Input;
p2.Value = new Patient_Vascular_Access_Array
{
Values = new Patient_Vascular_Access[]
{
new Patient_Vascular_Access { access_type_code = 21, access_location_code = 21, doctor_num = 231 },
new Patient_Vascular_Access { access_type_code = 22, access_location_code = 22, doctor_num = 231 },
new Patient_Vascular_Access { access_type_code = 23, access_location_code = 23, doctor_num = 231 }
}
};
List<OracleParameter> pList = new List<OracleParameter>() { p1, p2, p3, p4, p5, p6 };
List<VascularAccessViewModel> pR = await db.InsertPatientChildren<VascularAccessViewModel>("patient_child_VA.SavePatientVascularAccess", SetState_PatientVascular, pList);
return pR;
}
catch (Exception ex)
{
throw ex;
}
}
SP/ C# code is tested/ verified other than the binding of UDT Array everything works fine. SP works fine when called directly from Oracle SQL developer tool. There's some issue in UDT Array binding. Please see the screenshots for better understanding of the error. Exception Exception stack trace