UNIX variable to SQL statement


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting UNIX variable to SQL statement
# 1  
Old 12-24-2012
UNIX variable to SQL statement

The following is my script :

Code:
#!/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:

Code:
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
Code:
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


Last edited by Scott; 12-24-2012 at 11:37 AM.. Reason: Please take the time to format your posts correctly
# 2  
Old 12-24-2012
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.
Code:
sed "s/\$app_id/$app_id/" test.sql > test2.sql

sqlplus .... @test2
...

This User Gave Thanks to Scott For This Post:
# 3  
Old 12-24-2012
Your export is wrong, you are missing a $ sign
Code:
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:
Code:
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  
Old 12-27-2012
SP2-0226: Invalid line number

Quote:
Originally Posted by Scott
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.
Code:
sed "s/\$app_id/$app_id/" test.sql > test2.sql
 
sqlplus .... @test2
...


grt but i get the following error
Code:
SP2-0226: Invalid line number


Last edited by Scrutinizer; 12-27-2012 at 04:20 PM.. Reason: code tags
# 5  
Old 12-27-2012
That's not very helpful. Please show everything you did.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX Sqlplus - Capture the sql statement about to run and execution status

Greetings Experts, I am on AIX using ksh. Created a unix script which generates the CREATE OR REPLACE VIEW ... and GRANT .. statements, which are placed in a single .txt file. Now I need to execute the contents in the file (there are around 300 view creation and grant statements) in Oracle and... (4 Replies)
Discussion started by: chill3chee
4 Replies

2. Shell Programming and Scripting

SQL output to UNIX variable

I have a sql statement , i need to assign to a variable in Unix sel count(*) AS num_files from TABLE_A; i need to use "num_files" in unix statements. let me know how to assign unix variable to above num_files (1 Reply)
Discussion started by: nani1984
1 Replies

3. UNIX for Dummies Questions & Answers

SQL statement is not work on unix script

Hi, I have the following basic script. However, the statement (line 5) is not work. The output data is not able to set my request format a30. Any advise? :mad: echo " Column filename format a30"|sqlplus4 echo Input file list to check: read filelist for file in `cat $filelist.txt` do... (1 Reply)
Discussion started by: happyv
1 Replies

4. UNIX for Dummies Questions & Answers

unix script with SQL statement problem

Hello, I have a script to get the information from database, however, it's look like the loop is not work, can someone help? :confused: echo Input file list to check: read filelist for file in 'cat $filelist.txt' do echo "select FILENAME from FILE_TABLE where filename like '${file}'%;" >>... (9 Replies)
Discussion started by: happyv
9 Replies

5. Shell Programming and Scripting

Unix shell command output to a sql statement

Can i do this Say one command sed 's/:*/ /g' $summf is returning C1234 C2345 C3434 some no of rows, now this ouput i have to insert it into a DB table how do i do this?? (2 Replies)
Discussion started by: depakjan
2 Replies

6. Shell Programming and Scripting

I want to get the Unix variable value in the sql stmt

Hi All I have a requirement where in I am stuck. There is a shell script that is being developed by me. It consist of the sql stmt also. I need to export a variable called HOMEPAGE with a value say www.abc.com. and then use this $HOMEPAGE variable in the sql stmt. My ultimate aim is to fetch all... (1 Reply)
Discussion started by: amitsinha
1 Replies

7. Shell Programming and Scripting

Using Unix Variable in a PL/SQL block

Hi, I have a unix varaible called as account which hold values which i want to use in a PL/SQL block in a shell script. This variable value is being used in multiple places in the PL/SQL block and i get an erroe whenenevr is use this varaible with $prompt. Help Urgently (1 Reply)
Discussion started by: sumi_mn
1 Replies

8. Shell Programming and Scripting

how to pass a variable to an update sql statement inside a loop

hi all, i am experiencing an error which i think an incorrect syntax for the where clause passing a variable was given. under is my code. sqlplus -s ${USERNAME}/${PASSWORD}@${SID} << END1 >> $LOGFILE whenever sqlerror exit set serveroutput on size 1000000 declare l_rc ... (0 Replies)
Discussion started by: ryukishin_17
0 Replies

9. Shell Programming and Scripting

Executing a Oracle SQL statement in a UNIX script

Hi All, I need to select one column from a table based upon the passed in parameter. I tried this: sqlplus -silent $MISP_USER << EOF set feedback off; set verify off; set sqlprompt "" SELECT mail_flag FROM dailyjobs WHERE job_name = '$1'; exit 0 EOF exit... (1 Reply)
Discussion started by: ganga.dharan
1 Replies

10. Shell Programming and Scripting

sql query variable not exactly unix

I know htis isnt exactly unix.... but hopefully someone can help me or direct me someplace to get help. I can run sql queries in scripts against my informix db using: dbaccess mydb myquery.sql >> sql.output I need to write my script to select based on todays date. Its very... (5 Replies)
Discussion started by: MizzGail
5 Replies
Login or Register to Ask a Question