Re: Querying DBA_VIEWS column
Date: Fri, 8 Aug 2008 09:41:50 -0700 (PDT)
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_namefrom dba_views;
Once the offending LONG is a CLOB like filters work:
where text like '%STREAM%';
[lots of data here]
It's a thought.
David Fitzjarrell Received on Fri Aug 08 2008 - 11:41:50 CDT