A method to execute external programs via APEX

Some time ago, when I was working on an Oracle Forms project, we used the host command to execute commands on the server. For details check this link.

It is a really cool feature, because it allows you to execute external programs from outside the Oracle Forms environment. So for instance, one can start a script or program controlling some device. The command however is not available in SQL *Plus, giving a hint to availability in Oracle APEX.

That given, it raises the question on how to `access' the OS from within APEX. The answer is somewhat unexpected: DBMS_SCHEDULER. Here the CREATE JOB procedure allows for job type `EXTERNAL'. (note that the CREATE EXTERNAL JOB privilege has to be granted.).

To illustrate the above: here is the code I used on my Oracle XE installation (Windows):

-- Execute as SYS user and replace <<user>> by the correct user.
-- external job privilege is required to make scheduler jobs 
-- of type executable.
--
-- grant create external job to <<user>>;

begin
 dbms_scheduler.create_job(   job_name      => 'TST_EXT_001'
                            , job_type      => 'EXECUTABLE'
                            , job_action    => 'c:\script1.bat');
end;    

to execute the job (or schedule it, whatever fancies you):

-- execute job
begin
 dbms_scheduler.enable('TST_EXT_001');
end;

In case nothing happens: make sure that the OracleJobSchedulerXE service is up. Here script1.bat is a somewhat academical piece of code, writing the output of the windows dir command to a textfile. Here is the listing of the batch file:

 dir c:\temp > c:\templist.txt

To make the program more interesting we add some parameters. Here and here you will find an explaination for programming Windows batch files, and Unix bash files respectively. Let's add a parameter to the batch script that allows for a different output file.

@ECHO OFF
REM
REM This program will list the files in c:\temp and
REM write the output to a specified output file.
REM
REM If no outputfile is specified the program will write to
REM default output: c:\output.txt
REM

IF "%1"=="" ( SET OUTPUTFILE=c:\output.txt ) ELSE (  SET OUTPUTFILE=%1)

DIR C:\TEMP > %OUTPUTFILE%

REM Clean up, unsetting used variables

SET OUTPUTFILE=

Using the variable in the job_action variable of create_job, will not give the desired result. Instead, we add the set_job_argument_value procedure from dbms_scheduler, after the statement defining the job:

begin
 dbms_scheduler.create_job(   job_name      => 'TST_EXT_002'
                            , job_type      => 'EXECUTABLE'
                            , job_action    => 'c:\script2.bat'
                            , number_of_arguments => 1
                            , enabled => FALSE);

 dbms_scheduler.set_job_argument_value('TST_EXT_002',1,'c:\result.txt');
end;

Now when we enable the job, we see that on the filesystem a file is created, result.txt containing the directory listing of c:\temp. In case of unexpected result: one can check the outcome of the posted job via the following query:

select *
from   all_scheduler_job_run_details
where  job_name = 'TST_EXT_002'