About 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 an 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:

The deployment process described in the following paragraph.

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 an 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 folders 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.