Calling/Executing OS Commands from PL/SQL

You may never know when the need arrives to make an Operating System Command call from a PL/SQL Procedure...

Recently came across such requirement and found the below code and thought of updating my blog.

I used this procedure along with the Extract methods as mentioned in earlier post to extract about 300 million records from about 200+ tables by compressing and ftping to another server. All in 2:30 hours with one session.

Making an OS call from PL/SQL is of two folds:
1. Create JAVA Source Object
2. Create a wrapper procedure to call the JAVA Class created above.

JAVA Procedure Code:
CREATE OR REPLACE AND RESOLVE 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;
  }

};
/

Wrapper to call Java Source:
CREATE OR REPLACE procedure host_command (p_command  in  varchar2)
as language java
name 'Host.executeCommand (java.lang.String)';
/

Grants:

Now, grant DBMS_JAVA permissions to a specific user who should be allowed to make OS calls. Please be very very careful in giving this grant.

CONN / AS SYSDBA

DECLARE

  l_schema VARCHAR2(30) := 'USER_TO_MAKE_OS_CALL'; -- Adjust as required.

BEGIN

  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<>', '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;

/

Example:

Now, use this wrapper proc in your pl/sql to make any OS command.

t_os_call := '/bin/gzip --force '||t_os_path||'/*.dat'; --using -force option to overwrite  

host_command(t_os_call);

The above example shows that t_os_call variable is set to execute gzip with options built dynamically and host_command procedure uses this variable to execute this command in the OS.

Note:

Use dbms_java.set_output along with dbms_output.enable to get the actual command output from the OS call.

eg., sample showing dbms_java.set_output and storing the results

declare

t_output dbms_output.chararr;

begin

  dbms_output.disable; --flushing the pool

  dbms_output.enable(1000000);

  dbms_java.set_output(1000000);

  

  t_os_call := '/bin/gzip --force '||t_os_path||'/*.dat'; --using -force option to overwrite

  

  host_command(t_os_call);

  

  dbms_output.get_lines(t_output, t_lines);

  

  for i in 1.. t_lines loop

    dbms_output.put_line(t_output(i));

    p_message := p_message||t_output(i);

  end loop;

end;

Extract/Export Data from Tables into flat files...

We all know that the best and fastest way of exporting data from a table(s) in Oracle is using either EXP or EXPDP.
What if there is a need to fetch records based on some conditions or for certain criteria etc.,? Well, EXP and EXPDP supports with WHERE clause right so whats the big deal? hmm... let me think of another reason...
Yeah, how about exporting data from a query which joins more than one table??? Yes. neither EXP or EXPDP cant do that.
Also, with EXPDP you need to have an access to directory that is defined in the database.

Anyways, lets get to the point.
What I want to show in this post is, a way to extract data from any table dynamically using two options:
1. using plain old DBMS_SQL method to execute Dynamic SQL and
2. using combination of DBMS_SQL and NDS (Native Dynamic SQL) with EXECUTE IMMEDIATE.

Don't want to go too deep in to each (you can read manual if interested in knowing every single bit of it).
Here is the sample code demonstrating 1st option using only DBMS_SQL.
High Level Steps:
1. Form a Query
2. Execute Query
3. Fetch the Results

declare

t_cursor integer default dbms_sql.open_cursor;

t_sql varchar2(4000) := 'select * from user_tables' ; --> this query here can be built dynamically

t_col_count number;

t_col_value varchar2(4000);

t_desc_tbl dbms_sql.desc_tab;

t_exec_status integer;

t_rec_count number := 0;

t_col_separator char(2) := '||';

t_record varchar2(32567);

t_column_string varchar2(4000) := null;

