Calling Java from PL/SQL

Question

Can any one please help me on this: I want to call one java program from the Pl/SQL, Oracle RDBMS, the below are the settings

Windows 7 machine, Java is installed on C:\Program Files\Java\jdk1.7.0_02

I created one directory to keep the java files. D:\Java, it has one hello.java file in it.

public class Hello
{
  public static String world()
  {
    return "Hello world";
  }
}

this was compiled fine, and the .class file was generated in the same directory.

Since I have to call this function using PL/SQL, here is the PL/SQL function I've written:

create or replace
FUNCTION helloworld RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';

and this is the PL/SQL procedure:

create or replace
PROCEDURE hellow
AS
  my_string varchar2(400 char);
begin
  my_string:=helloworld();
  dbms_output.put_line('The value of the string is ' || my_string);
end;

both the function and the procedure compiled fine using the SQL/developer.

When I tried running this procedure:

set serveroutput on;
execute hellow;

the following error is coming:

Error starting at line 2 in command: execute hellow Error report: ORA-29540: class Hello does not exist ORA-06512: at "ORACLE_SOURCE.HELLOWORLD", line 1 ORA-06512: at "ORACLE_SOURCE.HELLOW", line 5 ORA-06512: at line 1
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.

I placed the .class file in the bin folder also, but still the same error is coming. Can anyone please have a look at this.

Vincent Malgrat · Accepted Answer · 2017-09-28 12:52:12Z

17

You can also compile and save your java source directly in the database, like stored procedures:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Hello" AS
public class Hello
{
  public static String world()
  {
    return "Hello world";
  }
};
/

> Java created

Calling this function is straightforward and doesn't need additional settings:

CREATE OR REPLACE
FUNCTION helloworld RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';
/

DECLARE
   my_string VARCHAR2(400 CHAR);
BEGIN
   my_string := helloworld();
   dbms_output.put_line('The value of the string is ' || my_string);
END;
/

> The value of the string is Hello world

> PL/SQL procedure successfully completed

edited Sep 28, 2017 at 12:52

answered Sep 3, 2013 at 6:55

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Santhosh Over a year ago

From which version of oracle database this can be done , anyother constraints ?

Vincent Malgrat Over a year ago

@SanKrish: The integrated jvm was introduced in Oracle 8i (1999). You need the CREATE PROCEDURE privilege to create a java source or a function.

Vincent Malgrat Over a year ago

@SanKrish This is a straight copy-paste from sql*plus, it should work as is. What error message exactly do you get?

Vincent Malgrat Over a year ago

@SanKrish That means it works as intended. Just use SET SERVEROUTPUT ON or your client's equivalent command to read/enable the DBMS_OUTPUT messages.

Vincent Malgrat Over a year ago

@SanKrish It should be possible, you might want to browse the Database Java Developer's Guide if you're going to use this feature.

|

Jim Garrison · Accepted Answer · 2018-02-13 08:47:18Z

3

Before you can invoke a Java program in the Oracle database you must upload it to the server. When PL/SQL invokes a Java class' methods it does so in a JVM running on the Oracle server, not on your local system, and the source files must be uploaded to the Oracle system, where they get compiled.

Use the loadjava tool, as described in Java Applications on Oracle

edited Feb 13, 2018 at 8:47

answered Sep 3, 2013 at 6:35

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

3 Comments

Divas Over a year ago

Thanks,understood the point, just one small doubt, in my case, the Oracle and JVM were installed on the same machine, but it worked only after uploading the java source code through the SQL developer to the oracle, how are these 2 different?

Jim Garrison Over a year ago

The Java code must be stored in the database. When you invoke it, it runs in Oracle's own internal JVM. Oracle does not have access to Java code on the system outside of its own database environment.

Daniel Wondyifraw Over a year ago

@JimGarrison where do we upload this files ? i have jar and oracle12c same machine ?

inquizitive · Accepted Answer · 2013-09-03 06:37:03Z

1

From http://docs.oracle.com/cd/B28359_01/java.111/b31225/chthree.htm

Load the class on the server using the loadjava tool. You must specify the user name and password. Run the loadjava tool as follows:

loadjava -user scott Hello.class
Password: password

Refer the URL for additional info.

answered Sep 3, 2013 at 6:37

inquizitive

6324 silver badges22 bronze badges

1 Comment

igr Over a year ago

He should not be on 11.1 - jdk 1.7 is not supported there (when need to load .class files)

Collectives™ on Stack Overflow

Calling Java from PL/SQL

3 Answers 3

10 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related