long到number转换 ts_关于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