Java classes in the Oracle database

I know that some people don’t have their best opinion on Oracle and of course there are truths to both sides. All things considered I think an Oracle database can be powerful, well-optimized and PL SQL provides us a great deal of functionalities at the database level. One of the advantages is that even if something seems “impossible” or quite complicated to be implemented in PL SQL, there is still no need to worry. We can turn to Java. Yes, you can write and load Java classes/sources within the database. Read more here, here and here with some key usages highlighted.

I am going to show two examples of a Java stored procedure used in the Oracle database.

1. Unzipping Archives Recursively

The first example is a solution for unzipping the content of an archive into a given folder. While this is possible, it would require significantly more time to do so from scratch with PL SQL, especially without using third party packages. I find this a good example to show how we can use a stored procedure running Java.

Step 1 - Write the Java source code

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;


public class UnzipFile {
    
    public static void unzipAll(String zipFile, String destFolder) {
        File destDir = new File(destFolder);
        try {
            byte[] buffer = new byte[1024];
            ZipInputStream zis = new ZipInputStream(new FileInputStream(zipFile));
            ZipEntry zipEntry = zis.getNextEntry();
            while (zipEntry != null) {
                 File newFile = newFile(destDir, zipEntry);
                 if (zipEntry.isDirectory()) {
                     if (!newFile.isDirectory() && !newFile.mkdirs()) {
                         throw new IOException("Failed to create directory " + newFile);
                     }
                 } else {
                     // fix for Windows-created archives
                     File parent = newFile.getParentFile();
                     if (!parent.isDirectory() && !parent.mkdirs()) {
                         throw new IOException("Failed to create directory " + parent);
                     }
                     // write file content
                     FileOutputStream fos = new FileOutputStream(newFile);
                     int len;
                     while ((len = zis.read(buffer)) > 0) {
                         fos.write(buffer, 0, len);
                     }
                     fos.close();
                 }
             zipEntry = zis.getNextEntry();
            }
            zis.closeEntry();
            zis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static File newFile(File destinationDir, ZipEntry zipEntry) 
           throws IOException {
        File destFile = new File(destinationDir, zipEntry.getName());

        String destDirPath = destinationDir.getCanonicalPath();
        String destFilePath = destFile.getCanonicalPath();

        if (!destFilePath.startsWith(destDirPath + File.separator)) {
            throw new IOException("Entry is outside of the target dir: " + 
                                   zipEntry.getName());
        }

        return destFile;
    }
    
}

// Source: https://www.baeldung.com/java-compress-and-uncompress

Step 2 - Add the Java class to the schema

Java classes/sources are stored as schema objects in the database as Java is a native part of the Oracle database. We can use the loadjava tool to create a schema object from our Java class. (If you use any IDE such as SQL Developer, you can do this from the UI as well.)
The loadjava tool can be run either from the command line or by using the loadjava method contained in the DBMS_JAVA package.

Command line (from the folder where the java file is):

loadjava -resolve -thin -user SCHEMA/PASSWORD@DATABASE UnzipFile.java

Using the DBMS_JAVA package:

call dbms_java.loadjava(' -resolve -thin -user SCHEMA/PASSWORD@DATABASE /path_to_file/UnzipFile.java ');

To drop a Java source, just execute this in the particular schema:

DROP JAVA SOURCE "UnzipFile";

Please note that in this example I am just loading the java file and not the compiled class file.

Step 3 - Create the PL SQL call specification

For each Java method that can be called from PL SQL, we must write a call specification which exposes the top-level entry point of the method to the Oracle Database.
We connect to the schema and execute the following:

create or replace procedure prc_unzip_all( p_zip_file    in varchar2, 
                                           p_dest_folder in varchar2 )
as language java
name 'UnzipFile.unzipAll( java.lang.String, java.lang.String )';
/

As you can see, in the call specification we have to indicate the parameters.

Ready to be executed

When the previous steps are completed, we can just call the PL SQL procedure to execute our Java code:

begin
    prc_unzip_all(p_zip_file    => '/path_to_file/file.zip',
                  p_dest_folder => '/path_to_dest');
end;
/

2. Insert the list of files in a directory into a table

In the second example I would like to show a small example for a function.

Let us assume we want to list the content of a directory and insert it into a table using Java. First we are going to truncate the existing data. At the end we return how many records were inserted into the table. This is not necessarily a real life scenario, but a good example.

For the sake of simplicity, I will not go through all the steps again in detail, please find below the Java class source code and the creation statement of the PL SQL function.

Java class:

import java.io.*;
import java.sql.*;


public class Directory {
    
    public static int insertDirContent(String dirPath) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        String sql = "insert into schema.dir_content (file_name) values (?)";
        File path = new File(dirPath);
        String[] list = path.list();
        String element;
        PreparedStatement pstmt;
        int counter = 0;
        
        for (int i = 0; i < list.length; i++) {
            element = list[i];
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, element);
            pstmt.executeUpdate();
            pstmt.close();
            counter += 1;
        }
        conn.commit();
        conn.close();
        
        return counter;
    }
    
}

PL SQL function:

create or replace function fnc_insert_dir_content( p_dir_path in varchar2 )
return number as 
language java
name 'Directory.insertDirContent( java.lang.String ) return int';
/

Note that we have to indicate the return type.

Execution of the PL SQL function:

declare
    i_count pls_integer := 0;
begin
    execute immediate 'truncate table schema.dir_content';
    i_count := fnc_insert_dir_content( p_dir_path => '/path_to_folder' );
    dbms_output.put_line('Records inserted: '||i_count);
end;
/

I hope these two examples will help you and provide some guidance on how to use Java code in an Oracle database. The point is that if PL SQL just doesn’t seem enough or you need to have a functionality which is intended to be written in Java, you have a more powerful language to turn to.