Database Reference
In-Depth Information
the same name as the file. However in our example, there is no
MyApp4.java
file, and the file we have is
not even named “MyApp4” dot anything.
The reason for this discrepancy is that we are only defining this class on the Oracle database. As it is
right now, we can't compile or run it on the client. And when we load it into the Oracle database, the
things I said about packages and classes suddenly becomes true again. The Oracle database creates a
virtual package directory that it can search like a CLASSPATH to find the class. Also, it compiles the code
just like a regular
*.java
file and creates the class files, that is virtual files, stored in the database.
Acquiring the Privilege to Load a Java Stored Procedure
Application security user,
appsec
, needs the
CREATE PROCEDURE
privilege to load Java into the database.
She gets that privilege through the
appsec_role
. Recall that we set the
appsec_role
to be a non-default
role for
appsec
user. For that reason, when
appsec
connects to Oracle database, she does not have that
role at the outset. Rather, she has to enable it by setting
appsec_role
for her current session. The method
shown (
SET ROLE appsec_role
) is sufficient, and as you recall, the new role becomes the only role that
appsec
has in her current session. An alternative approach is to request that all roles granted to
appsec
(both default and non-default) be enabled in the current session with this command:
SET ROLE ALL;
Loading Java in the Oracle Database
There are a couple ways to get Java code into the Oracle database. One way is to use the
loadjava.exe
utility. That utility is available in both the Oracle database and in some versions of the Oracle client
software. It is in the same bin directory as
sqlplus.exe
and other Oracle applications. Using
loadjava
, we
can submit a java file (or
sqlj
file, with SQL embedded in Java code), a class file, or even a jar file to the
Oracle database. The server handles whatever we submit in an appropriate manner. Here is an example
loadjava
command that we might have used to load a Java file named
MyApp4.java
(refer to Listing 4-1).
loadjava -force -resolve -user appsec/password@orcl pkg4/MyApp4.java
That command would attach to Oracle database as
appsec
user, then read the
MyApp4.java
file from
the
pkg4
directory and submit it to Oracle database. The new code would overwrite anything that existed
previously by the same name, and the Oracle database would compile the code and place a virtual
MyApp4.class
file in a virtual
pkg4
directory.
Another approach we can use to load Java code into Oracle database is what we've used in our
example, Listing 4-1. We have the Java code for the MyApp4 class listed after this Oracle statement:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED myapp4 AS
We can run this
CREATE
statement while connected to Oracle database, and it will accomplish the
same things that we would have accomplished through the
loadjava
command.
Handling Exceptions in a Java Stored Procedure
As you can see in the
getOracleTime()
method in the
MyApp4
class in Listing 4-1, we start out with a
try
/
catch
/
finally
block. Whenever you are dealing with databases, you must be prepared to catch or
throw a
SQLException
. There are many cases when a
SQLException
would be thrown; for example, if you
misspell a table name or a column name or if you don't have permission to read the data or if the Oracle
database has a problem responding.
IOExceptions
can also be generated when doing input/output (IO).
Communicating with Oracle database is I/O as is reading and writing files, and as is reading from
network resources, like web servers.