Complex Table parameter for a stored procedure

[Albert]

Hello,
I have a stored procedure with the following signature:

Code: Select all

function get_data(
    , pparents_view_path in t_aql_object_no_list
    , pparents_data_path in t_variant_named_matrix)return number is...
    

where:

Code: Select all

"T_AQL_OBJECT_NO_LIST" is table of number(12)

and t_variant_named_matrix described as:

Code: Select all

create or replace type t_variant_named_matrix is table of t_variant_named_list
...
create or replace type t_variant_named_list force as object
(
  items t_variant_named_table
)
...
create or replace type t_variant_named_table as table of t_variant_named
...
create or replace type t_variant_named force under t_variant
(
  name varchar2(1024),
 constructor function t_variant_named(pname varchar2) return self as result
)
...
create or replace type t_variant force as object
(
  value_number number
  , value_varchar2 varchar2(1024)
  , value_date date
  , value_object_no number(12)
  , value_type_no number(12)

 , constructor function t_variant(value_number number, value_varchar2 varchar2, value_date date, value_object_no number) return self as result
)

Could you specify, how I can call this stored procedure? Thanks!

[Pinturiccio]

If your parameter is of "TABLE OF ..." type, set OraclDbType to OracleDbType.Table. Assign the type name to the ObjectTypeName property. OracleParameter with OracleDbType.Table accepts values of OracleTable type. Here is an example of using your function:

Code: Select all

OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("GET_DATA", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;

var par1 = comm.Parameters.Add("pparents_view_path", OracleDbType.Table, "t_aql_object_no_list");
OracleTable table1 = new OracleTable(OracleType.GetObjectType("t_aql_object_no_list", conn));
table1.Add(1);
table1.Add(2);
par1.Value = table1;

OracleObject variant_named = new OracleObject(OracleType.GetObjectType("t_variant_named", conn));
variant_named["value_number"] = 15;
variant_named["value_varchar2"] = "varchar";
variant_named["value_date"] = DateTime.Now;
variant_named["value_object_no"] = 15;
variant_named["value_type_no"] = 15;
variant_named["name"] = "name";            

OracleTable named_table = new OracleTable(OracleType.GetObjectType("t_variant_named_table", conn));
named_table.Add(variant_named);
named_table.Add(variant_named);

OracleObject named_list = new OracleObject(OracleType.GetObjectType("t_variant_named_list", conn));
named_list["items"] = named_table;

OracleTable named_matrix = new OracleTable(OracleType.GetObjectType("t_variant_named_matrix", conn));
named_matrix.Add(named_list);

var par2 = comm.Parameters.Add("pparents_view_path", OracleDbType.Table, "t_variant_named_matrix");
par2.Value = named_matrix;

comm.Parameters.Add("RESULT", OracleDbType.Number).Direction = System.Data.ParameterDirection.ReturnValue;
comm.ExecuteNonQuery();
conn.Close();

For more information, please refer to
http://www.devart.com/dotconnect/oracle ... Table.html
http://www.devart.com/dotconnect/oracle ... jects.html

[Albert]

I have tried to call function exactly as you specified, excepting a misprint in

Code: Select all

var par2 = comm.Parameters.Add("pparents_view_path", OracleDbType.Table, "t_variant_named_matrix");

(parameter name should be "pparents_data_path")

On comm.ExecuteNonQuery() I get exception:

Code: Select all

ORA-06550: line X, column Y:
PLS-00306: wrong number or types of arguments in call to 'GET_DATA'
ORA-06550: line X, column Y:
PL/SQL: Statement ignored

What could be wrong? Thanks!

[Pinturiccio]

(parameter name should be "pparents_data_path")

You are right, sorry for the misprint. However, our application still runs correctly after we change the parameter name 'pparents_view_path' to 'pparents_data_path' for the par2 variable.

We have created an application based on the script of the get_data function that you have sent us. Maybe you have different function definition with different parameters. The code above runs successfully with the following function definition:

Code: Select all

CREATE OR REPLACE FUNCTION SEMENB.get_data(pparents_view_path IN t_aql_object_no_list, pparents_data_path IN t_variant_named_matrix)
  RETURN NUMBER
  IS
    xsettings CLOB;
  BEGIN
    RETURN 5;
  END get_data;

[Albert]

The code above runs successfully

Yes It was my fault. I passed parameters to function not in order as they described in function and I missed to set on OracleCommand PassParametersByName = true. Now everything works! Thank you for complete answer!