Using Oracle stored procedures under Java (detailed explanation) page 1 of 3

Time:2021-9-26

Any reprint, please respect the copyright. (by iihero on CSDN)
1、 How to create a Java stored procedure?
There are usually three ways to create Java stored procedures.
1. Use Oracle SQL statement to create:
e. G. use create or replace and compile java source named “< name >” as
Follow the Java source program. It is required that the method of the class must be public static to be used in stored procedures.

Copy codeThe code is as follows:
SQL> create or replace and compile java source named “javademo1”
2 as
3 import java.sql.*;
4 public class JavaDemo1
5 {
6 public static void main(String[] argv)
7 {
8 System.out.println(“hello, java demo1”);
9 }
10 }
11 /

Java created.

SQL> show errors java source “javademo1”
No errors.

SQL> create or replace procedure javademo1
2 as
3 language java name ‘JavaDemo1.main(java.lang.String[])’;
4 /

Procedure created.

SQL> set serveroutput on
SQL> call javademo1();

Call complete.

SQL> call dbms_java.set_output(5000);

Call complete.

SQL> call javademo1();
hello, java demo1

Call complete.

SQL> call javademo1();
hello, java demo1
Call complete.

2. Use external class files to load and create
e. G. since external files are used here, it is necessary to put the class file under a directory of Oracle server.

Copy codeThe code is as follows:
public class OracleJavaProc
{
public static void main(String[] argv)
{
System.out.println(“It’s a Java Oracle procedure.”);
}
}

SQL> grant create any directory to scott;

Authorization succeeded.

SQL> conn scott/[email protected]oracledb
Connected.
SQL> create or replace directory test_dir as ‘d:\oracle’;

Directory created.

SQL> create or replace java class using bfile(test_dir, ‘OracleJavaProc.CLASS’)
2 /

Java created.

SQL> create or replace procedure testjavaproc as language java name ‘OracleJavaProc.main(java.lang.String[])’;
2 /

Procedure created.

SQL> call testjavaproc();

Call complete.

SQL> execute testjavaproc;

PL / SQL procedure completed successfully.

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

Call complete.

SQL> execute testjavaproc;
It’s a Java Oracle procedure.