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!