十
14
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;
no comment untill now