Oracle/Unix shell script question...

From: Steve <ocsfan_at_yahoo.com>
Date: 11 Dec 2001 11:12:01 -0800
Message-ID: <b5af4727.0112111112.453d4d33@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 - 13:12:01 CST