Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Replace LONG columns in Data Dictionary views
Comments
-
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
-
I would settle for adding additional addition CLOB columns to preserve backward compatibility. For example, SQL_FULLTEXT was added to V$SQL.
-
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.
-
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.