search

oracle / dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
http://otn.oracle.com/dotnet
MIT License
408 stars 190 forks source link

Table of CustomType out parameter in store procedure #381

Closed Anbuselvam2001 closed 1 month ago

Anbuselvam2001 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

@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 commented 1 month ago

@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 commented 1 month ago

@alexkeh Thanks for your Response.

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

alexkeh commented 1 month ago

@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 commented 1 month ago

@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 commented 1 month ago

@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 commented 1 month ago

@Anbuselvam2001 ODP.NET doesn't support PL/SQL record types yet. There's an existing request to support this capability (https://github.com/oracle/dotnet-db-samples/issues/275) that can be upvoted.