How to do LONG datatype query?

[violin_1]

Hello,

If I want to do such query:

SQL> select count(*) from dba_views where text like '%GBM%';

select count(*) from dba_views where text like '%GBM%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes

SQL>

How could I achieve that?

'cause dba_views.text is LONG datatype,
cannot compare with characters.

Thanks for any tips.

Violin.

SQL> desc dba_views
Name Null? Type
------------------------------- -------- ----
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)

SQL>

[Yogeeraj_1]

hi Violin,

try this:
================
YD@MYDB.MU> l
1 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;
YD@MYDB.MU>


hope this helps!

regards
Yogeeraj

[violin_1]

Hello,

Thanks for your scripts,
but it running with errors:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 12


SQL>


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


SQL>

[Yogeeraj_1]

hi,

I have tested it on both Oracle 8i and Oracle 9i. works fine

which version of Oracle are you running?

regards
Yogeeraj

[Yogeeraj_1]

posting the script again:
set serveroutput on size 1000000;

declare

mtext long;
mcount number := 0;

cursor c1 is select text from dba_views;

begin

for r1 in c1 loop

mtext := r1.text;

if mtext like '%GBM%' then
mcount := mcount + 1;
end if;

end loop;

dbms_output.put_line(mcount||' views exists');

end;
/



regards
Yogeeraj

[Thierry Poels_1]

hi,

try with "to_char(mcount)" in dbms_output statement.

regards,
Thierry.

[violin_1]

Thanks,

But still fails.
It got errors when fetch.

Is there any solutions?
Thx.

Violin.

[Jean-Luc Oudart]

Hi,

check Metalink doc id 264961.995
"Search for a string in LONG datatype"

This is an example to search dblinks in views. I believe you can this script and adapt for your own purpose.

Regards
Jean-Luc

[Jean-Luc Oudart]

Another way is to convert differently the long into a varchar2 (but is limited to 4000 bytes !)
cf Metalink doc id 428044.999 convert long to varchar2- note:1022030.6

In previous post, the dbmssql package is used to copy a portion of the long into a varchar2 (with column_value_long)

Regards
Jean-Luc

[Yogeeraj_1]

hi,

can you post the exact error message?
what version of sqlplus are you using?

revert

regards
yogeeraj

[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.