PL/SQL Check if a variable is in a list from Execute Immediate

Question

In a function, I'm trying to check if a variable is in a list (this list is from a EXECUTE IMMEDIATE), but with no success, I couldn't find the right syntax for that.

I also tried this solutions on PL/SQL - Use “List” Variable in Where In Clause, How to pass varchar with single quotes to Stored Proc in Oracle [duplicate] and How to add values to a VARRAY using a loop, but with no success.

I created the TABLE TYPE as below, but I don't know if it's the right way of doing this.

CREATE OR REPLACE TYPE DS_FUNCESP.TP_BI_LIST AS TABLE OF VARCHAR2(4000)

What I tried:

CREATE OR REPLACE FUNCTION DS_FUNCESP.FNBIGB_CheckDataMissing2 
(pOwn IN VARCHAR2, pTab IN VARCHAR2, pCol IN VARCHAR2) RETURN NUMBER IS

 v_str VARCHAR2(2000); 
 
BEGIN

   v_Results := DS_FUNCESP.TP_BI_LIST();
   
   v_Qtd := 1;
   
   v_str := ' SELECT ''TEST1'' AS NM_COLUMN FROM DUAL UNION ALL
              SELECT ''TEST2'' AS NM_COLUMN FROM DUAL UNION ALL
              SELECT ''TEST3'' AS NM_COLUMN FROM DUAL';
    
   EXECUTE IMMEDIATE v_str into v_Results;
   
   -- I tried to show the TABLE TYPE to check if I was in the right way
   DBMS_OUTPUT.PUT_LINE('Total rows: '||v_Results.COUNT);       

   -- Check if variable is in a table/array/list (which one is the right one?)
   IF pCol IN (v_Results) THEN dbms_output.put_line('YES'); ELSE dbms_output.put_line('NO'); 
   END IF;       
  
EXCEPTION WHEN OTHERS THEN
       RETURN 0;
       
END FNBIGB_CheckDataMissing2;

The code above doesn't work, it gives the error:

PLS-00383: type mismatch found at 'PCOL' inside an IN or NOT IN clause

How can I do this? What is the best way?

What you get for DBMS_OUTPUT.PUT_LINE('Total rows: '||v_Colunas.COUNT); Will this not giving an error if the statement before exception handling is commented — Srinika Pinnaduwage, May 15 '20 at 20:07
@WoAiNii I'm sorry, it's v_Results. I already updated the post. That variable doesn't exist. — Guilherme Matheus, May 15 '20 at 20:13

score 4 · Accepted Answer · answered May 15 '20 at 20:14

Use the MEMBER OF operator:

CREATE FUNCTION DS_FUNCESP.FNBIGB_CheckDataMissing2 (
  pOwn IN VARCHAR2,
  pTab IN VARCHAR2,
  pCol IN VARCHAR2
) RETURN NUMBER
IS
  v_str     VARCHAR2(2000); 
  v_results DS_FUNCESP.TP_BI_LIST;
BEGIN
  v_str := ' SELECT ''TEST1'' AS NM_COLUMN FROM DUAL UNION ALL
             SELECT ''TEST2'' AS NM_COLUMN FROM DUAL UNION ALL
             SELECT ''TEST3'' AS NM_COLUMN FROM DUAL';

  EXECUTE IMMEDIATE v_str BULK COLLECT INTO v_Results;

  DBMS_OUTPUT.PUT_LINE('Total rows: '||v_Results.COUNT);       

  IF pCol MEMBER OF v_Results THEN
    dbms_output.put_line('YES');
  ELSE
    dbms_output.put_line('NO'); 
  END IF;
  RETURN 1;      
END;
/

(Also, correcting v_Columas to v_Results and EXECUTE IMMEDIATE ... INTO to EXECUTE IMMEDIATE ... BULK COLLECT INTO, declaring the variables and adding a RETURN statement.)

db<>fiddle

It worked, it didn't know about using `EXECUTE IMMEDIATE ... BULK COLLECT INTO` and `MEMBER OF`. Really thank you! — Guilherme Matheus, May 15 '20 at 20:49

PL/SQL Check if a variable is in a list from Execute Immediate

1 Answers1