6.5 Publishing Class Methods
The process of creating PL/SQL wrappers is known as publishing the Java methods in the database. If your Java method does not return a value, i.e., is a procedure, then you must use the CREATE PROCEDURE statement to create a procedure wrapper. If your Java method returns a value, i.e., is a function, then you must use the CREATE FUNCTION statement. The syntax for the CREATE PROCEDURE/FUNCTION statement to create a wrapper is as follows.
CREATE [OR REPLACE] { PROCEDURE procedure_name [(param[, param ...])] | FUNCTION function_name [(param[, param ...])] RETURN plsql_type } [AUTHID {DEFINER | CURRENT_USER}] [PARALLEL_ENABLE] [DETERMINISTIC] {IS | AS} LANGUAGE JAVA NAME 'java_method ( java_type[, java_type] ...) [return java_type]'; / param := parameter_name [IN | OUT | IN OUT] plsql_type The syntax elements and options are as follows:
- procedure_name/function_name
-
Specifies the name you want to give to the PL/SQL wrapper procedure or wrapper function that you are creating.
- plsql_type
-
Specifies the PL/SQL type of a wrapper function's return value.
- AUTHID {DEFINER | CURRENT_USER}
-
Determines whether the stored subprogram runs with the database privileges of the user who created it (DEFINER) or of the user who invoked it (CURRENT_USER). The default is CURRENT_USER.
- PARALLEL_ENABLE
-
Indicates that the function can be used in slave sessions in parallel DML evaluations. Parallel DML allows DML operations to be spread across multiple processes running in parallel to improve performance.
- DETERMINISTIC
-
Tells the database optimizer to avoid redundant function calls, assuming that the function is deterministic, that is, if the return result is dependent only on the input values. If the DETERMINISTIC hint is used and the function has already been called once before with the same input values, then the previous result returned by the function is reused.
- LANGUAGE JAVA clause
-
Indicates that the procedure or function invokes a Java method.
- NAME clause
-
Identifies the Java method being wrapped, and also its parameters. The NAME clause contains the following replaceable items:
- java_method
-
Must uniquely identify the Java method being wrapped. The method must be specified using the dot notation to identify both the class and the method.
- java_type
-
Specifies the type of the Java method parameter, which must be compatible with the corresponding plsql_type database type. For information on type compatibility, see Appendix A.
- parameter_name
-
Specifies the name of a PL/SQL parameter.
If you want to write a Java method that modifies the value of a parameter passed to it from the wrapper, then the parameter to the wrapper procedure or function must be declared as OUT or IN OUT. In addition, the Java parameter must be an array containing one element. For example, an OUT parameter of database type NUMBER can be mapped to a Java parameter of type int []. If you name the Java parameter num_array, then the OUT parameter is associated with element num_array[0]. If you have more than one OUT parameter, then you need two single-element arrays.
6.5.1 Creating and Calling PL/SQL Wrappers
In this section, I show how to create wrappers for the methods in the ServerExample1 class shown earlier in this chapter. I also show how to call these wrappers using SQL*Plus and how to bundle the wrappers together into a PL/SQL package.
There are four methods in the ServerExample1 class:
-
testMessage( )
-
addCustomer( )
-
displayCustomer( )
-
countCustomers( )
All of these methods are public and static. When you create a wrapper, remember that you must include the following items in your wrapper declaration:
-
The fully qualified name, including the package and class, of the Java method that you are wrapping.
-
Any parameter names and types for the method being wrapped.
In the following sections, you will see how to wrap each of the methods in the ServerExample1 class. You'll also find a detailed description of each method's purpose and functionality.
6.5.1.1 The testMessage( ) method
The testMessage( ) method shows the various ways of sending output from a server-based program to the database trace file and the buffers. The following example uses the CREATE PROCEDURE statement to create a PL/SQL wrapper procedure named test_message( ) for the testMessage( ) method in the ServerExample1 class. Notice the use of the LANGUAGE JAVA clause:
CREATE OR REPLACE PROCEDURE test_message AS LANGUAGE JAVA NAME 'ServerExample1.testMessage( )'; /
The examples in this section are done in SQL*Plus. To view the contents of the output buffers populated by your programs, you must first issue the following SQL*Plus command:
SET SERVEROUTPUT ON;
To call the PL/SQL test_message( ) method using SQL*Plus, enter the command:
CALL test_message( );
The test_message( ) wrapper procedure then invokes the testMessage( ) Java method in the ServerExample1 class. The testMessage( ) method begins by calling the System.out.println( ) method:
System.out.println("This line of output from ServerExample1.sqlj " + "appears in the database trace file"); This command writes the following line of text to the database trace file, as that is where any output will go by default:
This line of output from ServerExample1.sqlj appears in the database trace file
Next, testMessage( ) calls the dbms_output.enable( ) method to create an output buffer. This is followed by a call to dbms_output.put_line( ), the output from which is stored in the output buffer:
// use the PL/SQL procedure dbms_output.enable( ) to // set the output buffer to 1000 bytes #sql { CALL dbms_output.enable(1000) }; // use the PL/SQL procedure dbms_output.put_line( ) // to write the output to the buffer, which is eventually // displayed on the client computer screen #sql { CALL dbms_output.put_line( 'Displayed on the screen from dbms_output.put_line( )' ) }; Next, a call to dbms_java.set_output( ) creates another output buffer. This is followed by a call to System.out.println( ), and the output from that call is stored in the new buffer that you've created:
// use the PL/SQL procedure dbms_java.set_output( ) to // set the output buffer to 1000 bytes, this causes // output from System.out.println( ) calls to be buffered and // eventually displayed on the client computer screen #sql { CALL dbms_java.set_output(1000) }; System.out.println("Displayed on the screen " + "from System.out.println( )"); Once the call to testMessage( ) is complete, SQL*Plus will display the contents of the buffers, and you'll see the following output on the screen:
Displayed on the screen from dbms_output.put_line( ) Displayed on the screen from System.out.println( )
6.5.1.2 The addCustomer( ) method
The addCustomer( ) method adds a row to the customers table and writes a line to a Java buffer containing the new ID for the row. The following example uses the CREATE PROCEDURE statement to create a wrapper procedure named add_customer( ) for the addCustomer( ) method. Note the use of two java.lang.String variables to hold the two VARCHAR2 database types for the first_name and last_name parameters:
CREATE OR REPLACE PROCEDURE add_customer( first_name VARCHAR2, last_name VARCHAR2 ) AS LANGUAGE JAVA NAME 'ServerExample1.addCustomer(java.lang.String, java.lang.String)'; /
Remember, the database type used in the wrapper must be compatible with the Java type or class used in the Java method. See Appendix A for details about type compatibility.
The addCustomer( ) method adds a row to the customers table, with the first_name and last_name columns set to the values passed through the parameters, and writes the ID for the new row to a Java buffer. The following example shows how to call the add_customer( ) wrapper procedure, which then invokes the addCustomer( ) method:
SQL> SET SERVEROUTPUT ON; SQL> CALL add_customer('Jason', 'Price'); Customer has been assigned an id of 6 6.5.1.3 The displayCustomer( ) method
The displayCustomer( ) method retrieves a row from the customers table and writes several lines to a Java buffer containing the customer ID, first name, and last name. The following example uses the CREATE PROCEDURE statement to create a wrapper procedure named display_customer( ) for the displayCustomer( ) method in the ServerExample1 class. Notice the use of the Java int variable to hold the NUMBER database type for the id parameter:
CREATE OR REPLACE PROCEDURE display_customer( id NUMBER ) AS LANGUAGE JAVA NAME 'ServerExample1.displayCustomer(int)'; /
The displayCustomer( ) method retrieves the first_name and last_name columns for the row with the specified ID (passed as a parameter) and writes lines containing the id, first_name, and last_name values as output to a Java buffer. The following example shows display_customer( ) being called to display the details for customer #6:
SQL> SET SERVEROUTPUT ON SQL> CALL display_customer(6); Customer 6 has the following details: First name: Jason Last name: Price
6.5.1.4 The countCustomers( ) method
The countCustomers( ) method counts the total number of rows in the customers table and returns the total. The following CREATE FUNCTION statement creates a wrapper function named count_customers( ) for the countCustomers( ) method. The method returns a Java int variable, which is converted by the wrapper to a NUMBER database type.
CREATE OR REPLACE FUNCTION count_customers RETURN NUMBER AS LANGUAGE JAVA NAME 'ServerExample1.countCustomers( ) return int'; /
Since the wrapper function count_customers( ) is a PL/SQL function, you can call the function using a SELECT statement. For example:
SQL> SELECT count_customers( ) FROM dual; COUNT_CUSTOMERS( ) ----------------- 6
6.5.2 Creating and Calling PL/SQL Package Wrappers
If a Java class contains many procedures and functions, it is generally a good idea to encapsulate the wrappers in a PL/SQL package. By doing this, you can easily keep track of which wrapper calls which method in a class.
In Chapter 2, you learned that a package has two components: a specification and a body. The package specification acts as a header for the package and defines the signatures for the public procedures and functions. The package body contains the actual code that implements the package header. When you create a package of wrappers, you don't really have any PL/SQL code to place in the package body. However, you may still use a separate specification and a body. The difference between the declarations in the package specification and those in the body is that the declarations in the body contain the LANGUAGE JAVA and NAME clauses used when wrapping Java methods. These clauses may be omitted from the package specification.
The following statement creates a package specification for a package named server_example1 that contains the wrappers for the four methods in the ServerExample1 class:
CREATE OR REPLACE PACKAGE server_example1 AS PROCEDURE test_message; PROCEDURE add_customer ( first_name VARCHAR2, last_name VARCHAR2 ); PROCEDURE display_customer ( id NUMBER ); FUNCTION count_customers RETURN NUMBER; END server_example1; /
Next, the following statement creates the package body for the server_example1 package. Notice that the body contains the names of the Java methods in the ServerExample1 class:
CREATE OR REPLACE PACKAGE BODY server_example1 AS PROCEDURE test_message AS LANGUAGE JAVA NAME 'ServerExample1.testMessage( )'; PROCEDURE add_customer ( first_name VARCHAR2, last_name VARCHAR2 ) AS LANGUAGE JAVA NAME 'ServerExample1.addCustomer(java.lang.String, java.lang.String)'; PROCEDURE display_customer ( id NUMBER ) AS LANGUAGE JAVA NAME 'ServerExample1.displayCustomer(int)'; FUNCTION count_customers RETURN NUMBER AS LANGUAGE JAVA NAME 'ServerExample1.countCustomers( ) return int'; END server_example1; /
In this case, since the package does not contain any private functions, procedures, or cursors, you can dispense with the package body entirely and put the wrappers in the package specification. For example:
CREATE OR REPLACE PACKAGE server_example1 AS PROCEDURE test_message AS LANGUAGE JAVA NAME 'ServerExample1.testMessage( )'; PROCEDURE add_customer ( first_name VARCHAR2, last_name VARCHAR2 ) AS LANGUAGE JAVA NAME 'ServerExample1.addCustomer(java.lang.String, java.lang.String)'; PROCEDURE display_customer ( id NUMBER ) AS LANGUAGE JAVA NAME 'ServerExample1.displayCustomer(int)'; FUNCTION count_customers RETURN NUMBER AS LANGUAGE JAVA NAME 'ServerExample1.countCustomers( ) return int'; END server_example1; /
To call the methods in this package, you use the dot notation to specify the package name and method name. The following examples show the add_customer( ) and display_customer( ) procedures and the count_customers( ) function being called from SQL*Plus:
SQL> SET SERVEROUTPUT ON SQL> CALL server_example1.add_customer('Steve', 'Button'); Customer has been assigned an id of 7 SQL> CALL server_example1.display_customer(7); Customer 7 has the following details: First name: Steve Last name: Button SQL> SELECT server_example1.count_customers( ) FROM dual; SERVER_EXAMPLE1.COUNT_CUSTOMERS( ) --------------------------------- 7 6.5.3 Calling Wrappers from PL/SQL Procedures
So far, you have seen how to call wrappers only from SQL*Plus. However, you can also call wrappers anywhere that a call to a PL/SQL procedure or function is valid. The following example creates a PL/SQL procedure named add_display_count( ) that adds a new customer, displays a new customer, and counts the number of customers, all by calling the wrappers in the server_example1 package created earlier:
CREATE OR REPLACE PROCEDURE add_display_count AS number_of_customers INT; BEGIN server_example1.add_customer('Joe', 'Smith'); server_example1.display_customer(8); number_of_customers := server_example1.count_customers; dbms_output.put_line('There are ' || number_of_customers || ' customers'); END add_display_count; / The procedure add_display_count( ) calls server_example1.add_customer( ) to add a new row to the customers table. It then calls server_example1.display_customer( ) to display the details of the customer just added. Finally, it calls server_example1.count_customers( ) to obtain the number of rows in the customers table. The following example shows the PL/SQL procedure add_display_count( ) being called from SQL*Plus:
SQL> SET SERVEROUTPUT ON SQL> CALL add_display_count( ); Customer has been assigned an id of 8 Customer 8 has the following details: First name: Joe Last name: Smith There are 8 customers
