fro

Today I came across an issue where I needed to compare one list to another in Oracle before I could return the result set. One list is in a column of the table that I want to return. The other list is going to be passed in. I want the row returned if any value in the first list matches any value in the second. After doing a bit of searching, this is what I came up with.

I thought that it would be easier to work with the list if I could mutate it into it's own table. A friend of mine created a listToTable function in MS SQL Server a while back, so I knew there had to be an Oracle version out there. So I found this link , which I've modified slightly below.

/* you must first create a type for the returned table */
CREATE OR REPLACE TYPE split_table AS TABLE OF VARCHAR2(32767);

/* now we create the actual function */
CREATE OR REPLACE FUNCTION udf_split
    (
        nList VARCHAR2,
        nDelim VARCHAR2 := ','
    ) RETURN split_table PIPELINED
    IS
        wID PLS_INTEGER;
        wList VARCHAR2(32767) := nList;
        wValue VARCHAR2(32767);
    BEGIN
        LOOP
            wID := INSTR(wList,nDelim);
            IF wID > 0 THEN
                PIPE ROW(SUBSTR(wList, 1, wID - 1));
                wList := SUBSTR(wList, wID + LENGTH(nDelim));
            ELSE
                PIPE ROW(wList);
                EXIT;
            END IF;
        END LOOP;
    RETURN;
END udf_split;

From my understanding VARCHAR2(32767) is the largest varchar field you can have.

Now we have to create the package that we're going to call to get our results. This package will contain the procedure that will take in one list and compare it to another using the udf_split function twice.

/* first we create the package */
CREATE PACKAGE upk_test
AS
  TYPE cursor_type IS REF CURSOR;
  PROCEDURE getResults(
    pList IN VARCHAR2,
    pResults OUT cursor_type);
END upk_test;

/* then we create the package body */
CREATE OR REPLACE PACKAGE BODY upk_test
AS
  PROCEDURE getResults(
    pList IN VARCHAR2,
    pResults OUT cursor_type)
  IS
  BEGIN
    OPEN pResults FOR
      SELECT *
      FROM (
        SELECT a.*,
          CASE WHEN a.list_a IS NOT NULL THEN (
            SELECT COUNT(*)
            FROM TABLE(CAST(udf_split(a.list_a) AS split_table)) b
              INNER JOIN TABLE(CAST(udf_split(pList) AS split_table)) c
                ON b.column_value = c.column_value)
            ELSE 0
          END AS allowed
        FROM my_table a) d
      WHERE d.allowed > 0;
  END;
END upk_menu;

Now you can call the procedure for ColdFusion as such:

<cfstoredproc datasource="#myDSN#" procedure="upk_test.getResults">
   <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="pList" value="#someList#" />
   <cfprocresult name="menu" />
</cfstoredproc>

The above takes in a list like "1,3,7" and compares it to the list column of a table like:

Since rows 2 and 3 have a match to the input list, the package will return the following result set:

What do you think? Is this a long way of going about what I'm trying to do? I would imagine that a good REGEXP would do the trick, but I'm not that good with REGEXP.

Anyone?