getProcedureColumns MetaData no Longer Returns Element Data Type For PL/SQL Table Types

(Doc ID 1265195.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Prior to JDBC 10.2.0.4 it was possible to look up stored procedure information using the getProcedureColumns method and determine the data type of the elements of a PL/SQL collection type used as a argument in a stored procedure.

The getProcedureColumns returned 2 rows for the column, one row indicating that the argument type is a collection, the next row indicating that what the collection element's data type is such as VARCHAR2, NUMBER, etc.

Example:

DatabaseMetaData metaData = conn.getMetaData();
ResultSet rSet = metaData.getProcedureColumns(packageName, schemaName, procName, null);

while (rSet.next()) {


String procedureCat = rSet.getString("PROCEDURE_CAT");
String procedureName = rSet.getString("PROCEDURE_NAME");
String columnName = rSet.getString("COLUMN_NAME");
String scale = rSet.getString("SCALE");
int columnType = rSet.getInt("COLUMN_TYPE");
String typeName = rSet.getString("TYPE_NAME");

for the following package:

create or replace package ptypes as
TYPE string_varchar_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE integer_number_array IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
PROCEDURE proc_int_array_in (
arg1 IN ptypes.string_varchar_array);
end ptypes;
/

The the above example JDBC drivers prior to 10.2.0.4 would provide the following output:


*******************************
procedureCat=PTYPES
procedureName=PROC_INT_ARRAY_IN
columnName=ARG1
scale=null
columnType=1
typeName=PL/SQL TABLE
*******************************
procedureCat=PTYPES
procedureName=PROC_INT_ARRAY_IN
columnName=null
scale=null
columnType=1
typeName=VARCHAR2
*******************************

Starting with JDBC 10.2.0.4 and including all JDBC 11g drivers, the only meta data returned is:

*******************************
procedureCat=PTYPES
procedureName=PROC_INT_ARRAY_IN
columnName=ARG1
scale=null
columnType=1
typeName=PL/SQL TABLE

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms