Oracle calls external programs using Java source

Demand

Oracle calls third-party external programs. Oracle uses SQLULDR2 to quickly export large quantities of data and then use WinRAR to compress and send messages.

This document mainly implements the first two steps, the sending mail program is no longer explained here.

Original code

Authorized

Begin Dbms_java.grant_permission (' SCOTT ', ' SYS:java.io.FilePermission ', ' <<all files>> ', ' Read,write, Execute,delete '); End

Begin Dbms_java.grant_permission (' SCOTT ', ' java.lang.RuntimePermission ', ' * ', ' writefiledescriptor '); End

Java source

Create or replace and compile Java source named Jv_run_extpro as

Import java.io.*;

Import java.lang.*;

Import java.util.*;

Import java.sql.*;

Import oracle.sql.*;

public class Jv_run_extpro

{

public static void Run (String cmd) throws IOException

{

Process p=runtime.getruntime (). exec (CMD);

Streamgobbler Errorgobbler = new Streamgobbler (P.geterrorstream (), "Error");

Streamgobbler Outputgobbler = new Streamgobbler (P.getinputstream (), "Output");

Errorgobbler.start ();

Outputgobbler.start ();

Try

{

P.waitfor ();

}

catch (Interruptedexception IE)

{

System.out.println (IE);

}

}

public static class Streamgobbler extends Thread {

?

InputStream is;

String type;

?

Public Streamgobbler (InputStream is, String type) {

This.is = is;

This.type = type;

}

?

public void Run () {

try {

InputStreamReader ISR = new InputStreamReader (IS);

BufferedReader br = new BufferedReader (ISR);

String line = null;

while (line = Br.readline ()) = null) {

if (Type.equals ("Error")) {

System.out.println ("Error:" + line);

} else {

System.out.println ("Debug:" + line);

}

}

} catch (IOException IoE) {

Ioe.printstacktrace ();

}

}

}

}?

Description

Streamgobbler This class cannot be used for asynchronous reading of output from a command.

Stored Procedures

Create or Replace procedure Pro_jv_run_extpro (P_cmd varchar2) as

Language Java name ' Jv_run_extpro.run (java.lang.String) ';

Call

Begin

Pro_jv_run_extpro (' Sqluldr264.exe scott/[email protected] query= "SELECT * from emp" field=, Head=yes file=d:\ Desktop\tmp\sqluldr2\out2. TXT ');

Pro_jv_run_extpro (' "D:\Program files\winrar\rar.exe" a-ep-df "D:\Desktop\tmp\sqluldr2\20160916.rar" "D:\Desktop\ Tmp\sqluldr2\out2. TXT "');

End

Parameter description:

A: Compress files

-EP: The compression path does not appear in the package

-DF: Delete the original file after compression

Summarize

Program execution requires authorization, and if you need to view the execution log, you can do it first under Sqlplus:

Set Serveroutput on

Exec Dbms_java.set_output (5000);

Oracle calls external programs using Java source