Executing Linux command from Oracle PLSQL using Java Stored Procedure

In this blog I am going to use a JAVA stored procedure to execute Linux commands from PLSQL. In my earlier post we I have used a C program to achieve the same solution. But there are various methods to utilize it.

To execute any OS command from Oracle PLSQL we have to create a JAVA store procedure. Execute the below SQL from your schema from which you want to fire the operating system command.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "BASH_OS" AS
   /******************************************************************************
           NAME:       BASH_OS
           PURPOSE:    To perform the shell command using Java class 
                               
           REVISIONS:
           Ver        Date        Author    Description
           ---------  ----------  ------   ---------------------------------------------
           0.1        <<>>        S.Ray      Initial Version
                   
        ******************************************************************************/
        import java.io.*;
public class Host {
  public static void executeCommand(String command) {
try {
      String[] finalCommand;
       {
        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());
    }
  }
  
};

Once the above step is completed then we have to publish the Java call specification by executing the below SQL file:

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

After that login with SYS DBA user and grant the privileges to the Schema by executing the below SQL file :

 

---Grant Privileges
--CONN / AS SYSDBA

DECLARE
  l_schema VARCHAR2(30) := 'Your Schema Name'; -- Pass the correct Schema name
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;
/

Now Finally test it by running :

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as pgedev

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

Method called

SQL> BEGIN
  2          unix_command('/bin/ls -lrt /home/oracle/app/EXP_DIR');
  3  END;
  4  /

Process out :total 8
Process out :drwxrwxrwx 2 oracle oinstall 4096 Aug 12 12:21 attachments
Process out :-rw-r--r-- 1 oracle oinstall    5 Aug 12 18:21 test.txt

PL/SQL procedure successfully completed

Thank you for reading my blog , the reference is taken from OTN . Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Using EXISTS subqueries might be inefficient

Using EXISTS subqueries can be inefficient, the subquery executes for each and every row against the outer query’s table.

In the below example used on EXISTS subqueries which is non compliant way of writing SQL:


SELECT a.<<COLUMN NAME>>
FROM table1 a
where EXISTS (SELECT * FROM table2 b where a.column = b.column and b.column =<<Any value>> ) ;

A compliant solution would be like as below:


SELECT a.<<COLUMN NAME>>
FROM table1 a INNER JOIN table2 b
ON a.column = b.column and b.column =<<Any value>>;

Do not use Bulk collect without a LIMIT clause in Cursors

Normally when we use a cursor to fetch all the records at once we make use of BULK COLLECT. Without using a LIMIT clause will take all the records returned by the cursor and which will lead to extreme physical memory tiredness or we can say memory exhaustion.  So it’s always better to use LIMIT clause if we are using a cursor to fetch all the records at one shot and doing the operations.

To check this and validate the above told statement let us create a table which will be our target table to insert huge set of records using cursors:


SQL> SET SERVEROUTPUT ON
SQL> 
SQL> CREATE TABLE my_big_table(
  2    my_data VARCHAR2(42)
  3  ); 

Table created 

I am only using one column to demonstrate the LIMIT clause.

Let us insert some sample records into our sample table which we have created .

 

SQL> DECLARE
  2          timestart NUMBER;
  3  BEGIN
  4          timestart := dbms_utility.get_time();
  5          FOR i IN 1 .. 200001
  6          LOOP
  7                  INSERT INTO my_big_table VALUES ('test' || i);
  8  
  9          END LOOP;
10          dbms_output.put_line(dbms_utility.get_time() - timestart);
11  END;
12  / 

552 

SQL> select count(*) from my_big_table; 

  COUNT(*)
----------
    200001 

Now let us use BULK collect without an LIMIT clause and check how much time it takes to process the data just count the number of records.


SQL> DECLARE
  2          TYPE my_big_tablearraytype IS TABLE OF my_big_table%ROWTYPE;
  3          my_big_tablerowtype my_big_tablearraytype;
  4          CURSOR mycursor IS
  5                  SELECT * FROM my_big_table;
  6          timestart NUMBER;
  7  BEGIN
  8          timestart := dbms_utility.get_time();
  9          OPEN mycursor;
