How to call JavaSource in Oracle

There are usually three methods to create a java stored procedure. 1. Manually write Java stored procedure functions using SQL script code, and manually write Java stored procedures or functions that can be called by Oracle, and create common stored procedure functions, taking the stored procedure as an example, the syntax is as follows: CREATEORREPLACEANDCOMPILEJAVASOURCENAMEDjava _

There are usually three methods to create a java stored procedure. 1. Manually write Java stored procedures/functions using SQL script code, and manually write Java stored procedures or functions that can be called by Oracle, and common stored procedures/function creation methods, take the stored procedure as an example. Syntax: create or replace and compile java source named java _

There are usually three methods to create a java stored procedure.

1. manually compile Java stored procedures/functions

Using SQL script code, you can manually write Java stored procedures or functions that can be called by Oracle. The method for creating a stored procedure or function is similar to that for creating a common stored procedure or function. The syntax of a stored procedure is as follows:

Create or replace and compile java source named java_soure_name

AS

Example: create a simple Hello World output process and call JavaSource for implementation

1. Create a Java Source

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED OracleJavaProcASpublic class OracleJavaProc{  public static void main(String[] args) {System.out.println("Hello World!");}}

2. Create a stored procedure and call Java Source

CREATE OR REPLACE PROCEDURE testoraclejavaASLANGUAGE JAVANAME 'OracleJavaProc.main(java.lang.String [])';

3. Call the Stored Procedure

Begin dbms_java.set_output (2000); --- set the size of the Java output buffer; otherwise, testoraclejava cannot be output; --- Call the Stored Procedure end;

Output result: Hello World!

Ii. Use an external class file to load and create

Import the externally compiled Java class file (*. class) to the database server through commands to create the Java stored procedure.

1. First, to import external files, you need to create a directory to store *. class files.
2. Next, import the compiled class file to the server.
3. Create a stored procedure and call the imported Java Source

Create directory and Java Source

SQL> create or replace directory test_dir as 'd:/oracle; the directory has been created. SQL> create or replace java class using bfile (test_dir, 'oraclejavaproc. class') 2/Java has been created.

TIPS: If the second step appears: ORA-29516: Aurora asserted failure: Assertion failure at eox. c: 359
Uncaught exception System error: java/lang/UnsupportedClassVersionError. Multiple Java versions are installed on the machine, and the oracle Java version is earlier than the environment variable setting version.

Solution: use $ ORACLE_HOME/jdk/bin/javac to re-compile the java file.

3. Use the loadjava command to remotely load and create

• Create a class first:

import java.sql.*;  import oracle.jdbc.*;   public class OracleJavaProc {     //Insert a record to the database.     public static void insertRecord(int id, String name, int age ,String xman_id) {        System.out.println("Inserting new records for EMP...");        try   {  Connection conn = DriverManager.getConnection("jdbc:default:connection:");            String sql = "INSERT INTO emp " +     "(ID,NAME,SALARY,XMAN_ID) " +     "VALUES(?,?,?,?)";  PreparedStatement pstmt = conn.prepareStatement(sql);    pstmt.setInt(1,id);  pstmt.setString(2,name);  pstmt.setInt(3,age);  pstmt.setString(4,xman_id);  pstmt.executeUpdate();    pstmt.close();  }   catch(SQLException e)   {  System.err.println("ERROR! Inserting record: " + e.getMessage());  }     }   //Select a reords from database   public static boolean recordQuery(int id) {   System.out.println("Querying records from EMP...");   try   {   Connection conn = DriverManager.getConnection("jdbc:default:connection:");   String sql = "SELECT * FROM emp WHERE id = ?";   PreparedStatement pstmt = conn.prepareStatement(sql);   pstmt.setInt(1,id);   ResultSet rs = pstmt.executeQuery();   while(rs.next()) {   System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+  rs.getInt(3)+"\t"+rs.getString(4));   }   return true;   }   catch (SQLException e)   {   System.err.println("ERROR! Querying record: " + e.getMessage());   return false;   }   }} 

• Use the loadjava command to load it to the server and compile it:

D: \ Oracle> loadjava-u hbi/hbi @ orcl-v-resolve OracleJavaProc. java

Arguments: '-u''' hbi/hbi @ orcl ''-v''-resolve''' OracleJavaProc. Java'

Creating: source OracleJavaProc

Loading: source OracleJavaProc

Resolving: source OracleJavaProc

Classes Loaded: 0

Resources Loaded: 0

Sources Loaded: 1

Published Interfaces: 0

Classes generated: 0

Classes skipped: 0

Synonyms Created: 0

Errors: 0

• Create stored procedures and functions:

Create a stored procedure to insert data:

create or replace procedure INSERT_RECORD(V_ID      number,                                          V_NAME    varchar2,                                          V_SALARY  number,                                          V_XMAN_ID varchar2) aslanguage java name 'OracleJavaProc.insertRecord(int,java.lang.String,int,java.lang.String)';

Create a function to query data:

create or replace function QUERY_TABLE(v_id number) return number as  language java name 'OracleJavaProc.recordQuery(int) return int';

• Test results:

SQL> set serveroutput on size 2000 SQL> call dbms_java.set_output (2000); call completed. SQL> execute add_salgrade (29, 'Charles ', 5000, '029'); the Inserting new records for EMP... PL/SQL process has been completed successfully.

• Update the Java stored procedure you have compiled

After modifying the Java source code, first dropjava and then loadjava.

D: \ Oracle> dropjava-u hbi/hbi @ orcl-v OracleJavaProc
Dropping: source OracleJavaProc

D: \ Oracle> loadjava-u hbi/hbi @ orcl-v-resolve OracleJavaProc. java
Arguments: '-u''' hbi/hbi @ orcl ''-v''-resolve''' OracleJavaProc. Java'
Creating: source OracleJavaProc
Loading: source OracleJavaProc
Resolving: source OracleJavaProc
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0