SQL & PL/SQL

create or replace type t_circuitids is table of varchar2(100);

--Below anonymous block keeps on running and never ends

DECLARE
   v_circuitid    t_circuitids;
   v_count number;
   l_circuitids   VARCHAR2 (4000)
      := 'value1,value2,value3,value4,value5';
BEGIN
--Below query converts comma concatinated output to list and stores in nested table collection v_circuitids
   WITH a AS
        (SELECT ',' || l_circuitids || ',' AS circuitid
           FROM DUAL)
   SELECT DISTINCT TRIM (SUBSTR (circuitid,
                                 INSTR (circuitid, ',', 1, LEVEL) + 1,
                                   INSTR (circuitid, ',', 1, LEVEL + 1)
                                 - INSTR (circuitid, ',', 1, LEVEL)
                                 - 1
                                )
                        ) cid
   BULK COLLECT INTO v_circuitid
              FROM a
        CONNECT BY LEVEL <
                         LENGTH (circuitid)
                         - LENGTH (REPLACE (circuitid, ','));

   SELECT COUNT (1)
     INTO v_count
     FROM table
    WHERE name IN (SELECT COLUMN_VALUE
                                      FROM TABLE (v_circuitid));
END;
/

--I got the issue, query "SELECT COLUMN_VALUE FROM TABLE (v_circuitid)" which is used in above code is responsible for this.

--Same code works fine in Development and Test environments, But in prod it keeps on running

--I solved this issue by creating a temp table, loading all values in collection into the temp table and using that temp table in "in clause"    "

--Can any one answer why its behaving like this when i use collection in where clause?

--I am using Oracle 9i