Thursday, April 24, 2014

Filter data from Oracle PL/SQL Collection.

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;

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete