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

Question

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 pass the string I need ? thank you

possible duplicate of [array or list into Oracle using cfprocparam](http://stackoverflow.com/questions/2866851/array-or-list-into-oracle-using-cfprocparam) — APC, Nov 16 '10 at 14:49

score 3 · Answer 1 · edited May 23 '17 at 12:29

3

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.

edited May 23 '17 at 12:29

Community

1
1

answered Nov 16 '10 at 13:56

APC

144,005
19
170
281

score 1 · Answer 2 · answered Nov 17 '10 at 17:41

1

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)

answered Nov 17 '10 at 17:41

nix

378
1
8

I don't think this is a secure implementation. You are opening yourself up to a whole world of SQL Injection pain! – diagonalbatman Sep 06 '11 at 10:00
@diagonalbatman: passing arguments to sqlplus will always be prone to SQL injection since sqlplus simply replaces strings with others (there is no way to make use of binds with the parameters). SQL*Plus is a tool that should be used for simple tasks, such as batch jobs but never for handling user imput :) – Vincent Malgrat Sep 06 '11 at 10:09
@Vincent very true ;-) however we all know how these things evolve! You wouldn't believe how many times i have come accross things that were meant for the "simple" jobs, and have been adapted to be used for a whole multitude of things! There are ways of handling this better - for example, you can pass these to a PL/SQL script, which then validates and cleans the input before running anything - hence no direct injection into the SQL statement – diagonalbatman Sep 06 '11 at 10:25

Harrison · Answer 3 · 2010-11-16T14:44:10.737

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

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

3 Answers3