关于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