10  
11          FETCH mycursor BULK COLLECT
12                  INTO my_big_tablerowtype; -- Non-compliant
13  
14          dbms_output.put_line('Without Using Limit : ' || my_big_tablerowtype.COUNT ||
15                               ' records');
16  
17          CLOSE mycursor;
18          dbms_output.put_line(dbms_utility.get_time() - timestart);
19  END;
20  / 

Without Using Limit : 200001 records
10 

PL/SQL procedure successfully completed 

We can see from the above statement that it took 10 sec to process the data for 200001 records.

Now let us use LIMIT clause and check how much it takes to process same set of data for taking the count.


SQL> DECLARE
  2          TYPE my_big_tablearraytype IS TABLE OF my_big_table%ROWTYPE;
  3          my_big_tablerowtype my_big_tablearraytype;
  4          CURSOR mycursor IS
  5                  SELECT * FROM my_big_table;
  6          timestart NUMBER;
  7          counter   PLS_INTEGER := 0;
  8  BEGIN
  9          timestart := dbms_utility.get_time();
10          OPEN mycursor;
11  
12          LOOP
13                  FETCH mycursor BULK COLLECT
14                          INTO my_big_tablerowtype LIMIT 1000; -- Compliant
15  
16                  counter := counter + my_big_tablerowtype.COUNT;
17  
18                  EXIT WHEN mycursor%NOTFOUND;
19          END LOOP;
20  
21          dbms_output.put_line('Using Limit : ' || counter || ' records');
22  
23          CLOSE mycursor;
24          dbms_output.put_line(dbms_utility.get_time() - timestart);
25  END;
26  / 

Using Limit : 200001 records
8 

PL/SQL procedure successfully completed 

From the above execution we can see that it took only 8 sec for taking the count as without using limit clause it takes more time.

So better to use LIMIT clause on any bulk collect operation used without PLSQL code.

With a given scope do not use the variable more than once in PLSQL

It’s always good that we should not use a variable more than once with a given scope, as only one declaration of a variable is allowed in Oracle. If we write more variable then we are going to get PLSQL error i.e. PLS-00371 will be raised during runtime.

The Non-Compliant way of writing the code is as shown below :

SET SERVEROUTPUT ON

DECLARE
  my_var VARCHAR2(30) := 'input1';
  my_var VARCHAR2(30) := 'input2'; --
BEGIN
  DBMS_OUTPUT.PUT_LINE(my_var); 
END;
/

The below example will show how PLS-00371 error is raised during runtime :


SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  2    my_var VARCHAR2(30) := 'input1';
  3    my_var VARCHAR2(30) := 'input2';
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE(my_var);
  6  END;
  7  /

DECLARE
  my_var VARCHAR2(30) := 'input1';
  my_var VARCHAR2(30) := 'input2'; 
BEGIN
  DBMS_OUTPUT.PUT_LINE(my_var); 
END;

ORA-06550: line 5, column 24:
PLS-00371: at most one declaration for 'MY_VAR' is permitted
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

We can re-factor the above code to compliant way as like below:


SET SERVEROUTPUT ON

DECLARE
        my_var  VARCHAR2(30) := 'input1';
        my_var1 VARCHAR2(30) := 'input2';
BEGIN
        dbms_output.put_line(my_var);
END;
/

The above code will display the proper result what we will be looking for:


SQL> DECLARE
2          my_var  VARCHAR2(30) := 'input1';
3          my_var1 VARCHAR2(30) := 'input2';
4  BEGIN
5          dbms_output.put_line(my_var);
6  END;
7  /

input1
PL/SQL procedure successfully completed

There could be various reason we need to make use of same variable name but still we can make use of this by using global variable.

For example as like below compliant way of writing the global variable without getting PLS-00371 error.

DECLARE
             -- Global variables
           my_var1 VARCHAR2(30) := 'input1';
           my_var2 VARCHAR2(30) := 'input2';

