Re: Creating a Table of a Type which contains a nested table

On Feb 9, 3:31 am, "Dereck L. Dietz" <diet...@xxxxxxxxxxxxx> wrote:
"hpuxrac" <johnbhur...@xxxxxxxxxxxxx> wrote in message

news:5400e817-335b-4bca-808e-5d3a2849a2ea@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 8, 5:21 pm, "Dereck L. Dietz" <diet...@xxxxxxxxxxxxx> wrote:



Oracle 10.2.0.3
Windows Server 2003

Example 1 with VARRAY:

CREATE OR REPLACE TYPE t_test AS OBJECT
(
field1 VARCHAR2(10),
array1 t_array_va,
array2 t_array_va
);

CREATE OR REPLACE TYPE t_test_tab AS TABLE OF t_test;
/

Example 2 with NESTED TABLES:

CREATE OR REPLACE TYPE t_test AS OBJECT
(
field1 VARCHAR2(10),
array1 t_array_nt,
array2 t_array_nt
);

CREATE OR REPLACE TYPE t_test_tab AS TABLE OF t_test;
/

I've seen example 1 syntax in Oracle documentation but not for example 2.
Is the syntax for example 2 correct?

Are you getting an error message?

Syntax and documentation are available on ( among other places )
tahiti.oracle.com ...

I've been TRYING to look all weekend but nothing comes up.  Can somebody
just please answer my question and help me out?

I want to use a structure in a pipeline function that I will have to define
a type of and the base structure will be a table with one or two columns
which are defined as a nested table.  I can get the VARRAY to work but can't
figure out how to do it with Nested Tables.

Is this what you've been looking for?

SQL> create or replace type t_array_nt as table of number;
2 /

Type created.

SQL> create or replace type t_complex_type as object
2 (
3 field1 varchar2(10),
4 array1 t_array_nt,
5 array2 t_array_nt
6 );
7 /

Type created.

SQL> create or replace type t_complex_type_nt as table of
t_complex_type;
2 /

Type created.

SQL> create table test of t_complex_type
2 nested table array1 store as test$nt$array1
3 nested table array2 store as test$nt$array2
4 /

Table created.

SQL> create or replace function test_complex_obj( nt_size in number )
2 return t_complex_type_nt pipelined
3 as
4 ct t_complex_type;
5 i number := 1;
6 begin
7 while i <= nt_size loop
8 ct := t_complex_type(chr(ascii('A')-1+i), t_array_nt
(1,2,3,4,5), t_array_nt(6,7,8,9,10));
9 pipe row (ct);
10 i := i+1;
11 end loop;
12 return;
13 end test_complex_obj;
14 /

Function created

SQL> select * from table(test_complex_obj(5));

FIELD
-----
ARRAY1
-----------------------------------------
ARRAY2
-----------------------------------------
A
T_ARRAY_NT(1, 2, 3, 4, 5)
T_ARRAY_NT(6, 7, 8, 9, 10)

B
T_ARRAY_NT(1, 2, 3, 4, 5)
T_ARRAY_NT(6, 7, 8, 9, 10)

C
T_ARRAY_NT(1, 2, 3, 4, 5)
T_ARRAY_NT(6, 7, 8, 9, 10)

D
T_ARRAY_NT(1, 2, 3, 4, 5)
T_ARRAY_NT(6, 7, 8, 9, 10)

E
T_ARRAY_NT(1, 2, 3, 4, 5)
T_ARRAY_NT(6, 7, 8, 9, 10)

Hope this answers all your questions. Everything is documented in SQL
Reference and PL/SQL User's Guide and Reference. Tested on 10.2.0.4.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
.

Relevant Pages