The easiest way to use Java in Oracle

There are several methods to use Java in Oracle. I am going to show the easiest way. Basically, the Java interpreter starts running the application by calling main() method. However, Java applications within the database do not start by a call to the main() method. Therefore, Java application does not has to contain main() method in Oracle database. You can run it by calling any static method within the loaded class. Oracle Java virtual machine (JVM) sessions and conventional Oracle sessions are very similar to each other. Each Java session has a separate session memory and call memory. The following code shows that how a simple Java code can compile and run in Oracle database.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "welcome" 
AS
public class welcome {

	public static String msg() {
		return "Welcome to https://www.oracleplsqltr.com/";
	}
	
};
/
CREATE OR REPLACE FUNCTION f_java
RETURN VARCHAR2 
AS
LANGUAGE JAVA NAME 'welcome.msg() return java.lang.String';
/
declare
    v_return varchar2(500);
begin
    v_return := f_java;
    dbms_output.put_line(v_return);
end;
/

F_JAVA
--------------------------------------------------------------------------------
Welcome to https://www.oracleplsqltr.com/

You can check the created Java objects from USER_OBJECTS dictionary view.

SELECT object_name, object_type, status FROM user_objects WHERE object_type like '%JAVA%';


OBJECT_NAME                    OBJECT_TYPE                    STATUS
------------------------------ ------------------------------ --------------------------
welcome                        JAVA CLASS                     VALID
welcome                        JAVA SOURCE                    VALID

Running OS command via Java

The below example also illustrates that how to pass a parameter to Java method while calling it. Besides, in order to use java.io.* library, java.io.FilePermission have to be granted to the user as shown below.

BEGIN
 
DBMS_JAVA.GRANT_PERMISSION(
                           'DB_USER',
                           'SYS:java.io.FilePermission',
                           '<<ALL FILES>>',
                           'read,write,delete,execute'
                           );
 
END;
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "jos" 
AS
import java.io.*;

public class jos {
	
	public static String run_os_cmd(String p_cmd) {
		Process process;
		String ln = "";
		
		try {
			
			process = Runtime.getRuntime().exec(p_cmd);
			ln = printResults(process);
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return ln;
			
	}
	
	public static String printResults(Process process) throws IOException {
		
	    BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
	    String line = "";
	    String line2 = "";
	    
	    while ((line = reader.readLine()) != null) {
	        //System.out.println(line);
	    	line2 += line + "\n";
	    }
	    
	    return line2;
	}	
	
};
/
CREATE OR REPLACE FUNCTION f_execute_cmd(p_cmd varchar2)
RETURN VARCHAR2 
AS
LANGUAGE JAVA NAME 'jos.run_os_cmd(java.lang.String) return java.lang.String';
/
declare
    v_return varchar2(4000);
begin
    v_return := f_execute_cmd('pwd');
    dbms_output.put_line(v_return);
end;

/opt/oracle/product/19.0.0/dbhome_1/dbs

Performance Comparison


PL/SQL vs Java

There is no doubt that this will be the most interesting part. In this section I am going to compare Java and PL/SQL performance, based on simple calculations. Oracle 19c has been used for this process. In the following programs, loops have been used in order to populate and sum of the each array element. So, let’s run the following procedures for both Java and PL/SQL then compare the difference between them.

DROP JAVA SOURCE "jcalc";

DROP PROCEDURE JAVA_TEST_CALC;

DROP PROCEDURE PLSQL_TEST_CALC;

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "jcalc"
AS
public class jcalc {
 
    public static void test_calc(int p_iteration) {
     
        int ind = 0;
        int iteration = 0;
        int innerloop = 0;
        double sum = 0.0;
        int array_length = p_iteration;
        double[] arr = new double[array_length];
         
        for (ind = 0; ind < array_length; ind++)
            arr[ind] = ind;
             
        for (innerloop = 0; innerloop < array_length; innerloop++)
            sum += arr[(iteration + innerloop) % array_length];
        
        System.out.println(Double.valueOf(sum).longValue());
         
        arr = null;
    }
};
/
CREATE OR REPLACE PROCEDURE JAVA_TEST_CALC(P_ITERATION NUMBER)
AS
LANGUAGE JAVA NAME 'jcalc.test_calc(int)';
/
CREATE OR REPLACE PROCEDURE PLSQL_TEST_CALC(P_ITERATION INTEGER)
IS
    v_element integer := 0;
    v_iteration integer := 0;
    v_innerloop integer := 0;
    v_total number := 0.0;
    v_array_length integer := p_iteration;
    type t_arr is table of number;
    arr t_arr := t_arr();
BEGIN

    /* UPDATED as stated in the comment  */
    --SELECT LEVEL BULK COLLECT INTO ARR FROM DUAL CONNECT BY LEVEL <= v_array_length;
    
    FOR I IN 1..v_array_length
    LOOP
        ARR.EXTEND;
        ARR(ARR.COUNT) := I;
    END LOOP;
    
