Operating System - HP-UX
1748054 Members
4929 Online
108758 Solutions
New Discussion юеВ

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 13
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)