Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to pass a shellscript variable to a sql file?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pass a shellscript variable to a sql file?
# 1  
Old 04-08-2014
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
Code:
select $field from dual;

the way i am calling this is through sqlplus

Code:
field_name="sysdate"
sqlplus -s username/password@hostname:port/servicename <<EOF
@a.sql $field_name
EOF

# 2  
Old 04-08-2014
You cannot pass an argument to sql script. Try this

Code:
field_name="sysdate"
sql=$( sed "s/\$field/$field_name/" a.sql)
sqlplus -s username/password@hostname:port/servicename <<EOF
$sql
EOF

# 3  
Old 04-08-2014
how will i be able to pass parameter to this sql file. what i want is to removed the prompt command and replace it with a unix variable say client_id

main.sql

Code:
set verify off
set serverout on
set timing on
set linesize 120
undefine client_id
accept client_id prompt 'Enter Client ID for EDW On-boarding:'
@create_1.sql &client_id
@create_2.sql &client_id
@create_3.sql &client_id
@create_4.sql &client_id
@create_5.sql &client_id

# 4  
Old 04-08-2014
If I understand correctly:

Code:
$ cat a.sql
select &1 from dual;
$ field_name=sysdate
$ sqlplus -s '/ as sysdba' <<EOF
> @a.sql "$field_name"
> EOF
old   1: select &1 from dual
new   1: select sysdate from dual

SYSDATE
---------
08-APR-14

$

For clearer output:

Code:
$ sqlplus -s '/ as sysdba' <<EOF
> set ver off head off pages 0
> @a.sql "$field_name"
> EOF
08-APR-14

$

This User Gave Thanks to radoulov For This Post:
anbu23
# 5  
Old 04-08-2014
Remove the prompt and use &1 instead of &client_id.
This User Gave Thanks to radoulov For This Post:
reignangel2003
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need script to pass all sql file names in a directory to DB query

Hi All, In this path /home/all_files we have follwing files and direcotries proc_edf_sot.sql proc_ssc_sot.sql func_dfg_sot.sql sot unic cmr sdc under sot directory we have other directories sql pas ref under sql directory we have sql_sot sql_mat (6 Replies)
Discussion started by: ROCK_PLSQL
6 Replies

2. Shell Programming and Scripting

Unable to pass value from .Shell script to .SQL file

Hi All, I am new to shell script. I am trying to pass value from .sh file to .sql file . But I am able to run the .sql file from .sh file with values in sql file. But I am unable to pass the values from .sh file. can some one please help to resolve this. here is my .sh file s1.sh ... (4 Replies)
Discussion started by: reddy298599
4 Replies

3. Shell Programming and Scripting

Pass command line arg to sql file

Hi all, How to pass the command line argument to a sql file Script: #!/bin/ksh if ] ; then test.sql fi My Sql Informix DB: echo "select * from table where col1 = 2234 and col2 = '$3'"|dbaccess ddname But im getting `:' unexpected error (5 Replies)
Discussion started by: Roozo
5 Replies

4. UNIX for Dummies Questions & Answers

To pass multiple arguments from file in to an sql query

Hi all , I want to pass contents from a file say f1 as arguments to a sql query which has In statement using a script example select * from table_1 where login in ( `cat f1`) ; will this work or is there any other way to do it. (1 Reply)
Discussion started by: zozoo
1 Replies

5. 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

6. Shell Programming and Scripting

How to pass arguments to SQL file passed in shell script?

Hi, I am using SYBASE database. in my script i am connecting to DB via using isql. isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b0 -w3000 -h0 -s"|" -i${MYDIR}/ABC.sql -oXYZ.txt << FINSQL i am taking a ABC.sql file to use the queries written in it and storing the output in... (3 Replies)
Discussion started by: dazdseg
3 Replies

7. 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

8. 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

9. Shell Programming and Scripting

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; #!/bin/bash list=`sed q... (3 Replies)
Discussion started by: killboy
3 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
Login or Register to Ask a Question