Execution of .bat via PL / SQL

[gpereira]

Speak guys, how are you?

I have a problem, which may be simple to solve.

I need to run a .bat routine (stored in a network directory) through a procedure. This because I have a process in the database and soon after I have to run this .bat ...

Someone knows how to call a bat via a procedure?

Hugs.

[Trevisolli]

Brother,

I believe you can do, creating a Java class inside the BD Oracle and, in this class making the call.
I'll see if I find some example and put here ...

Embrace,

[gpereira]

Cool, if you can, I am grateful.

was leaving on the side of "external jobs", available in version 11 g.

Create an external job and call it in procedure. It is possible ? Remembering that the Oracle server is Linux and the machine that is Bath is Windows.

[emer_rosa]

Yes, I do this in several procedures, it is very simple, creates the sequencement way .bat file:

Select all

 
sqlplus useroracle/senhaoracle@instanciaoracle @script.sql 

and places the procedure within the script file .sql within the same directory. Prontinho, just do not forget to finish the COM / to work on SQLPLUS.

Any doubt sends aí, use enough .bat files to perform procedures.

[gpereira]

Ola Emerson,

I think it's not quite it! It is kind of contrary to the envoy.
Precido
of a procedure that calls BAT on another server. That is, the automation from inside my Oracle session run a BAT (which calls an application) on another server.

The way you have sent, you are calling a .sql file with SQL commands for execution within your session in Oracle. This really is very used.

Thanks for the help.

[Trevisolli]

Brothers,

I made here an example of Java class call, from within the Oracle database.

Select all

 
-- 1) Criação da Java Source no BD 
-- Java Source no BD 
create or replace and compile java source named gerenciabat as 
import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileOutputStream; 
import java.nio.channels.FileChannel; 
import java.io.FileNotFoundException; 
import java.io.IOException; 
-- Classe Java  
public class gerenciaBat { 
 
     /** 
     * Executa um .bat do SO. 
     * @nomeArquivo - passar diretório e arquivo .BAT 
     * @return String 
     * @since 1.1 
     */ 
    public static String executaArquivo(String nomeArquivo){ 
         
        String retorno = ""; 
        String ponto   = ""; 
         
        return "Arquivo: "+nomeArquivo; 
   
    } 
     
  }; 
 
 
-- 2) Criação da função que irá chamar a classe 
 
CREATE OR REPLACE FUNCTION fun_gerencia_bat(pArquivo in varchar2) 
   RETURN VARCHAR2 IS 
   LANGUAGE JAVA 
   NAME 'gerenciaBat.executaArquivo(java.lang.String) return String'; 
        
-- 3) Execução 
select fun_gerencia_bat('teste.bat') from dual;                                    

is a very simple example, just from the Java call, without performing .bat still.
This part of .Bat needs to be implemented.
I read on the internet and, to be called a file of the OS, the command is used:

Select all

 
Runtime.getRuntime().exec("..\bla\arquivo.bat")   

passing the directory and the file.
I think with that, you can ride what you need.

Anything, send us.
embrace,

[emer_rosa]

It's true, I misunderstood, I do not know how to do it.

[stcoutinho]

Hello GPeira,

I think by security measure, Oracle does not allow this type of operation (run a file on another server other than the database server).

Maybe you could adopt one of these alternatives.
A) On your Windows server you install an Oracle database (it could not be the XE because it would need Java installed as component). Both bases (UNIX / Windows) would be connected by DBLink. So you would run your UNIX procedure and update some control table at the Windows base. At the Windows base, there would be a routine that would constantly verify the execution of the remote procedure. When the procedure is detected, it would fire a Java stored procedure on the Windows Oracle Base to run this command in Windows OS;
(b) In an OTN Forum of Oracle I came across the idea of ??a driver to develop UNIX program that would connect to Windows via Telnet and would executry the BAT file. This solution may lead to a security risk because you would need to keep in the IP code, user and access password. Regardless, when performing its procedure in the bank, a stored bank procedure would be triggered in Java that would execute this Java program in the UNIX machine. See this suggestion at the end of this discussion forum: https://forums.oracle.com/forums/thread ... ID=2056768

Hugs and good luck,

Sergio Coutinho