Getting many column list in a variable in Oracle-CodePudding

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

CodePudding user response：

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>

CodePudding user response：

If you are looking for dynamism to procedure column list to be added to IN clause then something like this can be useful - Idea is to use LISTAGG.

select listagg('''column'||'_'||level||'''',',') as column_list from dual connect by level<5;

COLUMN_LIST
--------------------------------------------------------------------------------
'column_1','column_2','column_3','column_4'

Use above generated column list in your IN query.

Else, if you are only concerned about all_tab_columns then you are better off specifying column names, as mentioned by @Littlefoot.

CodePudding user response：

You have:

the wrong quotes in the substitution variable; and
don't need the quotes around the substitution variable in the query if they are present in the replacement text of the substitution variable.

Like this:

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);

The client application (i.e. SQL*Plus or SQL Developer, which are some of the few clients that support this syntax) will effectively do a find-replace on &column_name and replace it with the string assigned in your DEFINE command.

The documentation for substitution variables is here.