Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

1509366Feb 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
362 views