Use Java Stored Procedure in RDS for Oracle 19c

1. Prepare Oracle database, make sure JVM option installed

2. Prepare Testing Data

create table t (id int,last_update timestamp);
insert into t values(1,CURRENT_TIMESTAMP(0));
commit;

3. Prepare Java Code Example

import java.sql.*;
import java.io.*;
import oracle.jdbc.*;

public class DemoJava
{
    public static void PrintTable () throws SQLException 
    {
      String sql = "SELECT TO_CHAR(id)||', '||TO_CHAR(last_update) FROM t";
      try
      {
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        System.out.println(rset.getString(1));
        rset.close();
        pstmt.close();
      }
      catch (SQLException e)
      {
        System.err.println(e.getMessage());
      }
    }

    public static void ListFile(String myfolder) {
      File dir = new File(myfolder);
      String[] children = dir.list();
      
      if (children == null) {
         System.out.println( "Either dir does not exist or is not a directory");
      } else { 
         for (int i = 0; i< children.length; i++) {
            String filename = children[i];
            System.out.println(filename);
         }
      }
   }
}

4. Load Java Code into Oracle Database

loadjava -u admin/yourcomplexpassword@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL -v -r -t DemoJava.java

[oracle@OraceClient ~]$ loadjava -u admin/yourcomplexpassword@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL -v -r -t DemoJava.java
arguments: '-u' 'admin/***@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL' '-v' '-r' '-t' 'DemoJava.java' 
dropped  : JAVA$CLASS$MD5$TABLE
creating : source DemoJava
loading  : source DemoJava
resolving: source DemoJava
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

5. Test 1: Use Java to query table

CREATE OR REPLACE PROCEDURE DemoJavaPrintTable
AS LANGUAGE JAVA
NAME 'DemoJava.PrintTable()';
/

SET SERVEROUTPUT ON
CALL dbms_java.set_output(2000);
CALL DemoJavaPrintTable();

SQL> SQL> SET SERVEROUTPUT ON
SQL> 
SQL> CALL dbms_java.set_output(2000);

Call completed.

SQL> CALL DemoJavaPrintTable();
1, 13-JUL-22 10.20.14.000000 AM

Call completed.

6. Test 2: Use Java to to access file system

CREATE OR REPLACE PROCEDURE DemoJavaListFile (myfolder varchar2)
AS LANGUAGE JAVA
NAME 'DemoJava.ListFile(java.lang.String)';
/

SET SERVEROUTPUT ON
CALL dbms_java.set_output(2000);
CALL DemoJavaListFile('/rdsdbdata/log/diag/rdbms/');

SQL> CALL DemoJavaListFile('/rdsdbdata/log/diag/rdbms/');
Exception in thread "Root Thread" java.security.AccessControlException: the
Permission ("java.io.FilePermission" "/rdsdbdata/log/diag/rdbms" "read") has not
been granted to ADMIN. The PL/SQL to grant this is dbms_java.grant_permission(
'ADMIN', 'SYS:java.io.FilePermission', '/rdsdbdata/log/diag/rdbms', 'read' )
        at
java.security.AccessControlContext.checkPermission(AccessControlContext.java)
        at java.security.AccessController.checkPermission(AccessController.java:928)
        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.normalizedList(File.java:1110)
        at java.io.File.list(File.java:1159)
        at DemoJava.ListFile(DemoJava:29)
CALL DemoJavaListFile('/rdsdbdata/log/diag/rdbms/')
     *
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission ("java.io.FilePermission"
"/rdsdbdata/log/diag/rdbms" "read") has not been granted to ADMIN. The PL/SQL
to grant this is dbms_java.grant_permission( 'ADMIN',
'SYS:java.io.FilePermission', '/rdsdbdata/log/diag/rdbms', 'read' )

7. Troubleshooting

1. To find out what java permissions you have been granted in RDS for Oracle for Java, use following query:

SELECT * FROM dba_java_policy
  WHERE grantee IN ('RDS_JAVA_ADMIN', 'PUBLIC')
  AND enabled = 'ENABLED'
  ORDER BY type_name, name, grantee;

2. Make sure you have installed/added JVM option to the database, it supports both Oracle EE and SE2.

[oracle@OraceClient ~]$ loadjava -u admin/yourcomplexpassword@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL -v -r -t DemoJava.java
arguments: '-u' 'admin/***@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL' '-v' '-r' '-t' 'DemoJava.java' 
created  : JAVA$CLASS$MD5$TABLE
creating : source DemoJava
loading  : source DemoJava
created  : CREATE$JAVA$LOB$TABLE
Error while creating source DemoJava
    ORA-06550: line 1, column 75:
PLS-00201: identifier 'DBMS_JAVA.SET_COMPILER_OPTION' must be declared
ORA-06550: line 1, column 75:
PL/SQL: Statement ignored
ORA-06550: line 1, column 239:
PLS-00201: identifier 'NameFromLastDDL' must be declared
ORA-06550: line 1, column 231:
PL/SQL: Statement ignored

Error while computing shortname of DemoJava
    ORA-00942: table or view does not exist

The following operations failed
    source DemoJava: creation (createFailed)
exiting  : Failures occurred during processing