Converting a LONG Column to a CLOB on the fly

Background

The arcane LONG datatype has as you’re aware of many intrinsic limitations but unfortunately Oracle still uses quite a few LONG columns in the data dictionary. It’s beyond me why these haven’t been converted to CLOB a very long time ago (when Oracle deprecated usage of LONG). At the same time, Oracle only provides the following means of converting these LONG columns to eg CLOB:

1. TO_LOB: A function that converts a LONG column to a CLOB. Can only be used in a SELECT list of a subquery in an INSERT statement. This means that the function is only useful if you’re converting the underlying table definition.
2. ALTER TABLE <table_name> MODIFY <long_column_name> CLOB: This converts the column datatype and the data as well.
3. Use DBMS_REDEFINITION to redefine the column datatype.
4. Use Oracle Data Pump to convert the column datatype.
5. Use CAST function to cast from LONG to CLOB: Unfortunately, CAST doesn’t support LONG.
6. Write a PL/SQL function that performs a SELECT ... INTO l FROM user_views, where l is a PL/SQL variable of type LONG. However, in PL/SQL, a LONG variable can only hold up to 32,760 characters (yes, not 32,767, ie different from VARCHAR2) so this will only solve the problem for small to medium sized views.
7. Use dynamic SQL with DBMS_SQL.COLUMN_VALUE_LONG to access the LONG piecewise.

Problem

Imagine the following scenario:

• You need to access the source of a given view.
• USER_VIEWS.TEXT contains the view source but it is a column with datatype LONG so very limited in use.
• Using DBMS_METADATA to obtain the source is not an option for various reasons, eg performance, practicality, etc.

Solution

Seen that we need to access the data from the view, we cannot use TO_LOB or any of the other solutions that alter the underlying table column definition. However, the DBMS_SQL.COLUMN_VALUE_LONG function comes to the rescue as this allows us to fetch the LONG data piecewise and construct a CLOB with the same data. This obviously means that we have to use dynamic SQL for the query of the table/view we’re trying to convert the LONG column for.

We have two different methods of applying dynamic SQL to this problem:

1. Dynamic SQL for just the LONG column. We need to write a function that receives the primary key value(s) of the underlying table/view (could be ROWID if a table) as input parameter(s), builds a SELECT statement for the underlying table/view for the LONG column using bind variable(s) for the primary key value(s), does the DBMS_SQL magic and uses the DBMS_SQL.COLUMN_VALUE_LONG function. For scalability, the solution should keep a collection of parsed statements and re-use those without re-parsing.
2. Dynamic SQL for the whole underlying table/view generated and called in a pipelined table function that utilizes object types.

For this blog post I’ll use solution 2, for which I’ll demonstrate the following:

1. Create a standalone function with “LONG-to-CLOB” functionality.
2. Create an object type with attributes corresponding to USER_VIEWS. This object type will have member methods used with DBMS_SQL.
3. Create an object type collection based on the object type from 2.
4. Create a standalone function that takes an optional argument for a WHERE clause used against USER_VIEWS, using a combination of DBMS_SQL and 1., 2., and 3. above. This method is a pipelined table function.
5. Optionally create a view on top of the standalone function from 4.

You can apply the structure of this solution in use cases where you need to access a LONG value in a table/view as a CLOB, without converting the underlying persistent column: The standalone function from 1. above is generic — items 2. through 5. are specific to the underlying table/view.

We’ll go through the different parts in the following sections.

“LONG-to-CLOB” Function

This is the function that converts a LONG column to a CLOB value through a DBMS_SQL cursor that has been parsed, prepared (given column “defined” with DBMS_SQL.DEFINE_COLUMN_LONG) and executed:

create or replace function long_to_clob(
  dbms_sql_cursor in integer,
  col_id in integer
)
return clob as

/**
 * Fetches LONG column value and converts it to a CLOB.
 * @param   dbms_sql_cursor
 *                  DBMS_SQL cursor parsed, prepared (given column "defined"
 *                  with DBMS_SQL.DEFINE_COLUMN_LONG) and executed.
 * @param   col_id  Column ID.
 * @return  LONG column value as a CLOB.
 */

  long_val long;
  long_len integer;
  buf_len  integer := 32760;
  cur_pos  number := 0;

  result   clob;

begin
  -- Create CLOB.
  dbms_lob.createtemporary(result, false, dbms_lob.call);

  -- Piecewise fetching of the LONG column, appending to the CLOB.
  loop
    dbms_sql.column_value_long(
      dbms_sql_cursor,
      col_id,
      buf_len,
      cur_pos,
      long_val,
      long_len
    );
    exit when long_len = 0;
    dbms_lob.append(result, long_val);
    cur_pos := cur_pos + long_len;
  end loop;

  return result;
end long_to_clob;
/

Object Type

In Oracle 11.2.0.1.0, USER_VIEWS has the following columns:

