Re: Oracle/Unix shell script question...

On 11 Dec 2001 11:12:01 -0800, ocsfan_at_yahoo.com (Steve) wrote:

>Hi. I'm hoping someone can help me out with an Oracle/Unix question I
>have. I have a Unix script (RS/6000, Oracle 8i) that adds a record to
>an Oracle table. This script is in production and a cron job runs it
>weekly, on Saturdays. But I have to change this script EVERY week to
>do two things: Increment the "jobno" field (in the script, it is
>toward the bottom - "33") and change the "datetime" field to the date
>for "this Saturday". This is a bit cumbersome and is subject to
>forgetfulness. :) Is there some way I can automate this? Can I
>change it within the script, at runtime? Or should I create a
>seperate file to keep the last "jobno" and "datetime" and update them
>each week with an automated script? But I'm very new to Oracle (have
>Progress background) and wouldn't know how to change the hardcoded
>values into something like "expr $jobno" or something similar.
>
>If someone could spell it out for me, I'd add you to my Christmas card
>list! :)
>
>Thanks a bunch in advance. If you wouldn't mind, please email replies
>to ocsfan_at_PLEASEREMOVEyahoo.com or post here. My script is below.
>
>Thanks again!
>Steve
>ocsfan_at_PLEASEREMOVEyahoo.com
>
>
>#!/bin/ksh
>#
>#Script : archive_db.ksh
>#
>#
>#Usage : archive_db.ksh <dbname> <usrname> <pwd>
>#
>
>ORACLE_SID=o21p
>
>if [[ $# != 3 ]]
>then
> print "Invalid number of parameters"
> print "Usage:"
> print " $0 db user password"
> exit 1
>fi
>if [[ $ORACLE_SID != $1 ]]
>then
> print "Database name does not match your current SID"
> exit 1;
>fi
>
>sqlplus $2/$3 << SQLEND >$WINALOGS/archive_it.log
>set term on
>set pagesize 54
>set verify off
>clear columns
>clear breaks
>clear computes
>set transaction read only;
>commit;
>
>insert into aque
>(
>jobno
>,who
>,corr_acc_no
>,group_code
>,agent_code
>,currency
>,started
>,finished
>,datetime
>,filename
>,lock_flg
>,status
>,priority
>,modes
>)
>values
>(
>33, <--------Want to increment this by one each time it is run
>'AUTO',
>0,
>'ALL',
>' ',
>' ',
>'01-JAN-1900',
>'01-JAN-1900',
>'08-DEC-2001', <----------------Want this to always be "Next"
>Saturday
>' ',
>0,
>0,
>0,
>4
>);
>commit;
>SQLEND
>exit

1 create a sequence like this
create sequence yourseq start with 1 increment 1 -- etc replace the 33 by yourseq.nextval, after having this statement once before the next run of the job
2 replace the hardcoded date by
next_day(sysdate,'SAT')

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Dec 11 2001 - 13:26:39 CST