OJVM : Loading Java Code and Running it from a Database Session

 Oracle's JVM in the database, available since 8i and also known as "OJVM" allows you (Developer or DBA) to load Java code into the database and execute it from a database session.  Note that any OS calls that it makes will be from the server process, not the client process. Thus, if the code tries to read or write to a filesystem or to connect to an external website, the call will be executed by the database server process, not your client (sqlplus or any other program).

Here is a simple example :

SQL> -- grant Java Code permission to only READ only /home/oracle/tmp
SQL> -- this should have been provided by the DBA
SQL>
SQL> begin
  2     dbms_java.grant_permission('HEMANT',
  3                                      'SYS:java.io.FilePermission',
  4                                      '/home/oracle/tmp',  'read');
  5
  6     dbms_java.grant_permission( 'HEMANT',
  7                                      'SYS:java.io.FilePermission',
  8                                       '/home/oracle/tmp/*',
  9                                       'read' );
 10
 11     dbms_java.grant_permission( 'HEMANT',
 12                                      'SYS:java.lang.RuntimePermission',
 13                                       'getFileSystemAttributes',
 14                                       '' );
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL>
SQL> -- here is where I load the Java code into my schema
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL>
SQL> -- load the java code into the database
SQL> create or replace and compile java source named "readOSDirectory"
  2      as
  3  /* using  java.io */
  4      import java.io.*;
  5
  6  /* create the main class */
  7      public class readOSDirectory
  8      {
  9  /* create the class to be executed from a procedure */
 10       public static void getList(String directory)
 11       {
 12  /* use File class from java.io */
 13         File directoryPath = new File( directory );
 14         File filesList[] = directoryPath.listFiles();
 15
 16  /* read till end of list */
 17         for (File file : filesList)
 18           {
 19             System.out.println("File Name: "+file.getName()
 20                                + "  File Size:  "+file.length());
 21           }
 22        }
 23       }
 24  /

Java created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> /* create a PL/SQL procedure to call the getList class */
SQL> create or replace
  2     procedure Read_Directory( p_directory in varchar2 )
  3     as language java
  4     name 'readOSDirectory.getList( java.lang.String )';
  5  /

Procedure created.

SQL>
SQL> /* enable output to screen-- serveroutput is for my sqlplus session */
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL> CALL dbms_java.set_output (100000);

Call completed.

SQL>
SQL> begin
  2  Read_Directory('/home/oracle/tmp');
  3  end;
  4  /
File Name: File_1.txt  File Size:  13
File Name: File_2.txt  File Size:  25
File Name: Run_Sequence_Loop.sql  File Size:  1047
File Name: List_LongOps.sql  File Size:  303
File Name: create_db_HEMANT.sql  File Size:  1530
File Name: create_db_HEMANT.lst  File Size:  47

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2  Read_Directory('/home/oracle');
  3  end;
  4  /
Exception in thread "Root Thread" java.security.AccessControlException: the Permission ("java.io.FilePermission" "/home/oracle"
"read") has not been granted to HEMANT. The PL/SQL to grant this is dbms_java.grant_permission( 'HEMANT',
'SYS:java.io.FilePermission', '/home/oracle', 'read' )
        at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
        at java.security.AccessController.checkPermission(AccessController.java:926)
        at java.lang.SecurityManager.checkPermission(SecurityManager.java:551)
        at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:210)
        at java.lang.SecurityManager.checkRead(SecurityManager.java:890)
        at java.io.File.list(File.java:1117)
        at java.io.File.listFiles(File.java:1207)
        at readOSDirectory.getList(readOSDirectory:11)
begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
("java.io.FilePermission" "/home/oracle" "read") has not been granted to HEMANT. The PL/SQL to grant this is
dbms_java.grant_permission( 'HEMANT', 'SYS:java.io.FilePermission', '/home/oracle', 'read' )
ORA-06512: at "HEMANT.READ_DIRECTORY", line 1
ORA-06512: at line 2


SQL>
SQL> !ls -l /home/oracle/tmp
total 24
-rw-r--r--. 1 oracle oinstall   47 Apr 25 18:46 create_db_HEMANT.lst
-rw-r--r--. 1 oracle oinstall 1530 Apr 25 18:46 create_db_HEMANT.sql
-rw-r--r--. 1 oracle oinstall   13 Apr 25 18:07 File_1.txt
-rw-r--r--. 1 oracle oinstall   25 Apr 25 18:34 File_2.txt
-rw-r--r--. 1 oracle oinstall  303 Apr 25 18:46 List_LongOps.sql
-rw-r--r--. 1 oracle oinstall 1047 Apr 25 18:46 Run_Sequence_Loop.sql

SQL>

(Note : The Java call reads from the OS's directory object "as is" returned by the directory.  The "ls -l" command sorts the output, so you may see a difference in the sorted output)

Since OJVM runs in a database server process, strong permission controls are enforced.  So, the first set of calls show permissions being granted by the DBA to the "HEMANT" schema to only read from /home/oracle/tmp

Then, the user (HEMANT) connects and loads his Java code into the database.  (Note : The first time you run this, it may take some time to load the java classes).  Then he creates a PL/SQL procedure as a "wrapper" that can execute the Java code.

Finally, he executes the java code to read the target directory /home/oracle/tmp

I also demonstrate how his attempt to read /home/oracle fails as he has not been granted explicit permission on this directory