How to correctly develop, upload java library with dependencies to Oracle DB and call my Java function from PLSQL?

I have:

Oracle 19c
java 8 on its machine

What i did:

I write simple class with one method in Java 8.

package <mypackage>;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.networknt.schema.JsonSchema;
import com.networknt.schema.JsonSchemaFactory;
import com.networknt.schema.SpecVersion;
import com.networknt.schema.ValidationMessage;

import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.Set;
import java.util.stream.Collectors;

public class <classname> {

    <some simple validation function>
}

JavaScript
​x
 
package <mypackage>;​import com.fasterxml.jackson.databind.JsonNode;import com.fasterxml.jackson.databind.ObjectMapper;import com.networknt.schema.JsonSchema;import com.networknt.schema.JsonSchemaFactory;import com.networknt.schema.SpecVersion;import com.networknt.schema.ValidationMessage;​import java.io.IOException;import java.io.Reader;import java.sql.Clob;import java.sql.SQLException;import java.util.Set;import java.util.stream.Collectors;​public class <classname> {​    <some simple validation function>}​

I compile my project with maven and maven-assembly-plugin to build .jar file with dependencies.

I upload it with loadtool: loadjava -f -r -v -synonym -oracleresolver -resolve -grant <user> -thin -user <credentials> <filename>.jar

There were 0 errors during upload. All uploaded classes (including dependencies) have ‘VALID’ status in dba_objects table.

I write PL/SQL wrapper over my Java function.

create FUNCTION <funcname>(P_IN_BODY_TEXT CLOB, P_IN_BODY_SCHEMA_TEXT CLOB)
        RETURN VARCHAR2
    AS LANGUAGE JAVA NAME '<packagename>.<classname>.<funcname>(java.sql.Clob, java.sql.Clob) return java.lang.String';
/

JavaScript
 
create FUNCTION <funcname>(P_IN_BODY_TEXT CLOB, P_IN_BODY_SCHEMA_TEXT CLOB)        RETURN VARCHAR2    AS LANGUAGE JAVA NAME '<packagename>.<classname>.<funcname>(java.sql.Clob, java.sql.Clob) return java.lang.String';/​

I use this function in my ORDS REST Service.

When doing request to ORDS i am getting this exception:

The request could not be processed because an error occurred whilst attempting to evaluate 
the SQL statement associated with this resource. 
Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 29532, 
Error Message: ORA-29532: Java call terminated by uncaught Java exception: 
java.lang.NoClassDefFoundError ORA-06512: at <rest stacktrace that gives me nothing>

JavaScript
 
The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 29532, Error Message: ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError ORA-06512: at <rest stacktrace that gives me nothing>​

Quiestion is:

What is root of this problem? By -synonym flag tool creates synonyms for me, all classes is valid. Do i need some permisions to use java packages liike java.sql that in my class imports? I upload some others open sources java libraries into my Oracle, but they doesn’t hava dependencies – is that the problem?

Answer

Problem was in slf4j library that throws this exception. slf4j was dependency of library that i used. Didn’t dig the problem, I just pick another labrary with less dependencies and its works.

Advertisement

Answer