Methods for invoking Java classes to develop stored procedures and functions in an Oracle database

Invoking Java classes in Oracle database methods of developing stored procedures and functions time: December 24, 2014 viewed: 5,538 Times

Oracle database development is very flexible, not only support the most basic SQL, but also provides unique PL/SQL, in addition, can also be used in the most popular programming language Java for development. Oracle technology is a fascinating learning process as Oracle becomes more and more knowledgeable about Oracle. Before, I summed up in the blog quite a lot about SQL, PL/SQL, but the Oracle database in the Java class call is not summed up, but also because it was not very good, summed up a moment, special to share.

1. Basic idea of calling Java class in Oracle

To invoke Java classes in Oracle to implement stored procedures, functions, I think the approximate idea is this:

(1) First write Java code, you can use your favorite code editor, edit it, you can compile the code into a. class file, can also be hit into a jar package, or can not operate anything, is a Java source code file is placed there.

(2) To find ways to import the already written Java code into the Oracle database, there are two main ways to import, one is to use the Loadjava command, and the other is directly in the writing of PL/SQL code when writing Java code, these two methods I will summarize in detail below.

(3) Write stored procedures, functions to encapsulate Java code, to implement the post-facing Java function calls.

(4) The preparation has been done, the final call, and the way the call is exactly the same as PL/SQL, nothing different.

2, Loadjava command Introduction

Before you cut to the chase, let's briefly introduce the following Loadjava commands and their usage, the Loadjava command has a lot of arguments, and there's no need to know all about it, Loadjava's calling rules are shown in 1:

Figure 1:loadjava Call Rules

The Loadjava command is not called in the Sql*plus, but in the cmd window, the meaning of each parameter of Loadjava can be used: loadjava-help command for detailed help. The main parameters used in this article are-u (for input user name and password),-V (for output detailed feedback information),-resolve (for Java files not compiled, can be compiled with resolve),-f (This command I do not use in the example, it means force, Indicates that the Java class is forced to be imported again, regardless of whether it was previously imported. Loadjava can realize the import of class file, jar file, resource file, property file, and when importing class file, you can use the following command:

Loadjava–u Username/userpassword–v Filename.class

When importing Java files, you can use the following command:

Loadjava–u Username/userpassword–v-resolve Filename.java

When importing a jar file, you can use the following command:

Loadjava–u Username/userpassword–v-resolve Filename.jar

3. Example of Oracle calling Java full step

Example: A simple requirement, with Java code to achieve the area of the Circle, Pi Pi is 3.14, the input parameter is the radius of the circle R, the output circle of the area S, it is required in Oracle with PL/SQL code to call the Java class to achieve the area of the circle to find the function.

Step 1: Write Java code, PL/SQL developer itself although support to write Java code, but is not professional, Java tools provided is not particularly good, I like to use MyEclipse developed later copied over, Here is the code I developed in MyEclipse.

Circle Tool class, calculate the area of the circle public class Circletools {//define constant PI public static final double pi = 3.14;  Calculate area public static double Calcsquare (double R) {return PI * R * r; }}

Note that when writing Java code, in order to be able to be called directly in Oracle, it is necessary to add public and static before the method that needs to be called.

Step 2: Write the Java code, the following is to import Java into the Oracle database, the import method mentioned above can be used Loadjava or write directly. If you want to import with Loadjava, first save the above code file as Circletools.java, then enter the directory in the cmd command line, and then execute the following command:

Loadjava–u Username/userpassword–v-resolve Circletools.java

Executing the above command is OK, here is another way to write directly in PL/SQL, as follows:

CREATE OR REPLACE and COMPILE JAVA SOURCE NAMED circletools as//Round tool class, calculate the area of the circle public class Circletools {//define constant PI public  Static final Double PI = 3.14;  Calculate area public static double Calcsquare (double R) {return PI * R * r; }}

My Oracle development tool is PL/SQL Developer, so I executed the above code in PL/SQL Developer and then queried the User_objects table to see that the Circletools class has been imported into the Oracle database , as shown in 2:

Figure 2:user_objects Query to Circletools class in table

Step 3: Write a stored procedure (procedure) or function to encapsulate, in general, if a Java method does not return a value, then encapsulated into a stored procedure, if there is a return value, it is encapsulated into a function, Here I'll just give you an example of encapsulating a function:

CREATE OR REPLACE FUNCTION calc_square (r in number) RETURN number as LANGUAGE JAVA NAME ' Circletools.calcsquare (double) r Eturn Double ';

Step 4: The above steps have basically been achieved all, the last step of testing, I test the calc_square this function, the results as shown in 3:

Figure 3: Test Calc_square function

Step 5: If you do not want to use the Java class, you can use the Dropjava command to delete the system Java, which is similar to Loadjava, the following:

Figure 4:dropjava Command

For example:

Dropjava-user Username/[email protected]-V javasourcename

Note that the Java source name is deleted above, not the class name, that is, the object's object_type is Java source, not Java class. This place I am not sure, but I directly delete the class name is an error, the first to write it, the later found wrong, I will correct.

The idea of calling Java source in Oracle is probably the above, and the idea of running Java code in Oracle is still quite exciting, and the functionality has expanded a lot.

Methods for invoking Java classes to develop stored procedures and functions in an Oracle database