PL/SQL Package Types in JDBC
07 Aug 2019
Let’s suppose you have the following PL/SQL package source:
In older versions of Oracle if you tried to use the type
t_varchar2_300 in Java you got an exception.
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:
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:
Other examples can be found in the Oracle JDBC Guide