How to do LONG datatype query?

[violin_1]

Thanks for Meta Documents,
I'll reply the testing results asap.

Here is the db info:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2
3 mtext long;
4 mcount number := 0;
5
6 cursor c1 is select text from dba_views;
7
8 begin
9
10 for r1 in c1 loop
11
12 mtext := r1.text;
13
14 if mtext like '%GBM%' then
15 mcount := mcount + 1;
16 end if;
17
18 end loop;
19
20 dbms_output.put_line(mcount||' views exists');
21
22 end;
23 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 10


SQL>

[Jean-Luc Oudart]

Hi Violin,

the "like" operator works on CHAR or VARCHAR2 not on LONG.

see my previous posts for bypassing the issue. I believe the solution to your question is there.

Regards
Jean-Luc

[Thierry Poels_1]

hi,

Yogeeraj's solutions works, but only upto longs of 32K. Above that boundary things get more complex.

If this is a one time report, then how about this for a work around:


set long 2000000000
spool /tmp/myviews.txt
select owner, view_name, text
from dba_views;
spool off

and use view/more/whatever to find the GBM's.

regards,
Thierry.

[Thierry Poels_1]

first set
set linesize 4000
set longchunksize 4000

to avoid line wrapping in the middle of your search text.

regards,
Thierry.