Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Need help in executing the Oracle Package with UDT (table of varchar2)

Mahendran MFeb 26 2024

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);
}
}
Comments
Post Details
Added on Feb 26 2024
2 comments
61 views