How to convert a LONG column to CLOB directly in a query


This is a big problem since LONG is a very painful data type. You cannot search in it, use it in CTAS (create table as), pass it to a plsql object as parameter etc. Oracle strongly suggest not to use a LONG column anymore but unfortunately there are many LONG columns in data dictionary views like DBA_TRIGGERS, DBA_TAB_COLS, DBA_MVIEWS, DBA_VIEWS, DBA_TAB_PARTITIONS… I struggled with DBA_TAB_PARTITIONS a lot for example. HIGH_VALUE column is a LONG column and some of my codes to maintain partition operations need that data to identify the necessary partitions. I did so many works to get that data and today, finally I have found a way to convert LONG data to CLOB directly in a query!

I love Oracle community ( so many Gurus are answering to so many questions and I just learn a lot just by reading. I just saw a thread based on a different topic but a valuable member of community Odie_63 provides a brilliant answer:

SYS_DBUriGen function is allow you to convert long to clob directly:

Trigger_Body is a LONG column. first parameters in the function (Owner, trigger_name) are required for uniqueness of the row and they must be columns in the table. you can find information about Sys_DbUriGen at the documentation:

Of course performance is not much good but LONG data type is responsible of this. I hope Oracle will remove all LONG columns as soon as possible. even there is an idea thread for this at the community:

Thanks to community and Odie_63.

Leave a Reply

Your email address will not be published. Required fields are marked *