DECLARE
  -- FILE1     UTL_FILE.FILE_TYPE;
  v_sql        VARCHAR2(4000);
  file_name    varchar2(100);
  v_buf        long;
  v_buf1       long;
  v_amt        binary_integer := 255;
  position     integer := 1;
  file1        utl_file.file_type;
  v_length     number(17);
  i            number(10) := 1;
  id           number;
  v_str        clob;
  v_sub        varchar2(100);
  v_cnt        number;
  pattern      varchar2(10) := ',';
  v_cnt1       number;
  p_code       number;
  p_message    varchar2(4000);
  l_sql_id  varchar2(100);

begin

  ---Èç¹û¼ÓÉÏ exit when v_pos>30000; ¾Í»á³É¹¦¡£
  --file_name := 'my_sqltext.sql';
  --FILE1     := UTL_FILE.fopen('GJF_DIR', file_name, 'W');
  for i in (select  t.sql_text || ';' as sql_fulltext, t.sql_id as sql_id
              from sql_tmp2 t
             where t.outline_category is null) loop
    file_name    := 'my_sqltext' || i.sql_id|| '.sql';
    FILE1        := UTL_FILE.fopen('YHW_DIR', file_name, 'W');
    v_str        := i.sql_fulltext;
    l_sql_id := i.sql_id;
    v_cnt        := dbms_lob.instr(v_str, pattern, 1, 1);
    position     := 1;
    if v_cnt > 0 then
      WHILE v_cnt > 0 LOOP
        dbms_lob.read(v_str, v_cnt, position, v_buf);
        utl_file.put_line(file1, v_buf);
        position := dbms_lob.instr(v_str, pattern, position, 1) + 1;
        v_cnt    := dbms_lob.instr(v_str, pattern, position, 1) - position + 1;
        v_buf    := null;
     
      END LOOP;
      dbms_output.put_line(v_cnt);
    --else
      v_cnt1 := dbms_lob.instr(v_str, ';', 1, 1);
      dbms_output.put_line(v_cnt1);
      dbms_lob.read(v_str, v_cnt1, position, v_buf);
      utl_file.put_line(file1, v_buf);
      UTL_FILE.fflush(file => FILE1);
    end if;
    update sql_tmp2 t
       set outline_category = 'Y'
     where t.sql_id = i.sql_id;
    commit;
  END LOOP;
  utl_file.fclose(file1);
exception
  when others then
    p_code    := sqlcode;
    p_message := sqlerrm;
    insert into test_log
      (err_code, err_message, times, cnt, str, postion,sql_id ,buf)
    values
      (p_code,
       p_message,
       sysdate,
       v_cnt,
       v_str,
       position,
       l_sql_id,
       v_buf);
    update sql_tmp2 t
       set outline_category = 'Y'
     where t.sql_id = l_sql_id;
    commit;
 
END;

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;
/

Get Sql配置步骤(forDBA) .docx.jpg

get_sql_1105(epcis) .rar.jpg