Convert values from LONG to CLOB using dbms_metadata_util.long2clob in SQL queries

Oracle: 12.2.0.1.0

Today I’ll show you a small simple function to convert values from LONG to CLOB in SQL queries.
I’ve seen many articles where authors create their convert fuctions or create a table and insert the LONG value via INSERT INTO SELECT statement.
Oracle has also made its own, so we don’t need to create our own:)

This is the definition :
Package : dbms_metadata_util

 FUNCTION long2clob(length IN  NUMBER,   -- length of the LONG
                    tab    IN  VARCHAR2, -- table name
                    col    IN  VARCHAR2, -- column name
                    row    IN  ROWID)    -- rowid of the row from table
RETURN CLOB;

What is the most important thing: we can use it in SQL queries !! Awesome 🙂
This is my simple query to find some information in the definition view using dbms_metadata_util.long2clob:

SQL> ;
  1  SELECT a.OWNER,
  2         a.OBJECT_NAME,
  3         a.OBJECT_TYPE,
  4         sys.dbms_metadata_util.long2clob(w.textlength, 'SYS.VIEW$', 'TEXT', w.rowid) sql_text
  5    FROM sys.view$   w,
  6         dba_objects a
  7   WHERE a.object_id = w.obj#
  8     AND upper(sys.dbms_metadata_util.long2clob(w.textlength, 'SYS.VIEW$', 'TEXT', w.rowid)) LIKE upper('%ROWNUM%')
  9     AND a.OWNER='SYS'
 10*    FETCH FIRST 3 ROWS ONLY
SQL> /

OWN OBJECT_NAME                    OBJECT_TYPE SQL_TEXT
--- ------------------------------ ----------- ------------------------------------------------------------------------------
SYS USER_TAB_COL_STATISTICS        VIEW        select table_name, column_name, num_distinct, low_value, high_value,
                                                      dens

SYS ALL_TAB_COL_STATISTICS         VIEW        select owner, table_name, column_name, num_distinct, low_value, high_value,


SYS DBA_TAB_COL_STATISTICS         VIEW        select owner, table_name, column_name, num_distinct, low_value, high_value,



Wybrano wierszy: 3.

You can use it to find e.g. views or materialized views which are using the ROWNUM pseudocolumn to avoid the situation I described in my previous post.

So simple… 🙂

Leave a Reply

2 Comments on "Convert values from LONG to CLOB using dbms_metadata_util.long2clob in SQL queries"

avatar
Sort by:   newest | oldest | most voted
Luis Santos
Guest

The article is tagged with 12.1 and 12.1 tags. But I found the exactly same function on 11.2 and 10.2 Oracle versions. Should these tags be added too?

wpDiscuz