Data Transformation Engine Database Interface Designer Reference Guide

Chapter 8 - Using Stored Procedures Stored Procedures with Object Type Parameters
Database Interface Designer Reference Guide
129
Refer to 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.