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 Shell
  • Database: 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!

Comments

Popular posts from this blog

Oracle: Script to drop ALL tables