How to call Java stored procedures/Classes from PL/SQL
There is lot of requirements where we need to use Java like zipping the files stored in database, deleting files from server.
Oracle provides the way to access Java stored Procedure and classes from PL/SQL, we can use according to our need
Following steps are required:
- Create Java class according to your requirement
- Compile and load it into database
- Write one PL/SQL Wrapper Program to call this.
Simple Demonstration:
Requirement: To delete file from server, file name will be provided as input parameter
Steps to achieve this:
1. Write Java class to delete the file and compile it.
public class TestDelete {
public static int delete ...
public static void main (String args[]) {
System.out.println (
delete (args[0])
);
}
}
2. Load java class into database
C:\oracle9i\bin>loadjava -user scott/tiger -oci8 -resolve
TestDelete.class
3. Write PL/SQL Wrapper Program:
CREATE OR REPLACE FUNCTION fDelete (
file IN VARCHAR2)
RETURN NUMBER
AS LANGUAGE JAVA
NAME 'TestDelete.delete (
java.lang.String)
return int';
LoadJava utility: The loadjava utility (Oracle 8.1.5 and up) loads
Java source and class files into the database.When class files are
created in a conventional manner, outside the database,
loadjava is used to get them into the database.
Figure 1: Loading Java elements into Oracle
Here is the syntax:
loadjava {-user | -u} username/password[@database]
[-option_name [-option_name] ...] filename [filename ]...
where option_name stands for the following syntax:
{ {andresolve | a}
| debug
| {definer | d}
| {encoding | e} encoding_scheme_name
| {force | f}
| {grant | g} {username | role_name}[,{username | role_name}]...
| {oci8 | o}
| oracleresolver
| {resolve | r}
| {resolver | R} "resolver_spec"
| {schema | S} schema_name
| {synonym | s}
| {thin | t}
| {verbose | v} }
loadjava requires two database privileges to load java objects into your own schema: CREATE PROCEDURE and CREATE TABLE. To load Java objects into a schema other than the currently connected user, CREATE ANY PROCEDURE and CREATE ANY TABLE privileges are required.
This example will use a simple Java program that will be compiled outside of Oracle and then loaded into the database.
public class SimpleJava {
public void main(String[] args) {
System.out.println(“Here we are”);
}
From DOS or UNIX :
C:\oracle9i\bin>javac SimpleJava.java
C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.class
The class file is now loaded into the database and visible from the dba_objects view with an object type of JAVA CLASS.
From SQL*Plus, create the PL/SQL wrapper to invoke the newly loaded Java class:
SQL> create or replace procedure call_simplejava
2 as language java
3 name ‘SimpleJava.showMessage()’;
4 /
Execute the code from SQL*Plus:
SQL> set serveroutput on;
SQL> call dbms_java.set_output(50);
Call completed.
SQL> execute call_simplejava;
Here we are
PL/SQL procedure successfully completed.
In this example, the Java class file was loaded into the database. The Java source file can also be loaded. But, both the source and class files cannot be loaded at the same time.
C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.java
If loading many Java class files at one time, it is advisable to put them in a JAR file and load them into the database at one time, since the loadjava program will also load JAR files. A JAR file is a group of Java class files lumped into one file, a format similar to TAR (on UNIX ) and WinZip (on Windows). The contents of a JAR file can be viewed using these popular utilities. Java developers prefer to distribute a few JAR files rather than many individual Java class files.
Oracle8i has created two new roles to support Java security. For many Java-based operations within the database, you will not have to work with these roles. If, on the other hand, you want to interact with the operating system (to access or modify operating system files, for example), you need to be granted one of the following roles:
JAVASYSPRIV JAVAUSERPRIV
You grant these roles as you would any other database role. For example, if I want to allow SCOTT to perform any kind of Java-related operation, I would issue this command from a SYSDBA account:
GRANT JAVASYSPRIV TO SCOTT;
If I want to place some restrictions on what the user can do with Java, I might execute this grant instead:
GRANT JAVAUSERPRIV TO SCOTT; To access Java class methods from within Oracle, you must take the following steps:
- Create the Java code elements. You can do this in Oracle’s JDeveloper, or in any other Java Integrated Development Environment. Load the Java class(es) into Oracle using the loadjava command-line utility or the CREATE JAVA statement.
- Publish the Java class methods inside PL/SQL by writing wrapper programs in PL/SQL around the Java code.
- Grant privileges as required on the PL/SQL wrapper programs and the Java class referenced by the PL/SQL wrapper.
- Call the PL/SQL programs from any one of a number of environmentsOracle8i offers a variety of components and commands to work with Java following table summarizes these different elements:
Table 1: Oracle Components and Commands for Java
| |
| Component | Description |
| Aurora JVM | The Java Virtual Machine (JVM) that Oracle implemented in its database server |
| loadjava | An operating system command-line utility that loads your Java code elements (classes, .jar files, etc.) into the Oracle database |
| dropjava | An operating system command-line utility that drops your Java code elements (classes, .jar files, etc.) from the Oracle database |
| CREATE JAVA
DROP JAVA
ALTER JAVA
| New DDL statements that perform some of the same tasks as loadjava and dropjava |
| DBMS_JAVA | A built-in package that offers a number of utilities to set options and other aspects of the JVM |
| DBMS_JAVA_TEST | A built-in package you can use to more easily test your JSPs |
| JPublisher | A utility used to build Java classes around object types and REFs defined in the Oracle database |
Figure 9.1: Accessing JSPs from within the Oracle database
Data Type Mapping Between Java and SQL:
| SQL Type | Java Class |
| CHAR, NCHAR, LONG, VARCHAR2, NVARCHAR2 | oracle.sql.CHAR java.lang.String java.sql.Date java.sql.Time java.sql.Timestamp java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte, short, int, long, float, double |
| DATE | oracle.sql.DATE java.sql.Date java.sql.Time java.sql.Timestamp java.lang.String |
| NUMBER | oracle.sql.NUMBER java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte, short, int, long, float, double |
| RAW, LONG RAW | oracle.sql.RAW
byte[]
|
| ROWID | oracle.sql.CHAR oracle.sql.ROWID java.lang.String |
| BFILE | oracle.sql.BFILE
|
| BLOB | oracle.sql.BLOB oracle.jdbc2.Blob |
| CLOB, NCLOB | oracle.sql.CLOB oracle.jdbc2.Clob |
| OBJECT | oracle.sql.STRUCT oracle.SqljData oracle.jdbc2.Struct |
| REF | oracle.sql.REF oracle.jdbc2.Ref |
| TABLE, VARRAY | oracle.sql.ARRAY oracle.jdbc2.Array |
| Any of the above SQL types | oracle.sql.CustomDatum oracle.sql.Datum |
COMMENTS