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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, shubham.

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

Last updated: February 17, 2021 - 2:48 pm UTC

Version: 12c

Viewed 10K+ times! This question is

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 Chris 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.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Unable to load jar using dbms_java.loadjava in Oracle

Shubham Grover, February 14, 2020 - 11:42 am UTC

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
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

Shubham Grover, February 19, 2020 - 10:46 am UTC

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
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!

Unable to load jar using dbms_java.loadjava in Oracle

Shubham Grover, February 27, 2020 - 6:17 am UTC

I have checked mentioned three points. Followings are my observations:
I can create self signed certificate of url
https://drive.google.com/file/d/1nmYo8a5It613poK7bPGXnmRhfRVWarAQ/view
through chrome browser, but in real scenario i will have trusted CA certified certificate. so i download trusted certificate through internet
link : https://cacert.omniroot.com/bc2025.crt and add it in oracle wallet
through following query:
C:\Users\dataguise>orapki wallet add -wallet d:\orawallet -trusted_cert -cert "C:\Users\dataguise\Downloads\bc2025.crt" -pwd WalletPasswd789
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.


I have added ACl also
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'WWW ACL',
                                    principal   => 'TEST1_SHUBHAM',
                                    is_grant    => true,
                                    privilege   => 'connect');
 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'TEST1_SHUBHAM',
                                       is_grant  => true,
                                       privilege => 'resolve');
 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'http://cacert.omniroot.com/bc2025.crt');
END;


and execute following query
SELECT utl_http.request('http://cacert.omniroot.com/bc2025.crt') from dual;

same is visible on dba_network_acls
SELECT host, lower_port, upper_port, acl FROM   dba_network_acls;


So i came to point that i have already taken care of all three points mentioned in your thread.
Please suggest me the way forward.

Chris Saxon
February 27, 2020 - 11:16 am UTC

You'll need an ACL in place to talk to drive.google.com too, won't you?

What happens if you try a http request to drive.google.com instead of loadjava - does this work?

If you can read files from Drive in some other way, but not through loadjava I'm not sure what the issue is. Take it up with support.

UNABLE TO LOAD JAR USING LOADJAVA TOOL

Rohan Sutar, February 17, 2021 - 8:32 am UTC

I am trying to load jar which is build by using maven but I am getting below error.

call dbms_java.loadjava('/home/rsutar/Downloads/latest/17feb/First.jar");

ORA-29532: Java call terminated by uncaught Java exception:
oracle.aurora.rdbms.MalformedDefinitionException: In ClassHandle.create in
schema UDF
ORA-29509: incorrectly formed Java binary class definition

Could you please help me? How to resolve Java Binary class defination issue in maven java project.
Chris Saxon
February 17, 2021 - 2:48 pm UTC

There's a problem with the Java class. Exactly what I don't know - we have limited Java expertise here

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library