##############################################

UTL_COMPRESS : Compress and Uncompress Data from PL/SQL

The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE). It uses the Lempel-Ziv compression algorithm which is equivalent to functionality of the gzip utility. A simple example of it's use is shown below.

SET SERVEROUTPUT ON
DECLARE
  l_original_blob      BLOB;
  l_compressed_blob    BLOB;
  l_uncompressed_blob  BLOB;
BEGIN
  -- Initialize both BLOBs to something.
  l_original_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890'));
  l_compressed_blob   := TO_BLOB('1');
  l_uncompressed_blob := TO_BLOB('1');
  
  -- Compress the data.
  UTL_COMPRESS.lz_compress (src => l_original_blob,
                            dst => l_compressed_blob);
  
  -- Uncompress the data.
  UTL_COMPRESS.lz_uncompress (src => l_compressed_blob,
                              dst => l_uncompressed_blob);
  
  -- Display lengths.
  DBMS_OUTPUT.put_line('Original Length    : ' || LENGTH(l_original_blob));
  DBMS_OUTPUT.put_line('Compressed Length  : ' || LENGTH(l_compressed_blob));
  DBMS_OUTPUT.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));
  
  -- Free temporary BLOBs.             
  DBMS_LOB.FREETEMPORARY(l_original_blob);
  DBMS_LOB.FREETEMPORARY(l_compressed_blob);
  DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/
Original Length    : 40
Compressed Length  : 33
Uncompressed Length: 40

PL/SQL procedure successfully completed.

SQL>

The results of the compression will vary depending on the size and type of the data being compressed.

For more information see:

UTL_COMPRESS

##############################################

Creating and running Jobs:

/* Formatted on 2018/02/01 10:05 (Formatter Plus v4.8.8) */

BEGIN

DBMS_SCHEDULER.create_credential (credential_name => 'ORACLE_CRED',

username => 'oracle',

PASSWORD => 'oracle'

);

END;

/* Formatted on 2018/02/01 09:15 (Formatter Plus v4.8.8) */

BEGIN

DBMS_SCHEDULER.create_job (job_name => 'UNZIP',

job_type => 'EXECUTABLE',

job_action => '/home/oracle/zip/unzip.sh'

);

END;

begin

dbms_scheduler.set_attribute

(

NAME => 'UNZIP',

ATTRIBUTE => 'CREDENTIAL_NAME',

VALUE => 'ORACLE_CRED'

);

end;

/* Formatted on 2018/02/01 09:16 (Formatter Plus v4.8.8) */

BEGIN

DBMS_SCHEDULER.run_job ('UNZIP');

END;

################################################################################

Compressing the file using PL/SQL

DECLARE

in_filename VARCHAR2(100);

src_file BFILE;

v_content BLOB;

v_blob_len INTEGER;

v_file utl_file.file_type;

v_buffer RAW(32767);

v_amount BINARY_INTEGER := 32767;

v_pos INTEGER := 1;

BEGIN

in_filename := 'Test.txt';

src_file := bfilename('DIR_UTL_COM_TEST', in_filename);

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

v_content := utl_compress.lz_compress(src_file, 9);

v_blob_len := dbms_lob.getlength(v_content);

v_file := utl_file.fopen('DIR_UTL_COM_TEST',

in_filename || '.gz',

'wb');

WHILE v_pos < v_blob_len LOOP

dbms_lob.READ(v_content, v_amount, v_pos, v_buffer);

utl_file.put_raw(v_file, v_buffer, TRUE);

v_pos := v_pos + v_amount;

END LOOP;

utl_file.fclose(v_file);

EXCEPTION

WHEN OTHERS THEN

IF utl_file.is_open(v_file) THEN

utl_file.fclose(v_file);

END IF;

RAISE;

END;

Un compressing the compressed file.

/* Formatted on 2018/02/01 11:21 (Formatter Plus v4.8.8) */

