Problems Getting Back Table from Package in Direct Mode

[saschab]

Hello together,
i'm getting following error when I try to get back a Table from an Oracle Package :

Code: Select all

Error : Devart.Data.Oracle.OracleException: ORA-03115 unsupported network datatype or representation
   at Devart.Data.Oracle.OracleParameter.a(aq& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, av A_4, Boolean& A_5)
   at Devart.Data.Oracle.OracleCommand.a(Int32 A_0, OracleParameterCollection A_1, av A_2, Boolean& A_3)
   at Devart.Data.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
   at UseforFun.Form1.button11_Click(Object sender, EventArgs e) in

I know that this error occours in Direct mode, but have anybody an idear how the fix/ or workaround this problem? Or is there any plans to implement this in the DotNet provider?

My testpackage looks like this :

Package Header :

Code: Select all

CREATE OR REPLACE PACKAGE CHCSDBSHM.TESTPAK AS

  TYPE RetValue IS TABLE OF VARCHAR2(11) INDEX BY BINARY_INTEGER;

  FUNCTION FN_TEST_GET_ARRAY_BACK(temp IN NUMBER) RETURN 
RetValue;

END TESTPAK;

Package Body :

Code: Select all

CREATE OR REPLACE PACKAGE BODY CHCSDBSHM.TESTPAK AS
  FUNCTION FN_TEST_GET_ARRAY_BACK(temp IN NUMBER) RETURN RetValue 
  AS retVal RetValue;
  BEGIN
    retVal(1) := 'Hallo'; 
    retVal(2) := 'Welt';
    
    dbms_output.put_line('Hallo');
    RETURN retVal;
  END;

END TESTPAK;

Because i need this possibility, but have no possibility to use the Oracle Client Install, that's why we use Devart .


Thx and Best Regards

Sascha

[Shalex]

Please try running the following code.

Code: Select all

      OracleConnection c = new OracleConnection("User Id=XXX;Password=XXX;server=XXX");
      c.Open();

      OracleCommand co = new OracleCommand("TEST.TESTPAK.FN_TEST_GET_ARRAY_BACK", c);
      co.CommandType = CommandType.StoredProcedure;
      OracleCommandBuilder.DeriveParameters(co);
      co.Parameters[0].OracleDbType = OracleDbType.VarChar;
      co.Parameters[0].ArrayLength = 2;
      co.ExecuteNonQuery();

In this particular case there is no need to use OracleTable that is supported only via OCI (Direct=false), because you use the PgSql Table type (INDEX BY BINARY_INTEGER). So the type of the parameter should be OracleDbType.VarChar.

[saschab]

@Shalex:

i've tried it with you code, it works thanx. But I've still one question, the

Code: Select all

ArrayLength = 2 

Parameter declaration, is there a possibility to set it dynamic. So i must have knowledge about how many rows will be returned. Ok i can set it greater than the max rows returned back in max possibility.

Thx Sascha

[Shalex]

Yes, please set the max rows returned back in max possibility.

[saschab]

@Shalex:

Ok thanks a lot for your support.

Greets Sascha