Passing argumnets from shell script to sql


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing argumnets from shell script to sql
# 1  
Old 10-24-2007
Passing argumnets from shell script to sql

hi I all ,

I have sql statment in my shell script , I pass two argument to the script I need to pass the this two arguments to the sql statment

example :

runsql.sh "1" "2"

sql :

updat tables_x set y=0
where
A=:x should subsituted by "1"
and
B=:y shuold subsituted bt "2"


how I can do that


thanks
# 2  
Old 10-24-2007
Your commandline arguments show up in the script as "$1", "$2", etc. If you have more than 9 arguments ($1..$9) you must use "shift" to get all of them.

There is also a special variable "$#", which holds the number of passed arguments and "$*" which holds all passed arguments.

shift will make $9 to $8, ..., $2 to $1 and lose the previous content of $1. $9 which is freed this way will hold the 10th passed argument if there is one.

Here is a little demonstration script to show you the mechanism:

Code:
call the following script with different numbers of arguments:

script.sh a b c d
script.sh "a b" c d e f g h
etc.

#! /bin/ksh

typeset    chArg1=""     # a normal variable

print - "The number of commandline arguments was: $#"
print - "all arguments: $*"

chArg1="$1"             # store the first argument in chArg1

print - "you can transfer the arguments to normal variables:"
print - "the first argument directly: $1"
print - "and here the content of chArg1: $chArg1"

print - "we now see the shift-command in action"

while [ $# -gt 0 ] ; do
     print - "Next argument..: $1"
     print - "all arguments...: $*"
     print - "Nr of arguments.: $#"
     print - "------------"
     shift
done

exit 0

With these mechanisms you should be able to not only put your arguments into your SQL-Statements, but also validate your script to recognize too few/too many or syntaktically wrong arguments.

bakunin
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing string from SQL to a BASH script

OS Solaris 10, DB oracle 10g Hello, We currently have a BASH script that runs and moves image files from a remote server to the local db server. A snippet of the code shows that we are picking up all Images that are 'mtime -1' some code... for file in `ssh user@10.200.200.10 'find... (3 Replies)
Discussion started by: JonP
3 Replies

2. Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Hello, Here is the code i have written to get the count of a plsql query back to the unix. function checkforCOIDs { countcheck=`sqlplus -s $1/$2@$3 whenever oserror exit sql.oscode rollback whenever sqlerror exit sql.sqlcode rollback set serverout on size 2000; set head off feedback off... (2 Replies)
Discussion started by: santosh2eee
2 Replies

3. Shell Programming and Scripting

Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Hi all, I am executing shell script in which I am using SQLLDR In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following Spool... (2 Replies)
Discussion started by: shekharjchandra
2 Replies

4. Shell Programming and Scripting

Help required in passing multiple arguments from a shell script to a pl/sql block

Hi, hope everyone are fine. Please find my issue below, and I request your help in the same In a configuration file, i have a variable defined as below TEST = 'One','Two','Three' I am trying to pass this variable in to a sql script which is define in a pl/sql block as follows, In the... (1 Reply)
Discussion started by: ramakanth_burra
1 Replies

5. Shell Programming and Scripting

passing arguments to sql script

Hi Gurus, i have one requirement in unix script, i have a file called abc.txt in that few lines are there with the empid, i need to read each line and pass to .sql script. ex: abc.txt 2345 2346 1243 1234 i need to pass these arguments to .sql script rom unix ex: select * from... (1 Reply)
Discussion started by: Devendar
1 Replies

6. Shell Programming and Scripting

Problem in Passing sql query for a script

Unix prompt ========= echo "Enter the query" read q ========== User has entered : SELECT * FROM employee ===================== Now the problem starts.. echo $q Output: SELECT "all files names in the PWD" FROM employee ================================================ ... (5 Replies)
Discussion started by: Niroj
5 Replies

7. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

8. Shell Programming and Scripting

passing parameter from Shell-script to Sql-script

Dear Friends, Please help me to achieve the following: I want to pass one parameter from Shell-script to Sql-script. Example: My ShellScript.sh is calling report.sql like this: /bin/sqlplus /reports.sql And My report.sql is calling many Stored-Procedures like this: exec... (0 Replies)
Discussion started by: subodhbansal
0 Replies

9. Shell Programming and Scripting

Passing PL/SQL variable value to Shell Varible

Hi my UNIX Friends, Im calling some SQL scripts through Unix Shell scripting. How do I export the value of PL/SQL variable value into a Unix shell script variable? Also could any one inform me about the 'search' and 'cut' utility of PL/SQL (like 'grep' and 'cut' in Shell scripting). ... (10 Replies)
Discussion started by: ganapati
10 Replies

10. Linux

Passing variables to sql from batch shell in linux

Hi, I need to put this command in a batch shell. sqlplus -s user/password @test.sql and in the test.sql I have this command select * from pbempl where pebempl_id = $1; How I can pass the variable $1 from the batch shell??? Thanks (2 Replies)
Discussion started by: rama71
2 Replies
Login or Register to Ask a Question