Problem solve Get help with specific problems with your technologies, process and projects.

Simulating an in-list operator with a schema-level collection

This tip shows how to simulate a dynamic in-list operator (such as col1 IN ('A','B','C')) using a schema-level collection.

The following script shows how to simulate a dynamic in-list operator (such as col1 IN ('A','B','C')) using a schema-level collection.

The PL/SQL block given works on 9.2.0.3.0 and above only because of the array variable based on the cursor%rowtype. Prior to 9.2.0.3.0, you would have had to use parallel scalar arrays. The routine str2tbl will work on 8i and above. Ditto for the query shown.

-- collection of strings (the max string size allowed)
CREATE OR REPLACE TYPE nt_varchar2 IS TABLE OF VARCHAR2(32767);
/

-- parses a string (up to 32K-1)
-- breaking up tokens delimited by comma
-- and puts them into a collection
-- returns the collection
-- handles empty strings too
CREATE OR REPLACE
FUNCTION str2tbl(p_str IN VARCHAR2)
RETURN nt_varchar2
IS
    l_str   VARCHAR2(32767) := p_str || ',';
    l_n     PLS_INTEGER;
    l_data  nt_varchar2 := nt_varchar2();  -- initializes collection
BEGIN
    LOOP
        l_n := INSTR(l_str, ',');  -- find delimiter
        EXIT WHEN (NVL(l_n, 0) = 0);
        l_data.EXTEND;  -- add an element to collection
        l_data(l_data.COUNT) := LTRIM(RTRIM(SUBSTR(l_str, 1, l_n-1)));  -- populate it
        l_str := SUBSTR(l_str, l_n+1);  -- chop out token just found
    END LOOP;
    RETURN l_data;
END str2tbl;
/


-- example of using it
-- first look at all the users on the system
SELECT username FROM all_users;

-- now just look for certain ones
-- NOTE "column_value" is the name of the "column" returned by the table(cast(collection))
SELECT username, user_id
FROM all_users
WHERE username IN (
        SELECT column_value
        FROM TABLE(CAST(str2tbl('PP11553,PPDEV,PCAPCDB') AS nt_varchar2))
);

-- PL/SQL example
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
DECLARE
  p_str VARCHAR2(32767) := 'PP11553,PPDEV,PCAPCDB'; -- pretend this was a parameter
  CURSOR cur_user IS
  SELECT username, user_id
  FROM all_users
  WHERE username IN (
        SELECT column_value
        FROM TABLE(CAST(str2tbl(p_str) AS nt_varchar2))
  );
  TYPE nt_user IS TABLE OF cur_user%ROWTYPE;  -- matching collection
  arr_user nt_user := nt_user();  -- initialize collection so it's not atomically null
BEGIN
  OPEN cur_user;
  FETCH cur_user BULK COLLECT INTO arr_user;  -- might as well get them all!
  CLOSE cur_user;
  IF (arr_user.COUNT > 0) THEN  -- something to process
    FOR i IN arr_user.FIRST .. arr_user.LAST LOOP  -- through collection
      DBMS_OUTPUT.put_line(arr_user(i).username || '|' || arr_user(i).user_id);  -- display
    END LOOP;
  ELSE
    DBMS_OUTPUT.put_line('Empty Set');
  END IF;
END;
/
PPDEV|27
PCAPCDB|152
PP11553|151

This was last published in June 2004

Dig Deeper on Using Oracle PL-SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close