Getting many column list in a variable in Oracle

Question

How to pass many string values in a variable in oracle? This is what Im trying, but this returns nothing.

SET DEFINE ON;
DEFINE column_name ='"column_1","column_2","column_3","column_4","column_5"';
    
SELECT * FROM SYS.all_tab_columns WHERE column_name in ('&column_name');

For one value in variable it works fine, but how to pass many string value? All the examples that I've seen here did not help me

Answer 1

Here's one option (if it must be DEFINE):

SQL> define column_name = 'EMPNO,DEPTNO'
SQL> select table_name, column_name from user_tab_columns where column_name in
  2    (select regexp_substr('&&column_name', '[^,]+', 1, level) from dual
  3     connect by level <= regexp_count('&&column_name', ',') + 1
  4    );

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP                            EMPNO
DEPT                           DEPTNO
EMP                            DEPTNO

SQL>

---

Though, why bother? What's wrong with simple

SQL> select table_name, column_name from user_tab_columns where column_name in ('EMPNO', 'DEPTNO');

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DEPT                           DEPTNO
EMP                            EMPNO
EMP                            DEPTNO

SQL>