Skip to Main Content

Insert & Update into Nested Table

David BergerJun 5 2014 — edited Jun 5 2014

Hello

I have some questions because I am not sure that there would not be a better solution than mine.

CREATE TYPE bda_ty_test AS OBJECT ( id NUMBER, text VARCHAR2(100))

/

CREATE TYPE bda_tt_test AS TABLE OF bda_ty_test

/

DROP TABLE BDA_TAB PURGE

/

CREATE TABLE BDA_TAB

( num      NUMBER

, grp      VARCHAR2(100)

, nt_test  BDA_TT_TEST

)

NESTED TABLE nt_test STORE AS BDA_NT_TEST

/

-- FIRST CASE

-- ==========

INSERT INTO BDA_TAB

(num, grp, nt_test)

SELECT num, grp, nt_test FROM (SELECT 1 AS NUM, 'a' AS GRP, NULL AS NT_TEST FROM DUAL);

commit;

SELECT *

  FROM BDA_TAB   TAB

  CROSS

  JOIN TABLE(TAB.nt_test) NET;

--> It results : No rows selected. I know.. the nested table is not initialized.

--> With outer join it succeeds but the result CAN be much more because the rows in the nested table is null (Outer-Join).

SELECT *

  FROM BDA_TAB  TAB

  LEFT OUTER

  JOIN TABLE(TAB.nt_test) NET

    ON 1=1;

--> Question 1: Is it really so, that I have to use an outer join always if the nested table details are not known at the first insert and it was not initialized? Is not there a simplier select?

--> I want to Insert a record into the nested table, too:


INSERT INTO TABLE(SELECT nt_test

                    FROM BDA_TAB

                   WHERE num = 1

                 )

SELECT BDA_TY_TEST(11, 'aa') FROM DUAL

;

--> I get the Error-Message: "ORA-22908: reference to NULL table value".

--> Yes. The nested table is not initialized.


--> Question 2: Is there a way that I can Insert into the table so that the nested table is not initialized?

-- SECOND CASE

-- ===========

-- Let see an other way -> I initialize the nested table at the first insert:

TRUNCATE TABLE BDA_TAB;

INSERT INTO BDA_TAB

(num, grp, nt_test)

SELECT num, grp, nt_test FROM (SELECT 1 AS NUM, 'a' AS GRP, BDA_TT_TEST(BDA_TY_TEST(NULL, NULL)) AS NT_TEST FROM DUAL);

commit;

SELECT *

  FROM BDA_TAB   TAB

  CROSS

  JOIN TABLE(TAB.nt_test) NET;

NUM    GRP   NT_TEST    ID   TEXT

===    ===   =========  ==   ====

1      a     (DATASET)     

--> Now I get the whole row it succeeds.

--> Everything is good! ... or it looks good...


--> Now I make the FIRST Insert into the nested table:

INSERT INTO TABLE(SELECT nt_test

                    FROM BDA_TAB

                   WHERE num = 1

                 )

SELECT BDA_TY_TEST(11, 'aa') FROM DUAL

;

--> It succeeds!

--> BUT in the nested Table we have 2 rows!

SELECT *

  FROM BDA_TAB   TAB

  LEFT OUTER

  JOIN TABLE(TAB.nt_test) NET

    ON 1=1;

NUM    GRP   NT_TEST    ID   TEXT

===    ===   =========  ==   ====

1      a     (DATASET)     

1      a     (DATASET)  11   aa

--> Question 3: Is it really so, that if I have a table with a nested table AND I initialize the nested table THEN FIRSTLY I have to UPDATE the first row and THEN I can insert the records I want? It is not really simple!

Or what I'm doing wrong? How should I do it in the best way?

Thanks in advance!

Comments
Post Details
Added on Jun 5 2014
10 comments
5,474 views