SQL> desc user_views
 Name             Null?    Type
 ---------------- -------- --------------
 VIEW_NAME        NOT NULL VARCHAR2(30)
 TEXT_LENGTH               NUMBER
 TEXT                      LONG
 TYPE_TEXT_LENGTH          NUMBER
 TYPE_TEXT                 VARCHAR2(4000)
 OID_TEXT_LENGTH           NUMBER
 OID_TEXT                  VARCHAR2(4000)
 VIEW_TYPE_OWNER           VARCHAR2(30)
 VIEW_TYPE                 VARCHAR2(30)
 SUPERVIEW_NAME            VARCHAR2(30)
 EDITIONING_VIEW           VARCHAR2(1)
 READ_ONLY                 VARCHAR2(1)

which means that we could create our object type like the following (notice how TEXT is represented by a CLOB instead of the original LONG):

create or replace type user_views_t as object (
/**
 * Object type representing columns in data dictionary view USER_VIEWS, with the
 * TEXT column represented by a CLOB instead of a LONG.
 */

  view_name        varchar2(30),
  text_length      number,
  -- CLOB instead of LONG.
  text             clob,
  type_text_length number,
  type_text        varchar2(4000),
  oid_text_length  number,
  oid_text         varchar2(4000),
  view_type_owner  varchar2(30),
  view_type        varchar2(30),
  superview_name   varchar2(30),
  editioning_view  varchar2(1),
  read_only        varchar2(1),

  constructor function user_views_t
  return self as result,
  constructor function user_views_t(dbms_sql_cursor in integer)
  return self as result,

  member procedure define_columns(dbms_sql_cursor in integer)
);
/

The object type has two constructors and one member function. The member function is used to define the columns for DBMS_SQL and in order to be able to use the attributes, this has to be a member function (working on an object type instance as opposed to a static function) and the first constructor is used to create such a dummy instance, with all the attributes set to NULL. The second constructor sets all attributes to corresponding column values in a given fetched DBMS_SQL cursor.

This is the implementation of the object type body:

create or replace type body user_views_t as
/**
 * Constructor. Sets all attributes to NULL.
 * @return  New object type instance.
 */

  constructor function user_views_t
  return self as result as

  begin
    return;
  end user_views_t;

/**
 * Constructor. Sets all attributes to corresponding column values in fetched
 * DBMS_SQL cursor.
 * @param   dbms_sql_cursor
 *                  Executed and fetched DBMS_SQL cursor on a query from
 *                  USER_VIEWS.
 * @return  New object type instance.
 */

  constructor function user_views_t(dbms_sql_cursor in integer)
  return self as result as

  begin
    dbms_sql.column_value(dbms_sql_cursor, 01, view_name);
    dbms_sql.column_value(dbms_sql_cursor, 02, text_length);
    -- Convert LONG to CLOB.
    text := long_to_clob(dbms_sql_cursor,  03);
    dbms_sql.column_value(dbms_sql_cursor, 04, type_text_length);
    dbms_sql.column_value(dbms_sql_cursor, 05, type_text);

    dbms_sql.column_value(dbms_sql_cursor, 06, oid_text_length);
    dbms_sql.column_value(dbms_sql_cursor, 07, oid_text);
    dbms_sql.column_value(dbms_sql_cursor, 08, view_type_owner);
    dbms_sql.column_value(dbms_sql_cursor, 09, view_type);
    dbms_sql.column_value(dbms_sql_cursor, 10, superview_name);

    dbms_sql.column_value(dbms_sql_cursor, 11, editioning_view);
    dbms_sql.column_value(dbms_sql_cursor, 12, read_only);

    return;
  end user_views_t;

/**
 * Defines all columns in DBMS_SQL cursor.
 * @param   Parsed DBMS_SQL cursor on a query from USER_VIEWS.
 */

  member procedure define_columns(dbms_sql_cursor in integer) as

  begin
    dbms_sql.define_column(dbms_sql_cursor, 01, view_name, 30);
    dbms_sql.define_column(dbms_sql_cursor, 02, text_length);
    -- LONG column.
    dbms_sql.define_column_long(dbms_sql_cursor, 03);
    dbms_sql.define_column(dbms_sql_cursor, 04, type_text_length);
    dbms_sql.define_column(dbms_sql_cursor, 05, type_text, 4000);

    dbms_sql.define_column(dbms_sql_cursor, 06, oid_text_length);
    dbms_sql.define_column(dbms_sql_cursor, 07, oid_text, 4000);
    dbms_sql.define_column(dbms_sql_cursor, 08, view_type_owner, 30);
    dbms_sql.define_column(dbms_sql_cursor, 09, view_type, 30);
    dbms_sql.define_column(dbms_sql_cursor, 10, superview_name, 30);

    dbms_sql.define_column(dbms_sql_cursor, 11, editioning_view, 1);
    dbms_sql.define_column(dbms_sql_cursor, 12, read_only, 1);
  end define_columns;
