sqlplus pass a string containing ' ' such as 'index1','index2','index3'

I need to pass this value to my sql file cause I am executing then a where condition with a IN. For instance : Delete FROM table WHERE col IN ('index1','index2','index3')

I have an issue when I try to call this sql file from cmd using sqlplus command

set INDEXES = 'index1','index2','index3'
sqlplus script %INDEXES%

When I do that, only index1 is passed or there is a problem I tried to do that

set INDEXES = "'index1','index2','index3'"
sqlplus script %~INDEXES%

but there is a problem too

Here is my sql:

Delete FROM table WHERE col IN (&1)

Do you have any idea how I can successfully pas开发者_StackOverflow社区s the string I need ? thank you

Oracle does not come with a built-in string tokenizer. So, we have to build our own. There are several different solutions on SO. Here is one I published, which will work on 10g or higher. For earlier versions try this one.

Actually, your technique is correct.

sqlplus scott/tiger @script.sql "'index1','index2','index3'"

where script.sql is:

Delete FROM table WHERE col IN (&1)

will result in &1 being replaced, verbatim, with 'index1','index2','index3', resulting in sqlplus executing:

Delete FROM table WHERE col IN ('index1','index2','index3')

The problem i see is that the delete statement doesn't end in a semi-colon and the script doesn't commit/exit (maybe those were just excluded in your post).

So it follows that, if your command-line properly interpolates environment variables, then

set INDEXES = "'index1','index2','index3'"
sqlplus scott/tiger @script.sql %~INDEXES%

results in the same command as the first in my comment.

An easy way to see what sqlplus is doing with the command-line parameters is to simply add prompt to the beginning of the delete line in your script:

prompt Delete FROM table WHERE col IN (&1)

I would look at this as a variable in list question. These can be tricky and the answer varies based on the version of Oracle you have access to

create table aaa(aaa varchar2(50));
insert into aaa values('index1');
insert into aaa values('index2');
insert into aaa values('index3');
insert into aaa values('index4');
insert into aaa values('index5');


declare
 pindexes varchar2(100) ;
begin
 pindexes := 'index1,index2,index3';



 delete aaa where aaa in (
         select substr(pindexes,
                          loc+1,
                          nvl(
                                lead(loc) over (order by loc) - loc-1,
                                length(pindexes) - loc)
                 )
        from   (
                  select distinct (instr(pindexes, ',', 1, level)) loc
                  from   dual
                  connect by level < length(pindexes)
                 )
 ); 
 end ;
/


select * from aaa;
/
--drop table aaa;

this way you just pass in your string as 'index1,index2,index3'

this should work 9i+ http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425