Oracle Database Discussions

Dear all

I'd like to find and compile all invalid objects in an instance. This should be done every day as a scheduled job with the use of DBMS_SCHEDULER.

For this, I set up the following sql-script:

---------start script--------------
set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool /tmp/run_invalid.sql

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
;

spool off;

set heading on;
set feedback on;
set echo on;

@/tmp/run_invalid.sql

exit
------ end script ----

The script ist working well when executed manualy via sqlplus. As you can see, it spools the commands to a second file (run_invalid.sql) which is the beeing executed to compile the invalid objects found.

I now want to schedule this script via DBMS_SCHEDULER (running the job every day at 7AM). Creation of the job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'compile_invalid_objects',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/scripts/sql/compile_invalid_objects.sql',
start_date => trunc(sysdate)+1+7/24,
repeat_interval => 'trunc(sysdate)+1+7/24',
enabled => TRUE,
comments => 'SQL-Script, und invalid objects zu finden und zu kompilieren'
);
END;
/

Manualy execute and error message:

BEGIN
DBMS_SCHEDULER.RUN_JOB (job_name => 'compile_invalid_objects',
use_current_session => true);
END;
/

FEHLER in Zeile 1:
ORA-27369: Job vom Typ EXECUTABLE nicht erfolgreich mit Exit-Code: 255
ORA-06512: in "SYS.DBMS_ISCHED", Zeile 150
ORA-06512: in "SYS.DBMS_SCHEDULER", Zeile 441
ORA-06512: in Zeile 2


--> Sorry for this, I'm using german localized oracle.


Unfortunately, it seems that only Shell-Scripts can be scheduled when using job_type='EXECUTABLE'. Can you confirm this?

BTW: The script is chmoded to 777, therefore it can't be a permission problem.

Is there maybe another solution with one single script using dbms_output functionality and run the script in a loop?

To complete my post, here are the commands used to create and test the job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'compile_invalid_objects',
job_type => 'EXECUTABLE',
job_action => '/tmp/compile_invalid_objects.sql',
start_date => trunc(sysdate)+1+7/24,
repeat_interval => 'trunc(sysdate)+1+7/24',
enabled => TRUE,
comments => 'SQL-Script, und invalid objects zu finden und kompilieren'
);
END;
/


Thanks for your help

Casi