External storage functions

Java UDF

A Java UDF is a user-defined function implemented in Java. You can call a Java UDF or procedure from the PL/SQL layer. This feature expands the use cases of PL.

Construct and call a Java UDF

The steps are as follows:

1. Compile the jar package

   Notice

   • The following example is for learning only. In a production environment, use tools such as Maven to manage the lifecycle of Java code.
   • To avoid dependency issues, we recommend that you create a fat jar package, that is, a with-dependencies jar package.

     In the current version, a Java UDF must be compiled into a jar package and uploaded to the OBServer node. You can compile and package the following code into my_add.jar and then create a Java UDF in the OBServer node, as shown in the following example:

     package org.example;
     
     public class MyAdd {
         public static int myAddImpl(int a, int b) {
             int c = a + b;
             return c;
         }
     }
     

     Then, use the javac command to compile the MyAdd.java file into a jar package. Before you do this, make sure that you have installed the JDK. The command is as follows:

     mkdir -p target && javac -g -d target MyAdd.java && cd target && jar cvf my_add.jar *
     

   • Upload the jar package

     After you compile the jar package, you must upload it to the OBServer node to use it in a Java UDF. In OceanBase Database in Oracle mode, you can use DBMS_JAVA.OB_LOADJAR to upload a jar package. If the jar package is on the OBServer node, you can use utl_file to read the file content and upload it. If the jar package is on a remote node, you can use JDBC to upload it.

     Note

     DBMS_JAVA.OB_LOADJAR accepts two parameters. The first parameter is binary, which is a BLOB type jar package. The second parameter is an optional flag. If you specify -F, the existing result will be overwritten when a class with the same name is encountered.

     Upload a local jar package

     You can use the following PL/SQL statement to upload a jar package from the OBServer node:

     -- The directory where the jar package is stored
     CREATE OR REPLACE DIRECTORY JAR_DIR AS 'path/to/direcotry/of/jar';
     
     -- Read and upload the jar package
     DECLARE
     v_file      UTL_FILE.FILE_TYPE;
     v_buffer    RAW(32767);
     v_blob      BLOB;
     v_amount    BINARY_INTEGER := 32767;
     BEGIN
     
     DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
     
     v_file := UTL_FILE.FOPEN('JAR_DIR', 'my_add.jar', 'r');
     
     BEGIN
         LOOP
         UTL_FILE.GET_RAW(v_file, v_buffer, v_amount);
         DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_buffer), v_buffer);
         END LOOP;
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
         NULL;
     END;
     
     UTL_FILE.FCLOSE(v_file);
     
     -- -F for force
     DBMS_JAVA.OB_LOADJAR(v_blob, '-F');
     
     DBMS_LOB.FREETEMPORARY(v_blob);
     
     EXCEPTION
     WHEN OTHERS THEN
         IF UTL_FILE.IS_OPEN(v_file) THEN
         UTL_FILE.FCLOSE(v_file);
         END IF;
         IF DBMS_LOB.ISTEMPORARY(v_blob) = 1 THEN
         DBMS_LOB.FREETEMPORARY(v_blob);
         END IF;
         RAISE;
     END;
     /
     

     Upload a remote jar package

     You can use the following Java code to upload a jar package from a remote node to the OBServer node by using JDBC:

     // url is location of the jar
     void ob_loadjar(String url) throws IOException, SQLException {
         InputStream is = new URL(url).openStream();
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
     
         {
             byte[] bytes = new byte[40960];
             int len;
             while ((len = is.read(bytes)) != -1) {
                 baos.write(bytes, 0, len);
             }
         }
     
         PreparedStatement ps = conn.prepareStatement(
                 "declare\n" +
                         "jar blob := ?;\n" +
                         "flags varchar2(64) := ?;\n" +
                         "begin\n" +
                         "dbms_java.ob_loadjar(jar ,flags);\n" +
                         "end;");
     
         Blob b = new com.oceanbase.jdbc.Blob(baos.toByteArray());
         ps.setBlob(1, b);
         ps.setString(2, "-F");  // -F for force
         ps.execute();
         Statement s = conn.createStatement();
     }
     

   • View the upload result

     Java classes are schema-level objects. After a Java class is uploaded, it is saved in the current schema. After the upload, you can query the ALL_OBJECT, DBA_OBJECTS, or USER_OBJECTS view to view the uploaded Java class.

     Here is an example:

     obclient>select * from ALL_OBJECTS where OBJECT_TYPE = 'JAVA CLASS';
     +-------+-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     | OWNER | OBJECT_NAME       | SUBOBJECT_NAME | OBJECT_ID | DATA_OBJECT_ID | OBJECT_TYPE | CREATED             | LAST_DDL_TIME       | TIMESTAMP           | STATUS | TEMPORARY | GENERATED | SECONDARY | NAMESPACE | EDITION_NAME | SHARING | EDITIONABLE | ORACLE_MAINTAINED | APPLICATION | DEFAULT_COLLATION | DUPLICATED | SHARDED | IMPORTED_OBJECT | CREATED_APPID | CREATED_VSNID | MODIFIED_APPID | MODIFIED_VSNID |
     +-------+-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     | SYS   | org/example/MyAdd | NULL           |    501027 |           NULL | JAVA CLASS  | 2026-03-24 18:03:26 | 2026-03-24 18:03:26 | 2026-03-24 18:03:26 | VALID  | N         | N         | N         |         0 | NULL         | NULL    | NULL        | NULL              | NULL        | NULL              | NULL       | NULL    | NULL            |          NULL |          NULL |           NULL |           NULL |
     +-------+-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     1 row in set (1.195 sec)
     
     obclient>select * from DBA_OBJECTS where OBJECT_TYPE = 'JAVA CLASS';
     +-------+-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     | OWNER | OBJECT_NAME       | SUBOBJECT_NAME | OBJECT_ID | DATA_OBJECT_ID | OBJECT_TYPE | CREATED             | LAST_DDL_TIME       | TIMESTAMP           | STATUS | TEMPORARY | GENERATED | SECONDARY | NAMESPACE | EDITION_NAME | SHARING | EDITIONABLE | ORACLE_MAINTAINED | APPLICATION | DEFAULT_COLLATION | DUPLICATED | SHARDED | IMPORTED_OBJECT | CREATED_APPID | CREATED_VSNID | MODIFIED_APPID | MODIFIED_VSNID |
     +-------+-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     | SYS   | org/example/MyAdd | NULL           |    501027 |           NULL | JAVA CLASS  | 2026-03-24 18:03:26 | 2026-03-24 18:03:26 | 2026-03-24 18:03:26 | VALID  | N         | N         | N         |         0 | NULL         | NULL    | NULL        | NULL              | NULL        | NULL              | NULL       | NULL    | NULL            |          NULL |          NULL |           NULL |           NULL |
     +-------+-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     1 row in set (0.451 sec)
     
     obclient>select * from USER_OBJECTS where OBJECT_TYPE = 'JAVA CLASS';
     +-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     | OBJECT_NAME       | SUBOBJECT_NAME | OBJECT_ID | DATA_OBJECT_ID | OBJECT_TYPE | CREATED             | LAST_DDL_TIME       | TIMESTAMP           | STATUS | TEMPORARY | GENERATED | SECONDARY | NAMESPACE | EDITION_NAME | SHARING | EDITIONABLE | ORACLE_MAINTAINED | APPLICATION | DEFAULT_COLLATION | DUPLICATED | SHARDED | IMPORTED_OBJECT | CREATED_APPID | CREATED_VSNID | MODIFIED_APPID | MODIFIED_VSNID |
     +-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     | org/example/MyAdd | NULL           |    501027 |           NULL | JAVA CLASS  | 2026-03-24 18:03:26 | 2026-03-24 18:03:26 | 2026-03-24 18:03:26 | VALID  | N         | N         | N         |         0 | NULL         | NULL    | NULL        | NULL              | NULL        | NULL              | NULL       | NULL    | NULL            |          NULL |          NULL |           NULL |           NULL |
     +-------------------+----------------+-----------+----------------+-------------+---------------------+---------------------+---------------------+--------+-----------+-----------+-----------+-----------+--------------+---------+-------------+-------------------+-------------+-------------------+------------+---------+-----------------+---------------+---------------+----------------+----------------+
     1 row in set (0.839 sec)
     

   • Create a PL function

     When you create a PL UDF or procedure, specify the LANGUAGE JAVA clause to indicate that the PL function or procedure is a Java function or procedure wrapper. Then, specify the entry method signature by using the NAME clause. Here is an example:

     create or replace function my_add(a number, b number) return number as
     LANGUAGE JAVA
     NAME 'org.example.MyAdd.myAddImpl(int, int) return int';
     /
     

     Note

     • A PL function or procedure wrapper can use only the Java Class in the same schema.
     • There is no strict order between jar package upload and PL/SQL wrapper creation. As long as the PL/SQL wrapper can find the corresponding Java class object when it is called, the order is acceptable.

     • Call the Java UDF

       Notice

       Some UDFs may require sensitive privileges such as file and network access during execution. Before the execution, the DBA must grant the required privileges to the corresponding user or role in the SYS tenant. Otherwise, an error will be returned during execution. For example, grant the read privilege on the /etc/os-release file to the SYS user of the Oracle tenant.

       Here is an example:

       Note

       The function in this example does not require special privileges. You can skip the privilege granting step.

       obclient> call dbms_java.grant_permission('SYS', 'java.io.FilePermission', '/etc/os-release', 'read') tenant='oracle';
       

       After you grant the privileges, you can use the Java UDF like a regular PL UDF or procedure.

       obclient> select my_add(1, 2) from dual;
       +-------------+
       | MY_ADD(1,2) |
       +-------------+
       |           3 |
       +-------------+
       1 row in set (0.007 sec)
       

Limitations

• In OceanBase Database V4.4.2 BP1, you cannot create a Java UDF by using the Java source code on the OBServer node.
• If the source code contains oracle.sql.BLOB, you must use java.sql.Blob instead.
• If the source code contains oracle.sql.CLOB, you must use java.sql.Clob instead.

References

The DBMS_JAVA.OB_LOADJAR procedure uploads a Jar package as an external resource to the OBServer. For more information about this system package, see DBMS_JAVA.OB_LOADJAR.