Table of CustomType out parameter in store procedure

[Anbuselvam2001]

I have custom type in oracle db schema level
create or replace TYPE EMPLOYEE_TYPE AS OBJECT
(
name VARCHAR2(4),
salary NUMBER(6)
);

and also Table type
create or replace TYPE EMPLOYEE_TABLE
AS TABLE OF EMPLOYEE_TYPE ;

My Store procedure pretty simple
create or replace PROCEDURE get_employees(employees OUT EMPLOYEE_TABLE) AS
BEGIN
-- Populate the EMPLOYEE_TABLE with sample data
employees := EMPLOYEE_TABLE(
EMPLOYEE_TYPE ('John', 50000),
EMPLOYEE_TYPE('JACK', 60000),
EMPLOYEE_TYPE('Bob', 55000)
);
END get_employees;

Can anyone please guide me to achieve in C#?

am tried the below snippet.. getting so errors only

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "get_employees";
        //command.BindByName = true;

       // Create output parameter for EMPLOYEE_TABLE
        var outParam = new OracleParameter();
        outParam.ParameterName = "employees";
        outParam.Direction = ParameterDirection.Output;
        outParam.OracleDbType = OracleDbType.Array;
       
        command.Parameters.Add(outParam);
         command.ExecuteNonQuery();

[alexkeh]

Table 3-33 in the ODP.NET dev guide can be helpful to make sure you've set up your output parameter correctly.

I believe you want to use OracleDbType.Object as the parameter type since you are not passing a nested table nor VARRAY.

[Anbuselvam2001]

Thank you for you response @alexkeh
Note :- Am using .NET6.0
my udt class is

[OracleCustomTypeMapping("EMPLOYEE_TYPE")]
public class EmpType: CustomTypeBase<EmpType>
{
    [OracleObjectMapping("name")]
    public string? name { get; set; }

    [OracleObjectMapping("salary")]
    public int salary { get; set; }

   public override  void FromCustomObject(OracleConnection con, object udt)
   {

       if(name!=null)OracleUdt.SetValue(con, udt, "name", name);
       if(salary!=null)OracleUdt.SetValue(con, udt, "salary", salary);
   }

   public override  void ToCustomObject(OracleConnection con, object udt)
   {
       name = (string)OracleUdt.GetValue(con, udt, "name");
       salary = (int)OracleUdt.GetValue(con, udt, "salary");
   }
}

[OracleCustomTypeMapping("EMPLOYEE_TABLE")]
public class EmpTable : CustomCollectionTypeBase<EmpTable, EmpType>
{
}

And my procedure calling snippet is

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "YYYY.get_employees";
        //command.BindByName = true;

       // Create output parameter for EMP_TABLE
        var outParam = new OracleParameter();
        outParam.ParameterName = "employees";
        outParam.Direction = ParameterDirection.Output;
        outParam.OracleDbType = OracleDbType.Object;
        outParam.UdtTypeName = "YYYY.EMPLOYEE_TABLE";
       
        command.Parameters.Add(outParam);
         command.ExecuteNonQuery();

Am getting below errors message

         ORA-06550: line 1, column 13:\nPLS-00306: wrong number or types of arguments in call to 'GET_TYPE_SHAPE'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored

And i got some stack trace of error is

