CREATE TABLE ali_test_tab as
SELECT to_number(LEVEL) AS ID FROM dual WHERE mod(LEVEL,10)=0 CONNECT BY LEVEL<=1000

CREATE OR REPLACE PACKAGE ali_test 
IS 
       TYPE t_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; 
 
       PROCEDURE TEST_OK; 
       PROCEDURE TEST_NOK;        
END; 

CREATE OR REPLACE PACKAGE BODY ali_test 
IS 
       PROCEDURE TEST_OK 
       IS 
                 t  ali_test.t_type; 
                 t2 ali_test.t_type; 
                 c NUMBER; 
       BEGIN 
                 SELECT ID 
      BULK COLLECT INTO t 
                   FROM (SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL<=100) 
                 ; 
 
                 SELECT ID 
      BULK COLLECT INTO t2 
                   FROM (SELECT LEVEL AS ID FROM dual WHERE mod(LEVEL,10)=0 CONNECT BY LEVEL<=1000 ) q 
                   WHERE q.id IN (SELECT * FROM TABLE(t)) 
                 ; 
 
 
        SELECT COUNT(*) INTO c FROM ali_test_tab WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(t2));
        DBMS_OUTPUT.PUT_LINE('count = '||c);  
 
 
        FOR c IN (SELECT COLUMN_VALUE FROM TABLE(t2)) 
           LOOP 
             IF (c.column_value >20 ) THEN 
                         DBMS_OUTPUT.PUT_LINE(c.column_value);  
             END IF; 
          END LOOP; 
					 
					DBMS_OUTPUT.PUT_LINE('TEST_OK end');  
                    
      END; 
       
      PROCEDURE TEST_NOK 
       IS 
                 t ali_test.t_type; 
                 t2 ali_test.t_type; 
       BEGIN 
                 SELECT ID 
      BULK COLLECT INTO t 
                   FROM (SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL<=100) 
                 ; 
 
                 SELECT ID 
      BULK COLLECT INTO t2 
                   FROM (SELECT LEVEL AS ID FROM dual WHERE mod(LEVEL,10)=0 CONNECT BY LEVEL<=1000 ) q 
                   WHERE q.id IN (SELECT * FROM TABLE(t)) 
                 ; 
 
         -- ORA-00902: invalid datatype! 
         DELETE FROM ali_test_tab WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE(t2)); 
 
         DBMS_OUTPUT.PUT_LINE('deleted = '||SQL%ROWCOUNT); 
 
         ROLLBACK; 
				  
					DBMS_OUTPUT.PUT_LINE('TEST_NOK end'); 				  
 
      END; 
END; 

BEGIN 
	-- finishes with success 
  ali_test.TEST_OK; 
	 
	-- finishes with runtime error ORA-00902: invalid datatype 
	ali_test.TEST_NOK; 
END;