Forum Stats

  • 3,874,219 Users
  • 2,266,685 Discussions


Replace LONG columns in Data Dictionary views



  • User51642 Yong Huang
    User51642 Yong Huang Member Posts: 177 Bronze Badge

    My workaround is to use sys.dbms_metadata_util.long2clob. It's undocumented so I won't put it in production code.


    set linesize 200 long 20000 longc 200

    select obj#, sys.dbms_metadata_util.long2clob(1000000, 'SYS.VIEW$', 'TEXT', rowid) from sys.view$ where sys.dbms_metadata_util.long2clob(1000000, 'SYS.VIEW$', 'TEXT', rowid) like '%CAPTURE_MESSAGE_NUMBER%';

    to show the view text that has that string CAPTURE_MESSAGE_NUMBER

    (That's just an example. To find text in a view in 12c, just query text_vc unless the text is longer than 4000 bytes.)


    Sven W.
  • 2983299
    2983299 Member Posts: 1

    I would settle for adding additional addition CLOB columns to preserve backward compatibility.  For example, SQL_FULLTEXT was added to V$SQL.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    These days I use a function that queries user_tab_subpartitions to return high_value as a varchar2 (pass in table, partition and subpartition - partition not strictly needed but it's faster if you use it). It's not a generic solution or particularly efficient, but it's what Oracle is doing internally for the  _VC columns they have got around to supporting, and it's a lot easier for developers to work with than a cryptic XML incantation.

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 44 Blue Ribbon

    It's incredible that this got so few votes and that we still have to live with those LONGs. I just got

    SP2-0642: SQL*Plus internal error state 2147, context 0:0:0 unsafe to proceed

    when trying to convert dba_tab_cols.data_default to VARCHAR2 (after spending a lot of time searching the Internet again on how to get from LONG to VARCHAR2). My DBMS is Oracle Database 19c Enterprise Edition.

    Flado, toonpibiri