| |
 |
|
Execute
operating system commands in OracleOracle Tips by Burleson Consulting |
Question: How can I execute operating
system commands in Oracle?
This below, from
Dr. Hall's great book "Oracle
Job Scheduling", Dr. Hall explains options. Also see about
how
dbms_scheduler replaces cron for OS shell scripts.
Not all jobs that can be scheduled are written
as stored procedures. Sometimes it is necessary to schedule jobs to
run operating system commands or batch scripts. Typically, these
types of jobs have been scheduled using an operating system
scheduler, such as CRON, since it is not possible to call
operating system command or executable scripts natively from
PL/SQL. Splitting job scheduling between two schedulers can get
confusing, so many database administrators resign themselves to only
using the operating system scheduler.
The scheduler in Oracle10g can be used to
schedule operating system commands and scripts natively, giving the
option of avoiding the operating system scheduler. This is great if
using Oracle10g, but what can be done if using Oracle 8i and 9i?
One method is to use Java stored procedures to do the work.
First, the Java stored procedure that will
actually do the work needs to be created.
--
*************************************************
-- Parameters:
-- 1)
Host command or executable file to execute.
--
*****************************************************************
CREATE OR REPLACE AND
COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void
executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
SEE
CODE DEPOT FOR FULL SCRIPT
The Host.java procedure is loaded in the
same way as a PL/SQL stored procedure.
SQL> @Host.java
In order to call the Java stored procedure, a
PL/SQL call specification must be published. This is essentially a
PL/SQL wrapper with the correct parameter list which allows the Java
code to be called as if it were a PL/SQL procedure or function.
--
*************************************************
--
*************************************************
-- Parameters:
-- 1) Host command or
executable file to execute.
--
***********************************************************
CREATE OR REPLACE PROCEDURE
host_command (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
By default, the JServer has
very little access to the operating system of the database
server. To make sure there are no problems accessing the file
system and operating system commands, the appropriate
permissions using the grant_permission procedure of the
dbms_java package must be given to the user.
PROCEDURE grant_permission (
grantee IN
VARCHAR2,
permission_type IN
VARCHAR2,
permission_name IN
VARCHAR2,
permission_action IN
VARCHAR2)
Assuming that
job_user
is the schema that owns the Host Java stored procedure, the
following permissions need to be granted:
BEGIN
DBMS_JAVA.grant_permission ('JOB_USER',
'java.io.FilePermission',
'<>', 'read ,write, execute, delete');
DBMS_JAVA.grant_permission ('JOB_USER',
'SYS:java.lang.RuntimePermission',
'writeFileDescriptor',
'');
DBMS_JAVA.grant_permission ('JOB_USER',
'SYS:java.lang.RuntimePermission',
'readFileDescriptor',
'');
END;
/
The effects of these permissions are only seen
when the grantee reconnects. The host_command procedure can be tested
as follows:
SET
SERVEROUTPUT ON SIZE 1000000
CALL
DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
host_command (p_command => ‘touch /u01/app/oracle/test_file');
END;
/
With the host_command procedure in place,
the OS commands can now be scheduled using the dbms_job
package the same way any normal PL/SQL procedure is used.
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
