Java Programming with Oracle JDBC

Calling Stored Procedures

Calling a stored procedure from your Java program requires a five-step process:

1. Formulate a callable statement.

2. Create a CallableStatement object.

3. Register any OUT parameters.

4. Set any IN parameters.

5. Execute the callable statement.

In the sections that follow, I’ll describe each of the steps listed here. The function ToNumberFun, owned by the user SCOTT, will form the basis for most of the examples.

Formulating a Callable Statement

The first step in the process of calling a stored procedure is to formulate a stored procedure call, that is, properly format a string value that will be passed to a Connection object’s prepareCall( ) method in order to create a CallableStatement object. When it comes to formulating a stored procedure call, you have two syntaxes at your disposal: the SQL92 escape syntax and the Oracle syntax. In theory, because it’s not vendor-specific, the SQL92 escape syntax gives you better portability. But let’s be realistic. Stored procedure capabilities and syntax vary wildly from one database vendor to another. If you choose to invest in stored procedures, I’d say you’re not too interested in portability. Nonetheless, let’s first take a look at the SQL92 escape syntax.

SQL92 escape syntax

When using the SQL92 escape syntax, the String object or string literal you pass to the Connection object’s prepareCall( ) method to create a CallableStatement object takes on one of the following forms:

{? = call [schema.][package.]function_name[(?,?,...)]} {call ...