Hi,
I have Pl/SQL Package with 2 parameters
Input Parameter: ARRAY_TABLE -→ table of varchar2(400)
output parameter: AO_TABLE_TYPE -→ table of AO_TABLE_ARRAY -→ table of varchar2(400)
I have written C# application to execute the package and tried in different ways like passing array of strings, combined all strings and passed as single string, created custom type List<string> and passed that nothing worked
below is my code
any help to solve the issue
--
Mahendran
[OracleCustomTypeMapping("MEPC_APP.AO_TABLE_ARRAY")]
public class AO_TABLE_ARRAY : IOracleCustomType, INullable
{
private bool _isNull;
private List<string> _values;
public bool IsNull => _isNull;
public List<string> Values
{
get => _values;
set => _values = value;
}
public static AO_TABLE_ARRAY Null => new AO_TABLE_ARRAY { _isNull = true };
public void FromCustomObject(OracleConnection con, object pUdt)
{
_values = (List<string>)OracleUdt.GetValue(con, pUdt, 0);
}
public void ToCustomObject(OracleConnection con, object pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, _values);
}
}
[OracleCustomTypeMapping("MEPC_APP.AO_TABLE_TYPE")]
public class AO_TABLE_TYPE : IOracleCustomType, INullable
{
private bool _isNull;
private List<AO_TABLE_ARRAY> _values;
public bool IsNull => _isNull;
public List<AO_TABLE_ARRAY> Values
{
get => _values;
set => _values = value;
}
public static AO_TABLE_TYPE Null => new AO_TABLE_TYPE { _isNull = true };
public void FromCustomObject(OracleConnection con, object pUdt)
{
_values = (List<AO_TABLE_ARRAY>)OracleUdt.GetValue(con, pUdt, 0);
}
public void ToCustomObject(OracleConnection con, object pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, _values);
}
}
using (OracleConnection connection = new OracleConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new OracleCommand("MEPC_ML_PQ_INTERFACE.ALWAYS_ON_WRAPPER", connection))
{
command.CommandType = CommandType.StoredProcedure;
var param1 = new OracleParameter("P_ARRAY", OracleDbType.Varchar2)
{
UdtTypeName = ArrayTable,
Value = new ArrayTableUDT(){Values = dataArray.ToList()},
Direction = ParameterDirection.Input
};
var param2 = new OracleParameter("P_ARR_OUT", OracleDbType.Array)
{
UdtTypeName = AoTableType,
Direction = ParameterDirection.Output
};
command.Parameters.Add(param1);
command.Parameters.Add(param2);
await command.ExecuteNonQueryAsync();
// var result = (Oracle.Core.Types.AO_TABLE_TYPE)command.Parameters["P_ARR_OUT"].Value;
// var outputArray = new List<Array>();
// for (int i = 0; i < result.Length; i++)
// {
// outputArray.Add((Array)result.GetValue(i));
// }
//var r= outputArray;
}
}
[OracleCustomTypeMapping("MEPC_APP.ARRAY_TABLE")]
public class ArrayTableUDT : IOracleCustomType, INullable
{
private bool _isNull;
private List<string> _values;
public bool IsNull => _isNull;
public List<string> Values
{
get => _values;
set => _values = value;
}
public static ArrayTableUDT Null => new ArrayTableUDT { _isNull = true };
public void FromCustomObject(OracleConnection con, object pUdt)
{
object objectStatus = OracleUdt.GetValue(con, pUdt, 0);
if (objectStatus == DBNull.Value)
{
_isNull = true;
_values = null;
}
else
{
_isNull = false;
_values = ((string[])(objectStatus)).ToList();
}
}
public void ToCustomObject(OracleConnection con, object pUdt)
{
if (_isNull)
{
OracleUdt.SetValue(con, pUdt, 0, DBNull.Value);
}
else
{
OracleUdt.SetValue(con, pUdt, 0, _values.ToArray());
}
}
public override string ToString()
{
if (_isNull)
return "NULL";
return string.Join(",", _values);
}
}