at OracleInternal.UDT.Types.UDTNamedType.GetNamedTypeMetaData(OracleConnection conn, OracleCommand getTypeCmd)
  at OracleInternal.UDT.Types.UDTTypeCache.GetMetaData(OracleConnection conn, UDTNamedType udtType)
  at OracleInternal.UDT.Types.UDTTypeCache.CreateUDTType(OracleConnection conn, String schemaName, String typeName)
  at OracleInternal.UDT.Types.UDTTypeCache.GetUDTType(OracleConnection conn, String schemaName, String typeName)
  at OracleInternal.ConnectionPool.OraclePoolManager.GetUDTType(OracleConnection conn, String schemaName, String 
typeName)
  at Oracle.ManagedDataAccess.Client.OracleParameter.GetUDTType(OracleConnection conn)
  at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind_UDT(OracleConnection conn)
  at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind(OracleConnectionImpl connImpl, ColumnDescribeInfo 
cachedParamMetadata, Boolean& bMetadataModified, Int32 arrayBindCount, ColumnDescribeInfo& paramMetaData, Object& 
paramValue, Boolean isEFSelectStatement, SqlStatementType stmtType)
  at OracleInternal.ServiceObjects.OracleCommandImpl.InitializeParamInfo(ICollection paramColl, OracleConnectionImpl 
connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bMetadataModified, Boolean isEFSelectStatement, 
MarshalBindParameterValueHelper& marshalBindValuesHelper)
  at OracleInternal.ServiceObjects.OracleCommandImpl.ProcessParameters(OracleParameterCollection paramColl, 
OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bBindMetadataModified, 
Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper)
  at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection 
paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, 
OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& 
bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection 
connection, Boolean isFromEF)

[alexkeh]

That's an unusual error. There seems to be some incompatibility. Which DB version are you using? Which ODP.NET version are you using?

Please run the following command from SQL Plus or another DB command line and share the results:
desc sys.dbms_pickler

[Anbuselvam2001]

@alexkeh Here is my .csproj file

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Asp.Versioning.Mvc.ApiExplorer" Version="6.4.0" />
    <PackageReference Include="AspNetCore.HealthChecks.UI.Client" Version="6.0.5" />
    <PackageReference Include="AutoMapper" Version="12.0.1" />
    <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="12.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.26" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.26">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Oracle.EntityFrameworkCore" Version="6.21.130" />
     <PackageReference Include="Microsoft.Extensions.Diagnostics.HealthChecks" Version="6.0.25" />
    <PackageReference Include="Microsoft.Extensions.Diagnostics.HealthChecks.EntityFrameworkCore" Version="6.0.5" />
    <PackageReference Include="Serilog.AspNetCore" Version="6.1.0" />
    <PackageReference Include="Serilog.Settings.Configuration" Version="7.0.1" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </ItemGroup>
</Project>

My OracleDB Version is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

[alexkeh]

@Anbuselvam2001 Ah, the DB version is the source of your issue. Between DB 11.2 and 12.1, UDT metadata definitions made a major change such that 11.2 UDT DB APIs were no longer backward compatible.

The GET_TYPE_SHAPE error you see is indicative of one of those API differences between 11.2 and 12.1+.

When ODP.NET 21c was released, DB 11.2 had basically reached the end of Extended Support. Thus, ODP.NET 21c only supports DB 12.1 and higher. If you upgrade your DB, it's likely you'll then be able to get your code working.

[Anbuselvam2001]

@alexkeh Thanks for your Response.

Is there any alternative way to achieve the same instead of DB upgrade. Could you guide me..

[alexkeh]

@Anbuselvam2001 None that I can think of using UDTs in ODP.NET.

You could have PL/SQL operate on the UDT and then pass data to ODP.NET via an associative array or scalar types. You could convert the UDT into XML or JSON for ODP.NET to then use. These solutions require a non-trivial amount of work, however.

[Anbuselvam2001]

@alexkeh Thanks for your suggestion.
Am facing issue in another scenario, Is it possible to access package level (Type or Table) using UDTs in ODP.NET.

[Anbuselvam2001]

@alexkeh I successfully handled the TYPE scenario, but currently facing issues while trying to access the table of record in package level
my package is