begin

  --parse SQL

  dbms_sql.parse(t_cursor, t_sql, dbms_sql.native);

  --Get the column(s) list from the SQL

  dbms_sql.describe_columns(t_cursor, t_col_count, t_desc_tbl);

  --Show column names from the t_desc_tbl

  for x in 1 .. t_col_count loop

    t_column_string := t_column_string ||t_desc_tbl(x).col_name|| ',';

  end loop;

  dbms_output.put_line('Columns => '||rtrim(t_column_string,','));

     

  dbms_output.put_line('Fetching define '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  --define the output for the columns

  for j in 1..t_col_count loop

    dbms_sql.define_column(t_cursor, j, t_col_value, 4000);

    dbms_output.put_line('Column Value '||t_col_value);

  end loop;

  dbms_output.put_line('Execute '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  --execute the SQL

  t_exec_status := dbms_sql.execute(t_cursor);

     

  --Fetch the records from the above execution

  dbms_output.put_line('Fetching Rows '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  while dbms_sql.fetch_rows(t_cursor) > 0

  loop

    t_rec_count := t_rec_count + 1;

    for k in 1..t_col_count loop

      dbms_sql.column_value(t_cursor, k, t_col_value);

      t_record := t_record||t_col_value||t_col_separator;                   

    end loop;

    dbms_output.put_line('Record# '||t_rec_count||'=> '||rtrim(t_record,t_col_separator));

    t_record := null;

  end loop;

  dbms_sql.close_cursor(t_cursor);

end;

/ 

Here is the sample code using DBMS_SQL along with EXECUTE IMMEDIATE. 

High Level Steps:

1. Form a Query

2. Get the column(s) list from the Query

3. Re-form a query with individual Columns - showing an example to fetch the records as pipe delimited 

4. Execute and Fetch query using BULK COLLECT 

declare

t_cursor integer default dbms_sql.open_cursor;

t_sql varchar2(4000) := 'select * from user_tables' ;

t_col_count number;

t_desc_tbl dbms_sql.desc_tab;

t_rec_count number := 0;

t_col_separator char(2) := '||';

t_separator_csv varchar2(10) := null;

t_column_string varchar2(4000) := null;

t_bulk_limit number := 10000; --Define bulk collect limit

t_line_buffer varchar2(32767);

type csv_cur_type is ref cursor;

csv_cur csv_cur_type;

type myarray is table of varchar2(4000); --assuming the entire record length is 4000 bytes

t_data myarray;

begin

  --parse SQL

  dbms_sql.parse(t_cursor, t_sql, dbms_sql.native);

  --Get the column(s) list from the SQL

  dbms_sql.describe_columns(t_cursor, t_col_count, t_desc_tbl);

 

  --Close cursor as this is not needed anymore

  dbms_sql.close_cursor(t_cursor);

  --Show column names from the t_desc_tbl

  for x in 1 .. t_col_count loop

    t_column_string := t_column_string ||t_separator_csv||t_desc_tbl(x).col_name;

    t_separator_csv := '||''' || t_col_separator || '''||';

  end loop;

 

  dbms_output.put_line('Columns String to use in dynamic SQL');

  dbms_output.put_line(t_column_string);

 

  --now repalce select clause in SQL to use the above column string   

  --t_sql := regexp_replace(upper(t_sql), '^SELECT (.*?) FROM', 'SELECT ' || t_column_string || ' FROM');

  t_sql := replace(t_sql, ' * from', ' '||t_column_string || ' from');

  dbms_output.put_line('Actual SQL With Column String ');

  dbms_output.put_line(t_sql);

  --fetch records using bulk collect and process each row

  open csv_cur for t_sql;

  loop

    fetch csv_cur bulk collect

      into t_data limit t_bulk_limit;

    for j in 1 .. t_data.count loop

      begin

        if length(t_line_buffer || t_data(j)) >= 30000 then

          dbms_output.put_line(t_line_buffer);

          t_line_buffer := t_data(j);

        else

          if t_line_buffer is null then

            t_line_buffer := t_data(j);

          else

            t_line_buffer := t_line_buffer || chr(10) || t_data(j);

          end if;

        end if;

      end;

      t_rec_count := t_rec_count + 1;

    end loop;

    exit when csv_cur%notfound;

  end loop;

  close csv_cur;

  --show remaining records

  dbms_output.put_line(t_line_buffer);

end;

/

Now compare the results from option#1 and #2 and you will be amazed to see how best the 2nd option is using Execute Immediate with BULK COLLECT.

You can easily replace dbms_output with utl_file to write the content to a flat file on the server side.

Hope this helps me when I am in search of the samples :)