Re: Oracle/Unix shell script question...

Hi Steve,
You can create a sequence to start with whatever value you want and use it in the
insert statement.
for example,
create sequence jobno_seq increment by 1 start with 33 nomaxvalue nocache order;
then, you can use jobno_seq.nextval in the insert statement for the jobno column

if the cronjob runs every saturday only you can use SYSDATE to get the current
system datetime trunc(SYSDATE) will give you just the date if you want the next saturday's date just use trunc(sysdate+7). if the job is run
on 15-dec-2001, use trunc(sysdate+7) to get 22-dec-2001 ie the next saturday!

HTH,
Ranga Chakravarthi
OCP (it will be 33 the first time and subsequently incremented by 1) "Steve" <ocsfan_at_yahoo.com> wrote in message news:b5af4727.0112111112.453d4d33_at_posting.google.com...
> 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
>
Received on Tue Dec 11 2001 - 20:40:20 CST