    /* --------------------------------- */
    
    --Index of nested table array start with 1
    v_innerloop := 1;
      
    WHILE v_innerloop < v_array_length
    LOOP
        v_total := v_total + arr(mod((v_iteration + v_innerloop), v_array_length));
        v_innerloop := v_innerloop + 1;
    END LOOP;
      
    dbms_output.put_line(v_total);
      
    arr.delete();
      
END;
/
CALL dbms_java.set_output(2000);
SET SERVEROUTPUT ON;
declare
 
    V_START NUMBER;
     
    PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
        V_START := DBMS_UTILITY.GET_TIME;
    END;
 
 
begin
 
    V_START := DBMS_UTILITY.GET_TIME;
     
    PLSQL_TEST_CALC(10000000);
     
    SHOW_ELAPSED_TIME('PLSQL');
     
    JAVA_TEST_CALC(10000000);
     
    SHOW_ELAPSED_TIME('Java');
 
end;
/

49999995000000
PLSQL elapsed time: 3,56
49999995000000
Java elapsed time: ,48

Results show that PL/SQL takes 3.56 seconds whereas Java takes only 0.48 second. Therefore, it can be said that Java is much more faster then PL/SQL. Approximately 7 times faster for my test case. However, we have not taken into account database interaction. As it is known, PL/SQL is database programming language. Both languages are used for different purposes. if your main goal is to deal with data then PL/SQL is still one of the best choice for you.

4 thoughts on “The easiest way to use Java in Oracle

  1. Hey,

    It not fair to compare apples to oranges.

    Java is meant for a specific purpose its a general purpose programming language. PLSQL on the other hand is not a general purpose programming language. Also its very specific to Oracle.

    So this comparison of performance is flawed. It does not help!!

    Thanks.

    • Hello,

      As I stated in the post, both programming languages are used for different purposes. I agree with you. However, that doesn’t mean that I cannot compare them. We may need to use many different technologies in order to increase the application performance.

      Besides, it is very possible that Java and pl/sql are used together in many projects. Moreover, Java pool is one of the component of SGA. It is used for all session specific Java code and data within the JVM.

  2. Hello,

    Nice post, but I’m not sure the performance comparison is made right here. Since the plsql code contains sql, it will need to switch the engine to sql and come back to progress with the plsql code. Either you need to add sql to your java code, or remove the sql from the plsql code.

    The PLSQL performance also depends on the load of the DB. I’m sure, while testing the only load for the java virtual machine is the code you have provided above, whereas the load of your DB is not clear.

    If I rewrite the plsql code as below, in my DB environment it takes 2 seconds.

    declare
    v_element integer := 0;
    v_iteration integer := 0;
    v_innerloop integer := 0;
    v_total number := 0.0;
    v_array_length integer := 10000000;
    type t_arr is table of number;
    arr t_arr;
    BEGIN
    arr:=t_arr();
    arr.extend(v_array_length);
    for i in 1.. v_array_length
    loop
    ARR(i):=i;
    end loop;
    –Index of nested table array start with 1
    for v_innerloop in 1..v_array_length-1
    LOOP
    v_total := v_total + arr(mod((v_iteration + v_innerloop), v_array_length));
    END LOOP;

    dbms_output.put_line(v_total);
    arr.delete();
    end;

    • Hello Seyit,

      Actually, elapsed time of pl/sql can vary depending on your db environment. On the other hand, you are absolutely right about context switch. It is overlooked. The differences are compared in the following code. Populating collection with classic way is twice as fast then using SQL. Post has been updated in this way. Thanks for your contribution.

      DECLARE

      V_START NUMBER;

      PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
      IS
      BEGIN
      DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
      V_START := DBMS_UTILITY.GET_TIME;
      END;

      PROCEDURE PLSQL_TEST_CALC(P_ITERATION INTEGER)
      IS
      v_total number := 0.0;
      v_array_length integer := p_iteration;
      type t_arr is table of number;
      arr t_arr;
      arr2 t_arr := t_arr();
      BEGIN

      V_START := DBMS_UTILITY.GET_TIME;

      SELECT LEVEL BULK COLLECT INTO ARR FROM DUAL CONNECT BY LEVEL <= v_array_length;

      SHOW_ELAPSED_TIME('SQL population elapse time:');

      ARR.DELETE();

      FOR I IN 1..v_array_length
      LOOP
      ARR2.EXTEND;
      ARR2(ARR2.COUNT) := I;
      END LOOP;

      SHOW_ELAPSED_TIME('PL/SQL collection elapse time:');

      ARR.DELETE();

      END;

      BEGIN

      PLSQL_TEST_CALC(10000000);

      END;

      SQL population elapse time: elapsed time: 3,26
      PL/SQL collection elapse time: elapsed time: 1,52

Leave a Reply

Your email address will not be published.