Call shell commands in pl/SQL

Use the dbms_pipe package and create a daemon running on the OS

I think this method is complicated. I still need to use Pro * C. I didn't try it. Here is an example.

Use Java's getruntime(cmd.exe C

Java uses a lot of such advantages.

1) write a simple Java program executecmd. Java2) compile and generate executecmd. Class

Import java. Lang. runtime;
Import java. Lang. process;
Import java. Io. ioexception;
Import java. Lang. interruptedexception;

Class executecmd ...{

Public static void main (string ARGs [])... {

System. Out. println ("start executing ");

Try ...{

/** // * Execute the command using the runtime object and get
Process which controls this command */
PROCESS p = runtime.getruntime(cmd.exe C (ARGs [0]);

/** // * Use the following code to wait for the process to finish
And check the return code from the process */
Try ...{

P. waitfor ();

/** // * Handle exceptions for waitfor ()*/
} Catch (InterruptedException intexc )...{
System. out. println ("Interrupted Exception on waitFor:" + intexc. getMessage ());
}

System. out. println ("Return code from process:" + p. exitValue ());
System. out. println ("Done executing ");

/** // * Handle the exceptions for exec ()*/
} Catch (IOException e )...{

System. Out. println ("Io exception from Exec:" + E. getmessage ());
E. printstacktrace ();

}
}
}

 

Javac executecmd. Java

3) load to Oracle

$ Loadjava-user system/manager ExecuteCmd. class

4) generate a java stored procedure

Create or replace procedure executecmd (S1 varchar2)
As language Java
Name 'executecmd. Main (Java. Lang. String []) ';
/

 

Test:

SQL> set serveroutput on
SQL> call dbms_java.set_output (2000 );
SQL> EXEC executecmd ('/bin/touch/home/oracle/a.txt ');
Start executing
Return code from process: 0
Done executing
PL/SQL procedure successfully completed.

SQL> host
$ Ls/home/oracle
A.txt

The execution is successful, but there are still some problems. For example, the Environment Variable EXEC executecmd ('/bin/touch$ Home/A.txt ') execution fails. If the path is absolute or relative, the execution user (here the system user) must be granted the relevant permissions. Therefore, I think we should first write a shell executable script for what we want to do, and then call the above Code to save some trouble.

Executable jobs of Oracle

There is no research on DBMS_SCHEDULER. There is an example on the Forum, but I didn't try it out. If I have time to study it again, I should be able.

Exec dbms_scheduler.create_job (job_name => 'test13', job_type => 'executable', job_action => '/tmp/test1.sh ');
Exec dbms_scheduler.run_job (job_name => 'test13 ');