Some Notes on ORA-04061, ORA-04065 and ORA-06508

Today, i was doing unit tests of a PL/SQL package that is developed by me. As every oracle developer knows, if you compile a package and if it has some private or public variables in either package body or spec, they have to be reinstantiniated.(for more on package states, please refer to Dependent Objects and Object Statuses In Oracle). That is if you compile a package and then call a procedure in it, you can error. But when you call it again, the state will be refreshed and no more error would occur. But the situation i have faced today was a bit more different. I got the error, although i call the proc more than once:
ORA-04061: existing state of package body "HR.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "HR.PKG""
ORA-06508: PL/SQL: could not find program unit being called
When i investigate the error, all objects are valid. Normally on second run, session state is cleaned(expected) but; in one of my runs, i get the same error again and again whereas running the same procedure. Somehow Oracle could not clean session state(unexpected). When i run the same proc in another session, in a new sql window, it works fine.
I could not understand why this happened. If I were using a connection-pooled mechanism(such as web servers), the session may not have been reinstantiniated. I have to restart restart the web server, that is clean connections.But i am using a simple desktop application to connect and execute procedures in oracle.

Running Executables From PL/SQL with DBMS_SCHEDULER

It is possible two execute programs via PLSQL. In one of my previous post i have mentioned it. Now i want demonstrate how table data can be extraxted using SQL*PLUS with calling it programatically inside PLSQL.

I will use dbms_scheduler built-in package. First create a program that is an EXECUTABLE with points SQL*PLUS. Then create a job, enable them and run the job.

--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'EXP_DATA_PRG',
program_type => 'EXECUTABLE',
program_action => 'E:\oracle\product\10.2.0\db_2\BIN\sqlplus.exe hr/hr@ORCL @"E:\exp_table.sql" jobs',
number_of_arguments => 0,
enabled => FALSE,
comments => 'Export data Program');
END;
/
SELECT * FROM all_scheduler_programs p WHERE p.program_name = 'EXP_DATA_PRG';

--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'EXP_DATA_JOB',
program_name => 'EXP_DATA_PRG',
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Export data Job');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'EXP_DATA_JOB';
--enable program and program
BEGIN
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_PRG');
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATA_JOB',
use_current_session => TRUE);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'EXP_DATA_JOB';
SELECT * FROM all_scheduler_job_log l WHERE l.job_name = 'EXP_DATA_JOB' order by 1 DESC;
--stop job
BEGIN
DBMS_SCHEDULER.stop_job(job_name => 'EXP_DATA_JOB', force => TRUE);
END;
/

SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'EXP_DATA_JOB';
--drop program, job and arguments
BEGIN
DBMS_SCHEDULER.disable(NAME => 'EXP_DATA_PRG', force => TRUE);
DBMS_SCHEDULER.drop_program(program_name => 'EXP_DATA_PRG',
force => TRUE);
DBMS_SCHEDULER.drop_job(job_name => 'EXP_DATA_JOB', force => TRUE);
END;
/
SELECT * FROM all_scheduler_job_run_details WHERE job_name = 'EXP_DATA_JOB';

After running job, e:\log.txt will be created.
Content of E:\exp_table.sql is
set heading off;
set feedback off;
spool e:\log.txt
select * from &1;
spool off;
exit;

Running Oracle Jobs with DBMS_SCHEDULER

--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'TEST_PRG',
program_type => 'STORED_PROCEDURE',
program_action => 'EVENT_HANDLING.TEST',
number_of_arguments => 4,
enabled => FALSE,
comments => 'AAE event fetcher Program');
END;
/
SELECT * FROM all_scheduler_programs p WHERE p.program_name = 'TEST_PRG';
--create program arguments
BEGIN
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 1,
argument_name => 'pin_Start',
argument_type => 'NUMBER',
default_value => '1',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 2,
argument_name => 'pin_End',
argument_type => 'NUMBER',
default_value => '10',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 3,
argument_name => 'pin_DebugMode',
argument_type => 'NUMBER',
default_value => '0',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 4,
argument_name => 'pin_Rem',
argument_type => 'NUMBER',
default_value => '0',
out_argument => FALSE);
END;
/
SELECT * FROM all_scheduler_program_args WHERE program_name = 'TEST_PRG';
--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'TEST_JOB',
program_name => 'TEST_PRG',
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'AAE event fetcher Job');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'TEST_JOB';
--enable program and program
BEGIN
DBMS_SCHEDULER.enable(NAME => 'TEST_PRG');
DBMS_SCHEDULER.enable(NAME => 'TEST_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB',
use_current_session => TRUE);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'TEST_JOB';
SELECT * FROM all_scheduler_job_log l WHERE l.job_name = 'TEST_JOB' order by 1 DESC;
--stop job
BEGIN
DBMS_SCHEDULER.stop_job(job_name => 'TEST_JOB', force => TRUE);
END;
/
--drop prg args
BEGIN
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 1);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 2);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 3);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 4);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'TEST_JOB';
--drop program, job and arguments
BEGIN
DBMS_SCHEDULER.disable(NAME => 'TEST_PRG', force => TRUE);
DBMS_SCHEDULER.drop_program(program_name => 'TEST_PRG',
force => TRUE);
DBMS_SCHEDULER.drop_job(job_name => 'TEST_JOB', force => TRUE);
END;
/
SELECT * FROM all_scheduler_job_run_details order by 1 desc;
--change repeat interval
BEGIN
DBMS_SCHEDULER.set_attribute(NAME => 'TEST_JOB',
attribute => 'repeat_interval',
VALUE => 'FREQ=MINUTELY;INTERVAL=1');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'TEST_JOB';