DECLARE
in_filename VARCHAR2 (100);
src_file BFILE;
v_content BLOB;
v_blob_len INTEGER;
v_file UTL_FILE.file_type;
v_buffer RAW (32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
in_filename := 'test.txt.zip';
src_file := BFILENAME ('SMR_UPLOADS', in_filename);
DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
v_content := UTL_COMPRESS.lz_uncompress (src_file);
v_blob_len := DBMS_LOB.getlength (v_content);
v_file := UTL_FILE.fopen ('SMR_UPLOADS', in_filename || '.txt', 'wb');

WHILE v_pos < v_blob_len
LOOP
DBMS_LOB.READ (v_content, v_amount, v_pos, v_buffer);
UTL_FILE.put_raw (v_file, v_buffer, TRUE);
v_pos := v_pos + v_amount;
END LOOP;

UTL_FILE.fclose (v_file);
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (v_file)
THEN
UTL_FILE.fclose (v_file);
END IF;

RAISE;

END;

######################################################################

JAVA FOR USING OS COMMAND IN PLSQL.

Shell Commands From PL/SQL

Using a Java stored procedure it is possible to perform shell commands from PL/SQL.

This should only be used as a last resort if the functionality you require is not available via other means, like external jobs using the scheduler. As mentioned later, you need to be very careful what file system privileges you grant and/or who you give access to this functionality. If used unwisely, this could allow someone to damage files on the server, or cause a major security breach.

Create the Java Stored Procedure

First we need to create the Java class to perform the shell command.

CONN test/test

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;
  }

};
/

show errors java source "Host"

Publish the Java call specification

Next we publish the call specification using a PL/SQL "wrapper" PL/SQL procedure.

CREATE OR REPLACE PROCEDURE host_command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA 
NAME 'Host.executeCommand (java.lang.String)';
/

Grant Privileges

In this example we are granting access to all directories on the server. That is really dangerous. You need to be more specific about these grants and/or be very careful about who you grant access to this functionality.

The relevant permissions must be granted from SYS for JServer to access the file system. In this case we grant access to all files accessible to the Oracle software owner, but in reality that is a very dangerous thing to do.

CONN / AS SYSDBA

DECLARE
  l_schema VARCHAR2(30) := 'TEST'; -- 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;
/

The affects of the grant will not be noticed until the grantee reconnects. In addition to this, the owner of the Oracle software must have permission to access the file system being referenced.

Test It

Finally we call the PL/SQL procedure with our command text.

CONN test/test

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);

BEGIN
  host_command (p_command => 'move C:\test1.txt C:\test2.txt');
  --host_command (p_command => '/bin/mv /home/oracle/test1.txt /home/oracle/test2.txt');
END;
/

The same result could be achieved with COM Automation but in my opinion this method is much neater.

The output from the host command can be captured using the DBMS_OUTPUT.get_lines procedure.

CONN test/test

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);

DECLARE
  l_output DBMS_OUTPUT.chararr;
  l_lines  INTEGER := 1000;
BEGIN
  DBMS_OUTPUT.enable(1000000);
  DBMS_JAVA.set_output(1000000);

  host_command('dir C:\');
  --host_command('/bin/ls /home/oracle');

  DBMS_OUTPUT.get_lines(l_output, l_lines);

  FOR i IN 1 .. l_lines LOOP
    -- Do something with the line.
    -- Data in the collection - l_output(i)
    DBMS_OUTPUT.put_line(l_output(i));
  END LOOP;
END;
/

Known Issues

Depending on the environment, the process may continue running as a zombie after the command has been executed, even if the destroy() method is called manually. If this happens the process is only cleaned up when the session ends. Under normal circumstances this doesn't represent a problem, but when called as part of a job the zombie processes will only die when the Job Queue Coordinator is stopped.
No profile is run for the OS callout, so no environment variables will be set. As a result you will need to use full paths to any executables ("ls" becomes "/bin/ls") or scripts. Alternatively, write all operations as scripts and set the relevant environment variables inside the scripts.

###################################################################

countries

ORACLE

##############################################

UTL_COMPRESS : Compress and Uncompress Data from PL/SQL

##############################################

Shell Commands From PL/SQL

Create the Java Stored Procedure

Publish the Java call specification

Grant Privileges

Test It

Known Issues

No comments:

Post a Comment