How to call java methods (which is inside a java package) from PLSQL

Question

The requirement is to call java methods from plsql and I am able to achieve it through loadjava command. The steps which I am following is :

Step 1: Create the Java Class/jar file and place it on unix box

Step 2: Load the Java Class/jar into the Database and i can see a new object in ALL_OBJCETS table with object type as JAVA CLASS

Step 3: Publish the Function: Creating the function/proc using java static method

Step 4: Execute the Function/proc.

And it's working fine for me but the challenge I am facing here when the java classs is defined in java package. for example: the class file is:

==========================

package emrsn.com;
public class DBTEST {

    public static String callMe(String input){
        return input;
    }
}

========================

Now when I am uploading the above java code (class file) in database I can see the object name is "emrsn\com\DBTEST", instead of "DBTEST" in ALL_OBJECTS table and the status is INVALID. I am not able to even recompile the class file in database (using ALTER JAVA CLASS obj_name RESOLVE), when the object name is like emrsn\DBTEST.

Please let em know if there is any other specific way to refer this type of java class file in database , when it is packaged in Java. Can you please let me know how do I use these JAVA class objects (when it is emrsn/com/DBTEST) while creating function or while recompiling this object. As mentioned earlier, I can't recompile them to see the exact error.

ALTER JAVA CLASS com/emrsn/DBTEST RESOLVE

Getting below error

Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"
*Cause:

And if I am using ALTER JAVA CLASS DBTEST RESOLVE, it's saying object not found. Let me know how I can refer these objects.

Michael Simons · Accepted Answer · 2014-03-27 09:41:50Z

2

You need to use double quotes (and forward slashes) and the right keyword depending wether you used class or source files:

alter java source "emrsn/com/DBTEST" compile;
-- or 
alter java class "emrsn/com/DBTEST" compile;
-- alternatively use resolve clause

To get the full name of all invalid objects use

SELECT dbms_java.longname (object_name) FROM user_objects
WHERE object_type = 'JAVA CLASS' AND status = 'INVALID';

(http://docs.oracle.com/cd/B19306_01/java.102/b14187/appendixa.htm)

To use your "callMe"

CREATE OR REPLACE FUNCTION f_call_me(p_input VARCHAR2) RETURN VARCHAR IS LANGUAGE JAVA name 'emrsn.com.DBTEST.callMe(java.lang.String) return java.lang.String';
/
sho err
select f_call_me('blah') from dual;

answered Mar 27, 2014 at 9:41

Michael Simons

4,9301 gold badge31 silver badges39 bronze badges

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

4 Comments

Anonymous Over a year ago

While creating funcion, do I need to use the code as below: ============================ CREATE OR REPLACE FUNCTION DBTEST_FUNC (P_IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'emrsn/com/DBTEST.callMe (java.lang.String) return java.lang.String'; If I am doing so , its giving error atr runtime : ============================== *Cause: An attempt was made to execute a non-existent method in a Java class. Or here also I need to use double quotes. Appreciate your prompt response.

Michael Simons Over a year ago

Yes, you cannot use java stored procedures without a wrapper function or procedure. Those can be global (like in my example) or package based.

Anonymous Over a year ago

Yes I do agree. But got stuck for packaged java methods. how do I need to use them in DB functions with slash!!

Anonymous Over a year ago

Ohh I scrolled your comment and got the solution. Sorry!!

Collectives™ on Stack Overflow

How to call java methods (which is inside a java package) from PLSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related