Problems Getting Back Table from Package in Direct Mode

Problems Getting Back Table from Package in Direct Mode

Postby saschab » Tue 29 Sep 2009 03:20

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
saschab
 
Posts: 6
Joined: Tue 29 Sep 2009 03:08

Postby Shalex » Wed 30 Sep 2009 07:45

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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby saschab » Wed 30 Sep 2009 22:58

@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
saschab
 
Posts: 6
Joined: Tue 29 Sep 2009 03:08

Postby Shalex » Fri 02 Oct 2009 16:36

Yes, please set the max rows returned back in max possibility.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby saschab » Fri 02 Oct 2009 19:18

@Shalex:

Ok thanks a lot for your support.

Greets Sascha
saschab
 
Posts: 6
Joined: Tue 29 Sep 2009 03:08


Return to dotConnect for Oracle