Can you please give me an example to use this nested table

Thanks a lot.

hi tom,

there is one thing i cant solve. what if the table contains the type and not the list type. f.e.:

klassx@NINA.US.ORACLE.COM> create type a as object(
2 n1 number);
3 /

Type created.

klassx@NINA.US.ORACLE.COM> create type aa as table of a;
2 /

Type created.

klassx@NINA.US.ORACLE.COM> create type b as object(
2 k1 number,
3 b_aa aa);
4 /

Type created.

klassx@NINA.US.ORACLE.COM> desc a
Name Null? Type
----------------------------------------------------------------------------------- -------- -------------------------
------------------------------
N1 NUMBER

klassx@NINA.US.ORACLE.COM> desc aa
aa TABLE OF A
Name Null? Type
----------------------------------------------------------------------------------- -------- -------------------------
------------------------------
N1 NUMBER

klassx@NINA.US.ORACLE.COM> desc b
Name Null? Type
----------------------------------------------------------------------------------- -------- -------------------------
------------------------------
K1 NUMBER
B_AA AA

klassx@NINA.US.ORACLE.COM> create table l_b (
2 id number, type_b b );
create table l_b (
*
ERROR at line 1:
ORA-22913: must specify table name for nested table column or attribute

see, i want the table to include the type b which contains a list but how do i set the nested table clause at the create table statement?

i tried some things like

klassx@NINA.US.ORACLE.COM> r
1 create table l_b (
2 id number, type_b b )
3* nested table type_b.b_aa store as b ( nested table b_aa store as nt_a )
create table l_b (
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column "TYPE_B"."B_AA"
ORA-00904: : invalid identifier

klassx@NINA.US.ORACLE.COM> r
1 create table l_b (
2 id number, type_b b )
3* nested table type_b.b_aa store as b ( nested table b.aa store as nt_a )
create table l_b (
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column "TYPE_B"."B_AA"
ORA-00904: : invalid identifier

klassx@NINA.US.ORACLE.COM> r
1 create table l_b (
2 id number, type_b b )
3* nested table type_b.b_aa store as b ( nested table b.aa store as nt_a )
create table l_b (
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column "TYPE_B"."B_AA"
ORA-00904: : invalid identifier

etc. but i cant find a solution to create such a table. how can i create a table which contains an object type (not a list of objects types) which itself contains an table of objects?

ops$tkyte@ORA9IR2> create table t
  2  ( id number,
  3    type_b b
  4  )
  5  nested table type_b.b_aa store as b_aa_tab;
 
Table created.
 
 

Dear Tom. I have below tables:


CREATE TABLE hesablar
(hesabid NUMBER(7,0),
balanshesabiid NUMBER(4,0),
valyutaid NUMBER(2,0),
mushteri_id NUMBER(7,0),
fililalid NUMBER(3,0),
hesabinadi VARCHAR2(100),
hesabnomresi VARCHAR2(25))
/

CREATE TABLE hrs
(hrs_id NUMBER(2,0),
simvol CHAR(2),
hrs_adi VARCHAR2(30),
hrs_desc VARCHAR2(100))
/

For each "hesabid" value in table "hesablar" can be linked to one or more "hrs_id" value in table "hrs".

I can do it with creating another table:

CREATE TABLE hesablar_hrs_link
(hesabid NUMBER(7,0),
hrsid NUMBER(2,0))
/


So
If I have "hesabid" value in table "hesablar":

1
2
3
4

And If I have "hrs_id" value in table "hrs":

6
7
8
9

The rows in hesablar_hrs_link can be :

HesabID HRSID
1 6
1 7
2 8
2 6
3 9
3 6
3 7

How can I do this using nested tables?

Thanks in advance.

How do I update a column in a 3 level deep nested table.

Table Tab1 has columns A and B, with a 1 to many relationship from A to B. Column B is a collection type of Nested table, with two columns C and D, with a 1 to many relationship from C to D. Column D is a collection type of Nested table.

Question 1: How can I update a single element on column D.(3rd Level)
Question 2: How can I add an element to Column D.

How to create the nested tables.

Hi Tom,
      Thanks for providing such a wonderful forum for  getting queries resolved .

Here is my question :- 

I have a type and created a table (nested table) based on that type .
Now in my application i need to modify the type from varchar2(40) to varchar2(50) . For this i dropped and re-created the type but the table which was dependent on it has made the column ( referrencing type) as unused_column .

SQL> create type type_t1 as table of varchar2(40);
  2  /
Type created.

SQL> create table tab_t1
  2  ( name type_t1,
  3    id number)
  4  nested table name store as name_table;
Table created.
SQL> desc tab_t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 NAME                                               TYPE_T1
 ID                                                 NUMBER

SQL> drop type type_t1 force;

Type dropped.

SQL> desc tab_t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 ID                                                 NUMBER

SQL> create type type_t1 as table of varchar2(50);
  2  /

Type created.

SQL> desc tab_t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 ID                                                 NUMBER

SQL> select *
  2  from USER_UNUSED_COL_TABS
  3  where table_name = 'TAB_T1';

TABLE_NAME                          COUNT
------------------------------ ----------
TAB_T1                                  1


Is there any way by which i can increase the size of type value from varchar2(40) to varchar2(50) without affecting any other objects .


Thanks And Regards,
  Anupam

Hi All,

We are using 10gR2 Enterprise Edition 10.2.0.1.0
I have a table Game containing 3 columns Gameid,Crewid,Release_Yr
For each Game there are multiple rows for the crew worked on that Game.Different Games have different number of crew worked on that Game.

I want to develop a measure of prior relational commonality across teams in the video game database.
The idea is to understand the degree of familiarity people have in a team based on their prior team membership.

For example Game VG1 has 4 crew members are associated ( this will be captured as 4 rows in Game table )
Crew A,B,C,D.
So for crew A belonging to Game VG1 ,every prior association ( compare all games whose release year is equal or prior to current game release year)
will be checked.If Crew A has previously worked thrice with crew B on different Games( VG2,VG3,VG4) and once with D (VG2) them the measure will be 3+ 1= 4


I will try to explain this graphically ( with crew in columns, although in table they are in rows)

Lets Calculate Measure for VG1

Gameid Crewid Release_Yr Row ( Row is just for sake of explanation)
-------------------------- ------------------------
VG1 A B C D 2013 1

VG2 A B C D X 2012 2
VG3 A B C Z 2012 3
VG4 A B Y Z 2012 4

VG5 A Y C Z 2012 5

1)

Measure for VG1 for crew A with respect to B

Gameid Crewid Row
---------------------------------
VG2 A B 2
VG3 A B 3
VG4 A B 4
-----------------------------
Total 3

Measure for VG1 for crew A with respect to C

Gameid Crewid Row
---------------------------------
VG2 A C 2
VG3 A C 3
VG5 A C 5
-----------------------------
Total 3

Measure for VG1 for crew A with respect to D

Gameid Crewid Row
---------------------------------
VG2 A D 2
-----------------------------
Total 1

So the final measure for vg1 for crew A = 3+3+1 = 7
================================================

2) similarly calculate measure for crew B =
Measure for VG1 for crew B with respect to A + Measure for VG1 for crew B with respect to C + Measure for VG1 for crew B with respect to D
3+ 2+1 = 6

3) calculate measure for crew C =3+ 2+1 = 6

4) calculate measure for crew D =1 + 1 + 1=3

