Tuesday, March 27, 2007

DatabaseMetadata getProcedureColumns and oracle

Considerations for getProcedures() and getProcedureColumns() Methods






According to JDBC versions 1.1 and 1.2, the methods getProcedures() and getProcedureColumns() treat the catalog, schemaPattern, columnNamePattern, and procedureNamePattern parameters in the same way. In the Oracle definition of these methods, the parameters are treated differently:



  • catalog: Oracle does not have multiple catalogs, but it does have packages. Consequently, the catalog parameter is treated as the package name. This applies both on input (the catalog parameter) and output (the catalog column in the returned ResultSet). On input, the construct " " (the empty string) retrieves procedures and arguments without a package, that is, standalone objects. A null
    value means to drop from the selection criteria, that is, return
    information about both stand-alone and packaged objects (same as
    passing in "%"). Otherwise the catalog parameter should be a package name pattern (with SQL wild cards, if desired).



  • schemaPattern:
    All objects within Oracle must have a schema, so it does not make sense
    to return information for those objects without one. Thus, the
    construct " " (the empty string) is
    interpreted on input to mean the objects in the current schema (that
    is, the one to which you are currently connected). To be consistent
    with the behavior of the catalog parameter, null is interpreted to drop the schema from the selection criteria (same as passing in "%"). It can also be used as a pattern with SQL wild cards.



  • procedureNamePattern and columnNamePattern:
    The empty string (" ") does not make sense for either parameter,
    because all procedures and arguments must have names. Thus, the
    construct " " will raise an exception. To be consistent with the behavior of other parameters, null has the same effect as passing in "%".




Powered by ScribeFire.



Columns in the ResultSet returned by getProcedureColumns

Columns in the ResultSet returned by getProcedureColumns are as described by the API. Further details for some specific columns:
  • PROCEDURE_CAT

    always "null" in Derby

  • PROCEDURE_SCHEM

    schema for a Java procedure

  • PROCEDURE_NAME

    the name of the procedure

  • COLUMN_NAME

    the name of the parameter (see column-Name-Pattern)

  • COLUMN_TYPE

    short indicating what the row describes. Always is DatabaseMetaData.procedureColumnIn for method parameters, unless the parameter is an array. If so, it is DatabaseMetaData.procedureColumnInOut. It always returns DatabaseMetaData.procedureColumnReturn for return values.

  • TYPE_NAME

    Derby-specific name for the type.

  • NULLABLE

    always returns DatabaseMetaData.procedureNoNulls for primitive parameters and DatabaseMetaData.procedureNullable for object parameters

  • REMARKS

    a String describing the java type of the method parameter

  • COLUMN_DEF

    a String describing the default value for the column (may be null)

  • SQL_DATA_TYPE

    reserved by JDBC spec for future use

  • SQL_DATETIME_SUB

    reserved by JDBC spec for future use

  • CHAR_OCTET_LENGTH

    the maximum length of binary and character based columns (or any other datatype the returned value is a NULL)

  • ORDINAL_POSITION

    the ordinal position, starting from 1, for the input and output parameters for a procedure.

  • IS_NULLABLE

    a String describing the parameter's nullability (YES means parameter can include NULLs, NO means it can't)

  • SPECIFIC_NAME

    the name which uniquely identifies this procedure within its schema

  • METHOD_ID

    a Derby-specific column.

  • PARAMETER_ID

    a Derby-specific column.

1 comment:

Anonymous said...

Hello,
I found your blog useful to get DatabaseMetaData working for Oracle to retrieve the Stored procs.
Would like to add one more thing
1) Please provide input params to getProcedures() and getProcedureColumns() in CAPITAL so that it works.
For me I did not give the schema name in Capitals and hence it was not working.

Thanks,
Amit