CODEWITHSUNDEEP
javaplsql
Divas
Divas

Reputation: 453

Calling Java from PL/SQL

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.

Upvotes: 11

Views: 43030

Answers (3)

Jim Garrison
Jim Garrison

Reputation: 86774

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

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

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

Upvotes: 17

inquizitive
inquizitive

Reputation: 632

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.

Upvotes: 1

Related Questions