Hi All,

I have an issues running a shell script which contains Plsql Script from crontab or at(Command).

1)Script*************
#!/usr/bin/ksh
PATH=$PATH:/bin:/usr/bin
. /opt/admin/home/oracle/.profile


Trans_Comp_Cleanup()
{
sqlplus -s USERNAME/PASSWORD << EOM > Trans_Comp_Cleanup.log
set verify off
set heading off
set feedback off
SET serveroutput ON;
DECLARE

CURSOR processInstCur
IS
select * from comp_availability where create_dt < sysdate -90
and rownum < 2;

cur_count NUMBER := 0;
Duplicate VARCHAR2(2000) := 'NONE';

BEGIN
DBMS_OUTPUT.ENABLE (2000000);
DBMS_OUTPUT.PUT_LINE('Deleting records For Orders...');
FOR Cur_Loop IN processInstCur
LOOP

DELETE comp_availability
WHERE TRANSACTION_ID = Cur_Loop.TRANSACTION_ID
and create_dt < sysdate -90;


DELETE tran_availability
WHERE TRANSACTION_ID = Cur_Loop.TRANSACTION_ID
and create_dt < sysdate -90;



IF (Duplicate = 'NONE' OR Duplicate != Cur_Loop.TRANSACTION_ID) THEN
Duplicate := Cur_Loop.TRANSACTION_ID;
dbms_output.put_line (Cur_Loop.TRANSACTION_ID );

cur_count := cur_count +1;
END IF;



END LOOP;
COMMIT;
dbms_output.put_line ('Deleted ' || cur_count || ' Orders ');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Exception occurred : ' || SQLCODE || ' : ' || SQLERRM);
END;
/
EOM
}
Trans_Comp_Cleanup

echo Executed
at -f Trans_Comp_Cleanup.sh 11:00AM saturday

2)Entry in Crontab************* :
58 15 * * * /opt/admin/home/oracle/Trans_Comp_Cleanup.sh

I tried checking some posts but I was not able to resolve this issue.I think this is issue with environment.But was not able to resolve.
Any help is much appreciated.

Mostly path and rights issues since through crontab, which is executed as a super user may not have rights to oracle paths and directories.

This can be verified by redirecting the error messages (2&) of the shell script to a file and viewing the output.

Things to try.
(1) Change the path statement in the beginning of your script to include oracle paths for the executables like splplus
(2) Ensure that all the directories requiring access by oracle permit access by users outside of the oracle group.

If it still doesn't work, use an oracle feature.
I just got this by ggogling.
http://asktom.oracle.com/pls/asktom/...d:388480262167

OK