Use Java with a database. You can embed SQL in Java programs using SQLJ or JDBC. You can access database objects and packages from Java programs. Use Java stored procedures, which are Java methods in the database.

There are three aspects to using Java in the database:

• Using SQLJ or JDBC, both of which can be used to embed SQL in Java programs.

• Accessing database objects and PL/SQL packages from Java programs.

• Using Java stored procedures, which are Java methods that reside and run inside the database.

Choose whether to use SQLJ or JDBC to embed SQL in Java programs.

JDeveloper supports two mechanisms for embedding SQL in Java programs:

• SQLJ: If you know the PL/SQL tables and columns involved at compile time (static application), you can use SQLJ. SQLJ is an industry standard for defining precompiled SQL code in Java programs.

  SQLJ allows you to code at a higher level than JDBC, by embedding SQL statements directly in your Java code. The SQLJ precompiler that is integrated into JDeveloper translates the SQL into Java plus JDBC code for you. SQLJ with JDeveloper lets you write and debug applications much faster than you can using just JDBC.

• JDBC: If you require fine-grained control over database access, or if you are developing an application that requires precise information about database (or instance) metadata, you can code your application entirely in Java using the JDBC API.

You can mix JDBC calls with SQLJ statements in your program. One way to do this is through connection context sharing.

MyConnectionContext thisCtx = new MyConnectionContext (<myPath>, <myUID>, <myPasswd>, autocommit)

-option@context=value 

Java stored procedures are Java methods that reside and run in a database. Stored procedures can help improve the performance of database applications because they are efficient: they are stored in the RDBMS in executable form, and run in the RDBMS (rather than the client) memory space.

Use JDeveloper to write methods in Java for new stored procedures and deploy them to Oracle Database. When you deploy a Java class to Oracle, you can select the methods that you want to publish to PL/SQL for use as stored procedures. Methods can be deployed together in a package or separately. See Developing Java Stored Procedures.

A stored procedure is a program that resides and runs in a database. Application developers can use stored procedures to help improve the performance of a database application. Procedure calls are quick and efficient because a stored procedure is compiled once and stored in an executable form. Because a stored procedure runs in the RDBMS memory space, complex functions run faster than a routine run by a client. You can also use stored procedures to group PL/SQL statements so that they are executed in a single call. This reduces network traffic and improves round-trip response times. By designing applications around a common set of stored procedures, you can avoid redundant coding and increase your productivity.

A Java stored procedure contains Java public static methods that are published to PL/SQL and stored in Oracle Database for general use. To publish Java methods, you write call specifications, that map Java method names, parameter types, and return types to their PL/SQL counterparts. This allows a Java stored procedure to be executed from an application as if it were a PL/SQL stored procedure. When called by client applications, a Java stored procedure can accept arguments, reference Java classes, and return Java result values.

Any Java class can be deployed to Oracle Database and the conforming methods of the class can be published to PL/SQL as stored procedures. These Java stored procedures can then be executed from an application as if they were PL/SQL stored procedures. Java stored procedures can be an entry point for your application into other (Java and non-Java) procedures deployed to Oracle Database.

Deploying and publishing Java stored procedures to Oracle Database generates call specifications that act as PL/SQL wrappers for each of the methods selected for publishing. The PL/SQL wrappers allow the stored procedures to be accessible from SQL*Plus, JDBC, or any other Oracle application environment.

The call specifications (the PL/SQL wrappers) for Java stored procedure packages and methods deployed to a database schema can be inspected through Oracle Database connection. Only published Java stored procedures appear as PL/SQL blocks, and only public static methods in Java classes can be published to PL/SQL when deployed. Java classes can be deployed without being published, in which case they are not seen in the PL/SQL nodes.

Depending on how Java stored procedures were published, they appear in one of the following nodes under a schema:

• Packages include call specs for Java stored procedures deployed in packages.

• Functions include call specs for Java stored procedures deployed as functions (that return a value).

• Procedures include call specs for Java stored procedures deployed as procedures (that do not return a value).

To view a Java stored procedure's call specification, find its node in the schema's hierarchy, and double-click it.

You create Java stored procedures by first developing business application logic in a Java class file. Declare methods that are to become stored procedures as public static.

Use the editor in JDeveloper to add and edit business logic in the Java class. During deployment to Oracle Database, all public static methods included in the class file are available to be published to PL/SQL as stored procedures. You can choose which public static methods in the class to be published to PL/SQL.

There are different JDeveloper Java stored procedure creation scenarios:

• Use an existing Java class and make any necessary edits to the public static methods in the class that will be deployed to Oracle Database. The existing class could include public static methods used for validation or database triggers. The methods in the class might also be in local use by several applications. These methods could be deployed to Oracle Database and used by multiple applications from the database. The deployed methods could also supplement existing PL/SQL stored procedures and functions.

• Create a new class with methods designed for publishing as stored procedures. Use the editor in JDeveloper to create the public static methods that will be exposed as stored procedures. Write in industry-standard Java and use the original Java class for other application deployments. In Oracle Database, this programming could supplement existing PL/SQL stored procedures.

