麻袋爸爸

create or replace procedure EXP_DATA_TO_TXT(tname    in varchar2,
                                            strwhere in varchar2,
                                            strconn  in varchar2,
                                            utl      in varchar2,
                                            resu     out number) is
  type refcur is ref cursor;
  curdata       refcur;
  curstruc      refcur;
  cursh         refcur;
  out_file      utl_file.file_type;
  str           varchar2(30000);
  tmptext       varchar2(30000);
  intp          number;
  maxr          number;
  tmptname      varchar2(30);
  c_table_name  VARCHAR2(30);
  c_col_name    VARCHAR2(30);
  c_data_type   VARCHAR2(106);
  c_data_length NUMBER;
  c_col_id      NUMBER;
begin
  resu := 0;
  if substr(tname, 1, 1) = '$' then
    open curstruc for
      select table_name, column_name, data_type, data_length, column_id
        from user_tab_cols
       order by table_name, column_id;
  else
    open curstruc for
      select table_name, column_name, data_type, data_length, column_id
        from user_tab_cols
       where table_name = upper(tname)
       order by column_id;
  
  end if;
  loop
    fetch curstruc
      into c_table_name, c_col_name, c_data_type, c_data_length, c_col_id;
    exit when curstruc%notfound;
    if c_table_name != tmptname or tmptname is null then
      tmptname := c_table_name;
      intp     := 1;
      str      := '';
      out_file := utl_file.fopen(utl,
                                 lower(c_table_name) || '.ctl',
                                 'w',
                                 1000);
      utl_file.put_line(out_file, 'options (bindsize=655360,direct=true)');
      utl_file.put_line(out_file, 'load data');
      utl_file.put_line(out_file,
                        'infile ''' || lower(c_table_name) || '.txt''');
      utl_file.put_line(out_file,
                        'append into table ' || c_table_name || ' (');
      select count(*)
        into maxr
        from user_tab_cols
       where table_name = c_table_name;
    end if;
  
    if trim(c_data_type) = 'CHAR' or trim(c_data_type) = 'VARCHAR2' then
      str := str || 'lpad(nvl(' || c_col_name || ','' ''),' ||
             to_char(c_data_length) || ', '' '')||''|''||';
      if c_col_id = maxr then
        utl_file.put_line(out_file,
                          c_col_name || '  position(' || intp || ':' ||
                          to_char(intp + to_number(c_data_length) - 1) || ')');
      else
        utl_file.put_line(out_file,
                          c_col_name || '  position(' || intp || ':' ||
                          to_char(intp + to_number(c_data_length) - 1) || '),');
      end if;
      intp := intp + to_number(c_data_length) + 1;
    end if;
    if trim(c_data_type) = 'NUMBER' then
      str := str || 'rpad(nvl(to_char(' || c_col_name || '),'' ''),' ||
             to_char(c_data_length) || ', '' '')||''|''||';
      if c_col_id = maxr then
        utl_file.put_line(out_file,
                          c_col_name || '  position(' || intp || ':' ||
                          to_char(intp + to_number(c_data_length) - 1) || ')');
      else
        utl_file.put_line(out_file,
                          c_col_name || '  position(' || intp || ':' ||
                          to_char(intp + to_number(c_data_length) - 1) || '),');
      end if;
      intp := intp + to_number(c_data_length) + 1;
    end if;
    if trim(c_data_type) = 'DATE' then
      str := str || 'lpad(nvl(to_char(' || c_col_name ||
             ',''yyyymmddhh24miss''),'' ''),14,'' '')' || '||''|''||';
      if c_col_id = maxr then
        utl_file.put_line(out_file,
                          c_col_name || '  position(' || intp || ':' ||
                          to_char(intp + 13) ||
                          ') date ''yyyymmddhh24miss''');
      else
        utl_file.put_line(out_file,
                          c_col_name || '  position(' || intp || ':' ||
                          to_char(intp + 13) ||
                          ') date ''yyyymmddhh24miss'',');
      end if;
      intp := intp + 15;
    end if;
  
    if c_col_id = maxr then
      utl_file.put_line(out_file, ')');
      utl_file.fclose(out_file);
    
      str := 'select ' || trim('|' from str) || ' as text_string from ' ||
             c_table_name;
      if (strwhere is not null) and substr(tname, 1, 1) != '$' then
        str := str || ' where ' || strwhere;
      end if;
      out_file := utl_file.fopen(utl,
                                 lower(c_table_name) || '.txt',
                                 'w',
                                 1000);
      open curdata for str;
      loop
        fetch curdata
          into tmptext;
        exit when curdata%notfound;
        utl_file.put_line(out_file, tmptext);
      end loop;
      close curdata;
      utl_file.fclose(out_file);
    end if;
  end loop;
  close curstruc;

  open cursh for
    select distinct table_name from user_tab_cols;
  if instr(utl,'\')=0 then
     out_file := utl_file.fopen(utl, 'start.sh', 'w', 1000);
  else
     out_file := utl_file.fopen(utl, 'start.bat', 'w', 1000);
  end if;
  
  loop
    fetch cursh
      into c_table_name;
    exit when cursh%notfound;
    utl_file.put_line(out_file,
                      'sqlldr userid=' || strconn || ' control=' ||
                      c_table_name || '.ctl' || ' log=' || c_table_name ||
                      '.log' || ' bad=' || c_table_name || '.bad');
  end loop;
  utl_file.fclose(out_file);
  close cursh;
exception
  when others then
    resu := 1;
end EXP_DATA_TO_TXT;