create or replace java source named "ExternalCall" as
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;

public class ExternalCall {
  private static oracle.sql.Datum exec(String sCommand, Datum data, int iReturnType) throws Exception {
    Process             p = null;

    InputStreamReader   pStdOutR = null;
    InputStream         pStdOutS = null;
    OutputStreamWriter  pStdInW  = null;
    OutputStream        pStdInS  = null;

    Writer              paramClobWriter  = null;
    OutputStream        paramBlobOStream = null;
    Reader              paramClobReader  = null;
    InputStream         paramBlobIStream = null;

    Datum               tempLob;

    char[] caStdInBuffer = null;
    char[] caStdOutBuffer = null;
    byte[] baStdInBuffer = null;
    byte[] baStdOutBuffer = null;

    int iCharsRead = 0;

    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    if (iReturnType == oracle.jdbc.OracleTypes.BLOB) {
      tempLob = BLOB.createTemporary(con, true, BLOB.DURATION_CALL);
      paramBlobOStream = ((BLOB)tempLob).setBinaryStream(0L);
      baStdOutBuffer = new byte[((BLOB)tempLob).getChunkSize()];
    } else if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
      tempLob = CLOB.createTemporary(con, true, CLOB.DURATION_CALL);
      paramClobWriter = ((CLOB)tempLob).setCharacterStream(0L);
      caStdOutBuffer = new char[((CLOB)tempLob).getChunkSize()];
    } else {
      throw new IllegalArgumentException("ReturnType must be CLOB or BLOB");
    }

    if (data != null) {
      if (data instanceof oracle.sql.BLOB) {
        paramBlobIStream = ((BLOB)data).getBinaryStream();
        baStdInBuffer = new byte[((BLOB)data).getChunkSize()];
      } else if (data instanceof oracle.sql.CLOB) {
        paramClobReader  = ((CLOB)data).getCharacterStream();
        caStdInBuffer = new char[((CLOB)data).getChunkSize()];
      } else {
        throw new IllegalArgumentException("Argument for Standard Input must be of type CLOB or BLOB");
      }
    }

    p = Runtime.getRuntime().exec(sCommand);
    iCharsRead = 0;

    if (data != null) {
      if (data instanceof oracle.sql.BLOB) {
        pStdInS = p.getOutputStream();
        while ((iCharsRead = paramBlobIStream.read(baStdInBuffer, 0, baStdInBuffer.length)) != -1) {
          pStdInS.write(baStdInBuffer, 0, iCharsRead);
        }
        paramBlobIStream.close();
        pStdInS.flush();
        pStdInS.close();
      } else {
        pStdInW = new OutputStreamWriter(p.getOutputStream());
        while ((iCharsRead = paramClobReader.read(caStdInBuffer, 0, caStdInBuffer.length)) != -1) {
          pStdInW.write(caStdInBuffer, 0, iCharsRead);
        }
        paramClobReader.close();
        pStdInW.flush();
        pStdInW.close();
      }
    }

    p.waitFor();

    iCharsRead = 0;

    if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
      if (p.exitValue() == 0) {
        pStdOutR = new InputStreamReader(p.getInputStream());
      } else {
        pStdOutR = new InputStreamReader(p.getErrorStream());
      }
      while ((iCharsRead = pStdOutR.read(caStdOutBuffer, 0, caStdOutBuffer.length)) != -1) {
        paramClobWriter.write(caStdOutBuffer, 0, iCharsRead);
      }
      pStdOutR.close();
      paramClobWriter.flush();
      paramClobWriter.close();
    } else {
      if (p.exitValue() == 0) {
        pStdOutS = p.getInputStream();
      } else {
        pStdOutS = p.getErrorStream();
      }
      while ((iCharsRead = pStdOutS.read(baStdOutBuffer, 0, baStdOutBuffer.length)) != -1) {
        paramBlobOStream.write(baStdOutBuffer, 0, iCharsRead);
      }
      pStdOutS.close();
      paramBlobOStream.flush();
      paramBlobOStream.close();
    }
    return tempLob;
  }


  public static Datum execClob(String sCommand, CLOB dataClob) throws Exception {
    return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.CLOB);
  }

  public static Datum execClob(String sCommand, BLOB dataBlob) throws Exception {
    return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.CLOB);
  }

  public static Datum execBlob(String sCommand, BLOB dataBlob) throws Exception {
    return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.BLOB);
  }

  public static Datum execBlob(String sCommand, CLOB dataClob) throws Exception {
    return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.BLOB);
  }

  public static Datum execClob(String sCommand) throws Exception {
    return exec(sCommand, null, oracle.jdbc.OracleTypes.CLOB);
  }

  public static Datum execBlob(String sCommand) throws Exception {
    return exec(sCommand, null, oracle.jdbc.OracleTypes.BLOB);
  }
}
/

alter java source "ExternalCall" compile
/

create or replace package os_command is
  function exec_CLOB(p_command in varchar2, p_stdin in blob) return clob;
  function exec_CLOB(p_command in varchar2, p_stdin in clob) return clob;
  function exec_BLOB(p_command in varchar2, p_stdin in blob) return blob;
  function exec_BLOB(p_command in varchar2, p_stdin in clob) return blob;
  function exec_CLOB(p_command in varchar2) return Clob;
  function exec_BLOB(p_command in varchar2) return blob;
end os_command;
/
create or replace package body os_command is
  function exec_CLOB(p_command in varchar2, p_stdin in blob) return clob
  is language java name 'ExternalCall.execClob(java.lang.String, oracle.sql.BLOB) return oracle.sql.CLOB';

  function exec_CLOB(p_command in varchar2, p_stdin in clob) return clob
  is language java name 'ExternalCall.execClob(java.lang.String, oracle.sql.CLOB) return oracle.sql.CLOB';

  function exec_BLOB(p_command in varchar2, p_stdin in blob) return blob
  is language java name 'ExternalCall.execBlob(java.lang.String, oracle.sql.BLOB) return oracle.sql.BLOB';

  function exec_BLOB(p_command in varchar2, p_stdin in clob) return blob
  is language java name 'ExternalCall.execBlob(java.lang.String, oracle.sql.CLOB) return oracle.sql.BLOB';

  function exec_CLOB(p_command in varchar2) return Clob
  is language java name 'ExternalCall.execClob(java.lang.String) return oracle.sql.CLOB';

  function exec_BLOB(p_command in varchar2) return blob
  is language java name 'ExternalCall.execBlob(java.lang.String) return oracle.sql.BLOB';
end os_command;
/