Re: Querying DBA_VIEWS column

On Aug 8, 11:09 am, wfhol..._at_yahoo.com wrote:
> On Aug 8, 9:55 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>
> > Oracle 10.2.0.1.0
> > Windows 2003 Server
>
> > My manager is trying to run this query against the DBA_VIEWS column:
>
> > SELECT * FROM DBA_VIEWS WHERE OWNER = 'HBL' AND TEXT LIKE '%PRV_SGND_DI%'
>
> > 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

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