0

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

  • Please share the data that is passed to the stored procedure when this exception appears! Please don't use incomplete screenshots but full raw text for this kind of information so others can work with it. [ask] – Markus Aug 12 '22 at 06:21
  • Just for the info: I have tested the SP by calling it directly and it works fine. The c# code works fine too if called without UDT paramter. Issue is in UDT parameter binding from c# code. If you can help me with some generic example of how can I bind UDT array from c# code that would be really great. TIA. – Rabia Javed Aug 12 '22 at 12:15
  • Sorry I missed calling SP and passing parameters from c# code to share. Post edited. PF that code in it. Array values are hardcoded into the newly added code. Please see @Marcus. – Rabia Javed Aug 12 '22 at 12:16
  • Please don't share error messages and stack traces as screenshot, but share them as raw text. That way it is easier for others to search for them or to work on them! If you are able to reduce your current code to a minimal, reproducible version as described in [ask] it will be more likely that someone will work on it. – Markus Aug 12 '22 at 15:00
  • Did you check this post: https://stackoverflow.com/q/48569550/18667225 ? – Markus Aug 12 '22 at 15:00

0 Answers0