Page 1 of 1
SP returning associative arrays - possible?
Posted: Fri 07 Aug 2009 09:03
by TroelsLarsen
I'm having a lot of trouble getting data out of a stored procedure - the signature of it looks like this:
PROCEDURE net_drl_hdr_post_query(
p_drl_hdr_no IN drl_hdr.drl_hdr_no%TYPE,
p_norm_install_hours out norm_hour.install_norm_hour%TYPE,
p_quan_in_order out t_my_type,
p_quan_stock_tot out t_my_type,
p_quan_reserved_pick_lst out t_my_type
)
where t_my_type is:
TYPE t_my_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER
I try the following:
Code: Select all
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "STD73_UDV.S201_BIZ.NET_DRL_HDR_POST_QUERY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("P_DRL_HDR_NO", OracleDbType.Number));
cmd.Parameters["P_DRL_HDR_NO"].Value = 2815;
cmd.Parameters["P_DRL_HDR_NO"].Direction = ParameterDirection.Input;
cmd.Parameters.Add(new OracleParameter("P_NORM_INSTALL_HOURS", OracleDbType.Number));
cmd.Parameters["P_NORM_INSTALL_HOURS"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("P_QUAN_IN_ORDER", OracleDbType.Number));
cmd.Parameters["P_QUAN_IN_ORDER"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("P_QUAN_STOCK_TOT", OracleDbType.Number));
cmd.Parameters["P_QUAN_STOCK_TOT"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("P_QUAN_RESERVED_PICK_LST", OracleDbType.Number));
cmd.Parameters["P_QUAN_RESERVED_PICK_LST"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
var tmp = cmd.Parameters["P_QUAN_IN_ORDER"].Value;
}
Am I doing something wrong, or is this not possible?
Posted: Wed 12 Aug 2009 09:05
by Shalex
You can use PL/SQL Tables as parameters of stored procedures. For this, please use the ArrayLength property of OracleParameter.
http://www.devart.com/dotconnect/oracle ... table.html
http://www.devart.com/dotconnect/oracle/docs/ , the Index tab, the ArrayLength Property section.
script:
Code: Select all
CREATE OR REPLACE PACKAGE tp AS
TYPE t_my_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE net_drl_hdr_post_query(
p_drl_hdr_no IN NUMBER,
p_norm_install_hours out NUMBER,
p_quan_in_order out t_my_type,
p_quan_stock_tot out t_my_type,
p_quan_reserved_pick_lst out t_my_type
);
END tp;
/
CREATE OR REPLACE PACKAGE BODY tp AS
Lcntr NUMBER;
PROCEDURE net_drl_hdr_post_query(
p_drl_hdr_no IN NUMBER,
p_norm_install_hours out NUMBER,
p_quan_in_order out t_my_type,
p_quan_stock_tot out t_my_type,
p_quan_reserved_pick_lst out t_my_type
) IS
BEGIN
p_norm_install_hours := p_drl_hdr_no;
Lcntr := 0;
FOR Lcntr IN 1..20
LOOP
p_quan_in_order(Lcntr) := Lcntr;
END LOOP;
p_quan_stock_tot := p_quan_in_order;
p_quan_reserved_pick_lst := p_quan_in_order;
END;
END tp;
/
C# code:
Code: Select all
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = "TP.NET_DRL_HDR_POST_QUERY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("P_DRL_HDR_NO", OracleDbType.Number));
cmd.Parameters["P_DRL_HDR_NO"].Value = 2815;
cmd.Parameters["P_DRL_HDR_NO"].Direction = ParameterDirection.Input;
cmd.Parameters.Add(new OracleParameter("P_NORM_INSTALL_HOURS", OracleDbType.Number));
cmd.Parameters["P_NORM_INSTALL_HOURS"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("P_QUAN_IN_ORDER", OracleDbType.Number));
cmd.Parameters["P_QUAN_IN_ORDER"].Direction = ParameterDirection.Output;
cmd.Parameters["P_QUAN_IN_ORDER"].ArrayLength = 20;
cmd.Parameters.Add(new OracleParameter("P_QUAN_STOCK_TOT", OracleDbType.Number));
cmd.Parameters["P_QUAN_STOCK_TOT"].Direction = ParameterDirection.Output;
cmd.Parameters["P_QUAN_STOCK_TOT"].ArrayLength = 20;
cmd.Parameters.Add(new OracleParameter("P_QUAN_RESERVED_PICK_LST", OracleDbType.Number));
cmd.Parameters["P_QUAN_RESERVED_PICK_LST"].Direction = ParameterDirection.Output;
cmd.Parameters["P_QUAN_RESERVED_PICK_LST"].ArrayLength = 20;
cmd.ExecuteNonQuery();
var tmp = cmd.Parameters["P_QUAN_IN_ORDER"].Value;
}
Posted: Tue 02 Nov 2010 14:34
by rmontoya
I want to pass associative arrays as Input parameters and have followed the instructions in the documentation as best I could but am not having success. I have the following package:
Code: Select all
CREATE OR REPLACE PACKAGE PA_My_Package is
...
TYPE IntTyp IS TABLE OF int
INDEX BY BINARY_INTEGER;
...
function fn_Save(P_My_ID in IntTyp) return int;
...
I then have the following helper method which prepares my parameter:
Code: Select all
protected IDbDataParameter GetMyIdParameter(List myIds)
{
OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.Input);
param.ArrayLength = myIds.Count;
param.Value = myIds.ToArray();
return param;
}
When I execute I get the following error:
System.InvalidCastException: Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible'.
at System.Convert.ToInt32(Object value)
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, String A_13, au A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(ab& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, au A_5, Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(y A_0, Int32 A_1, OracleParameterCollection A_2, au A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean no...).
Posted: Wed 03 Nov 2010 17:56
by Shalex
1. Please tell us how we should modify the following sample to reproduce the error.
2. What versions of dotConnect for Oracle, Oracle Server, Oracle Client are you using?
I have tried this code with dotConnect for Oracle v 5.70.180, Oracle Server 11.1, and Oracle Client 11.1.
script
Code: Select all
CREATE OR REPLACE PACKAGE PA_My_Package is
TYPE IntTyp IS TABLE OF int
INDEX BY BINARY_INTEGER;
function fn_Save(P_My_ID in IntTyp) return int;
end PA_My_Package;
/
create or replace package body PA_My_Package
is
function fn_Save(P_My_ID in IntTyp) return int
as
begin
return P_My_ID(1) + P_My_ID(2);
end fn_Save;
end PA_My_Package;
C# code
Code: Select all
static protected IDbDataParameter GetMyIdParameter(List myIds)
{
OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
param.ArrayLength = myIds.Count;
param.Value = myIds.ToArray();
return param;
}
static void Main(string[] args)
{
List myIds = new List {1,2};
using (OracleConnection conn = new OracleConnection("server=ora1110;uid=scott;pwd=tiger;")){
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PA_My_Package.fn_Save";
cmd.Parameters.Add(GetMyIdParameter(myIds));
cmd.Parameters.Add("RESULT", OracleDbType.Integer).Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
Console.WriteLine("{0} + {1} = {2}", myIds[0], myIds[1], cmd.Parameters["RESULT"].Value);
Console.ReadLine();
}
}
Output: 1 +2 = 3
Posted: Wed 03 Nov 2010 20:10
by rmontoya
Ok, it's happening when the list is empty.
Change:
to:
In our scenario, we are building the list from another source so it can be empty. How can I specify a valid parameter for an empty list?
Posted: Thu 04 Nov 2010 13:06
by Shalex
We have reproduced the error. We will investigate the issue and notify you about the results as soon as possible.
Posted: Thu 04 Nov 2010 15:33
by Shalex
The reason of the error is that param.ArrayLength is set to 0 in the case of empty list. Please handle this situation in your code. If you are using the ArrayLength property, its value should be > 0.
Posted: Thu 04 Nov 2010 20:12
by rmontoya
Is there a way to construct a valid OracleParameter when the list is empty?
Code: Select all
if (myIds.Count > 0)
{
OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
param.ArrayLength = myIds.Count;
param.Value = myIds.ToArray();
return param;
}
else
{
// Can a parameter be constructed for an empty collection?
}
Or are you saying that I need to create some dummy value and then handle on DB side?
Code: Select all
if (myIds.Count == 0)
myIds.Add(-1); // Test for this value and treat as empty on the DB side
OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
param.ArrayLength = myIds.Count;
param.Value = myIds.ToArray();
return param;
Posted: Fri 05 Nov 2010 12:49
by Shalex
You can use this code - it initializes param with default value for its type in the case myIds.Count = 0.
Code: Select all
if (myIds.Count > 0) {
param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
param.Value = myIds.ToArray();
}
else {
param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
//the next line creates an array with 1 element that is initialized with
//default value for this type. It is 0 for OracleDbType.Integer
param.ArrayLength = 1;
}
Or you can set your own dummy value as you mentioned in your second snippet.
Posted: Fri 05 Nov 2010 19:34
by rmontoya
Thank you.