Describing a REF CURSOR in Oracle 10<i>g</i>+ Using PL/SQL, Java and C

Describing a REF CURSOR in Oracle 10g+ Using PL/SQL, Java and C

Introduction

Use case: You need to be able to describe any PL/SQL REF CURSOR in a uniform way across all editions of Oracle Database from 10g Release 1 and newer. Performance of the solution is not important. Potentially, you need to fetch the REF CURSOR into transient or persistent storage — I’ll come back to this in a future blog post.

Challenges: Description of a REF CURSOR directly in PL/SQL requires Oracle 11g Release 1 or newer. Java stored procedures are not supported by Oracle Database 10g Release 2 Express Edition.

Solution: I’ve developed code that uses a combination of Java, C and PL/SQL in order to implement the use case. This blog post describes the code, which has taken a significant amount of time to develop.

Alternatives

Basically, we have the following alternatives:

• On Oracle Database 11g Release 1 and newer you can use DBMS_SQL.TO_CURSOR_NUMBER to convert the REF CURSOR to a DBMS_SQL cursor and then use DBMS_SQL.DESCRIBE_COLUMNS3 to get a descriptions of the columns.
• On Oracle Database 10g Release 1 and 2 you can write a Java stored procedure that receives the REF CURSOR as a parameter, which Oracle converts to a java.sql.ResultSet, that you can describe through java.sql.ResultSetMetaData.
• On Oracle Database 10g Release 2 Express Edition (in fact with Oracle Database 9i Release 2 as well) you can write an external procedure in C and use Oracle Call Interface (OCI) to describe the REF CURSOR.

We’ll look at the alternatives and implement each of them in the following. I’ve chosen to implement the functionality such that the REF CURSOR description returned from each of the 3 functions is a VARCHAR2 with XML on the form:

<ROWSET generator="PL/SQL|Java|C">
  <ROW><!-- Column 1. -->
    <ID>column_id_1</ID>
    <NAME>column_name_1</NAME>
    <TYPE_CODE>data_type_code_1</TYPE_CODE>
    <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
    <!-- Character set form: 1: Database. 2: National. -->
    <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
    <NAME>column_name_1</NAME>
    <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
    <LENGTH>column_length</LENGTH>
    <!-- For CHAR, VARCHAR2. -->
    <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
    <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
    <PRECISION>precision_1</PRECISION>
    <!-- NUMBER, INTERVAL DAY TO SECOND. -->
    <SCALE>scale_1</SCALE>
    <!-- For object types, including collections. -->
    <OWNER>type_owner_1</LENGTH_SEMANTICS>
    <!-- For object types, including collections. -->
    <TYPE_NAME>type_name_1</TYPE_NAME>
    <DECLARATION>declaration_1</DECLARATION>
  </ROW>
  <!-- Columns 2 through n-1. -->
  <ROW><!-- Column n. -->
    <ID>column_id_n</ID>
    <NAME>column_name_n</NAME>
    <TYPE_CODE>data_type_code_n</TYPE_CODE>
    <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
    <!-- Character set form: 1: Database. 2: National. -->
    <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
    <NAME>column_name_n</NAME>
    <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
    <LENGTH>column_length</LENGTH>
    <!-- For CHAR, VARCHAR2. -->
    <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
    <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
    <PRECISION>precision_n</PRECISION>
    <!-- NUMBER, INTERVAL DAY TO SECOND. -->
    <SCALE>scale_n</SCALE>
    <!-- For object types, including collections. -->
    <OWNER>type_owner_n</LENGTH_SEMANTICS>
    <!-- For object types, including collections. -->
    <TYPE_NAME>type_name_n</TYPE_NAME>
    <DECLARATION>declaration_n</DECLARATION>
  </ROW>
</ROWSET>

I could have chosen to create an object type and collection for the description but I’ve chosen the XML form in order not to overcomplicate the code.

Type Code Mappings

Oracle uses different type codes in different contexts so we need to be able to map them from the native type codes used in PL/SQL, Java and C to a “uniform” type code, in order to make usages of the code we develop here portable across the various Oracle Database editions and versions. I’ve described such a “uniform” type code mapping in my blog post Oracle Type Code Mappings.

Implementations

We’ll have a look at the various implementations in the following sections. I’ve chosen not to use Conditional Compilation for separation of the language-specific variants of the describe functionality. This is in order to be able to support Oracle 10.1.0.2.0 and 10.1.0.3.0 as Conditional Compilation wasn’t introduced for Oracle 10g until 10.1.0.4.0.

We use a trick to be able to transfer REF CURSOR to a Java stored procedure and C external procedure: We obtain a cursor number for the REF CURSOR through a call to DBMS_ODCI.SAVEREFCURSOR, transfer the cursor number to Java/C and there obtain the REF CURSOR based on the number with DBMS_ODCI.RESTOREREFCURSOR. This shouldn’t be necessary for Java but experience shows that transferring the REF CURSOR directly fails with Oracle Database 10g Release 1 (Oracle sometimes throws various “invalid cursor” errors or provides a null java.sql.ResultSet) and furthermore, it’s not possible to transfer a REF CURSOR directly from PL/SQL to a C external procedure. Well, the latter turns out not to be true but more about that later.

PL/SQL: REF_CURSOR_DESCRIPTOR_PLSQL

This is the pure PL/SQL implementation (ref_cursor_descriptor_plsql.fnc):

create or replace function ref_cursor_descriptor_plsql(rc in out sys_refcursor)
return varchar2 as

/**
 * PL/SQL function that describes a REF CURSOR. Requires Oracle 11g Release 1 or
 * newer. Description is returned in the following XML format (without the XML
 * comments):
 * <ROWSET generator="PL/SQL">
 *   <ROW><!-- Column 1. -->
 *     <ID>column_id_1</ID>
 *     <NAME>column_name_1</NAME>
 *     <TYPE_CODE>data_type_code_1</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_1</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_1</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_1</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_1</TYPE_NAME>
 *     <DECLARATION>declaration_1</DECLARATION>
 *   </ROW>
 *   ...
 *   <ROW><!-- Column n. -->
 *     <ID>column_id_n</ID>
 *     <NAME>column_name_n</NAME>
 *     <TYPE_CODE>data_type_code_n</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_n</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_n</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_n</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_n</TYPE_NAME>
 *     <DECLARATION>declaration_n</DECLARATION>
 *   </ROW>
 * </ROWSET>
 * Feel free to use at your own risk.
 * @param   rc      REF CURSOR.
 * @return  XML describing the REF CURSOR.
 * @version   $Revision: 1 $
 * @author    Finn Ellebaek Nielsen, Ellebaek Consulting ApS.
 */

  rcc binary_integer;
  result varchar2(32767);
  column_count number;
  column_metadata dbms_sql.desc_tab3;
  type_code pls_integer;
  native_type_code pls_integer;
  xml varchar2(32767);

/**
 * Maps type code from native type code returned by DBMS_SQL.DESCRIBE_COLUMNS3
 * to "uniform" type code as described in blog article
 * http://ellebaek.wordpress.com/2011/02/25/oracle-type-code-mappings/
 * @param   type_code
 *                  Type code returned by DBMS_SQL.DESCRIBE_COLUMNS3.
 * @return  "Uniform" type code.
 */

  function map_type_code(type_code in pls_integer)
  return pls_integer as

  begin
    case type_code
      when 11 then
        -- ROWID.
        return type_codes.tc_rowid;
      when 100 then
        -- BINARY_FLOAT.
        return type_codes.tc_binary_float;
      when 101 then
        -- BINARY_DOUBLE.
        return type_codes.tc_binary_double;
      when 111 then
        -- Object REF.
        return type_codes.tc_ref;
      else
        return type_code;
    end case;
  end map_type_code;

/**
 * Append XML element, indented to given level (2 spaces per level, starting
 * from 1).
 * @param   level   Level for indentation.
 * @param   name    Element name.
 * @param   value   Element numeric value.
 */

  procedure append(
    level in pls_integer,
    name in varchar2,
    value in varchar2
  ) as

  begin
    xml := xml ||
      rpad(' ', (level - 1) * 2) ||
      '<' || name || '>' ||
      value ||
      '</' || name || '>' || chr(10);
  end append;

begin
  -- Convert the REF CURSOR to a DBMS_SQL cursor. Only possible with Oracle
  -- Database 11g Release 1 and newer.
  rcc := dbms_sql.to_cursor_number(rc);

  -- Describe the columns.
  dbms_sql.describe_columns3(
    c => rcc,
    col_cnt => column_count,
    desc_t => column_metadata
  );

  if column_count > 0 then
    xml := '<ROWSET generator="PL/SQL">' || chr(10);

    for i in 1 .. column_count loop
      native_type_code := column_metadata(i).col_type;
      type_code := map_type_code(native_type_code);
      xml := xml ||
          '  <ROW>' || chr(10);
      append(3, 'ID', i);
      append(3, 'NAME', column_metadata(i).col_name);
      append(3, 'TYPE_CODE', type_code);
      append(3, 'NATIVE_TYPE_CODE', native_type_code);
      if type_code in (
          type_codes.tc_char,
          type_codes.tc_varchar2,
          type_codes.tc_clob
        ) then
        -- Text.
        append(3, 'CHARSET_FORM', column_metadata(i).col_charsetform);
      end if;
      if type_code in (
          type_codes.tc_char,
          type_codes.tc_varchar2,
          type_codes.tc_raw,
          type_codes.tc_urowid
        ) then
        -- Text (not CLOB), RAW, UROWID.
        append(3, 'LENGTH', column_metadata(i).col_max_len);
      end if;
      if type_code in (
            type_codes.tc_char,
            type_codes.tc_varchar2
          )
          and column_metadata(i).col_charsetform = 1 then
        -- Database character set.
        append(3, 'LENGTH_SEMANTICS', 'BYTE');
      end if;
      if type_code in (
          type_codes.tc_number,
          type_codes.tc_interval_ym,
          type_codes.tc_interval_ds
        ) then
        -- NUMBER/INTERVAL YEAR TO MONTH/INTERVAL DAY TO SECOND.
        append(3, 'PRECISION', column_metadata(i).col_precision);
      end if;
      if type_code in (
          type_codes.tc_timestamp,
          type_codes.tc_timestamp_tz,
          type_codes.tc_timestamp_ltz
        ) then
        -- TIMESTAMP%.
        append(3, 'PRECISION', column_metadata(i).col_scale);
      end if;
      if type_code in (
          type_codes.tc_number,
          type_codes.tc_interval_ds
        ) then
        -- NUMBER/INTERVAL DAY TO SECOND.
        append(3, 'SCALE', column_metadata(i).col_scale);
      end if;
      if type_code = type_codes.tc_object then
        -- Object type/collection.
        append(3, 'OWNER', column_metadata(i).col_schema_name);
        append(3, 'TYPE_NAME', column_metadata(i).col_type_name);
      end if;
      xml := xml || '  </ROW>' || chr(10);
    end loop;

    xml := xml || '</ROWSET>';
  end if;

  -- Convert the DBMS_SQL cursor back to a REF CURSOR. Only possible with Oracle
  -- Database 11g Release 1 and newer. This is necessary in order to continue
  -- to use the REF CURSOR.
  rc := dbms_sql.to_refcursor(rcc);

  return xml;
