SP returning associative arrays - possible?

[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?

[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;
}

[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...).

[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

[rmontoya]

Ok, it's happening when the list is empty.

Change:

Code: Select all

List myIds = new List {1,2};

to:

Code: Select all

List myIds = new List();

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?

[Shalex]

We have reproduced the error. We will investigate the issue and notify you about the results as soon as possible.

[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.

[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;

[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.

[rmontoya]

Thank you.