Home > HowTo, SQL > How To : extract LONG column as CLOB using DBUriType

How To : extract LONG column as CLOB using DBUriType

Here’s a short post presenting an easy way to extract a LONG column as a CLOB.
Although the LONG data type was deprecated years ago in favour of CLOB, many Oracle dictionary views still use it.
Adrian Billington’s great article – working with long columns – shows different ways to access LONG columns in SQL, in particular when the size exceeds 32k. One of the solutions uses DBMS_SQL to fetch the LONG value piecewise into a CLOB.

What I’ll describe here is technically based on the same approach but only involves a single built-in function : SYS_DBURIGEN.

 

Example

SYS_DBURIGEN is used like this :

SYS_DBURIGEN ( <col1>, <col2>, ..., <coln>, <target>, 'text()' )

where <col1> to <coln> uniquely identify a row and <target> is the column we want to project.

Though in case the data source is a table, using ROWID directly is preferred :

SYS_DBURIGEN ( rowid, <target>, 'text()' )

 
For instance, in order to extract ALL_IND_EXPRESSIONS.COLUMN_EXPRESSION :

SQL> select index_owner
  2       , index_name
  3       , sys_dburigen( index_owner
  4                     , index_name
  5                     , column_expression
  6                     , 'text()'
  7                     ).getclob() as column_expression
  8  from all_ind_expressions
  9  where index_owner = 'SYS' ;
 
INDEX_OWNER  INDEX_NAME                        COLUMN_EXPRESSION
------------ --------------------------------- ---------------------------------------
SYS          IS_DBFS$_MOUNTS                   DECODE("S_MOUNT",NULL,"S_OWNER",NULL)
SYS          I_SCHEDULER_JOB1                  SYS_EXTRACT_UTC("NEXT_RUN_DATE")
SYS          I_SCHEDULER_JOB4                  BITAND("JOB_STATUS",515)
SYS          I_SCHEDULER_WINDOW1               SYS_EXTRACT_UTC("NEXT_START_DATE")
SYS          I_WRI$_OPTSTAT_AUX_ST             SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST     SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_HH_ST              SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST    SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_H_ST               SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_IND_OBJ#_ST        SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_IND_ST             SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_OPR_STIME          SYS_EXTRACT_UTC("START_TIME")
SYS          I_WRI$_OPTSTAT_OPR_TASKS_STIME    SYS_EXTRACT_UTC("START_TIME")
SYS          I_WRI$_OPTSTAT_TAB_OBJ#_ST        SYS_EXTRACT_UTC("SAVTIME")
SYS          I_WRI$_OPTSTAT_TAB_ST             SYS_EXTRACT_UTC("SAVTIME")
SYS          XS$ACL_PARAM_I3                   SUBSTRB("PVALUE2",1,2000)
SYS          XS$NSTMPL_ATTR_I1                 SUBSTRB("ATTR_NAME",1,2000)

 

Internals

Behind the scenes, Oracle builds a DBURI object, and upon invocation of the getClob() method, creates a SQL query to retrieve the column :

SQL> select index_owner
  2       , index_name
  3       , sys_dburigen(index_owner, index_name, column_expression, 'text()').getUrl() dburi
  4  from all_ind_expressions
  5  where rownum = 1 ;

INDEX_OWNER  INDEX_NAME          DBURI
-----------  ------------------- ------------------------------------------------------------
SYS          I_WRI$_OPTSTAT_H_ST /ORADB/PUBLIC/ALL_IND_EXPRESSIONS/ROW[INDEX_OWNER='SYS' and 
                                 INDEX_NAME='I_WRI$_OPTSTAT_H_ST']/COLUMN_EXPRESSION/text()

 
The system-generated query looks like this (formatted) :

SELECT alias000$."COLUMN_EXPRESSION" AS COLUMN_EXPRESSION 
FROM "ALL_IND_EXPRESSIONS" alias000$ 
WHERE 1 = 1 
AND ((alias000$."INDEX_OWNER"='SYS') 
AND (alias000$."INDEX_NAME"='I_WRI$_OPTSTAT_H_ST'))

Nice, except that it doesn’t use bind variables…

But we now have a easy way to convert a LONG value to a character data type that can be manipulated in SQL.

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s