1、在thomas kyte的书中，介绍了一个自定义函数,可以实现转换;

2、代码如下，把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;
/

3、验证

SQL> desc t12;
Name Type Nullable Default Comments
---- ---- -------- ------- --------
NAME LONG Y 

可以看到T12的NAME为LONG;

通过以下SQL，查询T12表的NAME所有内容;

SQL> select LONG_HELP.SUBSTR_OF('SELECT name FROM t12 ',1,4000) name from dual;

NAME
--------------------------------------------------------------------------------

00、前言

【有计划的备份应该是日常最重要、最紧急的工作内容，不仅要保证备份成功、还要确保?