BEGIN
           dbms_output.put_line('Outer Variable my_var1: ' || my_var1);
           dbms_output.put_line('Outer Variable my_var2: ' || my_var2);

           DECLARE       -- Local variables
           my_var1 VARCHAR2(30) := 'input3';
           my_var2 VARCHAR2(30) := 'input4';
           BEGIN       dbms_output.put_line('Inner Variable my_var1: ' || my_var1);
              dbms_output.put_line('Inner Variable my_var2: ' || my_var2);
           END;
END;

 

 

 

Use COUNT on indexed column or asterisk or 1

It is always prefered to use the count on indexed column or asterisk (*) or by representing it by one (1) for any table.

For showing the example all the below query will give the same output but there will be a difference in the execution plan.

Let us take the example of EMP table where an index is present on EMPNO column and ENAME column is not having any index on it, so let us first consider ENAME column to get the count and check its explan plan :

SQL>explain plan for
select count(ename) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 2836287311

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS FULL| EMP_TEMP | 23 | 161 | 2 (0)| 00:00:03 |
——————————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)

Now let us take the indexed column i.e. EMPNO column from EMP table:

SQL>explain plan for
select count(empno) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 1671363213

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMPNO_IDX | 23 | 1 (0)| 00:00:02 |
———————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)
From the above mentioned plans we can see that it is more efficient by using the indexed column to get the counts.
The similar results are also retrived from the below queries by using asterisk or one.

# By using one (1) to get the counts:

SQL>explain plan for
select count(1) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 1671363213

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMPNO_IDX | 23 | 1 (0)| 00:00:02 |
———————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)

# By using asterisk (*) to get the counts:

SQL>explain plan for
select count(*) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 1671363213

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMPNO_IDX | 23 | 1 (0)| 00:00:02 |
———————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)

Now we know in which column or how to optimize our query if we are taking counts.

EXISTS vs DISTINCT

It is alwasys prefered not to use DISTINCT qualifier on the SELECT statement where we need to determine the information from owner end means where one to many relationship exists.
For exmaple if we take departments from EMP table, then we see that departments has one to many relationship with employees column in EMP table.
Now if we want to show the result like all department numbers from EMP table and department name from DEPT table then deprtment number is only column where we can make a join to these two tables.

We get duplicate records when we execute only with deptno column as below:

SQL>SELECT E.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO;

OUTPUT:

DEPTNO DNAME
10 ACCOUNTING
30 SALES
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
30 SALES

But the above result is not that good what we want to see , by using DISTINCT we get the proper result where it removes the duplicate rows like as below:

SQL>SELECT DISTINCT E.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO;

OUTPUT:
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES
But to the same query if we can modify by using EXISTS then it will be much more faster and efficient because RDBMS kernel understand that if the sub-query has been satisfied once then the query should be terminated.
So let us see how we can use EXISTS in place of DISTINCT:

SQL>SELECT deptno, dname
FROM dept d
WHERE EXISTS (SELECT x
FROM emp e
WHERE e.deptno = d.deptno);

OUTPUT:
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES

Using DECODE to Reduce Processing:

By using DECODE statement we can avoid scanning the same rows repeatedly or to join the same table repetitively.
For let us take an example by to get the count and sum of salary for employee name like SMITH and from department 20.

To achieve this we can use the below query :

SQL>SELECT COUNT (*) D20_COUNT, SUM (sal) D20_SAL
FROM emp
WHERE deptno = 20 AND ename LIKE ‘SMITH%’;

PLAN_TABLE_OUTPUT

Plan hash value: 2083865914

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 17 | 3 (0)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“DEPTNO”=20 AND “ENAME” LIKE ‘SMITH%’)

But to achieve more efficiently we can make use of DECODE statement such as below:

SQL>SELECT COUNT(DECODE(DEPTNO,20, ‘X’, NULL))
D20_COUNT,
SUM(DECODE(DEPTNO,20, SAL, NULL))
D20_SAL
FROM EMP
WHERE ENAME LIKE ‘SMITH%’;

PLAN_TABLE_OUTPUT

Plan hash value: 2083865914

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 17 | 3 (0)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“ENAME” LIKE ‘SMITH%’)

From the above two select statement we can see the difference on the filer that is reduced by using DECODE statement.