Converting a LONG Column to a CLOB on the fly


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.


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.


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;

  -- Create CLOB.
  dbms_lob.createtemporary(result, false,;

  -- Piecewise fetching of the LONG column, appending to the CLOB.
    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, USER_VIEWS has the following columns:

SQL> desc user_views
 Name             Null?    Type
 ---------------- -------- --------------
 TEXT_LENGTH               NUMBER
 TEXT                      LONG
 TYPE_TEXT                 VARCHAR2(4000)
 OID_TEXT                  VARCHAR2(4000)
 VIEW_TYPE                 VARCHAR2(30)
 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

  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

    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);

  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

    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;

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;

  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();

  -- 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;

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

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).


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

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


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.


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


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.


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.

About ellebaek
Sun Certified Programmer for Java 5 (SCJP). Oracle Advanced PL/SQL Developer Certified Professional (OCP Advanced PL/SQL). ISTQB Certified Tester Foundation Level (ISTQB CTFL).

9 Responses to Converting a LONG Column to a CLOB on the fly

  1. Pingback: Converting Between Oracle Data and XML « Finn Ellebaek Nielsen's Blog

  2. Dave says:

    Or you know…Oracle could stop being lazy and update all their deprecated data types. You would think after 5-10 years it would be on the client to update their code and not the db vendor.

  3. Lee says:

    Thanks – very helpful. I’ve tweaked it a bit to allow NULLs to propagate through, though, as I wanted to use it with a nullable LONG:

    dbms_sql.column_value_long(dbms_sql_cursor, col_id, buf_len, cur_pos, long_val, long_len);

    IF long_val IS NULL THEN
    END IF;

    dbms_lob.CreateTemporary(result, false,;

    WHILE long_len > 0 LOOP
    dbms_lob.Append(result, long_val);

    cur_pos := cur_pos + long_len;

    dbms_sql.column_value_long(dbms_sql_cursor, col_id, buf_len, cur_pos, long_val, long_len);

    RETURN result;

  4. Mehmet Bekir Birden says:

    Damn you Oracle! Calling people not to use LONG but using yourself in crucial places? Look what kind of bizarre things have to be done to workaround it!

  5. I have a very vexing problem with a LONG column in a vendor’s database table.

    I am building a data warehouse, and I need to create a view to convert a LONG to a CLOB on the fly. I thought I found the solution here, but, unfortunately, I’ve run into a snag.

    My table has three keys and a LONG value:

    SQL> desc req_task_list
    Name Null? Type
    —————————- ——– ————-

    The keys have the following three combinations
    REQ_NUMBER, null, null
    null, null, WO_NUMBER

    I wrote the Type to return all the columns, and the Pipeline Function to not take any arguments. My ptf query is straight-forward:

    query :=
    ‘select tl.req_number, tl.phase, tl.wo_number, tl.task_list from req_task_list tl ‘ ||
    ‘where rownum < 101 ';

    When I run a view query on the collection, for example

    select * from table(req_task_list_t);

    it works fine and returns all 100 rows of data collected back in the ptf.

    But I have over 300,000 rows in my source table, not a mere 100, and the collection, which lives in memory, can't handle that many rows in a reasonable amount of time.

    I am considering rewriting my view query to incorporate the source table like this

    select r.req_number, r.phase, r.wo_number,
    (select tl.* from table(req_task_list_t(r.req_number, r.phase, r.wo_number)) task_list
    from req r;

    But this doesn't seen as elegant as your solution for USER_VIEWS. Am I overthinking this?

  6. ellebaek says:

    I’m not sure I see the benefit from the rewrite of the query. Have you looked into whether your pipelined table function is called more than you expect (in your case more than once)? Unfortunately, this is often the case. Sometimes hinting can improve on this. It’s a shame that the function result cache can’t be used with pipelined table functions and object types.

    The entire collection does not live in memory. That’s the point with pipelined table functions (the following taken from the Oracle docs):

    “Pipelining enables rows to be returned iteratively, as they are produced. This also reduces the memory that a table function requires, as the object cache does not need to materialize the entire collection.”

    Also, don’t you need to pass down some kind of WHERE clause against your underlying table? Do you need all 300,000 rows every time you select from the view? If not, how often does data in the underlying table change? If not very often, it would probably be a better idea to hold a persistent CLOB version of the LONG column somewhere.

  7. paulzipblog says:

    dbms_sql.column_value_long returns a buffer amount to a varchar2, not a long, so your long_val variable isn’t correct it should be a varchar2.

    • ellebaek says:

      Excellent point, well spotted! I guess I’ve been blinded by the fact that most DBMS_SQL.COLUMN_VALUE_ procedures return the value in the appropriate datatype.

      However, the implicit datatype conversions in PL/SQL saves the solution. So when DBMS_SQL.COLUMN_VALUE_LONG returns it implicitly converts the VARCHAR2 value to a LONG value and saves it in the LONG_VAL local variable. And when DBMS_LOB.APPEND is called, the LONG_VAL local variable is implicitly converted to a CLOB before the call is carried out.

      Here’s a test in Oracle in the SYS schema (pardon the useless formatting that WordPress allows for comments):

      select uvp.view_name,
      length(text) text_length2,
      abs(uvp.text_length - length(text)) text_length_diff,
      from table(user_views_ptf('DBA_%')) uvp
      where length(text) > 4000
      order by uvp.text_length desc;

      ---------------------------- ----------- ------------ ---------------- ------------------------------
      DBA_STREAMS_COLUMNS 36309 36309 0 select distinct, o.n...
      DBA_OBJ_AUDIT_OPTS 19247 19247 0 select,, 'TAB...
      DBA_MVIEWS 18577 18577 0 select s.sowner as OWNER, s...
      DBA_IND_STATISTICS 14597 14597 0 SELECT...
      DBA_TRIGGERS 12388 12388 0 select, trigob...
      DBA_TAB_STATISTICS 9316 9316 0 SELECT /* TABLES */...
      DBA_TRIGGER_COLS 8376 8376 0 select /*+ ORDERED NOCOST *...
      DBA_LOB_PARTITIONS 7794 7794 0 select,...
      DBA_TAB_PARTITIONS 7287 7287 0 select,, 'NO'...
      DBA_TABLESPACE_THRESHOLDS 7064 7064 0 SELECT tablespace_name,...
      DBA_TABLES 6966 6966 0 select,,...
      DBA_SCHEDULER_JOBS 6954 6954 0 SELECT,, jo...
      DBA_HIST_ACTIVE_SESS_HISTORY 6631 6631 0 select /* ASH/AWR meta attr...
      DBA_OBJECT_TABLES 6248 6248 0 select,,...
      DBA_TAB_COLS 5984 5984 0 select,,...
      DBA_INDEXES 5652 5652 0 select,,...
      DBA_NESTED_TABLE_COLS 5622 5622 0 select,,...
      DBA_IND_PARTITIONS 5388 5388 0 select,, 'NO...
      DBA_LOBS 5336 5336 0 select,,...
      DBA_MVIEW_DETAIL_RELATIONS 4950 4950 0 select,, du.n...
      DBA_LOB_SUBPARTITIONS 4805 4805 0 select,...
      DBA_PART_TABLES 4713 4713 0 select,,...
      DBA_VARRAYS 4581 4581 0 select,, ac....
      DBA_CUBE_VIEW_COLUMNS 4456 4456 0 SELECT...
      DBA_TAB_SUBPARTITIONS 4124 4124 0 select,, po....

      28 rows selected

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: