關於long型別的轉換

在oracle中對於long型別的處理時很糾結的。最開始引入這個資料型別的時候是對原有資料型別的補充，但是後面發現還是碰到了一些問題，使用Lob型別代替了。但是long型別從相容性上來說還得支援，而且從資料庫的資料字典中還是能夠看到Long型別的影子。
比如我們想檢視一個表中某個列的預設值情況，可以查詢user_tab_cols,或者dba_tab_cols等，欄位data_default是Long型別。
SQL> desc user_tab_cols
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(120)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HIDDEN_COLUMN                                      VARCHAR2(3)
 VIRTUAL_COLUMN                                     VARCHAR2(3)
 SEGMENT_COLUMN_ID                                  NUMBER
 INTERNAL_COLUMN_ID                        NOT NULL NUMBER
 HISTOGRAM                                          VARCHAR2(15)
 QUALIFIED_COL_NAME                                 VARCHAR2(4000)

如果想使用Like來模糊匹配或者重新建立一個臨時表，都會碰到Long型別的問題。

select *from user_tab_cols where data_default like 'a%'
                                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL> create table t1 as select *from user_tab_cols;
create table t1 as select *from user_tab_cols
                          *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype 
既然不支援，Oracle也提供了對應的一些方法來滿足我們的需要。
在thomas kyte的書中，對這種實現方法做了詳細的解釋。
使用的程式碼如下，基本就是把Long型別轉換為varchar2,按照每批4000個位元組的容量進行轉換。
create or replace package long_help authid current_user as function substr_of(p_query in varchar2, p_from in number, p_for in number, p_name1 in varchar2 default NULL, p_bind1 in varchar2 default NULL, p_name2 in varchar2 default NULL, p_bind2 in varchar2 default NULL, p_name3 in varchar2 default NULL, p_bind3 in
varchar2 default NULL, p_name4 in varchar2 default NULL, p_bind4 in varchar2 default NULL) return varchar2;
end;
/
create or replace package body long_help as
  g_cursor number := dbms_sql.open_cursor;
  g_query  varchar2(32765);
  procedure bind_variable(p_name in varchar2, p_value in varchar2) is
  begin
  if (p_name is not null) then dbms_sql.bind_variable(g_cursor, p_name, p_value);
  end if;
   end;
 function substr_of(p_query in varchar2, p_from in number, p_for in number, p_name1 in varchar2 default NULL, p_bind1 in varchar2 default NULL, p_name2 in varchar2 default NULL, p_bind2 in varchar2 default NULL, p_name3 in varchar2 default NULL, p_bind3 in varchar2 default NULL, p_name4 in varchar2 default NULL, p_bind4 in varchar2 default NULL) return varchar2 as
l_buffer varchar2(4000); l_buffer_len number;
begin
if (nvl(p_from, 0) <= 0) then raise_application_error(-20002, 'From must be >= 1 (positive numbers)');
end if;
if (nvl(p_for, 0) not between 1 and 4000) then raise_application_error(-20003, 'For must be between 1 and 4000');
end if;
if (p_query <> g_query or g_query is
NULL) then if (upper(trim(nvl(p_query, 'x'))) not like 'SELECT%') then raise_application_error(-20001, 'This must be a select only');
end if;
dbms_sql.parse(g_cursor, p_query, dbms_sql.native); g_query := p_query;
end if;
 bind_variable(p_name1, p_bind1); bind_variable(p_name2, p_bind2); bind_variable(p_name3, p_bind3); bind_variable(p_name4, p_bind4); dbms_sql.define_column_long(g_cursor, 1); if (dbms_sql.execute_and_fetch(g_cursor) > 0) then dbms_sql.column_value_long(g_cursor, 1, p_for, p_from - 1, l_buffer, l_buffer_len);
end if;
return l_buffer;
end substr_of;
end;
/

這個時候我們想查詢data_default的值就可以使用如下的sql
SELECT *
  FROM (SELECT OWNER,
               TABLE_NAME,
               COLUMN_NAME,
               DATA_TYPE,
               LONG_HELP.SUBSTR_OF('SELECT data_default FROM   DBA_TAB_COLS WHERE OWNER=:OWNER  AND TABLE_NAME=:TABLE_NAME AND COLUMN_NAME=:COLUMN_NAME',
                                   1,
                                   4000,
                                   'OWNER',
                                   OWNER,
                                   'TABLE_NAME',
                                   TABLE_NAME,
                                   'COLUMN_NAME',
                                   COLUMN_NAME) DATA_DEFAULT
          FROM DBA_TAB_COLS);

查詢結果如下：
OWNER                TABLE_NAME                     COLUMN_NAME                    DATA_TYPE                      DATA_DEFAULT
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                  RECO_SCRIPT_BLOCK$             CTIME                          DATE                           SYSDATE
SYS                  RECO_SCRIPT_BLOCK$             SPARE1                         NUMBER
SYS                  RECO_SCRIPT_BLOCK$             SPARE2                         NUMBER
SYS                  RECO_SCRIPT_BLOCK$             SPARE3                         NUMBER
SYS                  RECO_SCRIPT_BLOCK$             SPARE4                         VARCHAR2
SYS                  RECO_SCRIPT_BLOCK$             SPARE5                         VARCHAR2
SYS                  RECO_SCRIPT_BLOCK$             SPARE6                         DATE
SYS                  STREAMS$_COMPONENT_LINK        SOURCE_COMPONENT_ID            NUMBER
SYS                  STREAMS$_COMPONENT_LINK        DEST_COMPONENT_ID              NUMBER
SYS                  STREAMS$_COMPONENT_LINK        PATH_ID                        NUMBER
SYS                  STREAMS$_COMPONENT_LINK        POSITION                       NUMBER