当long这个已经过时的字段在long条件里时，会发生什么？

答案：会出现这个错误：

SQL> select VIEW_NAME from dba_views where TEXT like '%longtest%';
select VIEW_NAME from dba_views where TEXT like '%longtest%'
                                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

oracle会推荐你将long字段换成clob。

但是在一些老数据库的老表中，仍然需要用到这个讨厌的字段。

今天我在做一个task的时候，也碰到了这个问题，在metalink上找到一个plsql方案，我们自己也想出了一个简单的plsql的方案。

----方案1----

使用metalink上一个package:long_util

select view_name, text
from dba_views
where
owner = 'HAO'
and (
long_util.likestr(
'select text from dba_views where wner='''||owner||'''', '%testlong%')>0) ;

VIEW_NAME
------------------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
LONGTEST2
select "ID" from testlong

【long_util包定义见本文最后】

----方案2----

在plsql里使用instr函数。

set serveroutput on
declare
begin
  for r in (select view_name, text from dba_views where wner = 'HAO') loop
    if instr(r.text, 'testlong') > 0 then
    dbms_output.put_line(r.view_name||','||r.text);
    end if;
  end loop;
end;
/

LONGTEST2,select "ID" from testlong

PL/SQL procedure successfully completed.

-----------------------------

long_util包定义：

rem
rem since LONG values can't be passed as function arguments, these
rem functions require a SELECT statement yielding the long column as the
rem first select list item.
rem

CREATE OR REPLACE PACKAGE long_util authid current_user IS

FUNCTION substring(insql in varchar2, leng in number, startpos in number)
RETURN varchar2;

FUNCTION likestr(insql in varchar2, pat in varchar2)
RETURN NUMBER;

end long_util;
/
show errors;

CREATE OR REPLACE PACKAGE BODY long_util IS

FUNCTION substring(insql in varchar2, leng in number, startpos in number)
RETURN varchar2
is
sts number;
stringsegment varchar2(4000);
v_CursorID INTEGER;
bytesread number;
begin
v_CursorID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_CursorID, insql, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, 1);
sts := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);
bytesread := leng;
stringsegment := NULL;
DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, 1, leng, startpos,
stringsegment, bytesread);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
return stringsegment;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
end;

FUNCTION likestr(insql in varchar2, pat in varchar2)
RETURN number
is
startpos number;
sts number;
leng number;
patlen number;
chunksize number;
stringsegment varchar2(4000);
v_CursorID INTEGER;
bytesread number;
begin
v_CursorID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_CursorID, insql, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, 1);
sts := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);
patlen := length(pat);
chunksize := 4001 - patlen;
bytesread := 4000;
startpos := 0;
while bytesread = 4000 loop
DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, 1, 4000, startpos,
stringsegment, bytesread);
startpos := startpos + chunksize;
IF bytesread > 0 THEN
if stringsegment like pat then
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
return 1;
end if;
END IF;
end loop;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
return 0;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
end;
END long_util;
/
show errors