long查询结果转换为varchar2类型

来自Thomas Kyte 《Oracle9i/10g/11g编程艺术》 12章节中.由于long的操作限制,那么在操作long之前可以将该类型的结果转换为varchar2然后再使用,long_help.substr_of的基本意思是将long结果的的前4000字节转换为varchar2类型.如果long的数据超过了4000字节,那么可以将循环调用此函数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

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 ;

使用方法:查询DBA_TAB_PARTITIONS中的HIGH_VALUE

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT *    FROM ( SELECT TABLE_OWNER,                  TABLE_NAME,                  PARTITION_NAME,                  LONG_HELP.SUBSTR_OF( 'SELECT HIGH_VALUE FROM   DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER AND TABLE_NAME=:TABLE_NAME AND PARTITION_NAME=:PARTITION_NAME' ,                                       1,                                       4000,                                       'TABLE_OWNER' ,                                       TABLE_OWNER,                                       'TABLE_NAME' ,                                       TABLE_NAME,                                       'PARTITION_NAME' ,                                       PARTITION_NAME) HIGH_VALUE             FROM DBA_TAB_PARTITIONS);