Bulk Collect Into Nested Table With Extend
Hi All,
I Want to get all columns from emp and dept table. So i am using bulk collect into nested table concept.
*) I have written function in three different ways. EX:1 and 2(DM_NESTTAB_BULKCOLLECT_1 & DM_NESTTAB_BULKCOLLECT_2) is not giving the desired output.
**) It Gives only EMP table columns. Which means it takes DEPT & EMP table Columns but it gives only EMP table columns.
***) I think, there is something problem with nested table Extend.
****) I want to know onething,
Can we use bulk collect into nested table with extend?
If it is yes then correct the below codes(EX:1 & EX:2) and Can you please explain me?
**** The Codes Are Given Below****
CREATE OR REPLACE TYPE NEST_TAB IS TABLE OF VARCHAR2(1000);
EX:1:
----
-------------------- Bulk Collect Into Nested Table With Extend--------------------------------
CREATE OR replace FUNCTION DM_NESTTAB_BULKCOLLECT_1
RETURN NEST_TAB
AS
l_nesttab NEST_TAB := NEST_TAB();
BEGIN
FOR tab_rec IN (SELECT table_name
FROM user_tables
WHERE table_name IN( 'EMP', 'DEPT' )) LOOP
l_nesttab.extend;
SELECT column_name
bulk collect INTO l_nesttab
FROM user_tab_columns
WHERE table_name = tab_rec.table_name
ORDER BY column_id;
END LOOP;
RETURN l_nesttab;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END DM_NESTTAB_BULKCOLLECT_1;
SELECT *
FROM TABLE(DM_NESTTAB_BULKCOLLECT_1);
OUTPUT:
-------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
* Only EMP table columns are there in the nested table.
-----------------------------------------------------------------------------------------------------
EX:2:
-----
--------------------Bulk Collect Into Nested Table With Extend Based On Count------------------------
CREATE OR replace FUNCTION DM_NESTTAB_BULKCOLLECT_2
RETURN NEST_TAB
AS
l_nesttab NEST_TAB := NEST_TAB();
v_col_cnt NUMBER;
BEGIN
FOR tab_rec IN (SELECT table_name
FROM user_tables
WHERE table_name IN( 'EMP', 'DEPT' )) LOOP
SELECT MAX(column_id)
INTO v_col_cnt
FROM user_tab_columns
WHERE table_name = tab_rec.table_name;
l_nesttab.EXTEND(v_col_cnt);
SELECT column_name
bulk collect INTO l_nesttab
FROM user_tab_columns
WHERE table_name = tab_rec.table_name
ORDER BY column_id;
END LOOP;
RETURN l_nesttab;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END DM_NESTTAB_BULKCOLLECT_2;
SELECT *
FROM TABLE(DM_NESTTAB_BULKCOLLECT_2);
OUTPUT:
-------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
* Only EMP table columns are there in the nested table.
-------------------------------------------------------------------------------------------
EX:3:
-----
--------------------Bulk Collect Into Nested Table With Extend Using For Loop--------------
CREATE OR replace FUNCTION DM_NESTTAB_BULKCOLLECT_3
RETURN NEST_TAB
AS
l_nesttab NEST_TAB := NEST_TAB();
TYPE local_nest_tab
IS TABLE OF VARCHAR2(1000);
l_localnesttab LOCAL_NEST_TAB := LOCAL_NEST_TAB();
x NUMBER := 1;
BEGIN
FOR tab_rec IN (SELECT table_name
FROM user_tables
WHERE table_name IN( 'EMP', 'DEPT' )) LOOP
SELECT column_name
bulk collect INTO l_localnesttab
FROM user_tab_columns
WHERE table_name = tab_rec.table_name
ORDER BY column_id;
FOR i IN 1..l_localnesttab.COUNT LOOP
l_nesttab.extend;
L_NESTTAB(x) := L_LOCALNESTTAB(i);
x := x + 1;
END LOOP;
END LOOP;
RETURN l_nesttab;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END DM_NESTTAB_BULKCOLLECT_3;
SELECT *
FROM TABLE(DM_NESTTAB_BULKCOLLECT_3);
OUTPUT:
------
DEPTNO
DNAME
LOC
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
* Now I Got The Desired Result Set. Dept And Emp Table Columns Are There In The Nested Table.
Thanks,
Ann