long查询结果转换为varchar2类型

2019-11-09 13:41:30

字体：大中小

来源：转载

供稿：网友

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

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182 createor replace package long_helpauthidcurrent_userasfunctionsubstr_of( p_query invarchar2,p_from innumber,p_for innumber,p_name1invarchar2 defaultNULL,p_bind1invarchar2 defaultNULL,p_name2invarchar2 defaultNULL,p_bind2invarchar2 defaultNULL,p_name3invarchar2 defaultNULL,p_bind3invarchar2 defaultNULL,p_name4invarchar2 defaultNULL,p_bind4invarchar2 defaultNULL )returnvarchar2;end; createor replace package body long_helpas g_cursor number := dbms_sql.open_cursor; g_query varchar2(32765);PRocedurebind_variable( p_name invarchar2, p_value invarchar2 )isbegin if ( p_name isnot null ) then dbms_sql.bind_variable( g_cursor, p_name, p_value ); endif;end; functionsubstr_of( p_query invarchar2, p_from innumber, p_for innumber, p_name1invarchar2 defaultNULL, p_bind1invarchar2 defaultNULL, p_name2invarchar2 defaultNULL, p_bind2invarchar2 defaultNULL, p_name3invarchar2 defaultNULL, p_bind3invarchar2 defaultNULL, p_name4invarchar2 defaultNULL, p_bind4invarchar2 defaultNULL )returnvarchar2as 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)' ); endif; if ( nvl(p_for,0) notbetween 1 and4000 ) then raise_application_error (-20003,'For must be between 1 and 4000' ); endif; if ( p_query <> g_query org_query isNULL ) then if ( upper(trim(nvl(p_query,'x')))notlike 'SELECT%') then raise_application_error (-20001,'This must be a select only' ); endif; dbms_sql.parse( g_cursor, p_query, dbms_sql.native ); g_query := p_query; endif; 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 ); endif; returnl_buffer;endsubstr_of;end;

使用方法:查询DBA_TAB_PARTITIONS中的HIGH_VALUE

1234567891011121314151617 SELECT* FROM(SELECTTABLE_OWNER, TABLE_NAME, PARTITION_NAME, LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUEFROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNERAND TABLE_NAME=:TABLE_NAMEAND PARTITION_NAME=:PARTITION_NAME', 1, 4000, 'TABLE_OWNER', TABLE_OWNER, 'TABLE_NAME', TABLE_NAME, 'PARTITION_NAME', PARTITION_NAME) HIGH_VALUE FROMDBA_TAB_PARTITIONS);