Converting a LONG Column to a CLOB on the fly
06 Dec 2010 9 Comments
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
:
TO_LOB
: A function that converts aLONG
column to aCLOB
. Can only be used in aSELECT
list of a subquery in anINSERT
statement. This means that the function is only useful if you’re converting the underlying table definition.ALTER TABLE <table_name> MODIFY <long_column_name> CLOB
: This converts the column datatype and the data as well.- Use
DBMS_REDEFINITION
to redefine the column datatype. - Use Oracle Data Pump to convert the column datatype.
- Use
CAST
function to cast fromLONG
toCLOB
: Unfortunately,CAST
doesn’t supportLONG
. - Write a PL/SQL function that performs a
SELECT ... INTO l FROM user_views
, wherel
is a PL/SQL variable of typeLONG
. However, in PL/SQL, aLONG
variable can only hold up to 32,760 characters (yes, not 32,767, ie different fromVARCHAR2
) so this will only solve the problem for small to medium sized views. - Use dynamic SQL with
DBMS_SQL.COLUMN_VALUE_LONG
to access theLONG
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 datatypeLONG
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:
- 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 beROWID
if a table) as input parameter(s), builds aSELECT
statement for the underlying table/view for theLONG
column using bind variable(s) for the primary key value(s), does theDBMS_SQL
magic and uses theDBMS_SQL.COLUMN_VALUE_LONG
function. For scalability, the solution should keep a collection of parsed statements and re-use those without re-parsing. - 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:
- Create a standalone function with “LONG-to-CLOB” functionality.
- Create an object type with attributes corresponding to
USER_VIEWS
. This object type will have member methods used withDBMS_SQL
. - Create an object type collection based on the object type from 2.
- Create a standalone function that takes an optional argument for a
WHERE
clause used againstUSER_VIEWS
, using a combination ofDBMS_SQL
and 1., 2., and 3. above. This method is a pipelined table function. - 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:
LONG_TO_CLOB
function.USER_VIEWS_T
object type specification.USER_VIEWS_T
object type body.USER_VIEWS_C
object type collection.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:
- 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 theUSER_VIEWS_T
object type. - Create the object type collection for 1.
- Create the standalone pipelined table function.
- 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
.
Pingback: Converting Between Oracle Data and XML « Finn Ellebaek Nielsen's Blog
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.
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
RETURN NULL;
END IF;
dbms_lob.CreateTemporary(result, false, dbms_lob.call);
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);
END LOOP;
RETURN result;
Excellent point, thank you very much Lee!
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!
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
—————————- ——– ————-
REQ_NUMBER VARCHAR2(10)
PHASE NUMBER
WO_NUMBER VARCHAR2(10)
TASK_LIST LONG
The keys have the following three combinations
REQ_NUMBER, null, null
REQ_NUMBER, PHASE, 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?
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.
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.
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 11.2.0.1.0 in the SYS schema (pardon the useless formatting that WordPress allows for comments):
select uvp.view_name,
uvp.text_length,
length(text) text_length2,
abs(uvp.text_length - length(text)) text_length_diff,
uvp.text
from table(user_views_ptf('DBA_%')) uvp
where length(text) > 4000
order by uvp.text_length desc;
VIEW_NAME TEXT_LENGTH TEXT_LENGTH2 TEXT_LENGTH_DIFF TEXT
---------------------------- ----------- ------------ ---------------- ------------------------------
DBA_STREAMS_COLUMNS 36309 36309 0 select distinct u.name, o.n...
DBA_OBJ_AUDIT_OPTS 19247 19247 0 select u.name, o.name, '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 trigusr.name, 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 u.name,...
DBA_AUTOTASK_CLIENT 7750 7750 0 SELECT "CNAME_KETCL","STATU...
DBA_TAB_PARTITIONS 7287 7287 0 select u.name, o.name, 'NO'...
DBA_SCHEDULER_JOB_DESTS 7092 7092 0 SELECT dd.OWNER, dd.JOB_NA...
DBA_TABLESPACE_THRESHOLDS 7064 7064 0 SELECT tablespace_name,...
DBA_TABLES 6966 6966 0 select u.name, o.name,...
DBA_SCHEDULER_JOBS 6954 6954 0 SELECT ju.name, jo.name, jo...
DBA_HIST_ACTIVE_SESS_HISTORY 6631 6631 0 select /* ASH/AWR meta attr...
DBA_OBJECT_TABLES 6248 6248 0 select u.name, o.name,...
DBA_TAB_COLS 5984 5984 0 select u.name, o.name,...
DBA_INDEXES 5652 5652 0 select u.name, o.name,...
DBA_NESTED_TABLE_COLS 5622 5622 0 select u.name, o.name,...
DBA_IND_PARTITIONS 5388 5388 0 select u.name, io.name, 'NO...
DBA_LOBS 5336 5336 0 select u.name, o.name,...
DBA_MVIEW_DETAIL_RELATIONS 4950 4950 0 select u.name, o.name, du.n...
DBA_CUBE_ATTR_VISIBILITY 4914 4914 0 SELECT...
DBA_LOB_SUBPARTITIONS 4805 4805 0 select u.name,...
DBA_PART_TABLES 4713 4713 0 select u.name, o.name,...
DBA_VARRAYS 4581 4581 0 select u.name, op.name, ac....
DBA_CUBE_VIEW_COLUMNS 4456 4456 0 SELECT...
DBA_TAB_SUBPARTITIONS 4124 4124 0 select u.name, po.name, po....
28 rows selected