end ref_cursor_descriptor_plsql;
/

Java Stored Procedure: com.appatra.blog.RefCursorDescriptor + REF_CURSOR_DESCRIPTOR_JAVA

This is the Java stored procedure com.appatra.blog.RefCursorDescriptor (RefCursorDescriptor.sql):

create or replace and resolve java source named
    "com/appatra/blog/RefCursorDescriptor" as
package com.appatra.blog;

/**
 * Java class useful for describing REF CURSORs in PL/SQL. Requires Oracle 10g
 * Release 1 or newer. Does not work with Oracle 10g Release 2 Express Edition
 * as this doesn't support Java stored procedures.
 * Feel free to use at your own risk.
 * When installing, substitution must be switched off ("set scan off") or the
 * define character must be set to something different than & ("set define ^").
 * @version   $Revision: 1 $
 * @author    Finn Ellebaek Nielsen, Ellebaek Consulting ApS.
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Types;

import java.lang.StringBuffer;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleResultSetMetaData;

public class RefCursorDescriptor {
  /// Buffer.
  private static StringBuffer result;

/**
 * Java method that describes a REF CURSOR. Description is returned in the
 * following XML format (without the XML comments):
 * <ROWSET generator="Java">
 *   <ROW><!-- Column 1. -->
 *     <ID>column_id_1</ID>
 *     <NAME>column_name_1</NAME>
 *     <TYPE_CODE>data_type_code_1</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_1</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_1</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_1</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_1</TYPE_NAME>
 *   </ROW>
 *   <!-- Columns 2 through n-1. -->
 *   <ROW><!-- Column n. -->
 *     <ID>column_id_n</ID>
 *     <NAME>column_name_n</NAME>
 *     <TYPE_CODE>data_type_code_n</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_n</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_n</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_n</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_n</TYPE_NAME>
 *   </ROW>
 * </ROWSET>
 * @param   rS      Result set. Oracle translates a PL/SQL REF CURSOR to a Java
 *                  result set when this method is called. However, this only
 *                  works from Oracle 10g Release 2. For 10g Release 1, you need
 *                  to use the overloaded method that takes a REF CURSOR number
 *                  as input.
 * @return  XML describing the REF CURSOR.
 */

  public static String describe(java.sql.ResultSet rS)
  throws SQLException {
    result = new StringBuffer(10000);

    int typeCode, nativeTypeCode;
    String typeName;
    int precision;
    int scale;

    if (rS != null) {
      OracleResultSetMetaData rSMD = (OracleResultSetMetaData)rS.getMetaData();

      result.append("<ROWSET generator=\"Java\">\n");
      for (int i = 1; i <= rSMD.getColumnCount(); i++) {
        result.append("  <ROW>\n");
        append("ID", i);
        append("NAME", rSMD.getColumnName(i));
        typeName = rSMD.getColumnTypeName(i);
        nativeTypeCode = rSMD.getColumnType(i);
        typeCode = mapTypeCode(nativeTypeCode, typeName);
        append("TYPE_CODE", typeCode);
        append("NATIVE_TYPE_CODE", nativeTypeCode);
        if (typeCode == 1 ||
            typeCode == 96 ||
            typeCode == 112) {
          append("CHARSET_FORM", rSMD.isNCHAR(i) ? "2" : "1");
        }
        if (typeCode == 109 || typeCode == 111) {
          append("OWNER", typeName.substring(0, typeName.indexOf('.')));
          append("TYPE_NAME", typeName.substring(typeName.indexOf('.') + 1));
        }
        if (typeCode == 1 ||
            typeCode == 96 ||
            typeCode == 23 ||
            typeCode == 208) {
          append("LENGTH", rSMD.getColumnDisplaySize(i));
          if (typeCode != 23 && typeCode != 208 && ! rSMD.isNCHAR(i))
            append("LENGTH_SEMANTICS", "CHAR");
        }

        precision = 0;
        if (typeCode == 2 ||
            typeCode == 182 ||
            typeCode == 183)
          precision = rSMD.getPrecision(i);

        scale = 0;
        if (typeCode == 2 ||
            typeCode == 180 ||
            typeCode == 181 ||
            typeCode == 183 ||
            typeCode == 231)
          scale = rSMD.getScale(i);

        if (typeCode == 2 ||
            typeCode == 183) {
          // Number, INTERVAL DS
          append("PRECISION", precision);
          append("SCALE", scale);
        }
        if (typeCode == 182) {
          // INTERVAL YM.
          append("PRECISION", precision);
        }
        if (typeCode == 180 ||
            typeCode == 181 ||
            typeCode == 231) {
          // TIMESTAMP%,
          append("PRECISION", scale);
        }
        result.append("  </ROW>\n");
      }
      result.append("</ROWSET>");

      return result.toString();
    }
    else result.append("<ROWSET/>");

    return null;
  }

/**
 * Java method that describes a REF CURSOR. Description is returned in the
 * following XML format (without the XML comments):
 * <ROWSET generator="Java">
 *   <ROW><!-- Column 1. -->
 *     <ID>column_id_1</ID>
 *     <NAME>column_name_1</NAME>
 *     <TYPE_CODE>data_type_code_1</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_1</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_1</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_1</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_1</TYPE_NAME>
 *   </ROW>
 *   <!-- Columns 2 through n-1. -->
 *   <ROW><!-- Column n. -->
 *     <ID>column_id_n</ID>
 *     <NAME>column_name_n</NAME>
 *     <TYPE_CODE>data_type_code_n</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_n</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_n</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_n</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_n</TYPE_NAME>
 *   </ROW>
 * </ROWSET>
 * @param   rCN     REF CURSOR number for the REF CURSOR that is to be
 *                  described. This must have been obtained through a call to
 *                  DBMS_ODCI.SAVEREFCURSOR. Result set. This "trick" is
 *                  required for Oracle 10g Release 1. For For 10g Release 2 and
 *                  newer, you can use the overloaded method that takes a Java
 *                  result set as input.
 * @return  XML describing the REF CURSOR.
 */

  public static String describe(int rCN)
  throws SQLException {
    Connection connection =
        DriverManager.getConnection("jdbc:default:connection:");

    CallableStatement cS =
        connection.prepareCall("begin dbms_odci.restorerefcursor(:rc, :rcn); end;");

    ResultSet rS;

    cS.registerOutParameter(1, OracleTypes.CURSOR);
    cS.setInt(2, rCN);
    cS.execute();
    rS = (ResultSet)cS.getObject(1);

    return describe(rS);
  }

/**
 * Append XML element, indented to given level 3 (2 spaces per level).
 * @param   name    Element name.
 * @param   value   Element string value.
 */

  private static void append(String name, String value) {
    result.append("    <" + name + ">" + value + "</" + name + ">\n");
  }

/**
 * Append XML element, indented to given level 3 (2 spaces per level).
 * @param   name    Element name.
 * @param   value   Element integer value.
 */

  private static void append(String name, int value) {
    append(name, "" + value);
  }

/**
 * Maps from JDBC type code to "uniform" type code, which differs in many
 * cases. Refer to blog post
 * http://ellebaek.wordpress.com/2011/02/25/oracle-type-code-mappings/
 * @param   typeCode
 *                  JDBC type code.
 * @param   typeName
 *                  Type name, required to distinguish DATE and TIMESTAMP.
 * @return  "Uniform" type code.
 */

  private static int mapTypeCode(int typeCode, String typeName) {
    switch (typeCode) {
      case Types.VARCHAR:
        // VARCHAR2.
        return 1;
      case Types.NUMERIC:
        // NUMBER.
        return 2;
      case Types.LONGVARCHAR:
        // LONG.
        return 8;
      case Types.DATE:
        // DATE.
        return 12;
      case 100:
        // BINARY_FLOAT.
        return 21;
      case 101:
        // BINARY_DOUBLE.
        return 22;
      case Types.TIMESTAMP:
        // TIMESTAMP/DATE.
        if (typeName.equals("DATE"))
          return 12;
        return 180;
      case -101:
        // TIMESTAMP WITH TIME ZONE.
        return 181;
      case -102:
        // TIMESTAMP WITH LOCAL TIME ZONE.
        return 231;
      case -103:
        // INTERVAL YEAR TO MONTH.
        return 182;
      case -104:
        // INTERVAL DAY TO SECOND.
        return 183;
      case Types.VARBINARY:
        // RAW.
        return 23;
      case Types.LONGVARBINARY:
        // LONG RAW.
        return 24;
      case -8:
        // ROWID/UROWID, map to UROWID.
        return 208;
      case Types.CHAR:
        // CHAR
        return 96;
      case Types.CLOB:
        // CLOB.
        return 112;
      case Types.BLOB:
        // BLOB.
        return 113;
      case -13:
        // BFILE.
        return 114;
      case Types.STRUCT:
      case Types.ARRAY:
      case 2007:
        // Object type, collection etc.
        return 109;
      case 2006:
        // Object REF.
        return 111;
      case -10:
        // REF CURSOR.
        return 102;
    }

    return typeCode;
  }
};
/

