Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to do LONG datatype query?

violin_1
Advisor

How to do LONG datatype query?

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>
13 REPLIES
Yogeeraj_1
Honored Contributor

Re: How to do LONG datatype query?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
violin_1
Advisor

Re: How to do LONG datatype query?

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
Honored Contributor

Re: How to do LONG datatype query?

hi,

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

which version of Oracle are you running?

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: How to do LONG datatype query?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Thierry Poels_1
Honored Contributor

Re: How to do LONG datatype query?

hi,

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

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
violin_1
Advisor

Re: How to do LONG datatype query?

Thanks,

But still fails.
It got errors when fetch.

Is there any solutions?
Thx.

Violin.
Jean-Luc Oudart
Honored Contributor

Re: How to do LONG datatype query?

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
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: How to do LONG datatype query?

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
fiat lux
Yogeeraj_1
Honored Contributor

Re: How to do LONG datatype query?

hi,

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

revert

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
violin_1
Advisor

Re: How to do LONG datatype query?

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
Honored Contributor

Re: How to do LONG datatype query?

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
fiat lux
Thierry Poels_1
Honored Contributor

Re: How to do LONG datatype query?

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.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Thierry Poels_1
Honored Contributor

Re: How to do LONG datatype query?

first set
set linesize 4000
set longchunksize 4000

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

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.