Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
The DBMS_JAVA package is somewhat of an enigma. It is a PL/SQL package but it is not documented in the Supplied PL/SQL Packages Reference guide. It is designed to support Java in the database, so you might expect to find it in the Supplied Java Packages Reference guide (but you won't). It is actually documented in the Oracle8i Java Developer's Guide. We've used it many times in this book already without really going through it, so here we will cover the procedures I use within this package, how to use them, and what they do.
The DBMS_JAVA package has almost 60 procedures and functions, only a very small handful of which are useful to us as developers. The bulk of this package is in support of debuggers (not for us to debug with, but for others to write debuggers for us), various internal convenience routines, and the export/import utilities. We will skip these functions and procedures altogether.
These are utility routines to convert between a 'short' 30-character identifier (all Oracle identifiers are 30 characters or less), and the 'long' Java name. If you look in the data dictionary, you will typically find a 'hashed' name for the Java classes that are loaded into the database. This is because they come with really long names, which the server cannot deal with. These two routines allow you to see what the 'real' name is, given a short name (OBJECT_NAME column in USER_OBJECTS), and what the short name would be given a long name. Here is an example of the usage of each when logged in as the user SYS (who happens to own lots of Java code, if you have Java installed in the database):
sys@TKYTE816> column long_nm format a30 word_wrapped sys@TKYTE816> column short_nm format a30 sys@TKYTE816> select dbms_java.longname(object_name) long_nm, 2 dbms_java.shortname(dbms_java.longname(object_name)) short_nm 3 from user_objects where object_type = 'JAVA CLASS' 4 and rownum < 11 5 / LONG_NM SHORT_NM ------------------------------ ------------------------------ com/visigenic/vbroker/ir/Const /1001a851_ConstantDefImpl antDefImpl oracle/sqlj/runtime/OraCustomD /10076b23_OraCustomDatumClosur atumClosure com/visigenic/vbroker/intercep /10322588_HandlerRegistryHelpe tor/HandlerRegistryHelper ... 10 rows selected.
As you can see, using LONGNAME on the OBJECT NAME turns it into the original class name for the Java class. If we take this long name and pass it through SHORTNAME, we get back the hashed-shortened name Oracle uses internally.
You may specify most compiler options for the Java compiler in the database, in one of two places; the command line when using loadjava, or in the JAVA$OPTIONS database table. A setting on the command line will always override the JAVA$OPTIONS table. This only applies if you use the Oracle Java compiler in the database, of course. If you use a standalone Java compiler outside of the database (JDeveloper perhaps), you will set compiler options in that environment.
There are three compiler options we may set, and they all relate to the SQLJ compiler (a pre-compiler for Java, converts embedded SQL statements into JDBC calls) built-in to the database. They are:
| Option | Meaning | Values |
|---|---|---|
| ONLINE | Whether type checking is done at compile-time (online), or run-time. | True/False |
| DEBUG | Whether the Java code is compiled with debugging enabled. Equivalent to javac -g in a command line environment. | True/False |
| ENCODING | Identifies the source file encoding for the compiler. | Latin1 is the default |
| Note | The values in bold are the default settings. |
We'll demonstrate the use of DBMS_JAVA to set compiler options using the online SQLJ pre-compiler option. Normally, this option defaults to True, and will cause the SQLJ pre-compiler to attempt to perform semantic checking on our SQLJ code. What this means is that the SQLJ pre-compiler would normally verify each and every referenced database object exists, that the host variable bind types match, and so on. If you would like this checking to be performed at run-time (perhaps the tables your SQLJ code will access are not yet created, but you would like to install your code cleanly), we can use the DBMS_JAVA.SET_COMPILER_OPTIONS routine to disable this type checking.
As an example, we'll use this snippet of code. It attempts to INSERT into a table that does not exist in the database:
tkyte@TKYTE816> create or replace and compile 2 java source named "bad_code" 3 as 4 import java.sql.SQLException; 5 6 public class bad_code extends Object 7 { 8 public static void wont_work() throws SQLException 9 { 10 #sql { 11 insert into non_existent_table values ( 1 ) 12 }; 13 } 14 } 15 / Java created. tkyte@TKYTE816> show errors java source "bad_code" Errors for JAVA SOURCE bad_code: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 bad_code:7: Warning: Database issued an error: PLS-00201: identifier 'NON_EXISTENT_TABLE' must be declared 0/0 insert into non_existent_table values ( 1 ) 0/0 ^^^^^^^^^^^^^^^^^^ 0/0 ; 0/0 #sql { 0/0 ^ 0/0 Info: 1 warnings
Now, we'll set the compiler option ONLINE to FALSE. In order to do this, we have to disconnect and connect again. There is an issue whereby the Java run-time will look for the existence of the JAVA$OPTIONS table once it starts up. If this table does not exist, it never attempts to read it again in that session. The DBMS_JAVA.SET_COMPILER_OPTION routine will create this table for us, but only if it is invoked prior to the Java run-time being started. So, we need a 'clean' session for this to work.
In the following example, we establish a new session, and then see that the JAVA$OPTIONS table does not exist. We'll set the compiler option, and see that the table has been created for us. Lastly, we'll create the same Java routine as above, and see that it compiles without warnings this time, due to the compiler option setting:
tkyte@TKYTE816> disconnect Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production tkyte@TKYTE816> connect tkyte/tkyte Connected. tkyte@TKYTE816> column value format a10 tkyte@TKYTE816> column what format a10 tkyte@TKYTE816> select * from java$options; select * from java$options * ERROR at line 1: ORA-00942: table or view does not exist tkyte@TKYTE816> begin 2 dbms_java.set_compiler_option 3 ( what => 'bad_code', 4 optionName => 'online', 5 value => 'false' ); 6 end; 7 / PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from java$options; WHAT OPT VALUE ---------- -------------------- ---------- bad_code online false tkyte@TKYTE816> create or replace and compile 2 java source named "bad_code" 3 as 4 import java.sql.SQLException; 5 6 public class bad_code extends Object 7 { 8 public static void wont_work() throws SQLException 9 { 10 #sql { 11 insert into non_existent_table values ( 1 ) 12 }; 13 } 14 } 15 / Java created. tkyte@TKYTE816> show errors java source "bad_code" No errors.
The SET_COMPILER_OPTION takes three inputs in this case:
WHAT - A pattern to be matched against. Normally, Java programs would use packages and hence, the above name would be a.b.c.bad_code, not just bad_code. If you want to set an option for a package a.b.c, you may. Then, anything that matched a.b.c would use this option, unless there was a more specific pattern, which matches this package. Given a WHAT of a.b.c, and a.b.c.bad_code, then a.b.c.bad_code would be used, since it matches more of the name.
OPTIONNAME - One of the three values ONLINE, DEBUG, or ENCODING.
VALUE - The value for that option.
There are two routines related to SET_COMPILER_OPTION. They are:
GET_COMPILER_OPTION - This returns the value of a given compiler option, even if the value is defaulted.
RESET_COMPILER_OPTION - This removes any row from the JAVA$OPTIONS table that matches the WHAT pattern, and the OPTIONNAME.
Here are examples of both in action. We'll begin by using GET_COMPILER_OPTION to see the value of the online option:
tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> begin 2 dbms_output.put_line 3 ( dbms_java.get_compiler_option( what => 'bad_code', 4 optionName => 'online' ) ); 5 end; 6 / false PL/SQL procedure successfully completed.
and now we'll reset it using RESET_COMPILER_OPTION:
tkyte@TKYTE816> begin 2 dbms_java.reset_compiler_option( what => 'bad_code', 3 optionName => 'online' ); 4 end; 5 / PL/SQL procedure successfully completed.
Now we'll see that GET_COMPILER_OPTION will always return us a value for the compiler option, even though the JAVA$OPTIONS table is now empty (the RESET deleted the row):
tkyte@TKYTE816> begin 2 dbms_output.put_line 3 ( dbms_java.get_compiler_option( what => 'bad_code', 4 optionName => 'online' ) ); 5 end; 6 / true PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from java$options; no rows selected
This procedure is a lot like the SQL*PLUS command SET SERVEROUTPUT ON. Just as you need to use it to enable DBMS_OUTPUT, we need to use DBMS_JAVA.SET_OUTPUT to enable the results of System.out.println and System.err.print calls to come to the screen in SQL*PLUS. If you fail to call:
SQL> set serveroutput on size 1000000 SQL> exec dbms_java.set_output( 1000000 )
before running a Java stored procedure in SQL*PLUS, you must be aware that any of its System.out.println messages will be written to a trace file in the directory specified by the USER_DUMP_DEST init.ora parameter on the server. This procedure is truly useful when debugging Java stored procedures, as you can put calls to System.out.println in the code, much as you would put DBMS_OUTPUT.PUT_LINE calls in your PL/SQL. Later, you can disable this in your Java code by redirecting System.out to the 'bit bucket'.
So, if you ever wondered where your System.out calls where going in a Java stored procedure, now you know. They were going to a trace file. Now you can cause that output to come to your screen in SQL*PLUS.
These functions provide PL/SQL APIs to perform the job of the command line utilities loadjava and dropjava. As you might expect with these internal routines, you do not need to specify a -u username/password, or specify the type of JDBC driver to use - you are already connected! These routines will load the Java objects into the currently logged in schema. The supplied routines are:
PROCEDURE loadjava(options varchar2) PROCEDURE loadjava(options varchar2, resolver varchar2) PROCEDURE dropjava(options varchar2)
We could use this to load the activation8i.zip file, which we also use in the UTL_SMTP section, and more information on JavaMail API can be found at http://java.sun.com/products/javamail/index.html For example:
sys@TKYTE816> exec dbms_java.loadjava( '-r -v -f -noverify -synonym -g p ublic c:\temp\activation8i.zip' ) initialization complete loading : com/sun/activation/registries/LineTokenizer creating : com/sun/activation/registries/LineTokenizer loading : com/sun/activation/registries/MailcapEntry creating : com/sun/activation/registries/MailcapEntry loading : com/sun/activation/registries/MailcapFile creating : com/sun/activation/registries/MailcapFile loading : com/sun/activation/registries/MailcapParseException creating : com/sun/activation/registries/MailcapParseException ...
These are strange ones indeed. Do a DESCRIBE on DBMS_JAVA in the database, and tell me if you see GRANT_PERMISSION in that package. You won't, although you know it must exist since you've seen me use it quite a few times. It does exist, as do a couple of other permission-related functions. We'll describe the GRANT_PERMISSION/REVOKE_PERMISSION here, and its usage. For complete details on using the permissions routines, and all of the options, refer to the Oracle Java Developers Guide. Chapter 5 in this manual, Security for Oracle 8i Java Applications, covers these functions.
In Oracle 8.1.5, the granularity of privileges in Java was very coarse. You either had JAVAUSERPRIV or JAVASYSPRIV, pretty much. This would be like having just RESOURCE and DBA roles in the database - in both cases these roles may offer too much functionality to the end users. With Oracle 8.1.6, the Java in the database supports the Java 2 security classes. Now we have very granular privileges we can grant and revoke, just like the database has for its privilege set. For a general discussion and overview of these permission classes, I'll refer you to this web page http://java.sun.com/j2se/1.3/docs/api/java/security/Permission.html
So, the two main APIs we'll use here are GRANT_PERMISSION and REVOKE_PERMISSION. The question is, how do I find out what permissions I need? The easiest way is to install the Java, run it, and see what it tells you it needs. For example, I will refer you to the UTL_SMTP section. In there, I create the stored procedure SEND to send mail. I also show you the two grants we need to perform with GRANT_PERMISSION in order to get that to work. The way in which I discover exactly what those grants was to run SEND and see how it fails. For example:
tkyte@TKYTE816> set serveroutput on size 1000000 tkyte@TKYTE816> exec dbms_java.set_output( 1000000 ) PL/SQL procedure successfully completed. tkyte@TKYTE816> declare 2 ret_code number; 3 begin 4 ret_code := send( 5 p_from => 'me@here.com', 6 p_to => 'me@here.com', 7 p_cc => NULL, 8 p_bcc => NULL, 9 p_subject => 'Use the attached Zip file', 10 p_body => 'to send email with attachments....', 11 p_smtp_host => 'aria.us.oracle.com', 12 p_attachment_data => null, 13 p_attachment_type => null, 14 p_attachment_file_name => null ); 15 if ret_code = 1 then 16 dbms_output.put_line ('Successful sent message...'); 17 else 18 dbms_output.put_line ('Failed to send message...'); 19 end if; 20 end; 21 / java.security.AccessControlException: the Permission (java.util.Property Permission * read,write) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE))
Now, that is about as clear as you can get. It is telling me that TKYTE needs the permission type java.util.PropertyPermission with * and read and write. This is how I knew I needed to execute:
sys@TKYTE816> begin 2 dbms_java.grant_permission( 3 grantee => 'TKYTE', 4 permission_type => 'java.util.PropertyPermission', 5 permission_name => '*', 6 permission_action => 'read,write' 7 );
After I did this, I discovered the error:
java.security.AccessControlException: the Permission (java.net.SocketPer mission aria.us.oracle.com resolve) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE))
and after granting that, it told me I needed CONNECT in addition to RESOLVE. This is how I knew to add:
8 dbms_java.grant_permission( 9 grantee => 'TKYTE', 10 permission_type => 'java.net.SocketPermission', 11 permission_name => '*', 12 permission_action => 'connect,resolve' 13 ); 14 end; 15 /
to the privileges that this schema had. Note that I used * in the permission_name so I could actually resolve and connect to any host, not just my SMTP server.
Now, the opposite of GRANT_PERMISSION is REVOKE_PERMISSION. It operates exactly as you might think. If you pass it the same exact parameters you pass to GRANT_PERMISSION, it will revoke that privilege from the schema.
In this section, we covered using the DBMS_JAVA package to perform various operations for us. We started out by looking at how Oracle, which has a 30-character name limit, handles the very long names used in Java. It hashes a unique, 30-character name for each of the long Java names. The DBMS_JAVA package gives us a function to convert either a short name back into its corresponding long name, or to convert a long name into its short name representation.
Next we investigated using DBMS_JAVA to set, retrieve, and reset various Java compiler options. We saw how this feature uses the JAVA$OPTIONS table to permanently store default compiler options for us, and how we can use it to reset these values back to their defaults. Then we looked briefly at the SET_OUTPUT routine. This redirects the output generated by System.out.println Java calls to a SQL*PLUS or SVRMGRL session, much in the same way SET SERVEROUTPUT ON does for the PL/SQL routine DBMS_OUTPUT. We also saw how the DBMS_JAVA package provides an alternative method of loading Java source code, class files and jars into the database, via a stored procedure call in Oracle8i release 2 (version 8.1.6) and up. Lastly, we looked at the permission procedures provided by this package in Oracle8i release 2 and up. This interface allows us to grant very granular privileges to our Java routines, allowing us to strictly control what they can, and cannot do.
All in all, if you are using Java inside the Oracle database, you will find these routines invaluable in your day-to-day programming.