I’ll try to demonstrate how we can use loadjava to load our Java class into Oracle Database and use its methods as stored procedures. Before we begin to write the Java codes, I’ll create a simple table. When I call my java stored procedure, it will insert a record to this table.
|
1 2 3 4 5 |
CREATE TABLE hr.sampletable ( id NUMBER, name VARCHAR2(50), email VARCHAR2(50) ); |
I created this table in HR schema because I’ll load my java object in HR schema. If you’ll use another schema, then do not forget to load your java code in same schema or set required permissions. This is the main method I’ll use as my stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
public static void insert_into_table(int ID, String Name, String Email) { Connection DB = DriverManager.getConnection("jdbc:default:connection:"); String SQL ="INSERT INTO sampletable VALUES (?,?,?)"; PreparedStatement cmd = DB.prepareStatement(SQL); cmd.setInt(1, ID); cmd.setString(2, Name ); cmd.setString(3, Email ); cmd.executeUpdate(); cmd.close(); } |
As you see, we can use “default connection” to connect the database we’ll load our Java. I use “PreparedStatemen” to add the bind variables to SQL statement and run it. I need to handle exceptions, so I add try/catch block and add the required “import” directives, so this is the final version:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
import java.sql.*; import oracle.jdbc.*; public class JavaSample { public static void insert_into_table(int ID, String Name, String Email) { try { Connection DB = DriverManager.getConnection("jdbc:default:connection:"); String SQL ="INSERT INTO sampletable VALUES (?,?,?)"; PreparedStatement cmd = DB.prepareStatement(SQL); cmd.setInt(1, ID); cmd.setString(2, Name ); cmd.setString(3, Email ); cmd.executeUpdate(); cmd.close(); } catch(SQLException e) { System.err.println( "Error: " + e.getMessage()); } } } |
Save this file as “JavaSample” and then issue the following command:
|
1 2 3 4 5 6 7 |
loadjava -u hr/hr -v -r JavaSample.java arguments: '-u' 'hr/hr' '-v' '-r' 'JavaSample.java' creating : source JavaSample loading : source JavaSample creating : JavaSample resolving: source JavaSample |
Let’s check if it’s loaded:
|
1 2 |
SELECT object_name, object_type, status FROM user_objects WHERE object_name = ‘JavaSample’; |
Now we will create an interface procedure for our Java procedure in PL/SQL:
|
1 2 3 4 5 6 |
CREATE PROCEDURE hr.TestForJava ( id NUMBER, name VARCHAR2, email VARCHAR2 ) AS LANGUAGE JAVA NAME 'JavaSample.insert_into_table( int, java.lang.String, java.lang.String )'; / |
Let’s call our procedure and check if a new record will be inserted to our table:
|
1 2 3 4 5 6 7 |
EXEC HR.TestForJava( 1, 'Gokhan', 'gokhan@gokhan' ); SELECT * FROM hr.sampletable; ID NAME EMAIL ---------- --------------- ------------------------------- 1 Gokhan gokhan@gokhan |
