External Stored Functions

Java UDF

A Java UDF is a user-defined function implemented in the Java language. With the Java UDF feature, you can call UDFs or procedures implemented in Java from the PL/SQL layer, thereby further expanding the application scenarios of PL.

Construct and call a Java UDF function

The procedure is as follows:

1. Compile the JAR package.

   Notice

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

     In the current version, Java UDFs must be compiled into JAR packages before being uploaded to the OBServer. You can compile and package the following code into my_add.jar, then create a Java UDF in OBServer. The code is as follows:

     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 MyAdd.java into a JAR package (JDK must be installed in advance). 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 the JAR package is compiled, it must be uploaded to an OBServer node to be used by Java UDFs. In OceanBase Database's Oracle-compatible mode, you can use DBMS_JAVA.OB_LOADJAR to upload the JAR package. If the JAR package is on an OBServer node, you can use utl_file to read its contents and then 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 a BLOB type binary jar package, and the second parameter is an optional flag. If -F is specified, existing results will be overwritten when encountering a class with the same name.

     Upload a local JAR package

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

     -- Directory where the JAR package is located.
     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 the remote JAR package

     You can use the following Java code to upload a JAR package from an OBServer node via 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, so they are stored in the current schema after being uploaded. You can view successfully uploaded Java classes through the ALL_OBJECTS, DBA_OBJECTS, or USER_OBJECTS views.

     A sample query is as follows:

     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 creating a PL UDF or Procedure, specify the LANGUAGE JAVA clause to indicate that the PL is a Java function/procedure wrapper. Then, use the NAME clause to specify the entry method signature. 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

     • PL function or procedure wrapping can only use Java Classes within the same schema.
     • There is no strict order for uploading JAR packages and creating PL/SQL packages; as long as the corresponding Java class objects are available when the PL/SQL package is invoked, it will work.

     • Call the JAVA UDF function.

       Notice

       Some UDFs may require sensitive privileges such as file or network access during execution. The DBA must grant these privileges to the corresponding users/roles in the SYS tenant in advance; otherwise, an error will occur during execution. For example, grant the read privilege on the /etc/os-release file to the SYS user of an Oracle-compatible tenant.

       Example:

       Note

       The functions in this example do not require special privileges, so you can skip the authorization step.

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

       After authorization, you can use Java UDFs just like regular PL/UDFs or procedures.

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

Limitations

• Creating Java UDFs from Java source code on the OBServer side is not supported in OceanBase Database V4.4.2 BP1.
• Replace oracle.sql.BLOB with java.sql.Blob in the source code.
• Replace oracle.sql.CLOB with java.sql.Clob in the source code.

References

DBMS_JAVA.OB_LOADJAR is used to upload a JAR package as an external resource to the OBServer. For more information about this system package, see DBMS_JAVA.OB_LOADJAR.