UNIX for Advanced & Expert Users

Hello-

The code below works fine expect that it does not update the table CTL_INTERFACE "red highlight". Any idea what I'm doing wrong here?

Thanks

# coNNECT to the database and insert a row then get the new row id
cycle_id=`sqlplus -S $XXX_USER/$XXX_PW@$XXX_CONNECT << EOF

SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON
VARIABLE cycle_id NUMBER;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

INSERT INTO ctl_cycle(cycle_id,cycle_status_desc,cycle_eff_dt,cycle_desc,interface_id) values(SEQ_CTL_CYCLE.nextval,'$CYCLE_STAT',SYSDATE,'$CYCLE_DESC','$INTR
FC_ID');
commit;

SELECT MAX(cycle_id) INTO :cycle_id
FROM ctl_cycle
WHERE interface_id='$INTRFC_ID';

UPDATE CTL_INTERFACE
set CYCLE_ID=:cycle_id
where interface_id='$INTRFC_ID';
commit;

exit;
EOF`

The cycle_id may not be getting populated. Make this change and try,

Code:

exec SELECT MAX(cycle_id) INTO :cycle_id
FROM ctl_cycle
WHERE interface_id='$INTRFC_ID';

the post of ranj@chn points in a good direction. perhaps it will be useful to look at the errormessages your fine working script produces. these are stored in the unix-variable cycle_id. you can add a

Code:

spool output.lst

at the beginning of your sqlplus-script and a

Code:

spool off

at the end. the file output.lst also contains this output
mfg guenter

hey.....
you can try these follwoing 2 methods,

1.....could you please try the upate statement using another sqlplus block,as as your existing block is not returning anything......
since you have used cycle_id=`sqlplus -S $XXX_USER/$XXX_PW@$XXX_CONNECT << EOF

i think thru the max value getting out from select stmt is not populating for update one.

2.......you can combine the update with the select one as follows,
UPDATE CTL_INTERFACE
set CYCLE_ID=(SELECT MAX(cycle_id) FROM ctl_cycle
WHERE interface_id='$INTRFC_ID')
where interface_id='$INTRFC_ID';

but without any cycle_id=`sqlplus -S $XXX_USER/$XXX_PW@$XXX_CONNECT << EOF


just simple use :`sqlplus -S $XXX_USER/$XXX_PW@$XXX_CONNECT << EOF