Skip to Main Content
  • Questions
  • execute command scp from inside the database

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Edgar.

Asked: September 03, 2018 - 10:44 pm UTC

Last updated: September 06, 2018 - 3:12 am UTC

Version: 12c

Viewed 1000+ times

You Asked

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.

and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.