create or replace type STRARRAY as table of varchar2(255)
/

**********************************************************************************

CREATE OR REPLACE PACKAGE UTIL AS
 FUNCTION RUN_CMD(p_cmd IN VARCHAR2) RETURN NUMBER; 
 PROCEDURE COPY_FILE(p_srcFile IN VARCHAR2, p_destDir IN VARCHAR2);                                  
 PROCEDURE GET_DIR_LIST(p_dir IN VARCHAR2, p_dirList OUT STRARRAY);
END UTIL;
/

**********************************************************************************

CREATE OR REPLACE PACKAGE BODY UTIL AS
 FUNCTION RUN_CMD(p_cmd IN VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA NAME 'nl.amis.util.RunCmd.runThis(java.lang.String) return int'; 
 PROCEDURE COPY_FILE(p_srcFile IN VARCHAR2, p_destDir IN VARCHAR2) AS LANGUAGE JAVA NAME 'nl.amis.util.FileCopy.copy(java.lang.String, java.lang.String)';
 PROCEDURE GET_DIR_LIST(p_dir IN VARCHAR2, p_dirList OUT STRARRAY) AS LANGUAGE JAVA NAME 'nl.amis.util.DirList.getList(java.lang.String, oracle.sql.ARRAY[])';
END UTIL;
/

**********************************************************************************
//
//Running an operating system command:
//
 
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "RunCmd" AS
package nl.amis.util;

import java.io.*;

public class RunCmd 
{
  private static void pass( java.lang.String p_in,
                         java.lang.String[] p_out )
  {
    if ( p_in != null ){
        p_out[0] = p_in.toUpperCase();
    }
  }
  
  public static int runThis(String cmd)
  {
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;
    
    String[] args = new String[4096];
    pass(cmd, args);
    
    try
    {
       Process p = rt.exec(args[0]);
  
       int bufSize = 4096;
       BufferedInputStream bis =
        new BufferedInputStream(p.getInputStream(), bufSize);
       int len;
       byte buffer[] = new byte[bufSize];
  
       // Echo back what the program spit out
       while ((len = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);
  
       rc = p.waitFor();
    }
    catch (Exception e)
    {
       e.printStackTrace();
       rc = -1;
    }
    return rc;    
  }  
}
/

**********************************************************************************
//As SYS:

BEGIN
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:\WINDOWS\SYSTEM32\CMD.EXE', 'execute' );
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.lang.RuntimePermission', '*', 'writeFileDescriptor' );
END;
/

**********************************************************************************
 
CREATE OR REPLACE PROCEDURE RC(
   P_CMD IN VARCHAR2)
AS
   X                             NUMBER;
BEGIN
   X := UTIL.RUN_CMD (P_CMD);

   IF (X <> 0)
   THEN
      RAISE PROGRAM_ERROR;
   END IF;
END RC;
/

**********************************************************************************

set serveroutput on size 1000000
exec dbms_java.set_output(1000000)

exec rc('C:\WINDOWS\system32\cmd.EXE /c dir C:\Gregory')

**********************************************************************************
//
//Copying a file:
//

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileCopy" AS
package nl.amis.util;

import java.io.*;
 
public class FileCopy{

  public static void copy(String srcFile, String destDir) {
    File source = new File(srcFile);
    File dest = new File(destDir);
    File cpDestFile = null;
    try {
      if (dest.isDirectory()) {
          if(source.isFile()) {
            System.out.println("File name = " + source.getName());
            System.out.println("File name dest = " + dest.getPath() );
            System.out.println("File name cpDestFile = " + dest.getPath() + File.separator + source.getName());
            cpDestFile = new File(dest.getPath() + File.separator + source.getName()); 
          }
          else {
            System.out.println("Directory or File Not Found");
            return;
          }
      }
      BufferedInputStream  br = new BufferedInputStream (new FileInputStream (source));
      BufferedOutputStream  bw = new BufferedOutputStream (new FileOutputStream (cpDestFile));
      int read = 0;
      while((read = br.read()) != -1) {
        //System.out.println((char)read);
        bw.write(read);
      }
      br.close();
      bw.close();
    }
    catch (FileNotFoundException e) {
    e.printStackTrace();
    }
    catch (IOException e) {
    e.printStackTrace();
    }
  }
}
/

**********************************************************************************
//As SYS:

BEGIN
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:\Gregory', 'read' );
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:\temp\xmltool javadoc.zip', 'read' );
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:\Gregory\xmltool javadoc.zip', 'write' );
END;
/

**********************************************************************************

set serveroutput on size 1000000
exec dbms_java.set_output(1000000)

exec UTIL.COPY_FILE('C:\temp\xmltool javadoc.zip', 'C:\Gregory')

**********************************************************************************
//
//Getting a directory listing:
//

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirList" AS
package nl.amis.util;

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class DirList {  

  public static void getList(String directory, oracle.sql.ARRAY[] listArray) throws java.sql.SQLException,IOException{  
    File path = new File(directory);
    String[] list = path.list();
    
    Connection conn = new OracleDriver().defaultConnection();
    ArrayDescriptor descr =
       ArrayDescriptor.createDescriptor( "STRARRAY", conn ); //"STRARRAY" is SQL type name (table of varchar2(255))
    listArray[0] = new ARRAY( descr, conn, list );    
  }  
}
/

**********************************************************************************
//As SYS:

BEGIN
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:\temp', 'read' );
END;
/

**********************************************************************************

set serveroutput on size 1000000
exec dbms_java.set_output(1000000)

DECLARE
  vArray STRARRAY := STRARRAY ();
BEGIN
  UTIL.GET_DIR_LIST('C:\temp', vArray);
  DBMS_OUTPUT.PUT_LINE(CHR(09));
  DBMS_OUTPUT.PUT_LINE('Directory listing of C:\temp:');
  DBMS_OUTPUT.PUT_LINE(CHR(09));
  FOR i IN 1..vArray.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(vArray(i));
  END LOOP;
END;
/