Hi Tom,

Good Morning,

This is Venkatesh and I am from India,I need an SQL script to insert multiple bind variables all at a time into the "Select statement at where clause".

For eg:

Select * from Tab where col in ('val1','val2','val3'......'valn'); --is the Sql statement

If i have to enter the above values as dynamic values all with a single dynamic variable.


Select * from Tab where col in (&Val);
Enter values:
Val1 Val2 Val3 Val4...Valn.

The above script has to select fields of all the entered dynamic variables.

Please provide the script as specified and give info to work on such type of script.

SQL> variable acct varchar2(30)
SQL> exec :acct := '123,456,789'

SQL> select substr(:acct,
  2                nvl(lag(loc) over ( order by loc),0)+1,
  3                loc-nvl(lag(loc) over ( order by loc),0)-1
  4               ) list_as_rows
  5  from (
  6    select distinct (instr(:acct||',',',',1,level)) loc
  7    from dual
  8    connect by level <= length(:acct)-length(replace(:acct,','))+1
  9    );

LIST_AS_ROWS
--------------------------------
123
456
789