Replace LONG columns in Data Dictionary views

Chris Hunt

The LONG datatype has been deprecated for twenty years, yet they still feature in the Oracle data dictionary views, even (I think) relatively new ones like DBA_TAB_PARTITIONS.

LONG columns are a royal pain to deal with, and their presence in prominent Oracle-owned database objects send the message that it's a good idea to use them.

I realise that there are concerns about backward compatibility, but really, it's time to move on. If they can't be replaced, could we at least have CLOB versions of each LONG column added to the views?

Lothar Flatz

As former Member of ST I can assure you that nobody want's to bother with the Oracle core code. I think your idea is ok, but I miss rational arguments. Royal pain seems a bit exaggerating. There are some issues, yes, but I don't think this is a number one priority.

Randolf Geist

In 12c they've at least added VARCHAR2 columns to a number of views that in the past only had LONG. Don't ask me why VARCHAR instead of CLOB but it's at least more than nothing.

https://connormcdonald.wordpress.com/2014/11/06/a-simple-12c-query-with-a-cool-result/

Randolf

Cuauhtemoc Amox

A workaround (of sorts) can be have by using dbms_metadata, which would allow you to filter values stored on long columns and/or perform modifications and have an updated DML to change the object definition.

William Robertson

Another workaround is possible by harnessing the dark power of XML. (I'll update this comment if I can find my example - it's not something you can just type off the top of your head.) I agree though, it's immensely painful whenever you need to develop a partition-wise purge/archive solution and you can't get at the attributes you need without some kind of elaborate workaround.

Sven W.

My workaround is using materialized views to access such columns.

Typical example is the "DATA_DEFAULT" column in USER_TAB_COLUMNS.

If you want to known what the default value for a column at table level is, you can't simply do a

"select data_default from user-tab_columns where table_name = 'ABC'".

For example this select could be used to set the default values in an apex application.

It doesn't work, just because of that nasty long column.

It gets worse if the table is a remote table.

If anyone interested in the MV workaround, here is a description: https://svenweller.wordpress.com/2015/02/11/materialized-view-api-for-long-columns/

Randolf Geist

Another workaround is possible by harnessing the dark power of XML. (I'll update this comment if I can find my example - it's not something you can just type off the top of your head.) I agree though, it's immensely painful whenever you need to develop a partition-wise purge/archive solution and you can't get at the attributes you need without some kind of elaborate workaround.

Adrian Billington has probably written the most thorough discussion how LONG columns can be handled / worked around. One article here: working with long columns and the specific DLA utility (Dictionary Long Application) here: method 4 dynamic sql in pl/sql

It includes also examples how XML can be used as workaround.

Randolf

Kevan Gelling

+1 for "royal pain", especially when doing impact analysis

But the LONGs don't need to be replaced, if TO_LOB() worked in standalone SQL.

Thomas Teske-Oracle

A simple function to convert the LONG contents into a JSON or a VARCHAR would be a nice addition. We should really avoid creating new workarounds. This should be part of the product.

Mustafa_KALAYCI

I am totally agree with this idea. I am exhausted because of those long columns in data dictionary. especially HIGH_VALUE column in *_tab_partitions (subpartitions). I have not even seen any database that exceed varchar2 limit for high_value column. I am not saying no one has it but never heard, never seen for me. also not sure who can have a partition column size more than 4000.

since Oracle says that never use long and long raw data types anymore, I think this should be a number one priority and removed from dictionary. longs should be even removed from database! why bother? I am suspicious that long data type has not been removed just because of dictionary has it. otherwise on the next version that LOB introduced, it would be gone.

William Robertson

Another workaround as of 12c is to use an inline PL/SQL function (WITH clause function, whatever we're calling those) to query the LONG column, convert the result in PL/SQL and return the 4000-byte string version.

The _VC columns provided in the dictionary are very welcome, but for some reason they only appear in %_CONSTRAINTS and %_VIEWS. I think automating partition maintenance is the last main area where dictionary LONGs are a royal pain (and like Mustafa, I have never seen a partition/subpartition high value anywhere near the 4000 byte limit, although I suppose list subpartitions can potentially get verbose.) If we could just have a HIGH_VALUE_VC column in the %_PARTITIONS views, that would more or less fix it.