This is the PL/SQL wrapper for the Java stored procedure that takes a REF CURSOR number as input (ref_cursor_descriptor_java.fnc):

create or replace function ref_cursor_descriptor_java(rcn in pls_integer)
return varchar2 as
language java
name 'com.appatra.blog.RefCursorDescriptor.describe(int) return java.lang.String';
/

And here is the PL/SQL wrapper for the Java stored procedure that takes a REF CURSOR as input (ref_cursor_descriptor_java2.fnc):

create or replace function ref_cursor_descriptor_java2(rc in sys_refcursor)
return varchar2 as
language java
name 'com.appatra.blog.RefCursorDescriptor.describe(java.sql.ResultSet) return java.lang.String';
/

C External Procedures: refcurdesc and refcurdesc2

According to the Oracle documentation it’s not possible to pass a REF CURSOR to a C external procedure — but with Oracle Database 9i Release 2 and newer you can in fact transfer a REF CURSOR directly to your C external procedure and it will be received as OCIStmt **. With Oracle Database 9i Release 1 you get an error if you try this when you call the function (ORA-28577: argument 2 of external procedure refcurdesc2 has unsupported datatype UNKNOWN). With Oracle Database 8 and 8i you get an internal error.

I discovered this when I was working on my followup to this blog post Copying/Transforming a REF CURSOR in Oracle 10g+. Here I found that the DBMS_ODCI trick somehow changed the REF CURSOR such that it wasn’t possible to use it with DBMS_XMLGEN afterwards (ORA-24374: define not done before fetch or execute and fetch errors) and also it didn’t work consistently with sub REF CURSORs, rendering them impossible to fetch from after the describe (ORA-01001: invalid cursor, ORA-01008: not all variables bound errors etc).

I’ve kept the original implementation of the refcurdesc function and added a new function refcurdesc2 that uses the direct approach.

Here’s the implementation of the PL/SQL interfaces to the C external procedures, first ref_cursor_descriptor_c.fnc (indirect):

create or replace function ref_cursor_descriptor_c(
  rcn in pls_integer
)
return varchar2 as
language c
name "refcurdesc"
library refcurdesc
with context
parameters (
  context,
  rcn int,
  rcn indicator short,
  return indicator short,
  return length short,
  return string
);
/

and ref_cursor_descriptor_c2.fnc (direct):

create or replace function ref_cursor_descriptor_c2(
  rc in sys_refcursor
)
return varchar2 as
language c
name "refcurdesc2"
library refcurdesc
with context
parameters (
  context,
  rc,
  return indicator short,
  return length short,
  return string
);
/

Here’s the implementation of the C external procedure (refcurdesc.c):

/**
 * External C procedure used to describe a REF CURSOR. This is useful for
 * pre-Oracle 11.1 (with Oracle 11.1+ you can convert the REF CURSOR to a
 * DBMS_SQL cursor and then describe). It's also possible to write a Java
 * stored procedure, but obviously this is not supported with 10.2 XE.
 * refcurdesc() requires Oracle 10g Release 1 or newer.
 * refcurdesc2() requires Oracle 9i Release 2 or newer.
 * Feel free to use at your own risk.
 * @version   $Revision: 2 $
 * @author    Finn Ellebaek Nielsen, Ellebaek Consulting ApS.
 */

#if defined(_WINDOWS)
#define _CRT_SECURE_NO_WARNINGS 1
#endif

#define USE_OCI_STMT_PREPARE 1

#include <stdio.h>
#include <string.h>
#include <oci.h>
#include <ociextp.h>

static text temp[32600];
static text temp2[1000];
static text indent[1000];
static text declare[100];
static sword oci_status;

#define INDENT_PER_LEVEL 2

static OCIExtProcContext *oci_ctx;
static OCIEnv            *envhp;
static OCISvcCtx         *svchp;
static OCIError          *errhp;
static OCIParam          *pard;

static int line;
static int is_cursor_expression;

void describe_statement(int level, OCIStmt *refcur);
void append_element(int level, text *name, text *value);
void append_element2(int level, text *name, sb2 value);
void append2(int level, text *t);
void append(text *t);
int map_type_code(int type_code);
void disable_prefetch(OCIStmt *stmt);
void checkerr(OCIError *errhp, sword status, int line);

/**
 * OCI error handling: Check for returned errors and don't execute future calls
 * if an OCI error is raised. Any errors will be raised in an exception.
 * @param   call    Function call to execute if no previous calls have failed.
 */

#define CHECKERR(call) { line = __LINE__; if (oci_status == OCI_SUCCESS) checkerr(errhp, call, line); }

/**
 * Describe REF CURSOR and return description in XML form in a VARCHAR2.
 * Description is returned in the following XML format (without the XML
 * comments):
 * <ROWSET generator="C">
 *   <ROW><!-- Column 1. -->
 *     <ID>column_id_1</ID>
 *     <NAME>column_name_1</NAME>
 *     <TYPE_CODE>data_type_code_1</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_1</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_1</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_1</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_1</TYPE_NAME>
 *   </ROW>
 *   <!-- Columns 2 through n-1. -->
 *   <ROW><!-- Column n. -->
 *     <ID>column_id_n</ID>
 *     <NAME>column_name_n</NAME>
 *     <TYPE_CODE>data_type_code_n</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_n</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_n</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_n</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_n</TYPE_NAME>
 *   </ROW>
 * </ROWSET>
 * Limitations: 32KB limitation on the return value. The implementation is not
 * thread safe.
 * @param   ctx     OCI context provided by Oracle.
 * @param   refcurno
 *                  REF CURSOR number obtained on the original REF CURSOR
 *                  through a call to DBMS_ODCI.SAVEREFCURSOR. This is required
 *                  as according to the Oracle documentation we can't directly
 *                  transfer the REF CURSOR to this function.
 * @param   refcurno_i
 *                  NULL indicator for refcurno.
 * @param   result_i
 *                  NULL indicator for the VARCHAR2 we're returning.
 * @param   result_l
 *                  Length of the VARCHAR2 we're returning.
 * @return  Description of columns in REF CURSOR on XML form.
 *          An exception is raised if the result is larger than
 *          32KB.
 */

text *refcurdesc(
  OCIExtProcContext *ctx,
  int refcurno,
  short refcurno_i,
  short *result_i,
  short *result_l
)

{
  text *result;

  static OCIStmt *stmhp = (OCIStmt *)0;
  char *plsql_block = "begin dbms_odci.restorerefcursor(:rc, :rcn); end;";
  char *plsql_block2 = "begin dbms_odci.saverefcursor(:rc, :rcn); end;";
  OCIStmt *refcur = NULL;
  OCIBind *bndp1 = (OCIBind *)0;
  OCIBind *bndp2 = (OCIBind *)0;

  temp[0] = '\0';

  oci_ctx = ctx;
  oci_status = OCI_SUCCESS;

  if (refcurno_i == OCI_IND_NULL) {
    *result_i = (short)OCI_IND_NULL;
    // PL/SQL has no notion of a NULL ptr, so return a zero-byte string.
    result = (text *)OCIExtProcAllocCallMemory(ctx, 1);
    result[0] = '\0';
  }
  else {
    // Get the OCI handles from our Oracle session.
    CHECKERR(OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp))

    is_cursor_expression = 0;
#if USE_OCI_STMT_PREPARE
    // Convert the REF CURSOR number back to a REF CURSOR.
    CHECKERR(
      OCIHandleAlloc(
        (dvoid *)envhp, (dvoid **)&stmhp,
        (ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0
      )
    )
    //disable_prefetch(stmhp);
    // Prepare PL/SQL block.
    CHECKERR(
      OCIStmtPrepare(
        stmhp, errhp, (unsigned char *)plsql_block, (ub4)strlen(plsql_block),
        OCI_NTV_SYNTAX, OCI_DEFAULT
      )
    )
#else
    CHECKERR(
      OCIStmtPrepare2(
        svchp, &stmhp, errhp, (unsigned char *)plsql_block, (ub4)strlen(plsql_block),
        NULL, 0,
        OCI_NTV_SYNTAX, OCI_DEFAULT
      )
    )
#endif

    // Bind.
    CHECKERR(
      OCIHandleAlloc(envhp, (void **)&refcur, OCI_HTYPE_STMT, 0, NULL)
    )
    // Position 1: Output REF CURSOR.
    CHECKERR(
      OCIBindByPos(
        stmhp, &bndp1, errhp, (ub4)1,
        (void *)&refcur, (sb4)0, SQLT_RSET, (void *)0, (ub2 *)0,
        (ub2 *)0, (ub4)0, (ub4)0, (ub4)OCI_DEFAULT
      )
    )
    // Position 2: Input REF CURSOR number, previously saved with
    // DBMS_ODCI.SAVEREFCURSOR.
    CHECKERR(
      OCIBindByPos(
        stmhp, &bndp2, errhp, (ub4)2,
        (void *)&refcurno, sizeof(int), SQLT_INT, (void *)&refcurno_i, (ub2 *)0,
        (ub2 *)0, (ub4)0, (ub4)0, (ub4)OCI_DEFAULT
      )
    )

    // Execute.
    CHECKERR(OCIStmtExecute(svchp, stmhp, errhp, 1, 0, NULL, NULL, OCI_DEFAULT))

#if USE_OCI_STMT_PREPARE
    // Free PL/SQL statement.
    CHECKERR(OCIHandleFree(stmhp, OCI_HTYPE_STMT))
    stmhp = NULL;
#endif

    if (oci_status == OCI_SUCCESS)
      describe_statement(1, refcur);

    // Set length.
    *result_l = (short)strlen((char *)temp);
    // Set NULL indicator.
    if (*result_l > 0)
      *result_i = (short)OCI_IND_NOTNULL;
    else *result_i = (short)OCI_IND_NULL;

    // Allocate memory for result string, including NULL terminator.
    result = (text *)OCIExtProcAllocCallMemory(ctx, *result_l + 1);
    strcpy((char *)result, (char *)temp);

    // Free REF CURSOR handle: Seems to close the original REF CURSOR, rendering
    // this useless (ORA-01001: invalid cursor).
    //CHECKERR(OCIHandleFree(refcur, OCI_HTYPE_STMT))
    refcur = NULL;
  }

  // Return pointer, which PL/SQL frees later.
  return result;
}

