Oracle Java stored procedure access to Heterogeneous Databases

The following articles mainly introduce how to use Oracle Java stored procedures to access heterogeneous databases. We all know that there may be many types of Oracle databases in actual enterprise applications, the interconnection between these heterogeneous databases often becomes the bottleneck of an application.

There are many solutions. One is to bridge the heterogeneous databases through applications, and the other is the solutions provided by database vendors, such as the Link Server of SQL Server, the Transparent Gateway technology of Oracle; the third is through the database application extension interface, for example, Oracle PL/SQL allows access to external databases through Java extension.

This article focuses on the third point, taking Oracle connection to SQL Server as an example, discusses how to use Java to write PL/SQL to access SQL Server, this is especially meaningful for Oracle applications that require access to SQL Server on non-WINDOWS platforms.

The system configuration in this article is as follows.

I. Hardware and Operating System

Dell Intel Xeon Server + Windows2000 Server

Ii. database environment

Oracle 8.1.7

Iii. Installation

To enable Oracle to support Java, you must install the Jserver component on Oracle. When installing the Oracle software, if you choose a typical installation, Jserver is installed by default. If you choose custom installation, select the Jserver component. Another method is to manually install Jserver. The method is as follows:

Go to the \ javavm \ install directory under the Oracle installation directory and run initjvm as the sys user. SQL. This script configures a Java Runtime Environment for the database and loads the Basic java class library to the database. Before executing this script, we recommend that you read the \ javavm \ readme.txt file, which includes instructions on necessary modifications to some database configurations before installation, and descriptions on how to handle script execution errors.

The script execution time is about 10 minutes. If the script fails, you can execute it again.

After the script is executed, you can use the DBA Studio tool to see the addition of Jserver.

Iv. Grant Permissions

To execute a Java stored procedure in Oracle, you need a role named javauserpriv. This role is automatically created after Jserver is installed and assigned to each Oracle account.

5. Compile the Oracle Java stored procedure

1. Compile a Java class on the local machine.

 
 

  
  
public class TEST  
  
  
{  
  
  
public static void main (String args[])   
  
  
{  
  
  
System.out.println("HELLO THIS iS A JAVA PROCEDURE");  
  
  
}  
  
  
}  
  
  
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> 
 
 

2. Use the loadjava command to upload the written java class files to the database.

Example: C :\> loadjava-u test/oracle @ mytest-o-v-r d: \ TEST. java

Of course, in addition to uploading the. java file, you can also upload the. class/. jar/. properties file. the uploaded file will exist in the database as an Object.

If the file is changed, you can upload the file again. The previous version will be overwritten.

3. Generate a declaration Interface

The uploaded java class needs to be released into an interface that can be called. These interfaces can be Store Procesure, Trrigger, Function, and so on.

 
 

  
  
SQL>create or replace procedure test_java   
  
  
as language java   
  
  
name 'TEST.main(java.lang.String[])';  
  
  
/ 
 
 

4. Test

 
 

  
  
SQL> set serveroutput on size 5000  
  
  
SQL> call dbms_java.set_output(5000); 
 
 

The call is complete.

 
 

  
  
SQL> execute test_java;  
  
  
HELLO THIS iS A JAVA PROCEDURE 
 
 

The PL/SQL process is successfully completed.

 
 

  
  
SQL> call test_java();  
  
  
HELLO THIS iS A JAVA PROCEDURE 
 
 

The call is complete.

5. The above is a simple java class. to access a non-Oracle Database in Oracle, only

You can access non-Oracle databases in the java class.

 
 

  
  
public static String getStringFomSql(){  
  
  
String strResult ="";  
  
  
Connection conn = null;  
  
  
Statement stmt = null;  
  
  
ResultSet rs = null;  
  
  
try {  
  
  
Class.forName("com.inet.tds.TdsDriver").newInstance();   
  
  
String url="jdbc:inetdae7:10.24.09.192:1433?database=pubs";   
 
 

Pubs for your database

 
 

  
  
String user="sa";   
  
  
String password="123";   
  
  
conn= DriverManager.getConnection(url,user,password);   
  
  
String sql="select top 1 dtype,dname from test";   
  
  
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);   
  
  
rs=stmt.executeQuery(sql);   
  
  
if( rs.next()){  
  
  
strResult = "The result from sql server is:dtype->" + rs.getString("dtype") + " dname->" + rs.getString("dname");  
  
  
}  
  
  
} catch (InstantiationException e) {  
  
  
TODO Auto-generated catch block  
  
  
e.printStackTrace();  
  
  
strResult = "operator fail in InstantiationException";  
  
  
} catch (IllegalAccessException e) {  
  
  
TODO Auto-generated catch block  
  
  
e.printStackTrace();  
  
  
strResult = "operator fail in IllegalAccessException";  
  
  
} catch (ClassNotFoundException e) {  
  
  
TODO Auto-generated catch block  
  
  
e.printStackTrace();  
  
  
strResult = "operator fail in ClassNotFoundException";  
  
  
} catch (SQLException e) {  
  
  
TODO Auto-generated catch block  
  
  
e.printStackTrace();  
  
  
strResult = "operator fail in SQLException";  
  
  
}  
  
  
finally{  
  
  
try {  
  
  
rs.close();  
  
  
conn.close();  
  
  
} catch (SQLException e1) {  
  
  
e1.printStackTrace();  
  
  
}  
  
  
}  
  
  
return strResult;  
  
  
}  
 
 

It is worth noting that accessing SQL Server through java storage, if a result set is returned, there is no corresponding data type to be converted in Oracle versions below 9i, A work und is to either return a string or store the returned result set in the Oracle table. This is done to make it easier for programs that cannot directly access SQL Server on non-Windows platforms.

6. External programs call Oracle java stored procedures

The method for calling a java stored procedure is the same as that for calling a general Oracle Java stored procedure.