Re: Querying DBA_VIEWS column

From: <>
Date: Fri, 8 Aug 2008 09:41:50 -0700 (PDT)
Message-ID: <>

On Aug 8, 11:09 am, wrote:
> On Aug 8, 9:55 am, "Dereck L. Dietz" <> wrote:
> > Oracle
> > Windows 2003 Server
> > My manager is trying to run this query against the DBA_VIEWS column:
> > but receives ORA-00932 because the TEXT column in the DBA_VIEWS is a LONG
> > column.
> > Does anybody know a way around so that the column can be searched?
> > Thanks.
> Go to asktom and search for getlong (it's a pl/sql function).

Nice suggestion, but the function won't work on DBA_VIEWS:

SQL> select getlong('DBA_VIEWS','TEXT', rowid) from dba_views; select getlong('DBA_VIEWS','TEXT', rowid) from dba_views *
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

SQL> A possible solution would be to create a copy of DBA_VIEWS as a table containing a CLOB in place of the LONG and using to_lob(text) to convert the data on insert:

insert into dba_vw
select owner, view_name, text_length, to_lob(text),

          type_text_length, type_text, oid_text_length,
          oid_text, view_type_owner, view_type, superview_name
from dba_views;

Once the offending LONG is a CLOB like filters work:

select *
from dba_vw
where text like '%STREAM%';

[lots of data here]

It's a thought.

David Fitzjarrell Received on Fri Aug 08 2008 - 11:41:50 CDT

Original text of this message