Posts

Showing posts from January, 2016

Shell Script: Passing parameter with Comma and Single Quote to SQL script

I developed Shell script to call SQL command in Oracle. The parameter that I need to pass is part of this type of query:

DELETE FROM <table_name>
WHERE <column_name> IN ('XX','YY')

Which value 'XX','YY' need to parameter from Shell script.

I need to call SQL in another Shell script file.

The problem is when you passing parameter contains of both of Single Quote (') and Comma (,). If you use wrong syntax in parameter, SQL will not be proceeded.

Here is the solution that I use:

Environment:

Shell: Korn ShellDatabase: Oracle
Shell script: file.sh

parameter_1=XX"','"YY
#expansion is X X " ' , ' " Y Y
`sqlplus -s <orauser>/<orapwd>@<sid> @sqlfile.sql $parameter_1`

SQL script: sqlfile.sql

DELETE FROM <table_name>
WHERE <column_name> IN ('&1')

Now, you can add more value to match your criteria.

Good luck!