Pass varchar param to sql file via Batch file

Question

I have a batch file which runs multiple sql files and passes parameters to them. The input parameters are stored in a txt file which is read by batch file.

I am trying to pass varchar values to IN clause of SQL.

For e.g.

In my input file i this entry

var1="'tommy','jim'"

In Batch file

<code to read file , assuming %1 has the var1 value>
set param=%~1

sqlplus %DBCONN% @%programFolder%\test.sql %param%

test.sql (name is varchar2)

select * from table where name in (&1);

This gives error, saying invalid number as it tries to run

select * from table where name in (tommy);

If i echo right before the sql stmt, its displays 'tommy','jim' but in sql its removing jim and the single quotes ...

Please help!

Now i edited entry in Input file as var1="'''tommy''','''jim'''"

it goes as select * from table where name in ('tommy');

But it truncates the second value

Any clue how to include comma ??

Can you share more details on how you fixed this? I have following code in place //read input file- ('pdf','csv','jpeg','png') set /P ALLOWED_TYPES=<Allowed_TYPEs.txt sqlplus %connection_DB% @SQL_CMD.txt %ALLOWED_TYPES% — Cijo V J
– Cijo V J, Commented Jun 18, 2020 at 9:54

Sky · Accepted Answer · 2011-08-17 01:59:43Z

1

Finally found a way

input file - var1=('tommy','jim')

Remove the braces from the sql file select * from table where name in &1;

This works , i have no idea why was taking a comma was such an issue, there should have been some way to pass the comma ! If anyone finds out please let me know.

Thanks

answered Aug 17, 2011 at 1:59

Sky

2151 gold badge3 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick Cuff · Accepted Answer · 2011-08-16 10:12:47Z

0

The tilde (~) strips quotes from the variable.

Try using var1='tommy','jim' in your input file and set param=%1 in your batch script.

answered Aug 16, 2011 at 10:12

Patrick Cuff

30.1k12 gold badges70 silver badges95 bronze badges

1 Comment

Sky Over a year ago

If i remove tilde, the param is separated in batch itself. echo %var1% before the sql displays only 'tommy'

Collectives™ on Stack Overflow

Pass varchar param to sql file via Batch file

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related