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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close