Re: Creating a Table of a Type which contains a nested table
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