0

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

Wong Chloe
  • 45
  • 9
  • Does this answer your question? [Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL](https://stackoverflow.com/questions/4672545/using-the-in-clause-with-a-comma-delimited-string-from-the-output-of-a-replace) – OldProgrammer Apr 04 '22 at 20:32
  • @OldProgrammer This question is not about using string concatenation or bind variables in an `IN` clause as the OP is asking about using substitution variables (which effectively means that the client application will do a find-replace on the substituted value within the query string before it is sent to the database and the database will never see that a substitution has occurred). – MT0 Apr 04 '22 at 21:05
  • @OldProgrammer this is not about IN clause. It's about pass many string values as variables. – Wong Chloe Apr 05 '22 at 11:37

3 Answers3

2

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    thank you. This was just an example. In my case I have a lot of process, so I don't want to pass all columns in all queries all the time. This way I can set variables only one time and it works for many. I do also prefer the simple. Thanks – Wong Chloe Apr 05 '22 at 11:48
0

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.

Pankaj
  • 2,692
  • 2
  • 6
  • 18