CODEWITHSUNDEEP
plsql
Peti
Peti

Reputation: 1700

How to use a comma-separated list of strings as pl/sql stored function parameter inside a "NOT IN" clause of a select statement

I have a list of comma-separated strings (from a user input) and I'd like to use this list as a parameter in a pl/sql stored function in a nested sql block using a "not in where clause".

I can't find an elegant way to make it work...

That's what I'm thinking of:

CREATE TABLE example ( somevalue VARCHAR(36) NOT NULL);
--
INSERT INTO example VALUES ('value1');
INSERT INTO example VALUES ('value2');
INSERT INTO example VALUES ('value3');
--
SELECT * FROM example;
--
CREATE OR REPLACE
  FUNCTION resultmaker(
      ignoreList IN VARCHAR2)
    RETURN VARCHAR2
  IS
    result VARCHAR2(4000);
  BEGIN
    result  := 'Here is my calculated result, using ignorelist=' || ignoreList || ':'     || CHR(10);
    FOR rec IN
    (SELECT DISTINCT somevalue
    FROM example
    WHERE somevalue NOT IN resultmaker.ignoreList -- here's my issue, the NOT IN     clause using the parameter value
    )
    LOOP
      result := result || 'not in ignorelist: ' || rec.somevalue || CHR(10);
    END LOOP;
    result := result || '.' || CHR(10);
    --
    RETURN result;
  END resultmaker;
  /
--
-- simulate function call with user input 'value2, value3'
SELECT resultmaker('value2, value3') FROM dual; -- doesn't work  
--
DROP TABLE example;
DROP FUNCTION resultmaker;

Upvotes: 0

Views: 9714

Answers (5)

lizzybeth222
lizzybeth222

Reputation: 11

Just pass the parameter like '"value2","value3"' and have your statement replace the double quote with single quotes like REPLACE(@Param1,'"','''').

Call to function: SELECT * FROM Function1('"value2","value3"')

Inside function: NOT IN REPLACE(@Param1,'"','''')

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

There are some good solutions at asktom.oracle.com regarding taking a string of values and dynamically creating an IN clause for them:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

Upvotes: 0

Art
Art

Reputation: 5782

The classic and probably correct solution would be to use PL/SQL table passing it as prameter...

Upvotes: 0

Peti
Peti

Reputation: 1700

Here's my solution using dynamic sql for my original question above:

CREATE TABLE example ( somevalue VARCHAR(36) NOT NULL);
--
INSERT INTO example VALUES ('value1');
INSERT INTO example VALUES ('value2');
INSERT INTO example VALUES ('value3');
--
SELECT * FROM example;
--
CREATE OR REPLACE
  FUNCTION resultmaker(
      ignoreList IN VARCHAR2)
    RETURN VARCHAR2
  IS
    result VARCHAR2(4000);
    example_cursor sys_refcursor;
    rec example.somevalue%type;
  BEGIN
    result := 'Here is my calculated result, using ignorelist=' || ignoreList || ':' || CHR(10);
    OPEN example_cursor FOR ( 'SELECT DISTINCT somevalue FROM example WHERE somevalue NOT IN (' || ignoreList || ')' );
    FETCH example_cursor INTO rec;
    WHILE example_cursor%found
    LOOP
      result := result || 'not in ignorelist: ' || rec || CHR(10);
      FETCH example_cursor INTO rec;
    END LOOP;
    CLOSE example_cursor;
    result := result || '.' || CHR(10);
    --
    RETURN result;
  END resultmaker; 
/
--
-- simulate function call with user input 'value2', 'value3'
SELECT resultmaker('''value2'', ''value3''') FROM dual;
--
DROP TABLE example;
DROP FUNCTION resultmaker;

Upvotes: 0

Canburak Tümer
Canburak Tümer

Reputation: 1063

In every case you should parse that input. As there is no built-in string tokenizer in PL/SQL (at least I couldn't find it) You may want to look into these options,

http://blog.tanelpoder.com/2007/06/20/my-version-of-sql-string-to-table-tokenizer/

Does PL/SQL have an equivalent StringTokenizer to Java's?

After you parsed the string, you may create a new string like:

not_in_statement varchar2(1000);
CURSOR c1 IS select token from tokenized_strings_table;
BEGIN
    not_in_statement := '('
    FOR rec IN c1 LOOP
        not_in_statement := not_in_statement || '''||rec.token||'''||','
    END LOOP
    not_in_statement := not_in_statement||')'
END

SELECT DISTINCT somevalue
FROM example
WHERE somevalue NOT IN not_in_statement

You may need to make it dynamic SQL, I did not have time to try.

Upvotes: 0

Related Questions