/**
 * Describe REF CURSOR and return description in XML form in a VARCHAR2.
 * Description is returned in the following XML format (without the XML
 * comments):
 * <ROWSET generator="C">
 *   <ROW><!-- Column 1. -->
 *     <ID>column_id_1</ID>
 *     <NAME>column_name_1</NAME>
 *     <TYPE_CODE>data_type_code_1</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_1</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_1</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_1</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_1</TYPE_NAME>
 *   </ROW>
 *   <!-- Columns 2 through n-1. -->
 *   <ROW><!-- Column n. -->
 *     <ID>column_id_n</ID>
 *     <NAME>column_name_n</NAME>
 *     <TYPE_CODE>data_type_code_n</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_n</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_n</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_n</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_n</TYPE_NAME>
 *   </ROW>
 * </ROWSET>
 * Limitations: 32KB limitation on the return value. The implementation is not
 * thread safe.
 * @param   ctx     OCI context provided by Oracle.
 * @param   refcur
 *                  REF CURSOR (pointer to OCIStmt *). According to the Oracle
 *                  documentation we can't directly transfer a PL/SQL REF
 *                  CURSOR to this function but in fact we can with Oracle 9.2
 *                  and newer.
 * @param   result_i
 *                  NULL indicator for the VARCHAR2 we're returning.
 * @param   result_l
 *                  Length of the VARCHAR2 we're returning.
 * @return  Description of columns in REF CURSOR on XML form.
 *          An exception is raised if the result is larger than
 *          32KB.
 */

text *refcurdesc2(
  OCIExtProcContext *ctx,
  OCIStmt **refcur,
  short *result_i,
  short *result_l
)

{
  text *result;

  temp[0] = '\0';

  oci_ctx = ctx;
  oci_status = OCI_SUCCESS;

  CHECKERR(OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp))

  if (oci_status == OCI_SUCCESS)
    describe_statement(1, *refcur);

  // Set length.
  *result_l = (short)strlen((char *)temp);
  // Set NULL indicator.
  if (*result_l > 0)
    *result_i = (short)OCI_IND_NOTNULL;
  else *result_i = (short)OCI_IND_NULL;

  // Allocate memory for result string, including NULL terminator.
  result = (text *)OCIExtProcAllocCallMemory(ctx, *result_l + 1);
  strcpy((char *)result, (char *)temp);

  return result;
}

/**
 * Describes given ref cursor statement handle and appends to global description
 * XML.
 */

