[please] improve my shell/SQL*Plus script


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers [please] improve my shell/SQL*Plus script
# 1  
Old 12-05-2011
[please] improve my shell/SQL*Plus script

Hi

We generate with PL/SQL *.csv files, archive them and mail to the customer.

Here is my script (Solaris 10, ksh):

Code:
#!/bin/ksh
# Unix Shell Script Structure for PL/SQL queries with SQL*Plus

. ~/.profile

scriptdir=/opt/ora/scripts
queryname1=example
sender_mailbox=examplemailbox@example.com

SQL1 ()
{
sqlplus -S <<EOF / >> $scriptdir/"$queryname1".out

set echo Off
set term On
set pages 0
set head off
set ver off
set feed off
set trims on
set linesize 8000

SELECT SYSDATE FROM DUAL;

exit
EOF
}

rotate_output  ()
{
cp $scriptdir/"$queryname1".out $scriptdir/log_archive/"$queryname1".out.$(/bin/date '+%d%m%Y')
}

gen_header ()
{
echo "field1;field2;field3;field4" > $scriptdir/"$queryname1".out
}

mail ()
{
(cat $scriptdir/MSG.txt ; uuencode "$queryname1".out "$queryname1".csv ) | mailx -s "" -r $sender_mailbox example@example.com
}

gen_header && SQL1 && rotate_output && mail

please improve it. What can I do better or more sexy? The script works fine, but I wish to dig deeper into shell scripting. I am learning.
# 2  
Old 12-07-2011
IMHO, this is perfectly fine. I would have done a few things differently
  • not source ~/.profile, this script inheirits your enviornment
  • use variables for the files
  • not save the results in $scriptdir
I also tend to use YYYYMMDD for dates, so that ls sorting by filename works better.
[QUOTE=slashdotweenie;302579269]
Code:
#!/bin/ksh
# Unix Shell Script Structure for PL/SQL queries with SQL*Plus

scriptdir=/opt/ora/scripts
queryname=example
savedir=/opt/ora/out
outfile=${savedir}/${queryname}.out
csvfile=${savedir}/${queryname}.csv
sender_mailbox=examplemailbox@example.com

mark=$(date + ' +%Y%m%d')
SQL1 ()
{
sqlplus -S <<EOF / >> ${outfile}

set echo Off
set term On
set pages 0
set head off
set ver off
set feed off
set trims on
set linesize 8000

SELECT SYSDATE FROM DUAL;

exit
EOF
}

rotate_output  ()
{
    cp "${outfile}" "${outfile}.${mark}"
}

gen_header ()
{
    echo "field1;field2;field3;field4" > "${outfile}""
}

mail ()
{
    (
        cat $scriptdir/MSG.txt
        uuencode "${outfile}""${csvfile}"
    ) | mailx -s "" -r "${sender_mailbox}"
}

gen_header && SQL1 && rotate_output && mail

This User Gave Thanks to m.d.ludwig For This Post:
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

2. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

3. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

4. Shell Programming and Scripting

How to grep sql error in shell script and exit the script?

I need help in the following script. I want to grep the sql errors insert into the error table and exit the shell script if there is any error, otherwise keep running the scripts. Here is my script #!/bin/csh -f source .orapass set user = $USER set pass = $PASS cd /opt/data/scripts echo... (2 Replies)
Discussion started by: allinshell99
2 Replies

5. Shell Programming and Scripting

Improve the performance of a shell script

Hi Friends, I wrote the below shell script to generate a report on alert messages recieved on a day. But i for processing around 4500 lines (alerts) the script is taking aorund 30 minutes to process. Please help me to make it faster and improve the performace of the script. i would be very... (10 Replies)
Discussion started by: apsprabhu
10 Replies

6. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

7. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

8. Shell Programming and Scripting

Shell Script - SQL

Guys,Please look at a simple shell which validates presence of a customer table in OASIS schema. SQL Query for validation is wrong(Table name in quotes should not have schema name OASIS.). And shell is working correctly (with exit 1) as it will never get count=1 with this query. But it is... (2 Replies)
Discussion started by: bhush782003
2 Replies

9. Shell Programming and Scripting

SQL in shell script

Hi, I am able to insert the values into a database table from a flat file using SQL loader. Now can I do the same using only shell script.Can this be done without using sql loader and only with shell scripting(ie use of awk). Please put some light on this. Thanks in advance (3 Replies)
Discussion started by: sendhil
3 Replies

10. Shell Programming and Scripting

Shell script and sql

Hi, I have an issue. There is a file that has got some values seperated by commas. I have to write a shell script that would extrct these values and insert them into a table. How can this be done Thanx in advance (3 Replies)
Discussion started by: sendhil
3 Replies
Login or Register to Ask a Question