good day
Tom
-
I am writing to you because I am having problems with a development where I need to access a linux server to extract files dynamically and place them on my windows server using the scp command supported in a java library. but it is failing because it tells me that the command is not recognized "neither external nor internal".
that could be happening?
I append my code.
very grateful for your help
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];
// Use the appropriate path for your windows version.
//finalCommand[0] = "C:\\winnt\\system32\\cmd.exe"; // Windows NT/2000
finalCommand[0] = "C:\\windows\\system32\\cmd.exe"; // Windows XP/2003
//finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe"; // Windows 64-bit
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr = Runtime.getRuntime().exec(finalCommand);
pr.waitFor();
new Thread(new Runnable(){
public void run() {
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch (Exception ex) {}
}
}
}).start();
new Thread(new Runnable(){
public void run() {
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}
};
/
create or replace procedure runOn ( p_cmd in varchar2)
as
-- Created : 03/09/2018 10:40:00
-- Purpose : Ejecutar comando cmd out scope pl/sql
language java
name 'Host.executeCommand (java.lang.String)';
/
--
DECLARE
l_schema VARCHAR2(30) := 'VENTAS'; -- Adjust as required.
BEGIN
DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/
SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
begin
runOn ('cd c:\com\interfases\entrada');
runOn ('C:\Users\ecorona\Documents\MobaXterm\slash\bin\scp.exe root@999.99.99.999:/root/2018/img/154819-2.jpeg .');
end;
/
Call terminado.
Process err :The system cannot find the path specified.
Procedimiento PL/SQL terminado correctamente.
OK, I'm going to suggest a different approach because I think you'll find it simpler.
First we'll create an external table which has a pre-processor
SQL> create or replace directory TEMP as 'c:\temp';
Directory created.
SQL> create or replace directory BIN as 'c:\bin';
Directory created.
SQL>
SQL> create table host_command_external
2 ( text varchar2(4000) )
3 organization external
4 (
5 type oracle_loader
6 default directory temp
7 access parameters
8 (
9 records delimited by newline nologfile
10 preprocessor bin: 'run_command.cmd'
11 fields terminated by whitespace
12 ( text position(1:4000) )
13 )
14 location ('dummy.txt')
15 )
16 reject limit unlimited;
Table created.
Now that file 'dummy.txt' *must* exist but we don't care about the contents - so we just make an empty file
SQL> host touch c:\temp\dummy.txt
What we are really interested it in the contents of 'run_command.cmd'. So i'll add this first just as a test:
c:\bin\run_command.cmd
===============
@echo off
echo This is a test
and then query my table
SQL> select * from host_command_external;
TEXT
------------------------------------------
This is a test
Ok...you can see where this is going - *querying* my table will *run* the run_command.cmd script.
So now I'll augment it with UTL_FILE
SQL> declare
2 f utl_file.file_type;
3 begin
4 f := utl_file.fopen('BIN','run_command.cmd','w');
5 utl_file.put_line(f,'C:\bin\usr\local\wbin\ls -l c:\temp');
6 utl_file.fclose(f);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select * from host_command_external;
TEXT
------------------------------------------------------------------------------------------------------
c:\oracle\product\12.2\DATABASE>C:\bin\usr\local\wbin\ls -l c:\temp
total 1971276
-rw-rw-rw- 1 user group 105791 Jun 2 2017 122_download_now.jpg
-rw-rw-rw- 1 user group 134220 Sep 12 2017 12c.png
-rw-rw-rw- 1 user group 248032 Aug 23 11:08 18c_pic1.jpg
-rw-rw-rw- 1 user group 414154 Aug 23 11:08 18c_pic2.jpg
So dynamically changing the content of the script also works. So we can wrap that up in a function to make it nice and simple to use
SQL> create or replace
2 function host_command(p_command varchar2) return sys.odcivarchar2list pipelined is
3 f utl_file.file_type;
4 begin
5 f := utl_file.fopen('BIN','run_command.cmd','w');
6 utl_file.put_line(f,p_command);
7 utl_file.fclose(f);
8
9 for i in ( select * from host_command_external )
10 loop
11 pipe row ( i.text );
12 end loop;
13 return;
14 end;
15 /
Function created.
SQL>
SQL> select * from host_command('C:\bin\usr\local\wbin\ls -l c:\temp');
COLUMN_VALUE
-------------------------------------------------------------------------------------------
c:\oracle\product\12.2\DATABASE>C:\bin\usr\local\wbin\ls -l c:\temp
total 1971276
-rw-rw-rw- 1 user group 105791 Jun 2 2017 122_download_now.jpg
-rw-rw-rw- 1 user group 134220 Sep 12 2017 12c.png
-rw-rw-rw- 1 user group 248032 Aug 23 11:08 18c_pic1.jpg
-rw-rw-rw- 1 user group 414154 Aug 23 11:08 18c_pic2.jpg
and there you go. Run a host command with a select statement.
Now *obviously* you would NOT have a function that takes just *any* parameter and runs it blindly - that is an enormous risk. But in your case, the function might take *just* the file name, and then compose an appropriate scp command in the utl_file.put_line call.
But you can see how this can work - and much easier to debug/maintain than a java stored proc.