void describe_statement(int level, OCIStmt *refcur) {
  text column_name[31];

  ub2 type_code, native_type_code;
  ub2 col_width, charset_form, char_semantics, charset_id;
  text *s;
  ub4 slen;
  sb4 param_status;
  sb2 /*ub1*/ precision;
  sb1 scale;

  ub4 column_count;
  int i;

  text owner[31];
  text type_name[129];

  // Number of columns.
  column_count = 0;
  CHECKERR(
    OCIAttrGet(
      (void *)refcur, (ub4)OCI_HTYPE_STMT, (void *)&column_count,
      (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT, errhp
    )
  )

  if (column_count > 0)
    append2(level, "<ROWSET generator=\"C\">\n");

  for (i = 1; i <= (int)column_count; i++) {
    // For each column.
    append2(level + 1, "<ROW>\n");

    pard = (OCIParam *)0;

    // Get the column.
    param_status = OCIParamGet(
      (dvoid *)refcur, OCI_HTYPE_STMT, errhp,
      (dvoid **)&pard, (ub4)i
    );

    // Datatype.
    CHECKERR(
      OCIAttrGet(
        (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
        (dvoid *)&native_type_code, (ub4 *)0, (ub4)OCI_ATTR_DATA_TYPE,
        (OCIError *)errhp
      )
    )
    type_code = map_type_code(native_type_code);

    // Column name.
    slen = 0;
    CHECKERR(
      OCIAttrGet(
        (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
        (dvoid **)&s, (ub4 *)&slen, (ub4)OCI_ATTR_NAME,
        (OCIError *)errhp
      )
    )
    strncpy((char *)column_name, (char *)s, slen);
    column_name[slen] = '\0';

    append_element2(level + 2, "ID", i);
    append_element(level + 2, "NAME", column_name);
    append_element2(level + 2, "TYPE_CODE", type_code);
    append_element2(level + 2, "NATIVE_TYPE_CODE", native_type_code);

    // Character set.
    if (type_code == SQLT_AFC ||
        type_code == SQLT_CLOB ||
        type_code == SQLT_CHR ||
        type_code == SQLT_VCS) {
      CHECKERR(
        OCIAttrGet(
          (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&charset_form, (ub4 *)0, (ub4)OCI_ATTR_CHARSET_FORM,
          (OCIError *)errhp
        )
      )
      append_element2(level + 2, "CHARSET_FORM", charset_form);
      if (charset_form == SQLCS_EXPLICIT) {
        CHECKERR(
          OCIAttrGet(
            (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
            (dvoid *)&charset_id, (ub4 *)0, (ub4)OCI_ATTR_CHARSET_ID,
            (OCIError *)errhp
          )
        )
        append_element2(level + 2, "CHARSET_ID", charset_id);
      }
      else charset_id = 0;
    }
    else charset_form = 0;

    // Number/Interval YM/Interval DS: Precision.
    if (type_code == SQLT_NUM ||
        type_code == 182 ||
        type_code == 183) {
      CHECKERR(
        OCIAttrGet(
          (dvoid*)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&precision, (ub4 *)0,
          (ub4)OCI_ATTR_PRECISION, (OCIError *)errhp
        )
      )

      sprintf(temp2, "precision = %d", (int)precision);
      append_element2(level + 2, "PRECISION", precision);
    }
    // Number: Scale.
    if (type_code == SQLT_NUM) {
      CHECKERR(
        OCIAttrGet(
          (dvoid*)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&scale, (ub4 *)0,
          (ub4)OCI_ATTR_SCALE, (OCIError *)errhp
        )
      )
      append_element2(level + 2, "SCALE", scale);
    }
#ifdef OCI_ATTR_FSPRECISION
    // Timestamp/interval DS: Scale.
    if (type_code == 180 ||
        type_code == 181 ||
        type_code == 231 ||
        type_code == 183) {
      CHECKERR(
        OCIAttrGet(
          (dvoid*)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&scale, (ub4 *)0,
          (ub4)OCI_ATTR_FSPRECISION, (OCIError *)errhp
        )
      )
      if (type_code == 183)
        append_element2(level + 2, "SCALE", scale);
      else append_element2(level + 2, "PRECISION", scale);
    }
#endif

    // Length, length semantics.
    char_semantics = 0;
    if (type_code == SQLT_AFC ||
        type_code == SQLT_CHR ||
        type_code == SQLT_VCS ||
        type_code == SQLT_BIN ||
        type_code == 208) {
#ifdef OCI_ATTR_CHAR_USED
      CHECKERR(
        OCIAttrGet(
          (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&char_semantics,  (ub4 *)0, (ub4)OCI_ATTR_CHAR_USED,
          (OCIError *)errhp
        )
      )
      col_width = 0;
      if (char_semantics)
        // Column width in characters.
        CHECKERR(
          OCIAttrGet(
            (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
            (dvoid *)&col_width, (ub4 *)0, (ub4)OCI_ATTR_CHAR_SIZE,
            (OCIError *)errhp
          )
        )
      else
#else
      char_semantics = 2;
#endif
        // Column width in bytes.
        CHECKERR(
          OCIAttrGet(
            (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
            (dvoid *)&col_width, (ub4 *)0, (ub4)OCI_ATTR_DATA_SIZE,
            (OCIError *)errhp
          )
        )

      append_element2(level + 2, "LENGTH", col_width);
      if (charset_form == 1 && type_code != 208)
        append_element(level + 2, "LENGTH_SEMANTICS", char_semantics == 2 ? "" : (char_semantics ? "CHAR" : "BYTE"));
    }

    // Object type/collection or REF to object type/collection.
    if (type_code == 109 || type_code == 111) {
      // Schema name.
      slen = 0;
      CHECKERR(
        OCIAttrGet(
          (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&s, (ub4 *)&slen,
          (ub4)OCI_ATTR_SCHEMA_NAME, (OCIError *)errhp
        )
      )
      strncpy((char *)owner, (char *)s, slen);
      owner[slen] = '\0';
      append_element(level + 2, "OWNER", owner);

      // Type name.
      slen = 0;
      CHECKERR(
        OCIAttrGet(
          (dvoid *)pard, (ub4)OCI_DTYPE_PARAM,
          (dvoid *)&s, (ub4 *)&slen,
          (ub4)OCI_ATTR_TYPE_NAME, (OCIError *)errhp
        )
      )
      strncpy((char *)type_name, (char *)s, slen);
      type_name[slen] = '\0';
      append_element(level + 2, "TYPE_NAME", type_name);
    }

    append2(level + 1, "</ROW>\n");
  }

  if (column_count > 0)
    append2(level, "</ROWSET>");
}

/**
 * Append XML element, indented to column 5.
 * @param   level   Level for indentation.
 * @param   name    Element name.
 * @param   value   Element text value.
 */

void append_element(int level, text *name, text *value) {
  text temp[100];

  sprintf(temp, "%*s<%s>%s</%s>\n", (level - 1) * INDENT_PER_LEVEL, "", name, value, name);
  append(temp);
}

/**
 * Append XML element, indented to given level (2 spaces per level, starting
 * from 1).
 * @param   level   Level for indentation.
 * @param   name    Element name.
 * @param   value   Element numeric value.
 */

void append_element2(int level, text *name, sb2 value) {
  text temp[100];

  sprintf(temp, "%*s<%s>%d</%s>\n", (level - 1) * INDENT_PER_LEVEL, "", name, value, name);
  append(temp);
}

/**
 * Appends given text to internal buffer, indented to given level (2 spaces per
 * level, starting from 1).
 * @param   level   Level for indentation.
 * @param   t       Text to append.
 */

void append2(int level, text *t) {
  sprintf(indent, "%*s", (level - 1) * INDENT_PER_LEVEL, "");
  append(indent);
  append(t);
}

/**
 * Appends given text to internal buffer.
 * @param   t       Text to append.
 */

void append(text *t) {
  if (strlen(t) + strlen(temp) > sizeof(temp))
    // Too large, raise an exception.
    OCIExtProcRaiseExcpWithMsg(
      oci_ctx,
      20000,
      "refcurdesc: Too many columns, description is larger than 32KB, which isn't supported",
      0
    );

  strcat(temp, t);
}

/**
 * Maps from OCI type code to "uniform" type code, which differs in a few
 * cases. Refer to blog post
 * http://ellebaek.wordpress.com/2011/02/25/oracle-type-code-mappings/
 * @param   type_code
 *                  OCI type code.
 * @return  "Uniform" type code.
 */

int map_type_code(int type_code) {
  switch (type_code) {
#ifdef SQLT_IBFLOAT
    case SQLT_IBFLOAT:
      // BINARY_FLOAT.
      return 21;
#endif
#ifdef SQLT_IBDOUBLE
    case SQLT_IBDOUBLE:
      // BINARY_DOUBLE.
      return 22;
#endif
#ifdef SQLT_TIMESTAMP
    case SQLT_TIMESTAMP:
      // TIMESTAMP.
      return 180;
#endif
#ifdef SQLT_TIMESTAMP_TZ
    case SQLT_TIMESTAMP_TZ:
      // TIMESTAMP WITH TIME ZONE.
      return 181;
#endif
#ifdef SQLT_TIMESTAMP_LTZ
    case SQLT_TIMESTAMP_LTZ:
      // TIMESTAMP WITH LOCAL TIME ZONE.
      return 231;
#endif
#ifdef SQLT_INTERVAL_YM
    case SQLT_INTERVAL_YM:
      // INTERVAL YEAR TO MONTH.
      return 182;
#endif
#ifdef SQLT_INTERVAL_DS
    case SQLT_INTERVAL_DS:
      // INTERVAL DAY TO SECOND.
      return 183;
#endif
#ifdef SQLT_RDD
    case SQLT_RDD:
      // ROWID/UROWID, map to UROWID.
      return 208;
#endif
    case SQLT_NTY:
    case SQLT_NCO:
      // Object type or collection.
      return 109;
    case SQLT_REF:
      // REF to object type or collection.
      return 111;
    case SQLT_RSET:
      // REF CURSOR.
      return 102;
  }

  return type_code;
}

/**
 * OCI error checker. If any error is found, information about this is raised
 * in an exception and the global variable oci_status is updated with the value
 * of the error code, which will prevent any OCI calls wrapped up in CHECKERR
 * from being executed.
 * @param   errhp   Error handle.
 * @parem   status  OCI call return code.
 * @param   line    Line on which error occurred.
 */

void checkerr(OCIError *errhp, sword status, int line) {
  text errbuf[512];
  text message[1000] = "";
  text message2[1000] = "";
  sb4 errcode = 0;

  switch (status) {
    case OCI_SUCCESS:
      break;
    case OCI_SUCCESS_WITH_INFO:
      strcpy(message, "Error: OCI_SUCCESS_WITH_INFO");
      break;
    case OCI_NEED_DATA:
      strcpy(message, "Error: OCI_NEED_DATA");
      break;
    case OCI_NO_DATA:
      strcpy(message, "Error: OCI_NO_DATA");
      break;
    case OCI_ERROR:
      OCIErrorGet(
        (dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR
      );
      sprintf(message, "Error: %.*s", 512, errbuf);
      break;
    case OCI_INVALID_HANDLE:
      strcpy(message, "Error: OCI_INVALID_HANDLE");
      break;
    case OCI_STILL_EXECUTING:
      strcpy(message, "Error: OCI_STILL_EXECUTING");
      break;
    case OCI_CONTINUE:
      strcpy(message, "Error: OCI_CONTINUE");
      break;
    default:
      sprintf(message, "Error: %d", (int)status);
      break;
  }

  if (status != OCI_SUCCESS) {
    oci_status = status;
    sprintf(message2, "refcurdesc.c line %d: %s", line, message);
    OCIExtProcRaiseExcpWithMsg(oci_ctx, 20001, message2, 0);
  }
}

void disable_prefetch(OCIStmt *stmt) {
  ub4 prefetch = 0;

  // Set prefetch to 0 in order not to prefetch anything from embedded REF
  // CURSORs.
  CHECKERR(
    OCIAttrSet(
      (dvoid *)stmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&prefetch,
      0, (ub4)OCI_ATTR_PREFETCH_ROWS, errhp
    )
  );
  CHECKERR(
    OCIAttrSet(
      (dvoid *)stmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&prefetch,
      0, (ub4)OCI_ATTR_PREFETCH_MEMORY, errhp
    )
  );
}

Here’s refcurdesc.def, required for Windows only:

LIBRARY refcurdesc

EXPORTS
refcurdesc
refcurdesc2

Compilation Using Microsoft Visual Studio 2010

You can use the following commands to create refcurdesc.dll on Windows (32-bit, assuming that you have called vcvars32.bat to set up PATH, INCLUDE, LIB, etc and also that ORACLE_HOME is set, lines wrapped for legibility):

@setlocal
set INCLUDE=%INCLUDE%;%ORACLE_HOME%\oci\include
set LIB=%LIB%;%ORACLE_HOME%\oci\lib\msvc
cl /c /Zi /nologo /W3 /WX- /O2 /Oi /Oy- /GL /D WIN32 /D NDEBUG /D _WINDOWS /D
  _USRDLL /D REFCURDESC_EXPORTS /D _WINDLL /D _UNICODE /D UNICODE /Gm- /EHsc /MT
  /GS /Gy /fp:precise /Zc:wchar_t /Zc:forScope /Fo"Release\\"
  /Fd"Release\vc100.pdb" /Gd /TC /analyze- /errorReport:queue refcurdesc.c
link /ERRORREPORT:QUEUE /OUT:"Release\refcurdesc.dll" /INCREMENTAL:NO /NOLOGO
  oci.lib /DEF:"refcurdesc.def" /MANIFEST
  /ManifestFile:"Release\refcurdesc.dll.intermediate.manifest"
  /MANIFESTUAC:"level='asInvoker' uiAccess='false'" /DEBUG
  /PDB:"Release\refcurdesc.pdb" /SUBSYSTEM:WINDOWS /OPT:REF /OPT:ICF /LTCG
  /TLBID:1 /DYNAMICBASE /NXCOMPAT /IMPLIB:"Release\refcurdesc.lib" /MACHINE:X86
  /DLL Release\refcurdesc.obj
@endlocal

You need to link against a library file provided by the specific Oracle version you’re going to use the DLL with.

Compilation Using GCC

You can use the following commands to create refcurdesc.so on Linux (32-bit, assuming that ORACLE_HOME is set):

gcc -I $ORACLE_HOME/rdbms/public -fPIC -c refcurdesc.c
gcc -shared -static-libgcc -o refcurdesc.so refcurdesc.o

Installation

The compiled refcurdesc.dll/refcurdesc.so must be placed somewhere on the database server’s file system such that it can be referred from within Oracle. This is typically done in %ORACLE_HOME%\BIN on Windows and $ORACLE_HOME/bin on Linux. Please be aware that the directory name used for the Oracle library is case sensitive, even on Windows.

Configuration of Extproc Listener

If you get the following error when trying to execute the external procedure

ORA-28595: Extproc agent : Invalid DLL Path

you probably need to amend the configuration of the extproc listener to include a setting like

(ENVS="EXTPROC_DLLS=ANY")

or a more specific

(ENVS="EXTPROC_DLLS=ONLY:C:\ORACLE\O112\BIN\refcurdesc.dll")

Example:

SID_LIST_O112 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = O112)
      (ORACLE_HOME = c:\ORACLE\O112)
      (SID_NAME = O112)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProcO112)
      (ORACLE_HOME = C:\oracle\O112)
      (PROGRAM = extproc)
      (ENVS="EXTPROC_DLLS=ANY")
    )
    (SID_DESC =
      (SID_NAME = CLRExtProcO112)
      (ORACLE_HOME = C:\oracle\O112)
      (PROGRAM = extproc)
    )
  )

Main Package

The main package REF_CURSOR_DESCRIBE dispatches the incoming call to the specific implementation based on the following precedence order:

1. If Oracle Database 11g Release 1 or newer: Use pure PL/SQL.
2. If Oracle Database 10g Release 1 or 2: Use Java if Java option enabled.
3. If Oracle Database 10g Release 1 or 2: Use C.

If you have problems with this precedence order, you can control which implementation is used through setting of a package variable DESCRIBE_IMPL.
REF_CURSOR_DESCRIBE also adds an extra XML element for each column: DECLARATION, which can be used for declaring a placeholder for fetching the given column. The datatype used for the declaration of a placeholder for an embedded REF CURSOR is ANYDATA, which makes it possible to hold the value in an object type attribute.

Here’s the implementation of the REF_CURSOR_DESCRIBE package specification (ref_cursor_describe.pks):

create or replace package ref_cursor_descriptor
authid current_user as
/**
 * Functionality for describing a REF CURSOR in Oracle Database 10g Release 1 or
 * newer.
 * Feel free to use at your own risk.
 * @version   $Revision: 2 $
 * @author    Finn Ellebaek Nielsen, Ellebaek Consulting ApS.
 */

  /**
   * Overrides which implementation is used:
   * 'P':  PL/SQL.
   * 'J':  Java using DBMS_ODCI.
   * 'J2': Java direct.
   * 'C':  C using DBMS_ODCI.SAVE-/RESTOREREFCURSOR.
   * 'C2': C direct (not possible according to documentation).
   * '':   PL/SQL for Oracle 11.1 and newer, otherwise Java if Java option
   *       enabled (Java direct on 10.2 and newer, Java indirect on 10.1) and C
   *       if Java is not enabled (C direct).
   */
  describe_impl varchar2(2);

  function describe(rc in out sys_refcursor)
  return xmltype;

  function describe_plsql(rc in out sys_refcursor)
  return xmltype;
  function describe_java(rc in out sys_refcursor)
  return xmltype;
  function describe_c(rc in out sys_refcursor)
  return xmltype;

  function get_declaration(
    name in varchar2,
    type_code in pls_integer,
    charset_form in pls_integer,
    length_semantics in varchar2,
    precision in pls_integer,
    scale in pls_integer,
    length in pls_integer,
    owner in varchar2,
    type_name in varchar2
  )
  return varchar2;

  function quote_identifier(identifier in varchar2)
  return varchar2;
end;
/

And here’s the implementation of the REF_CURSOR_DESCRIBE package body (ref_cursor_describe.pkb):

create or replace package body ref_cursor_descriptor as
  --- $Revision: 2 $

  --- REF CURSOR number.
  rcn pls_integer;
  --- XML to be returned.
  xml varchar2(32767);
  --- Valid identifier characters.
  identifier_chars constant varchar2(40) :=
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890_$#';

  function add_declarations(xml in xmltype)
  return xmltype;

/**
 * Describe REF CURSOR and return description in XML form in a VARCHAR2.
 * Description is returned in the following XML format (without the XML
 * comments):
 * <ROWSET generator="PL/SQL|Java|C">
 *   <ROW><!-- Column 1. -->
 *     <ID>column_id_1</ID>
 *     <NAME>column_name_1</NAME>
 *     <TYPE_CODE>data_type_code_1</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_1</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_1</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_1</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_1</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_1</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_1</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_1</TYPE_NAME>
 *     <DECLARATION>declaration_1</DECLARATION>
 *   </ROW>
 *   <!-- Columns 2 through n-1. -->
 *   <ROW><!-- Column n. -->
 *     <ID>column_id_n</ID>
 *     <NAME>column_name_n</NAME>
 *     <TYPE_CODE>data_type_code_n</TYPE_CODE>
 *     <NATIVE_TYPE_CODE>native_data_type_code_n</NATIVE_TYPE_CODE>
 *     <!-- Character set form: 1: Database. 2: National. -->
 *     <CHARSET_FORM>native_data_type_code_n</CHARSET_FORM>
 *     <!-- For [N]CHAR, [N]VARCHAR2, RAW, UROWID. -->
 *     <LENGTH>column_length</LENGTH>
 *     <!-- For CHAR, VARCHAR2. -->
 *     <LENGTH_SEMANTICS>column_name_n</LENGTH_SEMANTICS>
 *     <!-- NUMBER, TIMESTAMP%, INTERVAL%. -->
 *     <PRECISION>precision_n</PRECISION>
 *     <!-- NUMBER, INTERVAL DAY TO SECOND. -->
 *     <SCALE>scale_n</SCALE>
 *     <!-- For object types, including collections. -->
 *     <OWNER>type_owner_n</LENGTH_SEMANTICS>
 *     <!-- For object types, including collections. -->
 *     <TYPE_NAME>type_name_n</TYPE_NAME>
 *     <DECLARATION>declaration_n</DECLARATION>
 *   </ROW>
 * </ROWSET>
 * Main entry. Dispatches to:
 * 1. PL/SQL if Oracle Database version >= 11.
 * 2. Java if Oracle Database version is 10 and Java option is enabled.
 * 3. C if Oracle Database version is 10.
 * Otherwise, an exception is raised.
 * @param   rc      REF CURSOR.
 * @return  Description of columns in REF CURSOR on XML form.
 *          An exception is raised if the result is larger than
 *          32KB.
 */

  function describe(rc in out sys_refcursor)
  return xmltype as

    result xmltype;

  begin
    case nvl(upper(describe_impl), 'D')
      when 'P' then
        result := describe_plsql(rc);
      when 'J' then
        result := describe_java(rc);
      when 'J2' then
        result := describe_java(rc);
      when 'C' then
        result := describe_c(rc);
      when 'C2' then
        result := describe_c(rc);
      when 'D' then
        -- Default.
        if portable.get_major_version >= 11 then
          result := describe_plsql(rc);
        elsif portable.get_major_version = 10 then
          if portable.is_option_enabled('Java') then
            result := describe_java(rc);
          else
            result := describe_c(rc);
          end if;
        else
          raise_application_error(
            -20000,
            'ref_cursor_descriptor.describe: ' ||
                'Oracle Database 10g Release 1 or newer is required'
          );
        end if;
      else
        raise_application_error(
          -20002,
          'ref_cursor_descriptor.describe: ' ||
              'Invalid value of DESCRIBE_IMPL: "' || describe_impl || '"'
        );
    end case;

    return add_declarations(result);
  end describe;

/**
 * At least Oracle 11.1. Pure PL/SQL solution.
 * @param   rc      REF CURSOR.
 * @return  Description of columns in REF CURSOR on XML form.
 *          An exception is raised if the result is larger than
 *          32KB.
 */

  function describe_plsql(rc in out sys_refcursor)
  return xmltype as

  begin
    xml := ref_cursor_descriptor_plsql(rc);

    if xml is not null then
      return xmltype(xml);
    else
      return null;
    end if;
  end describe_plsql;

/**
 * At least Oracle 10.1 and Java option enabled. Java solution.
 * @param   rc      REF CURSOR.
 * @return  Description of columns in REF CURSOR on XML form.
 *          An exception is raised if the result is larger than
 *          32KB.
 */

  function describe_java(rc in out sys_refcursor)
  return xmltype as

  begin
    if upper(describe_impl) = 'J' then
      /*
       * Necessary to make it work for Oracle 10.1 as Oracle on some platforms
       * will transfer the ResultSet as null.
       */
      dbms_odci.saverefcursor(rc, rcn);
      xml := ref_cursor_descriptor_java(rcn);
      dbms_odci.restorerefcursor(rc, rcn);
    else
      -- 10.2 or newer, no need for using DBMS_ODCI, in fact, for 10.2 and 11.1
      -- this can create problems.
      xml := ref_cursor_descriptor_java2(rc);
    end if;

    if xml is not null then
      return xmltype(xml);
    else
      return null;
    end if;
  end describe_java;

/**
 * At least Oracle 9.2. C solution.
 * @param   rc      REF CURSOR.
 * @return  Description of columns in REF CURSOR on XML form.
 *          An exception is raised if the result is larger than
 *          32KB.
 */

  function describe_c(rc in out sys_refcursor)
  return xmltype as

  begin
    if nvl(describe_impl, 'C2') = 'C2' then
      /*
       * Pass REF CURSOR directly even though this is not possible according to
       * documentation. Works on Oracle 9.2 and newer.
       */
      xml := ref_cursor_descriptor_c2(rc);
    elsif describe_impl = 'C' then
      /*
       * Necessary to make it work as we cannot pass on REF CURSORs to external
       * procedures according to documentation.
       */
      rcn := null;
      dbms_odci.saverefcursor(rc, rcn);
      xml := ref_cursor_descriptor_c(rcn);
      dbms_odci.restorerefcursor(rc, rcn);
    end if;

    if xml is not null then
      return xmltype(xml);
    else
      return null;
    end if;
  exception
    when others then
      if sqlcode in (-31011, -19202, -1001) then
        dbms_output.put_line(xml);
        raise;
      else
        raise;
      end if;
  end describe_c;

/**
 * Builds the declaration for given column. ANYDATA is used for REF CURSOR,
 * which makes it possible to fetch the REF CURSOR into an object type attribute
 * of data type ANYDATA (REF CURSOR is not supported for object type
 * attributes).
 * @param   name    Column name.
 * @param   type_code
 *                  "Uniform" type code.
 * @param   charset_form
 *                  Character set form: 1: Database CS, 2: National CS.
 * @param   length_semantics
 *                  Length semantics: 'BYTE' or 'CHAR'.
 * @param   precision
 *                  Precision.
 * @param   scale   Scale.
 * @param   length  Length.
 * @param   owner   Datatype owner (for object types, incl. collections).
 * @param   type_name
 *                  Datatype name (for object types, incl. collections).
 * @return  Declaration of the form '<name> <datatype>'.
 */

  function get_declaration(
    name in varchar2,
    type_code in pls_integer,
    charset_form in pls_integer,
    length_semantics in varchar2,
    precision in pls_integer,
    scale in pls_integer,
    length in pls_integer,
    owner in varchar2,
    type_name in varchar2
  )
  return varchar2 as

    declaration varchar2(1000);

  begin
    case type_code
      when type_codes.tc_varchar2 then
        declaration :=
            case when charset_form = 2 then 'N' else '' end ||
            'VARCHAR2(' || rtrim(length || ' ' || length_semantics) || ')';
      when type_codes.tc_number then
        -- TODO: Special cases.
        if precision = 0 /*and scale = -127*/ then
          declaration := 'NUMBER';
        else
          declaration := 'NUMBER(' || precision;
          if scale != 0 then
            declaration := declaration || ', ' || scale;
          end if;
          declaration := declaration || ')';
        end if;
      when type_codes.tc_long then
        declaration := 'LONG';
      when type_codes.tc_date then
        declaration := 'DATE';
      when type_codes.tc_binary_float then
        declaration := 'BINARY_FLOAT';
      when type_codes.tc_binary_double then
        declaration := 'BINARY_DOUBLE';
      when type_codes.tc_timestamp then
        declaration := 'TIMESTAMP(' || precision || ')';
      when type_codes.tc_timestamp_tz then
        declaration := 'TIMESTAMP(' || precision || ') WITH TIME ZONE';
      when type_codes.tc_timestamp_ltz then
        declaration := 'TIMESTAMP(' || precision || ') WITH LOCAL TIME ZONE';
      when type_codes.tc_interval_ym then
        declaration := 'INTERVAL YEAR(' || precision || ') TO MONTH';
      when type_codes.tc_interval_ds then
        declaration :=
            'INTERVAL DAY(' || precision || ') ' ||
            'TO SECOND(' || scale || ')';
      when type_codes.tc_raw then
        declaration := 'RAW(' || length || ')';
      when type_codes.tc_long_raw then
        declaration := 'LONG RAW';
      when type_codes.tc_rowid then
        -- Map ROWID to VARCHAR2(18), ROWID object type attributes not allowed.
        declaration := 'VARCHAR2(18)';
      when type_codes.tc_urowid then
        -- Map UROWID to VARCHAR2(18), UROWID object type attributes not allowed.
        declaration := 'VARCHAR2(18)';
      when type_codes.tc_char then
        declaration :=
            case when charset_form = 2 then 'N' else '' end ||
            'CHAR(' || length || ' ' || length_semantics || ')';
      when type_codes.tc_clob then
        declaration :=
            case when charset_form = 2 then 'N' else '' end || 'CLOB';
      when type_codes.tc_blob then
        declaration := 'BLOB';
      when type_codes.tc_bfile then
        declaration := 'BFILE';
      when type_codes.tc_object then
        declaration :=
            quote_identifier(owner) ||
            '.' ||
            quote_identifier(type_name);
      when type_codes.tc_ref then
        declaration :=
            'REF ' ||
            quote_identifier(owner) ||
            '.' ||
            quote_identifier(type_name);
      when type_codes.tc_ref_cursor then
        -- REF CURSOR, to be saved in ANYDATA.
        declaration := 'SYS.ANYDATA';
      else
        raise_application_error(
          -20003,
          'ref_cursor_descriptor.get_declaration: ' || type_code || ': ' ||
              'Unknown datatype'
        );
    end case;

    return quote_identifier(name) || ' ' || declaration;
  end get_declaration;

/**
 * Adds DECLARATION element for each column.
 * @param   xml     Description XML.
 * @return  Description XML with DECLARATION element for each column.
 */

  function add_declarations(xml in xmltype)
  return xmltype as

    declaration varchar2(200);
    result xmltype;

    doc dbms_xmldom.domdocument;
    row dbms_xmldom.domnode;
    rows dbms_xmldom.domnodelist;
    decl_element dbms_xmldom.domelement;
    decl_text_element dbms_xmldom.domtext;
    decl_node dbms_xmldom.domnode;
    decl_text_node dbms_xmldom.domnode;
    root dbms_xmldom.domelement;

  begin
    doc := dbms_xmldom.newdomdocument(xml);
    root := dbms_xmldom.getdocumentelement(doc);
    rows := dbms_xmldom.getchildrenbytagname(root, 'ROW');

    for r in (
          select extractvalue(value(r), '//ID') id,
                 extractvalue(value(r), '//NAME') name,
                 extractvalue(value(r), '//TYPE_CODE') type_code,
                 extractvalue(value(r), '//CHARSET_FORM') charset_form,
                 extractvalue(value(r), '//LENGTH') length,
                 extractvalue(value(r), '//LENGTH_SEMANTICS') length_semantics,
                 extractvalue(value(r), '//PRECISION') precision,
                 extractvalue(value(r), '//SCALE') scale,
                 extractvalue(value(r), '//OWNER') OWNER,
                 extractvalue(value(r), '//TYPE_NAME') type_name
          from   table(
                   xmlsequence(
                     extract(xml, '/ROWSET/ROW')
                   )
                 ) r
        ) loop
      -- Get declaration.
      declaration := get_declaration(
        r.name,
        r.type_code,
        r.charset_form,
        r.length_semantics,
        r.precision,
        r.scale,
        r.length,
        r.owner,
        r.type_name
      );

      row := dbms_xmldom.item(rows, r.id - 1);
      -- New element.
      decl_element := dbms_xmldom.createelement(doc, 'DECLARATION');
      decl_node := dbms_xmldom.makenode(decl_element);
      -- New text node.
      decl_text_element := dbms_xmldom.createtextnode(doc, declaration);
      -- Append text node to element node.
      decl_text_node := dbms_xmldom.appendchild(
        decl_node,
        dbms_xmldom.makenode(decl_text_element)
      );
      -- Insert new element in document.
      row := dbms_xmldom.appendchild(row, decl_node);

      dbms_xmldom.freenode(decl_text_node);
      dbms_xmldom.freenode(decl_node);

/* Requires 10.2 or newer.
      -- Append new DECLARATION element to column with given ID.
      result := result.appendchildxml(
        '/ROWSET/ROW[' || r.id || ']',
        xmltype('<DECLARATION>' || declaration || '</DECLARATION>')
      );
 */
    end loop;

    result := dbms_xmldom.getxmltype(doc);
    dbms_xmldom.freedocument(doc);

    return result;
  end add_declarations;

/**
 * Double quotes given identifier if it's necessary.
 * @param   identifier
 *                  Identifier.
 * @return  Identifier, potentially double quoted.
 */

  function quote_identifier(identifier in varchar2)
  return varchar2 as

  begin
    if identifier != upper(identifier) or
        ascii(substr(identifier, 1, 1)) not between ascii('A') and ascii('Z') or
        translate(identifier, ' ' || identifier_chars, ' ') is not null then
      return '"' || identifier || '"';
    end if;

    return identifier;
  end quote_identifier;

begin
  if portable.get_major_minor_version = 10.1 then
    /*
     * Necessary to make it work for Oracle 10.1 as Oracle on some platforms
     * will transfer the ResultSet as null or empty.
     */
    describe_impl := 'J';
  end if;
end ref_cursor_descriptor;
/

Installation

You need to provide the following grants to the schema in which you’re going to install the code:

grant create library to &&1;
grant execute on sys.dbms_system to &&1;
grant create procedure to &&1;

An alternative to the CREATE LIBRARY privilege is to let your DBA create the library and grant EXECUTE privilege on the library to your schema.

The following installation script is assumed to run within SQL*Plus (install.sql). You should run this again if you migrate this solution to another database version or edition as the foundation for the installation discovered whilst installing could potentially have changed:

-- Installation script assmed to be run with SQL*Plus connected to the schema
-- that will own the database objects. You will need write access to the current
-- directory as dynamic scripts are created during the installation.
-- Feel free to use at your own risk.
-- @version   $Revision: 1 $
-- @author    Finn Ellebaek Nielsen, Ellebaek Consulting ApS.

set serveroutput on format truncated
set trimspool on
set linesize 100

@portable.pks
@portable.pkb
@@type_codes.pks

prompt Creating library...

declare
  oracle_home varchar2(255);
  file_path varchar2(255);
  refcurdesc varchar2(14);
  path_separator char(1);
  bin varchar2(3);
  ddl varchar2(1000);
begin
  sys.dbms_system.get_env('ORACLE_HOME', oracle_home);

  if lower(dbms_utility.port_string) like '%win%' then
    refcurdesc := 'refcurdesc.dll';
    path_separator := '\';
    bin := 'BIN';
  else
    refcurdesc := 'refcurdesc.so';
    path_separator := '/';
    bin := 'bin';
  end if;
  file_path := oracle_home || path_separator || bin;

  begin
    ddl :=
        'create or replace library refcurdesc as ''' ||
            file_path || path_separator || refcurdesc ||
            '''';
    execute immediate ddl;

    dbms_output.put_line(
      'Library REFCURDESC created for "' ||
          file_path || path_separator || refcurdesc ||
          '".'
    );

    dbms_output.put_line('Please place ' || refcurdesc || ' in "' || file_path || '".');
  exception
    when others then
      dbms_output.put_line(ddl || ': ' || sqlerrm);
      raise;
  end;
end;
/

prompt done

prompt Creating PL/SQL stored procedure

set feedback off
spool install_plsql.sql

declare
  ddl varchar2(32767);
begin
  if portable.get_major_version >= 11 then
    dbms_output.put_line('@@ref_cursor_descriptor_plsql.fnc');
  else
    dbms_output.put_line('prompt Older than Oracle 11.1');
    dbms_output.put_line('');
    dbms_output.put_line('create or replace function ref_cursor_descriptor_plsql(rc in out sys_refcursor)');
    dbms_output.put_line('return varchar2 as');
    dbms_output.put_line('begin');
    dbms_output.put_line('  raise_application_error(-20002, ''Requires Oracle 11.1 or newer'');');
    dbms_output.put_line('end;');
    dbms_output.put_line('/');
    dbms_output.put_line('');
    dbms_output.put_line('show err');
  end if;
end;
/

spool off
set feedback 1

@@install_plsql.sql

prompt done

prompt Creating Java stored procedure

set feedback off
spool install_java.sql

declare
  ddl varchar2(32767);
begin
  if portable.is_option_enabled('Java') then
    dbms_output.put_line('set scan off');
    dbms_output.put_line('');
    dbms_output.put_line('@@RefCursorDescriptor.sql');
    dbms_output.put_line('');
    dbms_output.put_line('set scan on');
    dbms_output.put_line('');
    dbms_output.put_line('@@ref_cursor_descriptor_java.fnc');
    dbms_output.put_line('@@ref_cursor_descriptor_java2.fnc');
  else
    dbms_output.put_line('prompt Java option not enabled');
    dbms_output.put_line('create or replace function ref_cursor_descriptor_java(rcn in pls_integer)');
    dbms_output.put_line('return varchar2 as');
    dbms_output.put_line('begin');
    dbms_output.put_line('  raise_application_error(-20003, ''Java option not enabled'');');
    dbms_output.put_line('end;');
    dbms_output.put_line('/');
    dbms_output.put_line('');
    dbms_output.put_line('show err');
    dbms_output.put_line('');
    dbms_output.put_line('create or replace function ref_cursor_descriptor_java2(rc in sys_refcursor)');
    dbms_output.put_line('return varchar2 as');
    dbms_output.put_line('begin');
    dbms_output.put_line('  raise_application_error(-20003, ''Java option not enabled'');');
    dbms_output.put_line('end;');
    dbms_output.put_line('/');
    dbms_output.put_line('');
    dbms_output.put_line('show err');
  end if;
end;
/

spool off
set feedback 1

@@install_java.sql

prompt done

prompt Creating C external procedure

@@ref_cursor_descriptor_c.fnc
@@ref_cursor_descriptor_c2.fnc

prompt done

@@ref_cursor_descriptor.pks
@@ref_cursor_descriptor.pkb

@dbms_output_put_line.prc

Example

Here’s an example:

set long 50000

variable xml clob

prompt SELECT * FROM EMP

declare
  rc sys_refcursor;
begin
  open rc for
  select *
  from   emp;

  :xml := ref_cursor_descriptor.describe(rc).getclobval;
end;
/

print xml

which could produce the following output:

<ROWSET generator="C">
  <ROW>
    <ID>1</ID>
    <NAME>EMPNO</NAME>
    <TYPE_CODE>2</TYPE_CODE>
    <NATIVE_TYPE_CODE>2</NATIVE_TYPE_CODE>
    <PRECISION>4</PRECISION>
    <SCALE>0</SCALE>
    <DECLARATION>EMPNO NUMBER(4)</DECLARATION>
  </ROW>
  <ROW>
    <ID>2</ID>
    <NAME>ENAME</NAME>
    <TYPE_CODE>1</TYPE_CODE>
    <NATIVE_TYPE_CODE>1</NATIVE_TYPE_CODE>
    <CHARSET_FORM>1</CHARSET_FORM>
    <LENGTH>10</LENGTH>
    <LENGTH_SEMANTICS>BYTE</LENGTH_SEMANTICS>
    <DECLARATION>ENAME VARCHAR2(10 BYTE)</DECLARATION>
  </ROW>
  <ROW>
    <ID>3</ID>
    <NAME>JOB</NAME>
    <TYPE_CODE>1</TYPE_CODE>
    <NATIVE_TYPE_CODE>1</NATIVE_TYPE_CODE>
    <CHARSET_FORM>1</CHARSET_FORM>
    <LENGTH>9</LENGTH>
    <LENGTH_SEMANTICS>BYTE</LENGTH_SEMANTICS>
    <DECLARATION>JOB VARCHAR2(9 BYTE)</DECLARATION>
  </ROW>
  <ROW>
    <ID>4</ID>
    <NAME>MGR</NAME>
    <TYPE_CODE>2</TYPE_CODE>
    <NATIVE_TYPE_CODE>2</NATIVE_TYPE_CODE>
    <PRECISION>4</PRECISION>
    <SCALE>0</SCALE>
    <DECLARATION>MGR NUMBER(4)</DECLARATION>
  </ROW>
  <ROW>
    <ID>5</ID>
    <NAME>HIREDATE</NAME>
    <TYPE_CODE>12</TYPE_CODE>
    <NATIVE_TYPE_CODE>12</NATIVE_TYPE_CODE>
    <DECLARATION>HIREDATE DATE</DECLARATION>
  </ROW>
  <ROW>
    <ID>6</ID>
    <NAME>SAL</NAME>
    <TYPE_CODE>2</TYPE_CODE>
    <NATIVE_TYPE_CODE>2</NATIVE_TYPE_CODE>
    <PRECISION>7</PRECISION>
    <SCALE>2</SCALE>
    <DECLARATION>SAL NUMBER(7, 2)</DECLARATION>
  </ROW>
  <ROW>
    <ID>7</ID>
    <NAME>COMM</NAME>
    <TYPE_CODE>2</TYPE_CODE>
    <NATIVE_TYPE_CODE>2</NATIVE_TYPE_CODE>
    <PRECISION>7</PRECISION>
    <SCALE>2</SCALE>
    <DECLARATION>COMM NUMBER(7, 2)</DECLARATION>
  </ROW>
  <ROW>
    <ID>8</ID>
    <NAME>DEPTNO</NAME>
    <TYPE_CODE>2</TYPE_CODE>
    <NATIVE_TYPE_CODE>2</NATIVE_TYPE_CODE>
    <PRECISION>2</PRECISION>
    <SCALE>0</SCALE>
    <DECLARATION>DEPTNO NUMBER(2)</DECLARATION>
  </ROW>
</ROWSET>

Subtleties

I’ve found the following subtleties whilst developing the code:

• DBMS_SQL.DESCRIBE_COLUMN% always uses byte length semantics for data types CHAR and VARCHAR2, no matter how they were declared. Eg, if you have declared a table column with VARCHAR2(10 CHAR) in a database with characterset AL32UTF8 this will be reported as VARCHAR2(40 BYTE).
• Inversely, java.sql.ResultSetMetaData always uses character length semantics.
• It’s not possible to distinguish ROWID and UROWID in Java and C so both are mapped to UROWID.

Known Issues

• If you convert a REF CURSOR to a DBMS_SQL cursor and later invoke DBMS_XMLGEN.NEWCONTEXT on that same cursor (after you’ve converted it back to a REF CURSOR) you may encounter the following Oracle errors: ORA-01001: invalid cursor, ORA-24338: statement handle not executed and ORA-00600: internal error code, arguments: [psdmsc: psdinvdef#1]. I’ve only encountered these errors in Oracle 11.1.0.6.0 as they seems to have been fixed in 11.1.0.7.0 and 11.2.0.1.0.
• If you describe a REF CURSOR and then call either Oracle code that is implemented in C (not PL/SQL) or another external procedure written in C you’ll probably get Oracle errors like ORA-24374: define not done before fetch or execute and fetch. The problem seems to be that something on the REF CURSOR OCI statement handle is marked as processed when we obtain it in our C external procedure, which means that eg DBMS_XMLGEN doesn’t define the columns for its fetch, which results in an error. The workaround is to use the direct C implementation (not the indirect one using DBMS_ODCI) or fetch the REF CURSOR to either memory or a table, open a new REF CURSOR on that copy and then invoke DBMS_XMLGEN on the copy. I’ll come back to this in a future blog post.

Supporting Oracle Database 9i Release 2

The techniques in REF_CURSOR_COPY and the related blog can be made to work for Oracle 9.2 as well but you would need to remove calls to DBMS_ODCI.SAVE-/RESTOREREFCURSOR and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE as these procedures are not available in Oracle 9.2.
I haven’t used Conditional Compilation techniques for this as it would exclude Oracle versions 10.1.0.2.0 and 10.1.0.3.0.

Improvements

The following improvements could be implemented:

• Lift the 32KB limitation by using a CLOB instead of a VARCHAR2.
• Make the C implementation thread safe.

Conclusion

The code shown here has been fairly well tested with the following Oracle versions and editions:

• Oracle Database 10g Release 1 10.1.0.2.0 Personal Edition on Windows XP SP3.
• Oracle Database 10g Release 1 10.1.0.3.0 Enterprise Edition on Intel Solaris 10.
• Oracle Database 10g Release 2 10.2.0.1.0 Express Edition on Windows XP SP3.
• Oracle Database 10g Release 2 10.2.0.1.0 Express Edition on Red Hat Enterprise Linux 5.3.
• Oracle Database 10g Release 2 10.2.0.2.0 Enterprise Edition on Intel Solaris 10.
• Oracle Database 11g Release 1 11.1.0.6.0 Personal Edition on Windows XP SP3.
• Oracle Database 11g Release 1 11.1.0.7.0 Personal Edition on Windows XP SP3.
• Oracle Database 11g Release 1 11.1.0.6.0 Enterprise Edition on SPARC Solaris 10.
• Oracle Database 11g Release 2 11.2.0.1.0 Personal Edition on Windows 7.

Feel free to use the code at your own risk. I welcome your feedback and suggestions for improvements but the code as such is not supported.

Source Code

You can download the source code here.