"real" size of an indexed plsql table as an output parameter

silverfox · Post by **silverfox** » Wed 18 Jan 2012 12:10

Hello,

I'm working with a procedure using 2 indexed tables (defined in a package) as output parameters. First one is a number table, the second is a varchar table both are indexed by Pls_Integer.

I'd like to know if it is possible to have the "real" length of the returned array. I set the arrayLength to 10 and the returned arrays have this maximal size (10). How do I know what is the number of element assigned by my procedure ?

Here is my configuration :
Database : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Client Version 10.1.0.2.0
Devart.Data.Oracle 6.30.202.0
.Net Framework 4.0

Here is the pl/sql code

Code: Select all

CREATE OR REPLACE PACKAGE pck_indexed_array IS 

    Type tab_Varchar50    Is Table of VARCHAR2(50) Index By Pls_Integer;
    Type tab_Number Is Table of Number(4) Index By Pls_Integer;

    Procedure get_tables(po_tabv Out tab_Varchar50, po_tabn Out tab_Number);
    
END pck_indexed_array;
/

CREATE OR REPLACE PACKAGE BODY pck_indexed_array IS 

    Procedure get_tables(po_tabv Out tab_Varchar50, po_tabn Out tab_Number)
    Is
    Begin
        po_tabv(1) := 'abcd';
        po_tabv(2) := 'efgh';
        po_tabn(1) := 5;
        po_tabn(2) := 0;
        
    End get_tables;


END pck_indexed_array;
/

And here is the .Net code (autogenerated by the package wizard and slightly modified to make it work)

Code: Select all

    public void GetTables(out string[] poTabv, out decimal[] poTabn) {
      OracleParameterCollection parameters;
      parameters = this.Parameters;
      parameters.Clear();
      OracleParameter parameter;
      parameter = new OracleParameter("PO_TABV", Devart.Data.Oracle.OracleDbType.VarChar);
      parameter.Direction = System.Data.ParameterDirection.Output;
      parameter.Size = 50;
      parameter.ArrayLength = 10;
      parameter.IsNullable = true;
      parameters.Add(parameter);
      parameter = new OracleParameter("PO_TABN", Devart.Data.Oracle.OracleDbType.Number);
      parameter.Direction = System.Data.ParameterDirection.Output;
      parameter.ArrayLength = 10;
      parameter.IsNullable = true;
      parameters.Add(parameter);
      ExecuteProcedure("GET_TABLES", parameters);
      if ((Parameters["PO_TABV"].Value == System.DBNull.Value)) {
        poTabv = null;
      }
      else {
        poTabv = ((string[])(Parameters["PO_TABV"].Value));
      }
      if ((Parameters["PO_TABN"].Value == System.DBNull.Value)) {
        poTabn = null;
      }
      else {
        poTabn = ((decimal[])(Parameters["PO_TABN"].Value));
      }
    }

Here is the result :
poTabv = {"abcd","efgh", null, null, null, null, null, null, null, null}
poTabn = {5,0,0,0,0,0,0,0,0,0}

I'd like to know :
* poTabn.Length is 10 (the ArrayLength), how can I get the "real" length (the number of element assigned by my plsql code)

* Why devart/Oracle returns a decimal array instead of a Nullable ? How can I force it with my code ? Like this I could use null values to determine the end of the table. (I try Parameter.IsNullable = true but this does not work)

* Is it possible to define myself default values in theses arrays ?

I did not find something about this neither in the devart doc nor this forum. If I miss something can you redirect me please ?

Thanks in advance for you support.

Pinturiccio · Post by **Pinturiccio** » Fri 20 Jan 2012 11:56

We have reproduced the issue. We will investigate it and notify you about results as soon as possible. As a workaround, you can add one more output parameters to your stored procedure, which stores the information about the length of your arrays.

silverfox · Post by **silverfox** » Thu 26 Jan 2012 08:38

Hello.

Do you find something about my problem ?

I found another workaround which consists of using the OracleValue Property. This property will return me an array of OracleNumber where null is the default value.

The problem is that I am not able to know if null is a default value value or a value returned by my procedure.

If I could avoid an extra length output parameter for each table it will be better.

Thanks in advance for you support.

Pinturiccio · Post by **Pinturiccio** » Thu 26 Jan 2012 13:19

We have changed the dotConnect for Oracle behavior: The Value property of PL/SQL array output parameters now returns an array with the length equal to the length of the value, returned by Oracle.
We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio · Post by **Pinturiccio** » Fri 27 Jan 2012 14:06

The new build of dotConnect for Oracle 6.70.293 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23260