For example, assume the following Java package Welcome was created with the public class Greeting and the public static method Hello().

package Welcome;
 public class Greeting {
 public static String Hello() {
 return "Hello World!";
 }

When this package is deployed to Oracle Database and the Hello() method is published there, the call spec for the package as viewed in the source editor looks like this:

PACKAGE WELCOME AS
FUNCTION HELLO RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Welcome.Greeting.Hello() return java.lang.String'
END WELCOME;

You create a deployment profile for Java stored procedures, then deploy the classes and, optionally, any public static methods in JDeveloper using the settings in the profile.

Deploying to the database uses the information provided in the Deployment Profile wizard and two Oracle Database utilities:

• loadjava loads the Java class containing the stored procedures to Oracle Database.

• publish generates the PL/SQL call spec wrappers for the loaded public static methods. Publishing enables the Java methods to be called as PL/SQL functions or procedures.

To deploy Java stored procedures in JDeveloper:

1. If necessary, create a database connection in JDeveloper.

2. If necessary, create a deployment profile for Loadjava and Java stored procedures.

3. Deploy the objects.

The Loadjava and Java stored procedure deployment profile is very similar to the simple archive profile, except that the selected contents of the profile will be uploaded into Oracle Database via the command-line tool loadjava or in the case of Java stored procedures, they are stored in Oracle Database for general use.

To create a deployment profile for Loadjava or Java stored procedures in JDeveloper:

1. In the Applications window, select the project in which you want to create the deployment profile.

2. Choose File > New to open the New Gallery.

3. In the Categories tree, expand Database Tier and select Database Files. In the Items list, double-click Loadjava and Java Stored Procedures.

   If the category or item is not found, make sure the correct project is selected, and select All Technologies in the Filter By dropdown list.

4. In the Create Deployment Profile dialog, specify a location for the deployment profile or accept the defaults. The deployment profile is named with a .dbexport filename extension.

5. Click Save to display the Loadjava and Java Stored Procedures Deployment Profile Settings dialog. Configure the settings for each page as appropriate, and click OK when you are done.

   The newly created storedProc.dbexport deployment profile appears in the Applications window below the specified project.

6. Select and right-click storedProc.dbexport in the Applications window. Choose from the available context menu options.

7. (Optional) If you choose Add Stored Procedure Package, choose the methods you want to load as a stored procedure. For each Java method callable from SQL a call spec is required, which exposes the method's top-level entry point to the database. Typically, only a few call specs are needed. JDeveloper generates the call spec for you from this page.

8. Select a method and click Settings.

   If a method on the list is dimmed, this indicates a problem with deploying this method as a Java stored procedure. Click Why not? for an explanation.

9. Configure the Method Settings as required. These settings allow you to customize the parts of the CREATE PROCEDURE and CREATE FUNCTION SQL statements that are used to customize the stored procedure.

10. (Optional) Right-click and choose Preview SQL Statements to display a dialog that shows the SQL statements used to load the specifically selected item in the Applications window. In the case of top-level procedures or functions and packages, you will see complete SQL statements. In the case of packaged procedures or functions, you will only see fragments of SQL statements which represent the portion of the CREATE PACKAGE BODY statement corresponding to the packaged procedure or function.

    • (Optional) If you choose Add PL/SQL Package, enter the name of a PL/SQL package that you want to start building.

    • (Optional) Right-click and choose Preview SQL Statements to display a dialog that shows the SQL statements used to load the specifically selected item in the Applications window. In the case of top-level procedures or functions and packages, you will see complete SQL statements. In the case of packaged procedures or functions, you will only see fragments of SQL statements which represent the portion of the CREATE PACKAGE BODY statement corresponding to the packaged procedure or function.

    • To deploy the profile, see Deploying Loadjava and Java Stored Procedures Profile.

If necessary:

• Create a database connection in JDeveloper.

• Create a deployment profile for Loadjava and Java stored procedures.

To deploy Loadjava and Java stored procedures in JDeveloper:

1. Right-click storedProc.deploy which appears in the Applications window below the specified project.

2. From the context menu, choose Export to and select one of the already existing database connections; the Java application's source files are uploaded directly into the selected database.

   Or, choose New Connection to display the Create Database Connection Wizard.

3. (Optional) If you want to edit the deployment profile, right-click storedProc.deploy in the Applications window below the specified project and choose Settings.

The SQL CALL statement lets you call Java stored procedures.

To invoke a Java Stored Procedure using SQL:

1. In SQL*Plus, execute the CALL statement interactively to invoke a Java stored procedure, using the syntax:

   CALL [schema_name.][{package_name | object_type_name}][@dblink_name]
    { procedure_name ([param[, param]...])
    | function_name ([param[, param]...]) INTO :host_variable};
   where param represents this syntax: 
   {literal | :host_variable}
   

   Host variables, that is variables declared in a host environment, must be prefixed with a colon. The following examples show that a host variable cannot appear twice in the same CALL statement, and that a subprogram with no parameters must be called with an empty parameter list:

   CALL swap(:x, :x); -- illegal, duplicate host variables
    
   CALL balance() INTO :current_balance; -- () required
   

To invoke a Java stored procedure using JDBC:

1. Java stored procedures invoked from JDBC must be encapsulated in CallableStatement objects.

   Create a callable statement object:

   • Declare a callable statement object. For example:

     private CallableStatement checkIn;
     

   • Initialize the callable statement object by calling prepareCall on the connection with a SQL CALL statement for the stored procedure. For example:

     checkIn = connection.prepareCall(quot;{call NPL.CHECKIN(?, ?, ?)}");

   Note:

   The number of parameters in the stored procedure is represented by the number of place-holders in the SQL call.

2. Register the callable statement object's output parameters. Call registerOutParameter for each output parameter, identifying it by position, and declaring its type. For example, if the second parameter is an SQL INTEGER (which maps to a Java int), and the third is a SQL VARCHAR (which maps to a Java String), then:

   newCustomer.registerOutParameter(2, Types.INTEGER); 
   newCustomer.registerOutParameter(3, Types.VARCHAR); 
   

3. Execute the callable statement object:

   • Provide the callable statement object's input parameters by calling a set method, identifying the parameter by position, and assigning it a value. For example, if the first parameter is an int input parameter:

     checkIn.setInt(1, bookID); 
     

   • Execute the callable statement object. For example:

     checkIn.execute(); 
     

   • Extract the callable statement object's output parameters. Call a get method for each output parameter, identifying the parameter by position. The get methods return values of corresponding Java types. For example:

     int daysLate = checkIn.getInt(2); 
     String title = checkIn.getString(3); 
     

To invoke a Java stored procedure using SQLJ:

1. Declare and initialize input and in-out variables. For example, if the first parameter is an int input parameter:

   int bookID = scanID();
   

2. Declare output variables. For example:

   int daysLate; String title; 
   

3. Invoke the stored procedure in a SQLJ statement. In the statement identify the parameters by name, and designate them as :in, :out, or :inout. For example:

   #sql { call NPL.CHECKIN (:in bookID, :out daysLate, :out title)}
   

   Return values will be assigned to output and input variables.

To Invoke a Java Stored Procedure using PL/SQL

1. Use a CALL statement in the trigger body clause of a PL/SQL statement to invoke a stored procedure, and pass arguments to it.

   The CALL statement's arguments can be:

   • Literal values.

   • SQL expressions, but not bind variables.

   • Column references, qualified by correlation names.

   Correlation names are prefixes to column references. Use these names to qualify whether the reference applies to the existing column value of the row being processed by the trigger or the value being written by the triggering event:

   • OLD refers to the value of the column prior to the triggering operation.

   • NEW refers to the value being assigned to the column by the triggering operation. It is possible for the trigger body to redefine this value before the triggering operation occurs.

   An example of a complete trigger definition:

   CREATE TRIGGER check_salary
   BEFORE UPDATE OF salary ON employee
   CALL salaryCheck(:new.job, :old.salary, :new.salary, :old.employee
   

   CREATE TRIGGER check_salary

   BEFORE UPDATE OF salary ON employee

   CALL salaryCheck(:new.job, :old.salary, :new.salary, :old.employeeID)

For stored procedures deployed in packages, access the stored procedure by the package name and/or the stored procedure name set during deployment. The package name may be the default name taken from the project or another name entered during deployment. The stored procedure name may be the default name taken from the method name or a name chosen for the stored procedure during deployment. Stored Procedures may also be deployed without packages.

For example, assume a public static method hello()is in the Java package Welcome and the public class Greeting. Further assume it is deployed in a package Openings.

You could execute a PL/SQL query to the deployed stored procedure that executes the public static method deployed there and returns the result. To invoke SQL*Plus from within JDeveloper, right-click a connection or select it from the Tools menu.

With a working connection to the database, your SQL*Plus client could execute the following:

package Welcome;
 public class Greeting {
 public static String Hello() {
 return "Hello World!";
 }
 }

You could execute a PL/SQL query to the deployed stored procedure that executes the public static method deployed there and returns the result. To invoke SQL*Plus from within JDeveloper, right-click a connection or select it from the Tools menu.

With a working connection to the database, your SQL*Plus client could execute the following:

select Hello() from dual;Hello()

Executing the code displays:

Hello World!

For stored procedures deployed separately (not in packages), access the stored procedure by the stored procedure name set during deployment. The stored procedure name may be the default name taken from the method name or a name chosen for the stored procedure during deployment.

For example, for a public static method hello() that was deployed as hello from a class greeting and package welcome, you could execute a PL/SQL query to the deployed stored procedure that returns the result.

Assume the above hello() method as the example method, but this time assume it was deployed without a package.

With a working connection to the database, your SQL*Plus client could execute the following:

select Openings.Hello() from dual;
 
Openings.Hello()

The executed code displays:

Hello World!