Replace LONG columns in Data Dictionary views

User51642 Yong Huang

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

E.g.:

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

From:

http://yong321.freeshell.org/oranotes/ViewDefinitionSearch.txt

2983299

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

William Robertson

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

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.