當long欄位在where條件裡的解決方案

當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