| |
 |
|
Execute
operating system commands in OracleOracle Database Tips by Donald Burleson |
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.
There are three ways to issue a OS command from Oracle: - Java
- dbms_scheduler
- PL/SQL
Here is an example of chmod from dbms_scheduler: begin
dbms_scheduler.create_job (job_name => 'myjob',
job_type => 'executable',
job_action => '/bin/sh',
number_of_arguments => 2,
auto_drop => true);
dbms_scheduler.set_job_argument_value ('myjob', 1, '-c');
dbms_scheduler.set_job_argument_value ('myjob', 2, 'chmod a+r your_files');
dbms_scheduler.run_job ('myjob');
end;
/ You can also use PL/SQL to issue a chmod command: create or replace procedure
RunCHMOD( filename VARCHAR2, permission binary_integer ) is
external
library libc
name "chmod"
language C
calling standard C
parameters(
filename string,
permission long
);
/
SQL> exec RunCHMOD('/home/oracle/gplan.sql','744');
Note that you can also execute OS
commands from PL/SQL with the "host"
command":
declare
cmd varchar2(100):='C:\docs\runme.sql';
begin
host (cmd );
end;
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
and beyond 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.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
|
|
