RE: How to select data from a LONG datatype

From: Harsh Agrawal <HARSHA_at_Amdocs.com>
Date: Thu, 19 Jul 2001 23:09:22 -0700
Message-ID: <F001.0034F81A.20010719223100@fatcity.com>

I hope the question is pertaining to using long column in where clause. Try this

declare
cursor c0 is
select view_name,text
from user_views;

         Cview_name   varchar2(30);
         Ltext        long;

begin
open c0;
loop
fetch c0 into Cview_name,Ltext;
exit when c0%notfound;

if Ltext like '%/*%' then
dbms_output.put_line('View Name : '||Cview_name); end if;

end loop;

close c0;

end;
/

U can use this in SQL, One way is PL/SQL.

This PL/SQL can be used to insert all such view names into a temp table then u can display text for these views using a select command. Don't forget to set the size of long using set long 1000

I hope this answers ur question.

Bye
- Harsh

-----Original Message-----
Sent: Friday, July 20, 2001 1:41 AM
To: Multiple recipients of list ORACLE-L

How do I get past the inconsistent datatypes error on the following?

select *
from dba_views
where owner = 'SA'
and text like '%/*%';

We have some views that were migrated from 7.3.4 to 8.0.5 and we suspect
that we
lost our hints in the migration and upgrade. I want to find which views had hints in the old system so that I can bring them forward.

Rodd Holman
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Rodd Holman
INET: rodney.holman_at_lodgenet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Harsh Agrawal
INET: HARSHA_at_Amdocs.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------