PL/SQL Package Types in JDBC

07 Aug 2019

Let’s suppose you have the following PL/SQL package source:

create or replace package my_pkg is
   type t_varchar2_300 is table of varchar2(200);
end;
/

In older versions of Oracle if you tried to use the type t_varchar2_300 in Java you got an exception.

Array myArray = connection.createOracleArray("MY_PKG.T_VARCHAR2_300",
  new String[]{"X", "Y"});

var stmt = connection.prepareStatement("select ...");
stmt.setArray(1, myArray);

stmt.executeQuery();

This would throw java.sql.SQLException: invalid name pattern: MY_PKG.T_VARCHAR2_300.

Until Oracle 12.1 types declared in PL/SQL packages were not supported via JDBC and you had to create a standalone type:

create type t_varchar2_300 is table of varchar2(200);

Luckily with 12.1 you can now use types declared in PL/SQL. Both the database and the JDBC driver need to be 12.1 or newer.

This makes it possible to avoid ugly workarounds like creating standalone types that would logically belong to a package and also enables you to group related types together in a single package.

For the sake of completeness here’s the full example code:

public class PlsqlPackageTypes {

  private static final String CONNECTION_STRING = 
      "jdbc:oracle:thin:scott/tiger@oracle-database-183:1521/orcl1803";

  private static String SQL = "select * from ("
      + " select 'X' as xyz from dual "
      + " union all select 'Y' from dual"
      + " union all select 'Z' from dual) "
      + "where xyz in (select column_value from table(?))";

  public static void main(String[] args) throws SQLException {
    OracleConnection connection =
        (OracleConnection) DriverManager.getConnection(CONNECTION_STRING);
    Array myArray = connection.createOracleArray("MY_PKG.T_VARCHAR2_300", 
        new String[]{"X", "Z"});

    var stmt = connection.prepareStatement(SQL);
    stmt.setArray(1, myArray);

    ResultSet rs = stmt.executeQuery();
    while(rs.next()) {
      String xyz = rs.getString("XYZ");
      System.out.println(xyz);
    }
  }
}

Other examples can be found in the Oracle JDBC Guide.

Unfortunately, package types still only work in SELECT statements and cannot be used in DML. This is a general limitation in Oracle and is not specific to JDBC.

blog. aregger.io

PL/SQL Package Types in JDBC