Work With Java Oracle

How can I work with Java in the Database?

Administration Tips

How can I work with Java in the Database? You can start by using Java Stored Procedures. Here, I’ll show you how you can make Java do the absolute classic job of updating the salary field for a specified employee in the EMP table. It’s a useless thing to do in real life: Java is a complex language, and makes doing very simple things (like ‘update emp set sal=…’) incredibly hard work –and you’d be a mug to do it in real life. But Java’s complexities also mean that doing very complex things are much easier than trying to do it with PL/SQL or SQL alone. I’m not taking you down the very-complex-made-easy path (partly because I think we’ll both get lost!). But at least this simple example will give you an idea of how to start to get Java working for you. First, copy this script exactly into a text editor (notepad or vi, for example), and save it in a file called Test.java (and yes, there’s a capital “T” and a lower case “j” there, and both are significant). Save it in your Oracle_Home directory (it doesn’t have to be saved there, but it makes things a bit easier later on). import java.sql.*; public class Test { public static void updateSal(int empid, int salary) { try { Connection connection = DriverManager.getConnection("jdbc:oracle:kprb:"); Statement statement = connection.createStatement(); int norows = statement.executeUpdate("UPDATE emp SET sal = "+salary+" WHERE empno = "+empid); connection.commit(); } catch (SQLException e) { System.out.println(e); } } }

A word to the wise: that statement is case sensitive like you wouldn’t believe! When I inadvertently tried it with its line 7 reading “…CreateStatement();” the second stage produced errors complaining that “CreateStatement” was not a method it was aware of. Quite right too: it knows about ‘createStatement’ with the lower case “c”! If you can’t manage to type all that in exactly, therefore, you’ll find a working, tested version on the “Scripts” page of this website. Second, in your Oracle_Home directory (wherever that might be) type the following command: loadjava –u scott/tiger@SID –r Test.java The “SID” there should obviously be replaced with the actual name of the Instance in which you wish to run this Java Stored Procedure. That must relate to an entry in your tnsnames.ora file. Copyright © Howard Rogers 2001

1/11/2001

Page 1 of 2

How can I work with Java in the Database?

Administration Tips

If you haven’t got tnsnames.ora configured, then you can replace it with the usual host:port:sid connection string (where host is the name of your machine, and port Is the port on which the Listener is listening for connections to the Instance named by SID), but you also need to add in a new parameter, like this: loadjava –thin –u scott/tiger@host:port:sid –r Test.java Note also the precise capitalisation of the ‘Test.java’ name at the end of either of these variants of the command. What you are doing at this point is loading the text file we produced earlier into the database as a Java Stored Procedure. Third, you connect to the database as Scott, and create a procedure that calls the new Java Stored Procedure we’ve just created. You do that because JSPs can’t be called directly, but only via PL/SQL. In other words: connect scott/tiger@SID create or replace procedure updatesal (“empid” in number, “salary” in number) as language java name ‘Test.updateSal(int,int)’; / Again, capitalisation is important in that last line. Fourth and finally, it’s time to actually run the procedure. In SQL Plus, type: exec updatesal(7934,1000) Notice how this works –you call the PL/SQL procedure, supplying the relevant parameters (in this case, employee number and new salary figure). The PL/SQL procedure then calls the Java Stored Procedure we earlier loaded into the database, passing it the supplied parameter values in the process, and it is the Java code which then does the actual updating and commiting of the requested update. If you follow all that with a normal select * from emp; you should see that employee 7934 now has a $1000 salary. And that’s how you do it, more or less. Create Java procedures in a text editor, create Java Stored Procedures within the database by loading the code with the ‘loadjava’ application, and then create a PL/SQL procedure which actually calls the Java procedure. Hopefully, that will give you enough to play around with, and see what else you can do!

Copyright © Howard Rogers 2001

1/11/2001

Page 2 of 2