Monday, April 13, 2015

How to use plsql collections in select statment

Step 1 : Declare Collection type Globally

For Example:


 Test_Coll can be declared globally as "TYPE  Test_Coll IS TABLE OF INTEGER"

Step 2 :

DECLARE

 test_coll_tab  Test_Coll;
 MyName         VARCHAR2(100);

BEGIN

test_coll_tab =  Test_Coll();
test_coll_tab.extend;
test_coll_tab(1):=1;
test_coll_tab.extend;
test_coll_tab(2):=2;
test_coll_tab.extend;
test_coll_tab(3):=3; 
test_coll_tab.extend;
test_coll_tab(4):=4;
test_coll_tab.extend;
test_coll_tab(5):=5; 

 SELECT Name
      INTO MyName
    FROM Item
 WHERE ItemId NOT IN (select * from table(test_coll_tab));

END;



0 comments:

Post a Comment