How to Convert LONG to Clob PART 2 – Keep Learning Keep Living

Hi,

I just want to write a small post today. Some time ago, I shared a way to convert long to clob in a query directly: https://mustafakalayci.me/2020/04/06/how-to-convert-a-long-column-to-clob-in-a-query/ Even if this method works, it works really slow and anything slow annoys me!

So, I want to share another method which is much much faster than Sys_dburigen function but THIS IS UNDOCUMENTED and UNSUPPORTED! It is used by internally by oracle so it works fine but you must be careful. Since, no one uses LONG column in their application (right? you don’t! please don’t!) this kind of code is handy for mostly admins because for backward compatibility data dictionary has some LONG data type columns.

What we are going to use is a LIBRARY called UTL_XML_LIB . I almost never create objects under SYS user, so I will create this code under a privileged admin user. first we must grant privileges on UTL_XML_LIB library and then create an EXTERNAL procedure in plsql.

sqlplus sys/oracle@orcl as sysdba

create user mustafa identified by mustafa;
grant dba to mustafa;

grant all on UTL_XML_LIB to mustafa;

sqlplus sys/oracle@orcl as sysdba

create user mustafa identified by mustafa;

grant dba to mustafa;

grant all on UTL_XML_LIB to mustafa;

now create external procedure:

sqlplus mustafa/mustafa@orcl

create or replace procedure mustafa.my_long2cloc (stmt    in varchar2,
                          rowid   in rowid,
                          lobloc  in out nocopy clob
                         ) is
  external
  name "kuxLong2Clob"
  language c
  library sys.utl_xml_lib
  with context
  parameters (context,
              stmt     ocistring,      
              stmt     indicator sb4,
              rowid    ocistring,      
              rowid    indicator sb4,
              lobloc   ociloblocator,  
              lobloc   indicator sb4
             );
             /

sqlplus mustafa/mustafa@orcl

create or replace procedure mustafa.my_long2cloc (stmt in varchar2,

rowid in rowid,

lobloc in out nocopy clob

) is

external

name "kuxLong2Clob"

language c

library sys.utl_xml_lib

with context

parameters (context,

stmt ocistring,

stmt indicator sb4,

rowid ocistring,

rowid indicator sb4,

lobloc ociloblocator,

lobloc indicator sb4

);

“kuxLong2Clob” is a function which returns void (basically it is a procedure) written in C by Oracle. this procedure takes a select statement for your LONG column and rowid. procedure returns CLOB data as an out parameter. I created my_long2clob procedure but to be able to call it via SQL statement, it must be a function, so:

create or replace function mustafa.f_my_long2cloc (p rowid, p_table varchar2, p_col varchar2) return clob as
  retval clob;
begin
  dbms_lob.createtemporary(retval,true);
  mustafa.my_long2cloc('select ' ||p_col ||' from ' || p_table || ' where rowid = :1', p, retval);
  return retval;
end;
/

create or replace function mustafa.f_my_long2cloc (p rowid, p_table varchar2, p_col varchar2) return clob as

retval clob;

begin

dbms_lob.createtemporary(retval,true);

mustafa.my_long2cloc('select ' ||p_col ||' from ' || p_table || ' where rowid = :1', p, retval);

return retval;

end;

to test it, I won’t create a new table with a LONG column but instead I will use one of in the data dictionary (which I use these kind of codes against). Let’s use SYS.TRIGGER$ table which is the base table for DBA_TRIGGERS view. ACTION# column is a LONG data type. of course we must first grant select privilege on SYS.TRIGGER$ to mustafa user.

sqlplus sys/oracle@orcl as sysdba

 grant select on TRIGGER$ to mustafa;

sqlplus mustafa/mustafa@orcl

select  mustafa.f_my_long2cloc(rowid, 'SYS.TRIGGER$', 'ACTION#') 
from SYS.TRIGGER$; 

...
...
...

sqlplus sys/oracle@orcl as sysdba

grant select on TRIGGER$ to mustafa;

sqlplus mustafa/mustafa@orcl

select mustafa.f_my_long2cloc(rowid, 'SYS.TRIGGER$', 'ACTION#')

from SYS.TRIGGER$;

...

I suppressed the output but here it is. really fast long to clob conversion in SQL.

as a CON, it will only work against tables not VIEWs! you cannot use it as “f_my_long2cloc(rowid, ‘DBA_TRIGGERS’, ‘TRIGGER_BODY’)”. Since it requires ROWID, you must be referring underlying table and its rowid.

beyond that, if you want to get LONG as VARCHAR2, similarly you can use “kpdbLong2Varchar2” procedure in DBMS_PDB_LIB library. as UTL_XML_LIB it is very fast. only difference is, it returns VARCHAR2 and trims the overflow part. Where is it in use? Check how DBA_VIEWS shows TEXT_VC column data 😉

I hope this helps to fight against LONG columns in your codes.

Wish you all healthy, happy days.

Leave a Reply Cancel reply