IF statement with IN Operator in PL/SQL Code

Question

I want to compare values (VARCHAR2 with one of value from list) using IN operator. For example, value is A, list Values is B and C. Values is

Simple static code that works:

IF 'A' NOT IN ('B', 'C') THEN
    DBMS_OUTPUT.PUT_LINE('Not');
ELSE
    DBMS_OUTPUT.PUT_LINE('In');
END IF;

Non-working dynamic code:

IF 'A' NOT IN ( SELECT COLUMN_VALUE FROM APEX_STRING.SPLIT('B:C', ':') ) THEN
    DBMS_OUTPUT.PUT_LINE('Not');
ELSE
    DBMS_OUTPUT.PUT_LINE('In');
END IF;

Getting an error:

Error at line 39/48: ORA-06550: line 20, column 67:
PLS-00103: Encountered the symbol "(" when expecting one of the following:

   . ) , @ with <an identifier>
   <a double-quoted delimited-identifier> group having intersect
   minus partition start subpartition union where connect sample
The symbol "having" was substituted for "(" to continue.

Can't understand where is mistake. In SQL query similar code with IN operator works.

Answer 1

If you are not set on the IN operator, I would always suggest to use MEMBER OF when doing anthing with apex_t_varchar2 (the result of apex_string.split).

Again, as Massi already pointed out, you can't use SELECT statements without INTOor defining a cursor. So we'll shorten it to just the split function.

In your case, you could write:

BEGIN
    IF 'A' NOT MEMBER OF (APEX_STRING.SPLIT('B:C', ':')) THEN
        DBMS_OUTPUT.PUT_LINE('Not');
    ELSE
        DBMS_OUTPUT.PUT_LINE('In');
    END IF;
END;