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;
/
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));
}
}
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.