Querying DBA_VIEWS column

[Dereck L. Dietz]

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.

[wfho...@yahoo.com]

Go to asktom and search for getlong (it's a pl/sql function).

[ora...@msn.com]

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

[Dereck L. Dietz]

Thank you both