Skip to Main Content
  • Questions
  • Unable to load jar using dbms_java.loadjava in Oracle

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, shubham.

Asked: February 12, 2020 - 11:41 am UTC

Answered by: Chris Saxon - Last updated: February 20, 2020 - 10:37 am UTC

Category: Database Development - Version: 12c

Viewed 1000+ times

You Asked

I need to upload a jar file in Oracle RDBMS using dbms_java.loadjava method. I have granted all the required permission and able to run below function successfully.

create or replace function get_java_property(prop in varchar2)
return varchar2 is
language java name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';

select get_java_property('java.version') from dual;

1.8.0_231


However, I also need to upload an external jar and use the same. I am successfully able to do the same by placing jar on Oracle Server and running loadjava command. However I want to do the same using dbms_java.loadjava from my local system. I am trying following query.

call dbms_java.loadjava('–proxy -Dhttp.proxyHost=my_local_system_ip -Dhttp.proxyPort=22 file:///home/Documents/abc.jar');


Its returning Call completed. However, jar is not uploaded as I have tried to access methods in Jar and getting error

ORA-29540: class com/pkg_name/function_name does not exist
29540. 00000 -  "class %s does not exist"  
*Cause:    Java method execution failed to find a class with the indicated name.
*Action:   Correct the name or add the missing Java class.


However same methods are accessible when jar is uploaded through loadjava command. Please suggest. May be I am missing some basics.

and we said...

When you use DBMS_Java.loadjava, it tries to load files from the database server.

I see you have provided a proxy. But you need a webserver running on your machine for this to work. And the database needs permission to talk to it. It's highly unlikely the ACLs (access control lists) in the database allow this.

and you rated our response

  (2 ratings)

Reviews

Unable to load jar using dbms_java.loadjava in Oracle

February 14, 2020 - 11:42 am UTC

Reviewer: Shubham Grover from India

I have created a public http link and try to call dbms_java.loadjava procedure (as mentioned below) from remote server but it still not working through proxy.
However i am getting a success message "Call completed.".
call dbms_java.loadjava('-proxy googledrive.com:443/ https://drive.google.com/uc?id=1nmYo8a5It613poK7bPGXnmRhfRVWarAQ&export=download' );

I have created oracle function
create or replace function getAppMtd RETURN varchar2
as language java
NAME 'org/dataguise/demo/App.getAppMtd()
return java.lang.String';

when i execute that function through select getAppMtd() from dual; It gives error:
ORA-29540: class org/dataguise/demo/App does not exist
29540. 00000 - "class %s does not exist"
*Cause: Java method execution failed to find a class with the indicated name.
*Action: Correct the name or add the missing Java class

However I am able to do the same on database server by calling below plsql procedure.
call dbms_java.loadjava('D:\demo-app-jar-with-dependencies.jar');
and after executing select getAppMtd() from dual;
it shows result: "Hello Ask TOM"

Please suggest as i want to load jar from remote server by making http link of it.
Link : https://drive.google.com/uc?id=1nmYo8a5It613poK7bPGXnmRhfRVWarAQ&export=download
Chris Saxon

Followup  

February 18, 2020 - 10:49 am UTC

You need to ensure that:

- The network firewalls allow your database to reach Google Drive
- You've added the ACLs in the database to allow it to reach Google Drive
- You've configured the certificates and any other authentication necessary to read the file

This article on Oracle-base can help you configure database access https://oracle-base.com/articles/misc/utl_http-and-ssl

Unable to load jar using dbms_java.loadjava in Oracle

February 19, 2020 - 10:46 am UTC

Reviewer: Shubham Grover from India

Since i am getting success response while calling
dbms_java.loadjava('-proxy googledrive.com:443/ https://drive.google.com/uc?id=1nmYo8a5It613poK7bPGXnmRhfRVWarAQ&export=download' );

so i am assuming all of these three points mentioned above have already been taken care of.
Please suggest further.
Chris Saxon

Followup  

February 20, 2020 - 10:37 am UTC

I also get "call completed" when trying to load that jar. But I can assure you, I haven't configured access. And the jar isn't loaded:

SQL> set define off
SQL> call dbms_java.loadjava('-proxy -Dhttp.proxyHost=googledrive.com -Dhttp.proxyPort=443 https://drive.google.com/uc?id=1nmYo8a5It613poK7bPGXnmRhfRVWarAQ&export=download' );

Call completed.

SQL> select * from user_java_classes;

no rows selected


Check you really have access!

More to Explore

DBMS_JAVA

More on PL/SQL routine DBMS_JAVA here