declare
l_cursor integer := dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(32767);
l_long_len number;
l_curpos number;
r_path varchar2(100) := 'SQL_DIR'; /*需要在DB创建此directory用于存放输出的sql文本*/
r_file utl_file.file_type;
l_the_rest varchar2(2000);
begin
dbms_sql.parse(l_cursor,
'select sql_text
from sql_temp20120921
where hash_value = :x',
dbms_sql.native);
/*sql_temp20120921表为存放抓取到的sql的表,sql_text为包含clob的字段,请修改为实际表和实际字段*/
dbms_sql.define_column_long(l_cursor, 1);
for cc1 in (select hash_value from sql_temp20120921 order by hash_value) loop
/*hash value或者sql id根据实际修改*/
if (utl_file.is_open(r_file)) then
utl_file.fclose(r_file);
end if;
r_file := utl_file.fopen(r_path, cc1.hash_value || '.sql', 'w', 32765);
utl_file.new_line(r_file);
dbms_sql.bind_variable(l_cursor, ':x', cc1.hash_value);
l_curpos := 0;
l_n := dbms_sql.execute(l_cursor);
if dbms_sql.fetch_rows(l_cursor) > 0 then
loop
dbms_sql.column_value_long(c => l_cursor,
position => 1,
length => 30000,
offset => l_curpos,
value => l_long_val,
value_length => l_long_len);
l_long_val := l_the_rest || l_long_val;
loop
l_n := instr(l_long_val, ',');
exit when nvl(l_n, 0) = 0;
utl_file.put_line(r_file, substr(l_long_val, 1, l_n - 1));
l_long_val := substr(l_long_val, l_n + 1);
end loop;
l_the_rest := l_long_val;
l_curpos := l_curpos + l_long_len;
exit when l_long_len = 0;
end loop;
utl_file.put_line(r_file, substr(l_long_val, 1));
l_the_rest := '';
end if;
end loop;
if (utl_file.is_open(r_file)) then
utl_file.fclose(r_file);
end if;
dbms_sql.close_cursor(l_cursor);
end;