8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JServer - Java In The Database
Oracle 8i allows Java to be stored and executed from within the database. The following code gives is an example of how to use Java Stored Procedure/Functions. The function (addNumbers) is pretty stupid since it makes a database connection to add two numbers together, but it does serve as an example of the use of JDBC within the database. Using Java Stored Procedures requires the following steps:
Create a Java Stored Procedure
First we need to create a Java class so we have something to call.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Mathematics" AS import java.lang.*; import java.sql.*; import oracle.sql.*; import oracle.jdbc.driver.*; public class Mathematics { public static int addNumbers (int Number1, int Number2) { try { int iReturn = -1; // Connect to the database Connection conn = null; OracleDriver ora = new OracleDriver(); conn = ora.defaultConnection(); // Check record exists, and create it if it doesn't Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT " + Number1 + " + " + Number2 + " FROM dual"); if (resultSet.next()) { iReturn = resultSet.getInt(1); } resultSet.close(); statement.close(); conn.close(); return iReturn; } catch (Exception e) { return -1; } } }; / show errors java source "Mathematics"
Publish the Java call specification
Once this Java class is compiled into the database you will need to publish a call specification for it's method. This involves writing a PL/SQL "Wrapper" to allow the method to be called from PL/SQL. The following code sample creates a function called Addition which publishes the Mathematics method Add.
CREATE OR REPLACE FUNCTION AddNumbers (p_number1 IN NUMBER, p_number2 IN NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME 'Mathematics.addNumbers (int, int) return int'; /
Test It
We are now ready to use the Java class. The PL/SQL Wrapper allows it to be called from both SQL and PL/SQL.
SELECT addNumbers(1,2) FROM dual;
For a more realistics example of using Java within the database check out the article on Oracle interMedia.
Hope this helps. Regards Tim...