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;