Complex Table parameter for a stored procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Albert
Posts: 20
Joined: Fri 06 Dec 2013 06:38

Complex Table parameter for a stored procedure

Post by Albert » Mon 25 Aug 2014 13:24

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!
Last edited by Albert on Thu 28 Aug 2014 12:12, edited 1 time in total.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Complex Table parameter for a stored procedure

Post by Pinturiccio » Wed 27 Aug 2014 15:20

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
Posts: 20
Joined: Fri 06 Dec 2013 06:38

Re: Complex Table parameter for a stored procedure

Post by Albert » Thu 28 Aug 2014 12:46

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Complex Table parameter for a stored procedure

Post by Pinturiccio » Thu 28 Aug 2014 15:33

Albert wrote:(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
Posts: 20
Joined: Fri 06 Dec 2013 06:38

Re: Complex Table parameter for a stored procedure

Post by Albert » Fri 29 Aug 2014 05:39

Pinturiccio wrote: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!

Post Reply