PL/SQL Oracle. Best way to implement an IS_CONTAINED operator

Question

I am newbie so that maybe this question has been made one or two million times, but it is not findable / searchable in the knowledge database.

In Oracle PL/SQL, it is normal to query as follows:

select a,b,c 
from   table_foo 
where  c in (select k from table(array_bar));

But I need all the opposite of that. I need a kind of "IS_CONTAINED" operator, like this:

select a,b,c 
from   table_foo 
where  AT_LEAST_ONE_OF_THE_ITEMS_IN (select k from table(array_bar)) IS_CONTAINED_IN c;

I have my own ideas to implement it using a function with a loop. But maybe some genius has found a simple way to do it without a function. This is to say, maybe the operator IS_CONTAINED is already invented by Oracle and I haven't found it out.

Sorry if this question is repeated. I promise I have searched for it in the knowledge base. But it seems that nobody in the space-time of this Universe has never needed the super-obvious operator IS_CONTAINED.

SOLUTION:

Thanks to everybody for the suggestions. In the end, I had to use some functions, but I think I got a good solution. The situation is: I have a table of centers. Each center can be in one or more cities, this is to say, it's a 1 to N relationship. But this relationship is done using a single table. This table contains some fields. One of these fields, named 'cities_list', contains all related cities separated by semicolons. It's like this:

CODE    DESCRIPTION   CITIES_LIST
----    -----------   -----------
0001    Desc 0001     London; Berlin; NY; SF
0002    Desc 0002     Paris; Madrid; Rome
0003    Desc 0003     Berlin; Paris; London
0004    Desc 0004     Madrid;NY;Tokyo
0005    Repe 0005     Rome;Rome;Rome;LA;LA;LA;
0006    One  0006     NY
0007    Desc 0007     Sydney;Tokyo;Madrid
0008    Desc 0008     LA;SF;NY
0009    Desc 0009     Seoul;Beijing;
0010    Error0010     Beijing;;;;OZ;
0011    None 0011     (null)
0012    All  0012     London;Paris;Berlin;Madrid;Rome;NY;SF;LA;Seoul;Beijing;Tokyo;Sydney

Possible cities are: London; Paris; Berlin; Madrid; Rome; NY; SF; LA; Seoul; Beijing; Tokyo; Sydney.

