Complex Table parameter for a stored procedure

Complex Table parameter for a stored procedure

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

Re: Complex Table parameter for a stored procedure

Postby 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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleTable.html
http://www.devart.com/dotconnect/oracle/docs/?Objects.html
Pinturiccio
Devart Team
 
Posts: 1951
Joined: Wed 02 Nov 2011 09:44

Re: Complex Table parameter for a stored procedure

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

Re: Complex Table parameter for a stored procedure

Postby 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;
Pinturiccio
Devart Team
 
Posts: 1951
Joined: Wed 02 Nov 2011 09:44

Re: Complex Table parameter for a stored procedure

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


Return to dotConnect for Oracle