This is very interesting for me. I have fine a solution for filter data form collection.
CREATE TYPE dept_t AS OBJECT
(
DEPARTMENT_ID NUMBER (10) ,
DEPARTMENT_NAME VARCHAR2 (300) ,
MANAGER_ID NUMBER (10) ,
LOCATION_ID NUMBER (10)) ;
CREATE TYPE dept_tab IS TABLE OF dept_t;
create or replace PROCEDURE DPR_COLLECTION_FILTER IS
T_RC dept_tab:=dept_tab();
T_DATA dept_tab:=dept_tab();
BEGIN
SELECT dept_t (DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID) bulk collect INTO T_RC FROM DEPARTMENTS;
SELECT dept_t (DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID) bulk collect INTO T_DATA FROM TABLE(T_RC) t WHERE t.DEPARTMENT_ID > 30;
FOR I IN T_DATA.FIRST .. T_DATA.LAST LOOP
DBMS_OUTPUT.PUT_LINE(T_DATA(I).DEPARTMENT_ID|| ' >> '||T_DATA(I).DEPARTMENT_NAME);
END LOOP;
END;
CREATE TYPE dept_t AS OBJECT
(
DEPARTMENT_ID NUMBER (10) ,
DEPARTMENT_NAME VARCHAR2 (300) ,
MANAGER_ID NUMBER (10) ,
LOCATION_ID NUMBER (10)) ;
CREATE TYPE dept_tab IS TABLE OF dept_t;
create or replace PROCEDURE DPR_COLLECTION_FILTER IS
T_RC dept_tab:=dept_tab();
T_DATA dept_tab:=dept_tab();
BEGIN
SELECT dept_t (DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID) bulk collect INTO T_RC FROM DEPARTMENTS;
SELECT dept_t (DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID) bulk collect INTO T_DATA FROM TABLE(T_RC) t WHERE t.DEPARTMENT_ID > 30;
FOR I IN T_DATA.FIRST .. T_DATA.LAST LOOP
DBMS_OUTPUT.PUT_LINE(T_DATA(I).DEPARTMENT_ID|| ' >> '||T_DATA(I).DEPARTMENT_NAME);
END LOOP;
END;
This comment has been removed by a blog administrator.
ReplyDelete