Stored procedures with object type parameters

The Oracle adapter provides full support for Oracle object types to be used as parameters to stored procedures or functions. Mapping to a stored procedure call in an output card is no different than mapping to a table; object types as parameters are fully supported.

Similarly, invoking stored procedures with object type parameters using the 'call' syntax is possible. For output parameters, there is no special syntax required. Using a question mark character (?) will result in the entire object being returned from the stored procedure call. However, for input parameters, a special syntax is required to specify the objects. The syntax rules are as follows:

• The object must be contained within square brackets. An example of this is: "[.......]"
• Each element of the type is separated from other elements of the type by the pipe (|) delimiter character.
• Spaces are not allowed unless they are contained within the data itself.

see the following example. The type 'outer' is defined by the following 'create type' statements:

create type inner as object (
a_char varchar(10),
b_int  number(10,0));
create type outer as object (
x_inner inner,
y_date  date);

Assume that there is a table named 'object_holder' and a stored function 'insert_object' defined to insert an object of type 'outer' and to return the number of objects in the table. The SQL required to create this follows:

create table object_holder (
myobj outer);
create or replace function insert_object (obj in outer) return 
number is
row_count number;
begin
insert into object_holder values (obj);
select count(*) into row_count from object_holder;
return row_count;
end;

To provide an object with the following attributes:

x_inner.a_char = 'hello'
x_inner.b_int = 23
y_date = 2000-10-12 04:59:23

use the following query:

call ?= insert_object([[hello|23]2000-10-12 04:59:23])

Note that the inner object is also delimited by square brackets.