To access Java class methods from within Oracle, you must take the following steps:
1.
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.
2.
Publish the Java class methods inside PL/SQL by writing wrapper
programs in PL/SQL around the Java code.
3.
Grant privileges as required on the PL/SQL wrapper programs and
the Java class referenced by the PL/SQL wrapper.
4.
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. OracleJVM to Java SE Compatibility
OracleJVM Version
|
Java SE Compatibility
|
|
|
Oracle 11g
|
JDK 1.5 (aka Java SE 5)
|
Oracle 10g
|
JDK 1.4
|
Oracle XE
|
No JVM Available
|
Oracle 9i
|
JDK 1.3
|
Oracle 8i
|
JDK 1.2
|
create or replace java source named FirstClass as
public class FirstClass{
public static String greeting(String name){
return "Hello " + name + "!";
}
}
SQL> @firstclass.java.sql
Java created.
Function created.
SQL> select firstclass_greeting('Bruce') from dual;
FIRSTCLASS_GREETING('BRUCE')
--------------------------------------------------------------------------------
Hello Bruce!
Next,
you publish Java method rowCount, which returns the number of rows in a
given database table.
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class RowCounter {
public static int rowCount (String tabName) throws SQLException {
Connection conn = new OracleDriver().defaultConnection();
String sql = "SELECT COUNT(*) FROM " + tabName;
int rows = 0;
try {
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql);
while (rset.next()) {rows = rset.getInt(1);}
rset.close();
stmt.close();
} catch (SQLException e) {System.err.println(e.getMessage());}
return rows;
}
}
In the following call spec, the return type is NUMBER, not INTEGER, because NUMBER subtypes (such as INTEGER, REAL, and POSITIVE) are not allowed in a call spec:
CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'RowCounter.rowCount(java.lang.String) return int';
Notice
that a Java method and its call spec can have the same name.
References:
http://qamarsyed.blogspot.in/2010/07/calling-java-from-plsql-inside-oracle.html - Calling Java from PLSQL inside Oracle
http://www.dba-oracle.com/tips_oracle_sqlj_loadjava.htm - Using loadjava
https://forums.oracle.com/forums/thread.jspa?threadID=397705&start=15&tstart=0
https://forums.oracle.com/forums/thread.jspa?messageID=1102281
http://ebookbrowse.com/oracle-database-programming-using-java-and-web-services-pdf-d108651943 -
GOOD PDF
http://200.69.103.48/comunidad/profesores/lwanumen/libros/Libro_BasesDatos_OracleJava.pdf
http://ebookbrowse.com/gdoc.php?id=108651943&url=21cf7985265b1fd20743691985b5fa7e
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5013.htm -
Using CREATE JAVA
http://www.oracle-base.com/articles/8i/jserver-java-in-the-database.php
http://docstore.mik.ua/orelly/oracle/guide8i/ch09_03.htm
http://java.sun.com/developer/technicalArticles/javase/oracle_db_triggers/
http://www.dimlar.com/paper.pdf
http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/03_pub3.htm
http://docs.oracle.com/cd/B10500_01/java.920/a96656/appover.htm -
Writing Java Applications on Oracle9i
http://docs.oracle.com/cd/B10500_01/java.920/a96656/invokeap.htm#1007092 -
Invoking Java Methods 1) Using SQLJ, RMI, JNI, Stored Procedure
http://docs.oracle.com/cd/B10501_01/java.920/a96659/02_load.htm
http://www.coderanch.com/t/304220/JDBC/databases/Create-Java-source-Oracle-jdbc
select status, count(status) from all_objects where object_type='JAVA CLASS' group by status;
select owner, object_name, status from all_objects where object_name like '% TestReturnString %' and object_type like 'JAVA%';
SELECT name, type, sequence, substr(text,0,100) FROM user_errors where name like '% TestReturnString %';
ALTER JAVA class "TestString" resolve;
select text from user_errors;
create or replace
FUNCTION pMeProcFunction (
message VARCHAR2
)
RETURN STRING AS LANGUAGE JAVA
NAME 'TestReturnString.getMessage(java.lang.String) return java.lang.String';
==Java Class
public class TestReturnString {
public static String getMessage(String strHello)
{
return strHello;
}
}
grant create any directory to scott;
create or replace directory bfile_dir as '/java/test';
CREATE JAVA CLASS USING BFILE (bfile_dir,'Bar.class');
drop java class “Method_nc”;
SQL>select count(*) from user_objects where object_type='JAVA CLASS';
SQL>select object_name from user_objects
where object_type = 'JAVA CLASS'
SQL>select count(*) from user_objects
where object_type = 'JAVA CLASS' and status = 'VALID';
SQL>select object_name from user_objects
where object_type = 'JAVA CLASS' and status != ‘VALID’;
alter java source StateXCall compile;
set serveroutput on
call dbms_java.set_output(50000);
select text from user_errors;
call dbms_java.restrict_permission or call dbms_java.revoke_permission
call dbms_java.loadjava('-v –r –f /tmp/foo.java’);
OracleRuntime.exitSession(1);
System.exit() terminates the Java VM (i.e., all threads) in the
RDBMS session with no state preservation. As illustrated by the
state across calls sample, it does not and should not terminate the
RDBMS session nor cause the client to disconnect (i.e., the
SQL*Plus session continues).
2. OracleRuntime.exitSession() terminates the Java VM as well
as the RDBMS session, causing the client to disconnect.
3. The behavior of OracleRuntime.exitCall() requires a little
more explanation, because it varies depending on the
OracleRuntime.threadTerminationPolicy(). This method
returns a Boolean, which, if true, means that any active threads
should be terminated (rather than left quiescent) at the end of a
database call.
