Forum Stats

  • 3,824,762 Users
  • 2,260,415 Discussions
  • 7,896,308 Comments

Discussions

Replace LONG columns in Data Dictionary views

Chris Hunt
Chris Hunt Member Posts: 2,066 Gold Trophy
edited Jan 11, 2016 6:16PM in Database Ideas - Ideas

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?

Chris HuntberxLothar FlatzManish ChaturvediborneselMortenBratenabhinivesh.jainmtefftZlatko SiroticWilliam RobertsonAish13top.gunSven W.Martin PreissBPeaslandDBAGeert GruwezJagadekarasysassysdba991901kulikouskiitshakulohmannNimish Gargfac586UtsavApexBineBeGinuser7904656jbosmanKevan GellingN.B.user7048955FatMartinR[Deleted User]GregVMeeuwtjeRichard SmithTexasApexDevelopersensoft3259943Aparna Dutta-OracleMKJ10930279Jon TheriaultAntonio NavarroAnnEdmundThomas Teske-OracledherzhauNiels HeckerDavidMcWhinnieJustin WarwickUser_YVFODBEDEUser_P8A68Andreas HuberMustafa_KALAYCIuser1609428GulliGMike Kutzblessed DBAuser3623132Evandro Lima-OracleCherif bhPeter HraškoDanilo PiazzalungaMarwim3563003User_RA3A5connor_mc_d-OracleBilly Verreynnejormart-OracleUser_9GEWMdominiquecomtemathguycormacoUser_HX4GRuser11970842User_W9QUEThorsten KettnerUser_SYXICsdstuberUser_G3UV3
82 votes

Active · Last Updated

«1

Comments

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    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.

    Gerald Venzl-Oracle
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy

    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

  • 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
    William Robertson Member Posts: 9,567 Bronze Crown

    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.
    Sven W. Member Posts: 10,535 Gold Crown
    edited May 12, 2015 10:02AM

    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/

    ApexBineBEDEMustafa_KALAYCI
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy

    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
    Kevan Gelling Member Posts: 33 Green Ribbon

    +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.

    MeeuwtjeSven W.User_SYXIC
  • Thomas Teske-Oracle
    Thomas Teske-Oracle Member Posts: 5 Employee

    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.

    Thorsten KettnerUser_SYXIC
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    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.

    GulliG
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Nov 17, 2019 9:07AM

    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.