create or replace PACKAGE sample_package AS

    -- Package level Record type
    TYPE USER_REC IS RECORD (
        FNAME VARCHAR2(50),
        LNAME VARCHAR2(50),
        SALARY NUMBER
    );

    -- Package level table type
    TYPE USER_TAB IS TABLE OF USER_REC INDEX BY BINARY_INTEGER;

    -- Procedure to populate the USER_TAB
    PROCEDURE get_users(p_users OUT USER_TAB);
   END sample_package;
   create or replace PACKAGE BODY sample_package AS
   PROCEDURE get_users(p_users OUT USER_TAB) AS
    a number;
    BEGIN
        -- Populate sample user data into the user_table
        p_users(1).FNAME := 'John';
        p_users(1).LNAME := 'Doe';
        p_users(1).SALARY := 50000;

        p_users(2).FNAME := 'Alice';
        p_users(2).LNAME := 'Smith';
        p_users(2).SALARY := 60000;

        p_users(3).FNAME := 'Bob';
        p_users(3).LNAME := 'Johnson';
        p_users(3).SALARY := 55000;
    END get_users;
    END sample_package;

My Mapping class

[OracleCustomTypeMapping("SAMPLE_PACKAGE.USER_REC")]
public class UserRec : CustomTypeBase<UserRec>
{
    [OracleObjectMapping("FNAME")]
    public string? Fname { get; set; }

    [OracleObjectMapping("LNAME")]
    public string? Lname { get; set; }

    [OracleObjectMapping("SALARY")]
    public int? Salary { get; set; }


    public override void FromCustomObject(OracleConnection con, object pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "FNAME", Fname);
        OracleUdt.SetValue(con, pUdt, "LNAME", Lname);
        OracleUdt.SetValue(con, pUdt, "SALARY", Salary ?? OracleDecimal.Null);
    }

    public override void ToCustomObject(OracleConnection con, object pUdt)
    {
        Fname = (string?)OracleUdt.GetValue(con, pUdt, "FNAME");
        Lname = (string?)OracleUdt.GetValue(con, pUdt, "LNAME");
        Salary = (int?)OracleUdt.GetValue(con, pUdt, "SALARY");
    }
}


[OracleCustomTypeMapping("SAMPLE_PACKAGE.USER_TAB")]
public class UserTab : CustomCollectionTypeBase<UserTab, UserRec>
{
}

My C# code is

          command.CommandType = CommandType.StoredProcedure;
           command.CommandText = "sample_package.get_users";
           command.BindByName = true;

           // Create output parameter for USER_TAB
           var outParam = new OracleParameter();
           outParam.ParameterName = "p_users";
           outParam.Direction = ParameterDirection.Output;
           outParam.OracleDbType = OracleDbType.Array;
           outParam.UdtTypeName = "sample_package.USER_TAB";
           //outParam.Value = new UserRec[]{new UserRec()};
           // Add the output parameter to the command
           command.Parameters.Add(outParam);

           // Execute the stored procedure
           command.ExecuteNonQuery();

Below Error Getting -
Column contains NULL data

Stack trace -

at OracleInternal.UDT.Types.UDTNamedType.GetNamedTypeMetaData(OracleConnection conn, OracleCommand getTypeCmd)
         at OracleInternal.UDT.Types.UDTTypeCache.GetMetaData(OracleConnection conn, UDTNamedType udtType)
         at OracleInternal.UDT.Types.UDTTypeCache.CreateUDTType(OracleConnection conn, String schemaName, String typeName)
         at OracleInternal.UDT.Types.UDTTypeCache.GetUDTType(OracleConnection conn, String schemaName, String typeName)
         at OracleInternal.ConnectionPool.OraclePoolManager.GetUDTType(OracleConnection conn, String schemaName, String typeName)
         at Oracle.ManagedDataAccess.Client.OracleParameter.GetUDTType(OracleConnection conn)
         at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind_UDT(OracleConnection conn)
         at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind(OracleConnectionImpl connImpl, ColumnDescribeInfo cachedParamMetadata, Boolean& bMetadataModified, Int32 arrayBindCount, ColumnDescribeInfo& paramMetaData, Object& paramValue, Boolean isEFSelectStatement, SqlStatementType stmtType)

[alexkeh]

@Anbuselvam2001 ODP.NET doesn't support PL/SQL record types yet. There's an existing request to support this capability (#275) that can be upvoted.