In order to filter records of that table, the user can select, through a combo, one or more of those cities. Selected cities are passed to the PL/SQL query as a string (varchar) of cities separated by a hash sign (#). For instance 'London#Paris#Sydney'.

The PL/SQL has to select the records that have at least one city in common between the field 'cities_list' and the string of cities passed from the combo. First, I put here the PL/SQL code and I will explain it later on:

--1.SELECT AND EXECUTE THIS:
    SET SERVEROUTPUT ON;

--2.SELECT AND EXECUTE THIS:
    DROP TABLE table_centers; CREATE GLOBAL TEMPORARY TABLE table_centers (code VARCHAR2(10), description VARCHAR2(100), cities_list VARCHAR2(1000));

--3.SELECT AND EXECUTE THIS:
    CREATE OR REPLACE TYPE table_TYPE IS TABLE OF VARCHAR2(250);

--4.SELECT AND EXECUTE THIS:
    CREATE OR REPLACE FUNCTION VARCHAR_TO_TABLE (input_varchar VARCHAR2, separator VARCHAR2 DEFAULT ';')
    RETURN table_TYPE
        IS
            --VARS
            output_table table_TYPE := table_TYPE(); 
        BEGIN
            --For better performance, input_varchar is splitted without blanks into output_table using the regular expression [^;]+
            SELECT
                --The Keyword 'level' in statement 'regexp_substr' refers to a pseudocolumn in Oracle
                TRIM(regexp_substr(input_varchar,'[^' || separator || ']+', 1, level))
            BULK COLLECT INTO
                output_table
            FROM DUAL
            CONNECT BY
                regexp_substr(input_varchar,'[^' || separator || ']+', 1, level) IS NOT NULL;
            --Now we have all chunks into the table output_table
        RETURN output_table;
    END VARCHAR_TO_TABLE;

--5.SELECT AND EXECUTE THIS:
    CREATE OR REPLACE FUNCTION INTERSECT_TABLES(input_A VARCHAR2 , separator_A VARCHAR2 , input_B VARCHAR2 , separator_B VARCHAR2)
    RETURN NUMBER
        IS
            --VARS
            A table_TYPE;
            B table_TYPE;
            result BOOLEAN;
        BEGIN
            --Splits input_A and input_B into tables and checks if there is overlapping
            A := VARCHAR_TO_TABLE(input_A, separator_A);
            B := VARCHAR_TO_TABLE(input_B, separator_B);
            --If intersection is not empty result is TRUE
            result := A multiset intersect B is not empty;        
            -- Returns 1 if intersection is not empty, returns 0 otherwise (Note that functions called from a SQL query cannot take any BOOLEAN parameters)
            IF result = TRUE THEN RETURN 1; ELSE RETURN 0; END IF;
    END INTERSECT_TABLES;

--6.SELECT AND EXECUTE THIS:
    CREATE OR REPLACE PROCEDURE GET_CENTERS (cities_input VARCHAR2 , separator_input VARCHAR2 , out_Cursor OUT sys_refcursor)
    AS
    BEGIN       
        OPEN out_Cursor FOR
        SELECT tc.code, tc.description, tc.cities_list
        FROM   table_centers tc
        --Has current record some city in common with cities_input? If yes, select current record
        WHERE  INTERSECT_TABLES(cities_input , separator_input , tc.cities_list , ';') = 1;
    END GET_CENTERS;

--7.SELECT AND EXECUTE THIS:
    BEGIN
        DELETE FROM table_centers; COMMIT;
        INSERT ALL
            --We'll use following cities: London Paris Berlin Madrid Rome NY SF LA Seoul Beijing Tokyo Sydney
            INTO table_centers (code,description,cities_list) VALUES ('0001', 'Desc 0001', 'London; Berlin; NY; SF')
            INTO table_centers (code,description,cities_list) VALUES ('0002', 'Desc 0002', 'Paris; Madrid; Rome')
            INTO table_centers (code,description,cities_list) VALUES ('0003', 'Desc 0003', 'Berlin; Paris; London')
            INTO table_centers (code,description,cities_list) VALUES ('0004', 'Desc 0004', 'Madrid;NY;Tokyo')
            INTO table_centers (code,description,cities_list) VALUES ('0005', 'Repe 0005', 'Rome;Rome;Rome;LA;LA;LA;')
            INTO table_centers (code,description,cities_list) VALUES ('0006', 'One  0006', 'NY')
            INTO table_centers (code,description,cities_list) VALUES ('0007', 'Desc 0007', 'Sydney;Tokyo;Madrid')
            INTO table_centers (code,description,cities_list) VALUES ('0008', 'Desc 0008', 'LA;SF;NY')
            INTO table_centers (code,description,cities_list) VALUES ('0009', 'Desc 0009', 'Seoul;Beijing;')
            INTO table_centers (code,description,cities_list) VALUES ('0010', 'Error0010', 'Beijing;;;;OZ;')
            INTO table_centers (code,description,cities_list) VALUES ('0011', 'None 0011', '')
            INTO table_centers (code,description,cities_list) VALUES ('0012', 'All  0012', 'London;Paris;Berlin;Madrid;Rome;NY;SF;LA;Seoul;Beijing;Tokyo;Sydney')
        SELECT 1 FROM DUAL;
    END;

--8.SELECT AND EXECUTE THIS:
    SELECT * FROM table_centers;

I have used 'Oracle SQL Developer'. You can select the sentences one by one and execute them with the F9 key. You can also create a Package.

If someone wants to test that code, you can also select and execute with F9 the following query:

--9.SELECT AND EXECUTE THIS:
    DECLARE
        --VARS
        out_Cursor      sys_refcursor;
        cities_array    table_TYPE;
        citiesA         varchar(1000) := 'London#Paris#Berlin#Madrid#Rome#NY#SF#LA# Seoul # Beijing # Tokyo # Sydney ';
        citiesB         varchar(1000) := 'London;Paris;Berlin;Madrid;Rome;NY;SF;LA; Seoul ; Beijing ; Tokyo ; Sydney ';
        Rcode           table_centers.code%TYPE;
        Rdescription    table_centers.description%TYPE;
        Rcities_list    table_centers.cities_list%TYPE;
        CR              char := CHR(13);
        TAB             char := CHR(9);
    BEGIN
        --TEST 1
            dbms_output.put_line('TEST 1: ' || CR);
            cities_array := table_TYPE();
            cities_array := VARCHAR_TO_TABLE(citiesA, '#');
            --Now we have all cities in the array cities_array
            FOR elem in 1 .. cities_array.count LOOP
                dbms_output.put_line(TAB || elem || ':' || cities_array(elem) || '.');
            END LOOP;
        --TEST 2
            dbms_output.put_line('TEST 2: ' || CR);
            cities_array := table_TYPE();
            cities_array := VARCHAR_TO_TABLE(citiesB, ';');
            --Now we have all cities in the array cities_array
            FOR elem in 1 .. cities_array.count LOOP
                dbms_output.put_line(TAB || elem || ':' || cities_array(elem) || '.');
            END LOOP;
        --TEST 3
            dbms_output.put_line('TEST 3: ' || CR);
            GET_CENTERS(citiesA, '#', out_Cursor);
            fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            WHILE out_Cursor%FOUND LOOP
                dbms_output.put_line(TAB || 'CITIES:' || Rcities_list || '.');
                fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            END LOOP;
            close out_Cursor;
        --TEST 4
            dbms_output.put_line('TEST 4: ' || CR);
            GET_CENTERS('London#Paris#Sydney', '#', out_Cursor);
            fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            WHILE out_Cursor%FOUND LOOP
                dbms_output.put_line(TAB || 'CITIES:' || Rcities_list || '.');
                fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            END LOOP;
            close out_Cursor;
        --TEST 5
            dbms_output.put_line('TEST 5: ' || CR);
            GET_CENTERS('Madrid', '#', out_Cursor);
            fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            WHILE out_Cursor%FOUND LOOP
                dbms_output.put_line(TAB || 'CITIES:' || Rcities_list || '.');
                fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            END LOOP;
            close out_Cursor;
        --TEST 6
            dbms_output.put_line('TEST 6: ' || CR);
            GET_CENTERS('Gotham City', '#', out_Cursor);
            fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            WHILE out_Cursor%FOUND LOOP
                dbms_output.put_line(TAB || 'CITIES:' || Rcities_list || '.');
                fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            END LOOP;
            close out_Cursor;
        --TEST 7
            dbms_output.put_line('TEST 7: ' || CR);
            GET_CENTERS('', '#', out_Cursor);
            fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            WHILE out_Cursor%FOUND LOOP
                dbms_output.put_line(TAB || 'CITIES:' || Rcities_list || '.');
                fetch out_Cursor into Rcode,Rdescription,Rcities_list;
            END LOOP;
            close out_Cursor;
    END;

You can modify TEST 7 and put your own values in the first parameter of the function 'GET_CENTERS'. I have executed this query and I have got these results:

TEST 1: 
    1:London.
    2:Paris.
    3:Berlin.
    4:Madrid.
    5:Rome.
    6:NY.
    7:SF.
    8:LA.
    9:Seoul.
    10:Beijing.
    11:Tokyo.
    12:Sydney.
TEST 2: 
    1:London.
    2:Paris.
    3:Berlin.
    4:Madrid.
    5:Rome.
    6:NY.
    7:SF.
    8:LA.
    9:Seoul.
    10:Beijing.
    11:Tokyo.
    12:Sydney.
TEST 3: 
    CITIES:London; Berlin; NY; SF.
    CITIES:Paris; Madrid; Rome.
    CITIES:Berlin; Paris; London.
    CITIES:Madrid;NY;Tokyo.
    CITIES:Rome;Rome;Rome;LA;LA;LA;.
    CITIES:NY.
    CITIES:Sydney;Tokyo;Madrid.
    CITIES:LA;SF;NY.
    CITIES:Seoul;Beijing;.
    CITIES:Beijing;;;;OZ;.
    CITIES:London;Paris;Berlin;Madrid;Rome;NY;SF;LA;Seoul;Beijing;Tokyo;Sydney.
TEST 4: 
    CITIES:London; Berlin; NY; SF.
    CITIES:Paris; Madrid; Rome.
    CITIES:Berlin; Paris; London.
    CITIES:Sydney;Tokyo;Madrid.
    CITIES:London;Paris;Berlin;Madrid;Rome;NY;SF;LA;Seoul;Beijing;Tokyo;Sydney.
TEST 5: 
    CITIES:Paris; Madrid; Rome.
    CITIES:Madrid;NY;Tokyo.
    CITIES:Sydney;Tokyo;Madrid.
    CITIES:London;Paris;Berlin;Madrid;Rome;NY;SF;LA;Seoul;Beijing;Tokyo;Sydney.
TEST 6: 
TEST 7: 
    CITIES:.

The nub of the issue is the function 'INTERSECT_TABLES'. This function uses the sentence " result := A multiset intersect B is not empty; ". A and B are variables of type 'TABLE'. The operator '... multiset intersect ... is not empty' returns TRUE if tables A and B have at least one item (row) with the same value (text or number), regardless of its order or position in each table.

EXPLANATION:

I have created a temporary table named 'table_centers' and I have filled it in with some data. In order to query this table, I have created following functions:

The function 'VARCHAR_TO_TABLE' converts a string (varchar) into a 'table' type variable. You must pass a separator character as a parameter, so that each chunk of the string separated by that character will be one item (=row) of the resulting table. This way, I can use the same function regardless whether cities are separated by a semicolon (;) or by a hash (#). This function uses 'regexp_substr' and BULK COLLECT instead of a LOOP for better performance. The Keyword 'level' in statement 'regexp_substr' refers to a pseudocolumn in Oracle. See Is there a function to split a string in PL/SQL?.

In order to execute the final query to 'table_centers', I have implemented the function 'GET_CENTERS'. It has only one SELECT that selects the records of 'table_centers' that have in their field 'cities_list' at least one city in common with the string 'cities_input', which is passed as a parameter. Both strings are compared by the function 'INTERSECT_TABLES', being these strings previously splitted into tables through the function 'VARCHAR_TO_TABLE'.

The function 'INTERSECT_TABLES' is used in the clause 'WHERE' because the filtering must be done through this function. This is because a 'table' type can not be used inside a SQL query. Otherwise, you'll get an error "collection types can not be used inside a SQL statement". Therefore, using this function in the WHERE clause is mandatory. Also, boolean types can not be used, therefore, the function 'INTERSECT_TABLES' returns the numbers 0 or 1, not FALSE or TRUE.

Sample data and results would help a lot. I'm not sure what 'the opposite of that' is or what your `is_contained`/`is_contained_in` operator would do. — William Robertson, Jul 09 '18 at 13:47
- There are 10 cities: **London, NY, Paris, Rome, Madrid, Berlin, LA, SF, Toronto, Hong Kong** - An user chooses some of these 10 cities and they are placed in the array - The field c contains cities separated by semicolon, e.g, c = **"Rome;NY; London"**, A strange way for doing an 1 to N relationship - If the user chooses **"SF, LA, NY"**, and a record has c = **"Rome;NY; London"** then NY is contained and this record is selected - If the user chooses **"Hong Kong, Paris, Madrid"**, and a record has c = **"Rome;NY; London"** then no city is contained and this record is not selected — comrlr, Jul 09 '18 at 15:53
Ychdziu : It exists, but it's incorrect, so I prefer not to put it here. Thanks. — comrlr, Jul 09 '18 at 15:57
@comrlr: your database design is fundamentally flawed. Delimited text strings such as you describe above should never be used to store multiple values in a field. Instead, you should be using a junction table to store the relationship between the multiple cities and whatever it is they're related to. Doing so makes this problem go away, which is precisely why there's no such thing as your `IS_CONTAINED` operator. Best of luck. — Bob Jarvis - Слава Україні, Jul 09 '18 at 16:55
@comrlr - thanks for the sample data, but can you include it within the question instead of in comments? — William Robertson, Jul 09 '18 at 19:34
William Robertson: OK, I'll include the data in within the question. — comrlr, Jul 13 '18 at 11:30
@BobJarvis : It can't be changed. But I think that situations like that are not unusual. — comrlr, Jul 13 '18 at 11:37

score 1 · Answer 1 · answered Jul 09 '18 at 13:54

1

Perhaps you are looking for multiset conditions. For example:

create or replace type number_tt as table of number;

select 'Yes' as member
from   dual
where  1 member of number_tt(1,2,3);

select 'Yes' as subset
from   dual
where  number_tt(2,3) submultiset of number_tt(1,2,3,4);

answered Jul 09 '18 at 13:54

William Robertson

15,273
4
38
44

Thanks for the quick reply. I will try it and I will put the results here. – comrlr Jul 09 '18 at 16:22

Sentinel · Answer 2 · 2018-07-09T16:26:28.373

Taking William Robertson's answer a step further, to check if at least one member of a set is a member in another set:

create or replace type number_tt as table of number;
/

with t1(id, c) as (
  select 1, number_tt(1,2,3) from dual union all
  select 2, number_tt(4,5,6) from dual union all
  select 3, number_tt(7,8,9) from dual
)
select id, 'Yes' Intersects
from   t1
where  c multiset intersect number_tt(1,2,3,8) is not empty;

Yields the following results:

ID  INTESECTS
1   Yes
3   Yes

Updating based on the sample data provided. Note: converting from string data to sets is left as an exercise for the student ;)

create or replace type varchar30_tt as table of varchar2(30);
/

with t1(id, c) as (
  select 1, varchar30_tt('Rome','NY','London') c from dual union all
  select 2, varchar30_tt('LA','SF','Torronto') c from dual union all
  select 3, varchar30_tt('Paris','London','Rome') c from dual
)
select id
    , 'Yes' Intesects
from   t1
where  c multiset intersect varchar30_tt('SF','LA','NY') is not empty;

score 0 · Answer 3 · answered Jul 09 '18 at 13:24

0

You need OR cond -

with array_bar as (select k from table(array_bar))
select a,b,c 
from table_foo
where c in array_bar 
or    b in array_bar
or    a in array_bar;

answered Jul 09 '18 at 13:24

Ankit Bajpai

13,128
4
25
40

Thanks for the quick reply. I will try it and I will put the results here. – comrlr Jul 09 '18 at 16:22

PL/SQL Oracle. Best way to implement an IS_CONTAINED operator

3 Answers3