Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!k37g2000hsf.googlegroups.com!not-for-mail
From: "fitzjarrell@cox.net" <oratune@msn.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Querying DBA_VIEWS column
Date: Fri, 8 Aug 2008 09:41:50 -0700 (PDT)
Organization: http://groups.google.com
Lines: 55
Message-ID: <c4266bb2-5e7b-4147-ab04-a0918f5c6067@k37g2000hsf.googlegroups.com>
References: <5hYmk.16083$cW3.4434@nlpi064.nbdc.sbc.com> <51d5a434-f2b1-4747-ac85-5225c42554d1@d1g2000hsg.googlegroups.com>
NNTP-Posting-Host: 138.32.32.166
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1218213711 3260 127.0.0.1 (8 Aug 2008 16:41:51 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 8 Aug 2008 16:41:51 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: k37g2000hsf.googlegroups.com; posting-host=138.32.32.166; 
 posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 
 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ;  Embedded Web 
 Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 
 2.0.50727),gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.oracle.server:447058
X-Received-Date: Fri, 08 Aug 2008 12:41:51 EDT (text.usenetserver.com)

On Aug 8, 11:09=A0am, wfhol...@yahoo.com wrote:
> On Aug 8, 9:55=A0am, "Dereck L. Dietz" <diet...@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 =3D 'HBL' AND TEXT LIKE '%PRV_SGND_=
DI%'
>
> > but receives ORA-00932 because the TEXT column in the DBA_VIEWS is a LO=
NG
> > 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