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

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Mon, 9 Feb 2009 02:22:39 -0800 (PST)
Message-ID: <7b69b632-2628-4ba8-9d3d-49001acbb1c0_at_j35g2000yqh.googlegroups.com>



On Feb 9, 3:31 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote in message
>
> news:5400e817-335b-4bca-808e-5d3a2849a2ea_at_p36g2000prp.googlegroups.com...
> On Feb 8, 5:21 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> 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 Received on Mon Feb 09 2009 - 04:22:39 CST

Original text of this message