Re: Oracle/Unix shell script question...

Sybrand Bakker schrieb:

> On 11 Dec 2001 11:12:01 -0800, ocsfan_at_yahoo.com (Steve) wrote:
>
> [...]
> 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

Hi,

This is the most elegant way for this specific problem. But you can use shell variables in the inline-SQL, if can't solve the problems by SQL.
#!/bin/sh
function insert()
{
mydate=$4
myjobno=$5
sqlplus -s $2/$3 << EOF
insert into aque
(
jobno,
...
datetime,
...
) values (
$myjobno,
...
$mydate,
...
);
commit work;
EOF
}

mydate=`date +%D%...` # adjust to your favourite date_format myjobno=... # get jobno by SQL or by ini-file

insert $ORACLE_SID $2 $3 $mydate $myjobno exit 0

So you can use any shell variable. I like it :-))

Hope this helps,

    Holger Received on Tue Dec 11 2001 - 17:11:27 CST