Can you call a java function from an oracle stored procedure?

Answer 1

The simple answer to this is yes. You can either build your java class on the server side machine and then load it up into Oracle using the loadjava command, which can also compile it if you so desire, or load it direct from PL/SQL using: create or replace and compile java source named <class name> AS ...

and then the java code. e.g. ..... create or replace and compile java source named helloBob AS

public class HelloBob

{

public static String hello()

{

return "Hello Bob!";

} public static String hello(String name)

{

return "Hello " + name + "!";

}

} NOTE: There are performance improvements in loading it into Oracle as a compiled class. Once in Oracle, the the functions in this class can be called by defining it as a function or procedure... in the example featured two functions can be defeind as follows: CREATE OR REPLACE FUNCTION HelloBob RETURN VARCHAR2

IS LANGUAGE JAVA NAME 'HelloBob.hello() return String';

/

CREATE OR REPLACE FUNCTION Hello(name VARCHAR2) RETURN VARCHAR2

IS LANGUAGE JAVA NAME 'HelloBob.hello(java.lang.String) return String';

/ Note: Good programming practice is to put functions in a 'package' in Oracle... it makes them easier to roll out. You can now call these functions: e.g. select hellobob() AS "Greeting" FROM DUAL; should return a column "Greeting" with the value 'Hello Bob!' and... SELECT hello('Fred') AS "Greeting" FROM DUAL; should return a column "Greeting" with the value 'Hello Fred!' You can pass and return all types of Oracle variable, including collections.... see the oracle.sql package definition for more info. If you need to make database calls from the java function then use OracleDriver().defaultConnection() to get a connection (contained in the oracle.jdbc package).