关于long类型的转换
- 格式：docx
- 大小：18.77 KB
- 文档页数：3
在oracle中对于long类型的处理时很纠结的。最开始引入这个数据类型的时候是对原有数据类型的补充，但是后面发现还是碰到了一些问题，使用Lob类型代替了。但是long类型从兼容性上来说还得支持，而且从数据库的数据字典中还是能够看到Long类型的影子。
比如我们想查看一个表中某个列的默认值情况，可以查询user_tab_cols,或者dba_tab_cols等，字段data_default是Long类型。
SQL>descuser_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也提供了对应的一些方法来满足我们的需要。
在thomaskyte的书中，对这种实现方法做了详细的解释。
使用的代码如下，基本就是把Long类型转换为varchar2,按照每批4000个字节的容量进行转换。
create or replace package long_helpauthidcurrent_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;
