UNIX variable to SQL statement

Shell Programming and Scripting
1

The following is my script :

#!/bin/bash
echo "please give app_instance_id"
read app_instance_id
echo "id is $app_instance_id"
export app_id=app_instance_id
sqlplus -s nnviewer/lookup@//nasolora008.enterprisenet.org:1521/LOAD3 @test.sql<<EOF
SPOOL /home/tibco/MCH/Data/qa/raak/name.xls
SPOOL OFF
EXIT;
EOF

My sql file:

set linesize 300
set heading on
set pagesize 50
set feedback off
set echo off
select * from NSPL_561.CL1_APP_INSTANCE where APP_INSTANCE_ID=$app_id;

I want to pass the app_id values to the sql statement in sql file and export to excel.But when i try to execute it i am getting following error..kindly help.The APP_INSTANCE_ID datatype is number .

please give app_instance_id

615317759
id is 615317759
select * from NSPL_561.CL1_APP_INSTANCE where APP_INSTANCE_ID=$app_id
*
ERROR at line 1:
ORA-00911: invalid character
2

You can't use shell variables ($app_id) in your SQL file. It's an SQL file, not a shell script.

I would suggest that you either include the SQL directly inside the SQLPlus in your shell script, or you substitute the value in your SQL file before passing it (@) into SQLPlus.

i.e.

sed "s/\$app_id/$app_id/" test.sql > test2.sql

sqlplus .... @test2
...
1 Like
3

Your export is wrong, you are missing a $ sign

read app_instance_id
echo "id is $app_instance_id"
export app_id=$app_instance_id

BTW you can read value directly into app_id , no need to read it in one variable and later export it to a different variable.

Also put your query inside the SQL block rather than calling it from a file:

sqlplus -s nnviewer/lookup@//nasolora008.enterprisenet.org:1521/LOAD3 << EOF
SPOOL /home/tibco/MCH/Data/qa/raak/name.xls
select * from NSPL_561.CL1_APP_INSTANCE where APP_INSTANCE_ID=$app_id;
SPOOL OFF
EXIT;
EOF
4

grt but i get the following error

SP2-0226: Invalid line number
5

That's not very helpful. Please show everything you did.