long转换为varchar2脚本
create or replace package long_help authid current_user asfunction substr_of(p_query in varchar2,
p_fromin 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;
/
select table_owner,table_name,max(high_value) from (
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))
group by table_owner,table_name;
页:
[1]