Visit Our UNIX and Linux User Community


Pass variable to sql


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Pass variable to sql
# 1  
Old 07-31-2009
Pass variable to sql

Please help. I got these error. I'm try to pass variable extract from data-file.txt to sql file(select.sql).


cat: cannot open select
cat: cannot open *
cat: cannot open from
cat: cannot open user
cat: cannot open where
cat: cannot open name=$list;

Code:
#!/bin/bash

list=`sed q "data-file.txt"`
sqlfile=`cat select.sql`
script=$(eval echo $(cat $sqlfile))

echo "$script"
sqlplus -s user/password@instance <<EOF
$script
EOF

cat select.sql
select \* from prepaid_invoice where invoice_no=$list;

Thank you
# 2  
Old 07-31-2009
Why don't you use

Code:
sqlplus -s user/password@instance @select.sql

# 3  
Old 07-31-2009
If your sql file contains reference to variable of your script, you can do something like in this example :
Code:
$ cat ev.sh
my_var=Magic
eval text=\"$(<ev.txt)\"
cat <<EOD
$text
EOD
$ cat ev.txt
Value of variable my_var is ${my_var}  
$ ./ev.sh
Value of variable my_var is Magic 
$

Jean-Pierre.
# 4  
Old 08-02-2009
Quote:
Originally Posted by ranjithpr
Why don't you use

Code:
sqlplus -s user/password@instance @select.sql

If I do like this, when I run the script,it will be asking to enter value manually. And I don want this...

Enter value for list: 1234567890
old 1: select * from table where list='&list'
new 1: select * from prepaid_invoice where
list='1234567890'

---------- Post updated at 04:17 AM ---------- Previous update was at 04:15 AM ----------

Quote:
Originally Posted by aigles
If your sql file contains reference to variable of your script, you can do something like in this example :
Code:
$ cat ev.sh
my_var=Magic
eval text=\"$(<ev.txt)\"
cat <<EOD
$text
EOD
$ cat ev.txt
Value of variable my_var is ${my_var}  
$ ./ev.sh
Value of variable my_var is Magic 
$

Jean-Pierre.
Could u elaborate more. I'm new in shell scripting. Still a lot need to learn.
Could u give example based on my code. Or how to use it with my code. Thank you.

Previous Thread | Next Thread
Test Your Knowledge in Computers #428
Difficulty: Medium
JavaScript supports regular expressions in a manner similar to Perl.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. UNIX for Advanced & Expert Users

To pass one month range in sql script

Hi Guys, i am having .sql script which inserts data from one table to another table based on date condition, i need to pass range on based on how many number of months, for e.g set timing on; whenever sqlerror exit failure; spool myscript.log append accept start_date... (7 Replies)
Discussion started by: rohit_shinez
7 Replies

3. Shell Programming and Scripting

How to pass date to SQL server SP?

HI, I am calling a stored procedure to archieve records from the DB based on a date condition. The syntax for the same is as below: shell script - xyz.sh sqlcmd -s<servername> -U <username> -p<password> -I <embedded SQL input file> The contents of the Embedded SQL INput file are... (10 Replies)
Discussion started by: Rahul Raj
10 Replies

4. Shell Programming and Scripting

How to pass a shellscript variable to a sql file?

Hi, i wan't to pass a shellscript variable to a sql file. a.sql select $field from dual; the way i am calling this is through sqlplus field_name="sysdate" sqlplus -s username/password@hostname:port/servicename <<EOF @a.sql $field_name EOF (4 Replies)
Discussion started by: reignangel2003
4 Replies

5. Shell Programming and Scripting

How to pass string into sql query?

Hi Gurus, I have a request which needs to pass string into sql. dummy code as below: sqlplus -s user/password@instance << EOF >>output.txt set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep , select emp_no, emp_name from emp where emp_no in ('a', 'b', 'c'); exit;... (4 Replies)
Discussion started by: ken6503
4 Replies

6. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

7. Red Hat

How to pass value of pwd as variable in SED to replace variable in a script file

Hi all, Hereby wish to have your advise for below: Main concept is I intend to get current directory of my script file. This script file will be copied to /etc/init.d. A string in this copy will be replaced with current directory value. Below is original script file: ... (6 Replies)
Discussion started by: cielle
6 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

Pass a variable to SQL script

Hi Guys, I like to pass a variable to a sql file in a unix script.. I tried a below code.. var=200903 db2 -vf test.sql 200903 test.sql is as below. select * from db2.users where quarter = $1; Please tell me where i go wrong.. Thanks in advance, Magesh (2 Replies)
Discussion started by: mac4rfree
2 Replies

10. Shell Programming and Scripting

How to pass enviroment variable from csh to Informix sql script

Hello, I have a csh script that creates an environment variable. I want to pass the environment variable(CURR_TABLE_DATE) to an Informix sql script. Here is the csh: #!/bin/csh -f setenv INFORMIXSERVER market3_tcp setenv CURR_TABLE_DATE 20090714 set DATABASE = gm_cdr set SQL_DIR =... (0 Replies)
Discussion started by: jwoj
0 Replies

Featured Tech Videos