Generating JDBC code for Oracle PL/SQL package

Question

I have a large Oracle PL/SQL package with several procedures with IN and OUT type parameters. And several of these parameters are User defined Types. Some of them are Types defined in the same package. (Defined package Specification).

Is there a easy way to Generate Java code directly from Oracle Package that can be used to call these procedures via JDBC?

Answer 1

First off, up until Oracle 11g, package types cannot be discovered easily via Oracle's dictionary views, and they're rather hard to serialise / deserialise over JDBC. If you want to support package types in stored procedures from Java, a good idea might be to write bridge methods that wrap / unwrap the package types in SQL TABLE / VARRAY / OBJECT types instead. More info here:

• Discover PL/SQL package-level types using Oracle dictionary views
• Fetch Oracle table type from stored procedure using JDBC

Writing such a code generator yourself:

In order to write a source code generator for all the packages and standalone routines (procedures / functions), and all the TABLE / VARRAY / OBJECT types that might be involved, you'll have to query the following dictionary views:

Packages

SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PACKAGE'

Standalone routines

SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE')

Package routines

(Watch for overloaded routines and other caveats)

SELECT *
FROM ALL_OBJECTS o
JOIN ALL_PROCEDURES p ON o.OWNER = p.OWNER AND o.OBJECT_NAME = p.OBJECT_NAME
WHERE o.OBJECT_TYPE = 'PACKAGE'

Routine arguments

SELECT *
FROM ALL_ARGUMENTS

TABLE / VARRAY types

SELECT *
FROM ALL_COLL_TYPES
WHERE COLL_TYPE IN ('VARYING ARRAY', 'TABLE')

OBJECT types

SELECT *
FROM ALL_TYPES
WHERE TYPECODE = 'OBJECT'

OBJECT attributes

SELECT *
FROM ALL_TYPE_ATTRS

Using the above information, you can generate the necessary source code taking into account the following features:

• OUT parameters need to be registered with their appropriate java.sql.Types type to a CallableStatement.
• OBJECT types have to be registered in a specific type mapping with each bind variable
• OBJECT types should be generated as java.sql.SQLData.
• In case you're using object-oriented PL/SQL (MEMBER PROCEDURES on OBJECT types, preferably make use of the non-OO syntax passing the OBJECT value as the SELF argument).
• TABLE and VARRAY type references have to be created using ojdbc's createARRAY() API.
• Special care needs to be taken when nesting OBJECT and TABLE / VARRAY types, there are numerous caveats, especially when BLOB or CLOB values are involved.
• REF CURSOR types need to be taken into account.
• Overloaded procedures in packages are best disambiguated by using PL/SQL named parameter syntax.

Using a third-party library

You could use jOOQ, a commercial library which provides native support for all of the above. (Disclaimer: I work the company behind jOOQ) A typical example would look like this:

CREATE TYPE FILM_T AS OBJECT (
  film_id int,
  title VARCHAR(255)
);
/
CREATE TYPE FILMS_T AS TABLE OF FILM_T;
/
CREATE TYPE CUSTOMER_T AS OBJECT (
  customer_id INT,
  first_name VARCHAR(45),
  last_name VARCHAR(45)
);
/
CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
  customer CUSTOMER_T,
  films FILMS_T
);
/
CREATE PACKAGE RENTALS AS
  FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) 
  RETURN CUSTOMER_RENTAL_HISTORY_T;
END RENTALS;
/

Using jOOQ, you could then call the RENTALS.GET_CUSTOMER_RENTAL_HISTORY function as follows:

CustomerRentalHistoryTRecord result =
  Rentals.getCustomerRentalHistory(config, new CustomerTRecord(1, "John", "Wayne"))

for (FilmTRecord film : result.getFilms()) {
  System.out.println(film.getTitle());
}