end;
/

Notice how the second constructor uses our LONG_TO_CLOB function.

The process of matching the original table/view columns into attributes and handling of these in one of the constructors and the DEFINE_COLUMNS method is tedious and error prone. If you need to do this often you should consider writing a generator that generates the object type specification and body based on the definition of a given table/view.

Object Type Collection

This is the implementation of the object type collection, using a nested table:

create or replace type user_views_c as
table of user_views_t;
/

We need this object type collection for the pipelined table function as this pipes back a collection of object type instances back to the SQL engine — namely, one object type instance for each row in USER_VIEWS this function finds.

Pipelined Table Function

This is the implementation of the standalone pipelined table function that takes an optional parameter to be matched against the VIEW_NAME column in a LIKE expression and uses DBMS_SQL to parse the query from USER_VIEWS, uses the USER_VIEWS_T and USER_VIEWS_C object types (which in turn calls LONG_TO_CLOB) and pipes the rows back to the SQL engine (type PTF suffix refers to “Pipelined Table Function”):

create or replace function user_views_ptf(
  view_name_like in varchar2 := '%'
)
return user_views_c pipelined as

/**
 * Gets collection of user views representing rows in USER_VIEWS. Resolved
 * through a dynamic SQL query against USER_VIEWS and the TEXT column is
 * converted from a LONG to a CLOB.
 * @param   view_name_like
 *                  LIKE expression used in a WHERE clause predicate against
 *                  USER_VIEWS.VIEW_NAME. Default '%', ie all.
 * @return  Collection that can be used in a FROM clause with a TABLE() cast.
 */

  query           varchar2(200);
  dbms_sql_cursor binary_integer;
  n               pls_integer;
  each            user_views_t;

begin
  query :=
     'select * ' ||
     'from   user_views uv ' ||
     'where  uv.view_name like :view_name_like';

  -- Create cursor, parse and bind.
  dbms_sql_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(dbms_sql_cursor, query, dbms_sql.native);
  dbms_sql.bind_variable(dbms_sql_cursor, 'view_name_like', view_name_like);

  -- Define columns through dummy object type instance.
  each := user_views_t();
  each.define_columns(dbms_sql_cursor);

  -- Execute.
  n := dbms_sql.execute(dbms_sql_cursor);

  -- Fetch all rows, pipe each back.
  while dbms_sql.fetch_rows(dbms_sql_cursor) > 0 loop
    each := user_views_t(dbms_sql_cursor);

    pipe row(each);
  end loop;

  dbms_sql.close_cursor(dbms_sql_cursor);
exception
  when others then
    dbms_output.put_line('long_to_clob: ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
    if dbms_sql.is_open(dbms_sql_cursor) then
      dbms_sql.close_cursor(dbms_sql_cursor);
    end if;

    raise;
end user_views_ptf;
/

It’s inconvenient that we can’t create this function as a member function on USER_VIEWS_T where it really belongs. However this is not possible because that would introduce a cyclical dependency between USER_VIEWS_T and USER_VIEWS_C, which is not allowed (even using forward object type declarations).

SELECT

You can select from the pipelined table function with a TABLE expression, such as:

select *
from   table(user_views_ptf('V%'));

Installation

You need to install the solution objects in the following order:

1. LONG_TO_CLOB function.
2. USER_VIEWS_T object type specification.
3. USER_VIEWS_T object type body.
4. USER_VIEWS_C object type collection.
5. USER_VIEWS_PTF function.

Test

A small test case:

create or replace view v1 as
select 1 n,
       'abc' vc2,
       sysdate d,
       systimestamp(6) t
from   dual;

SQL> select view_name,
  2         text
  3  from   table(user_views_ptf('V%'));

VIEW_NAME                      TEXT
------------------------------ ------------------------
V1                             select 1 n,
                                      'abc' vc2,
                                      sysdate d,
                                      systimestamp(6) t
                               from   dual

View

You can optionally create a view on top of the pipelined table function. In this case, you cannot push the LIKE expression into the argument to the table function so the optimizer has no alternative to perform a full table scan on USER_VIEWS and then a match on the returned rows on whatever predicate the view is used with.

Maintenance

Every time you need to convert a LONG column in a table/view to a CLOB you need to do the following:

1. Create the object type with attributes corresponding to the columns of the table/view you need to obtain data for. The object type needs two constructors and the member function DEFINE_COLUMNS as for the USER_VIEWS_T object type.
2. Create the object type collection for 1.
3. Create the standalone pipelined table function.
4. Optionally create a view on top of 3.

If you prefer to use packages over standalone functions, you could bundle them up in a package called LONG_TO_CLOB and rename the function LONG_TO_CLOB to TO_CLOB.