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-28T12:52:12.780

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 '17 at 12:52

answered Sep 03 '13 at 06:55

Vincent Malgrat

66,725
9
119
171

From which version of oracle database this can be done , anyother constraints ? – Santhosh Feb 05 '15 at 07:37
@SanKrish: [The integrated jvm was introduced in Oracle 8i (1999)](http://www.orafaq.com/wiki/Oracle_8i). You need the [`CREATE PROCEDURE`](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5014.htm#SQLRF01211) privilege to create a java source or a function. – Vincent Malgrat Feb 05 '15 at 09:10
thanks . i got it right , but couldnt run the function using the 3rd part of your code :( – Santhosh Feb 05 '15 at 09:12
1

@SanKrish This is a straight copy-paste from sql*plus, it should work as is. What error message exactly do you get? – Vincent Malgrat Feb 05 '15 at 09:31
i dont get any error messges , rather it outputs `Anonymus block executed`. I am using sql developer to execute – Santhosh Feb 05 '15 at 09:35
1

@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 Feb 05 '15 at 09:38
Thank you :) it prints . sorry i am completely new to this. is it possible to add external jars with the java file ? – Santhosh Feb 05 '15 at 09:40
1

@SanKrish It should be possible, you might want to browse the [Database Java Developer's Guide](http://docs.oracle.com/cd/E11882_01/java.112/e10588/toc.htm) if you're going to use this feature. – Vincent Malgrat Feb 05 '15 at 09:43
Thanks for your response .. will look into it :) – Santhosh Feb 05 '15 at 09:58
@VincentMalgrat dont think so it is weired scenario like uploading .java in DB and calling from SQL ? In which all scenarios do we need to do this ? In the weblayer only we can java,why to load to DB!! – Nagappa L M Feb 26 '21 at 06:53
@Santhosh dont think so it is weired scenario like uploading .java in DB and calling from SQL ? In which all scenarios do we need to do this ? In the weblayer only we can java,why to load to DB!! – Nagappa L M Feb 26 '21 at 06:53

Jim Garrison · Answer 2 · 2018-02-13T08:47:18.760

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 '18 at 08:47

answered Sep 03 '13 at 06:35

Jim Garrison

85,615
20
155
190

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? – Divas Sep 03 '13 at 06:46
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. – Jim Garrison Sep 03 '13 at 16:32
@JimGarrison where do we upload this files ? i have jar and oracle12c same machine ? – Develop4Life Feb 13 '18 at 08:43

score 1 · Answer 3 · answered Sep 03 '13 at 06:37

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 03 '13 at 06:37

inquizitive

622
4
21

He should not be on 11.1 - jdk 1.7 is not supported there (when need to load .class files) – igr Sep 03 '13 at 08:46

Calling Java from PL/SQL

3 Answers3

Linked