--script
create table game
(
gameid varchar2(30),
crewid varchar2(30),
release_yr number(4,0)
);

insert into game values ( 'VG1','A',2013);
insert into game values ( 'VG1','B',2013);
insert into game values ( 'VG1','C',2013);
insert into game values ( 'VG1','D',2013);

insert into game values ( 'VG2','A',2012);
insert into game values ( 'VG2','B',2012);
insert into game values ( 'VG2','C',2012);
insert into game values ( 'VG2','D',2012);
insert into game values ( 'VG2','X',2012);

insert into game values ( 'VG3','A',2012);
insert into game values ( 'VG3','B',2012);
insert into game values ( 'VG3','C',2012);
insert into game values ( 'VG3','Z',2012);

insert into game values ( 'VG4','A',2012);
insert into game values ( 'VG4','B',2012);
insert into game values ( 'VG4','Y',2012);
insert into game values ( 'VG4','Z',2012);

insert into game values ( 'VG5','A',2012);
insert into game values ( 'VG5','Y',2012);
insert into game values ( 'VG5','C',2012);
insert into game values ( 'VG5','Z',2012);
*/

--output
GAMEID CREWID RELEASE_YR Measure
VG1 A 2013 7
VG1 B 2013 6
VG1 C 2013 6
VG1 D 2013 3

VG2 A 2012 and so on
VG2 B 2012
VG2 C 2012
VG2 D 2012

I can implement solution using sql or plsql and have full freedom to use any approach.
My actual table has around 6,82,803 video games
Total records in the table Game is approx 28,03,800
Average crew per game is 4
and some games has crew as great as 900.
---------------
My thoughts on this is to use nested table POWERMULTISET_BY_CARDINALITY to find combinations of crew.
And then use submultiset.

CREATE OR REPLACE TYPE "NCSU"."CREWID_NTT" as table of varchar2(30);

create or replace TYPE CREWID_NTT_NTT AS TABLE OF CREWID_NTT;


select gameid,RELEASE_YR, crewids,cast ( POWERMULTISET_BY_CARDINALITY(crewids,2) as CREWID_NTT_NTT ) crewids_tab
from
(select gameid,RELEASE_YR,
cast ( collect(CREWID) as crewid_ntt) crewids
FROM NCSU.game
group by gameid,RELEASE_YR );

I am stuck ,i am not able to take the logic further.

Many thanks.