Hi,
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 (community.oracle.com). 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: https://community.oracle.com/message/15591644#15591644
SYS_DBUriGen function is allow you to convert long to clob directly:
|
1 2 3 |
Select Owner, Trigger_Name, Sys_Dburigen(Owner, Trigger_Name, Trigger_Body, 'text()').Getclob() As Trigger_Body From All_Triggers Where Rownum < 10; |
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: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_DBURIGEN.html#GUID-ABA33BEB-F7B7-477B-9FF2-028D62768797
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: https://community.oracle.com/ideas/3319
Thanks to community and Odie_63.