topic Re: Execute a SQL script WITH PARAMETERS using an execute script in Operating System - HP-UX

Execute a SQL script WITH PARAMETERS using an execute script

Sander Derix — Tue, 08 Oct 2002 09:40:40 GMT

Hi group,

I have a script called ShellSql.sh. I use it to run SQL*Plus scripts. The ShellSql.sh script has 1 parameter, the name of the sql script to be executed. The line below executes the sql script.
#----------------------------------------------#Run the SQL*Plus script, storing results in a variable
#----------------------------------------------SQLRESULT=`sqlplus -s / @$SQL_SCRIPT $OUTPUT < $DIR/endsql.txt`

I want to change my ShellSql.sh script so that the user can add parameters for the SQL script to be executed. The problem is that the number of arguments (parameters) can differ from 0 to x.

I have an idea on how to create something like it but I have no idea how to build this. The idea is to let the user type in all parameters they need after the parameter SQL_SCRIPT.
1)Check if there is anything behind the word SQL_SCRIPT;
2)Count the number of spaces between the arguments to find the total number of arguments(0 means 1 argument, 1 means 2 arguments,e tc.)
3) insert these arguments after the name of the sql_script.
This would result in something like this:
SQLRESULT=`sqlplus -s / @$SQL_SCRIPT $ARRAY_OF_ARGUMENTS
$OUTPUT < $DIR/endsql.txt`

I know its a lot to ask but I really need it.

Thnx
Sander

Re: Execute a SQL script WITH PARAMETERS using an execute script

harry d brown jr — Tue, 08 Oct 2002 11:48:33 GMT

Sander,

It's time to learn perl:

http://www.linuxjournal.com/article.php?sid=3958
http://www.stormloader.com/yonghuang/computer/OracleAndPerl.html
http://thomas.eibner.dk/oracle/dbi/
http://tlowery.hypermart.net/perl_dbi_dbd_faq.html

live free or die
harry

Re: Execute a SQL script WITH PARAMETERS using an execute script

Christian Gebhardt — Tue, 08 Oct 2002 12:29:08 GMT

Hi

I hope I understand it correctly:

ShellSql.sh sqlskript.sql para1 para2 para3

should result in

...
SQLRESULT=`sqlplus -s / @sqlskript.sql para1 para2 para3 $OUTPUT < $DIR/endsql.txt`
...

Try this

SQL_SCRIPT=`echo $* | awk '{print $1'}`
ARRAY_OF_ARGUMENTS=`echo $* | awk '{for ( i=2;i<=NF;i++) printf(" %s ", $i)}'`
echo $SQL_SCRIPT
echo $ARRAY_OF_ARGUMENTS

SQLRESULT=`sqlplus -s / @$SQL_SCRIPT $ARRAY_OF_ARGUMENTS $OUTPUT < $DIR/endsql.txt`

Chris

Re: Execute a SQL script WITH PARAMETERS using an execute script

Jean-Luc Oudart — Tue, 08 Oct 2002 12:41:34 GMT

I understand same as Christian , therefore

SQL_SCRIPT=$1
shift
ARRAY_OF_ARGUMENTS=$*

command is the same

Jean-Luc
PS : sorry Harry no need to learn perl for this !

Re: Execute a SQL script WITH PARAMETERS using an execute script

John Poff — Tue, 08 Oct 2002 12:43:31 GMT

Hi,

Here is one way to do it in a script (Korn shell).
It tests for the number of arguments passed in,
and it uses the shift command to pop off the first argument and put the rest of them into a variable if there is more than one argument passed on the command line. You don't have to worry about counting spaces to figure out the number of arguments, just use the $# variable. This way, no matter how many or how few variables they type in behind the script name, they will all wind up in the $ARGS variable.

JP

>cat testargs

#!/bin/ksh

# testargs

MYSCRIPT=$1

if [[ $# -gt 1 ]]
then
shift
ARGS=$*
fi

echo "MYSCRIPT is $MYSCRIPT"
if [[ ! -z $ARGS ]]
then
echo "args are $ARGS"
else
echo "No other args passed"
fi

>./testargs abc
MYSCRIPT is abc
No other args passed

>./testargs abc 1 2 3
MYSCRIPT is abc
args are 1 2 3

Re: Execute a SQL script WITH PARAMETERS using an execute script

John Poff — Tue, 08 Oct 2002 12:45:21 GMT

Hi again,

And it is actually even easier than my sample script. Try something like this:

MYSCRIPT=$1
shift
ARRAY_OF_ARGUMENTS=$*

then you should be able to use the SQLRESULT line just as you have it posted.

JP

Re: Execute a SQL script WITH PARAMETERS using an execute script

Wodisch — Tue, 08 Oct 2002 12:48:44 GMT

Hi Sander,

the only thing that could be missing is the way to actually USE the parameters passed to your SQL interpreter:
&1 will be substituted for the first parameter,
&2 for the seocnd, and so on...
Yes, shell uses "$1", but SQL uses "&1".

HTH,
Wodisch