create or replace procedure IMP_DATA
(
  file_name in varchar2 --要导入的文件名,包含路径(如:d:\test\exp_0003.txt)
 ,p_user    in varchar2 default SYS_CONTEXT('USERENV', 'CURRENT_USER') --要导入的用户,缺省为当前用户
 ,p_sep     in varchar2 default ',' --字段分隔符,默认为逗号(需要打开要导入的文件确认导入的字段分隔符)
) AS
  /*
  描述:根据EXP_DATA过程导出的数据进行导入
  created by cryking 2013.03.07
  注意:1.本存储建议由SYS账户或具有SYSDBA权限的账户执行
       2.不要在其他事务中运行本存储过程
       3. 默认导入的数据(p_user为空,或未指定)全部在当前用户下
  */
  v_file UTL_FILE.file_type;
  TYPE t_filed IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;
  v_fileds t_filed;
  TYPE t_data IS TABLE OF varchar2(4000) INDEX BY BINARY_INTEGER;
  v_data     t_data;
  v_datatype t_data;
  v_sql      varchar2(30000);
  V_esql     varchar2(30000);
  v_filed    VARCHAR2(100) := '';
  v_filedstr VARCHAR2(4000) := '';
  V_TABLE    VARCHAR2(1000);
  v_user     varchar2(20);
  v_path     varchar2(500);
  v_filename varchar2(50);
  v_sep      varchar2(10);
  v_text     varchar2(32600);
  v_textTmp  varchar2(32600);
  i_flag     integer:=0;
  I_TABLE    INTEGER;
  exp_sep exception;
  ex_table exception;
BEGIN
  /*----------输入参数检查部分----------*/
  --没有输入用户的情况
  if trim(p_user) is null then
    v_user := SYS_CONTEXT('USERENV', 'CURRENT_USER');
  else
    v_user := upper(p_user);
  end if;

  if trim(p_sep) is null then
    v_sep := ',';
  else
    v_sep := p_sep;
  end if;

  --获取路径
  select replace(file_name, regexp_REPLACE(file_name, '\\*[^\\*]*\\'), '')
    into v_path
    from dual;
  --获取文件名
  select regexp_REPLACE(file_name, '\\*[^\\*]*\\')
    into v_filename
    from dual;

  /*------------------------------------*/
  --设置日期格式
  EXECUTE IMMEDIATE 'ALTER session SET nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
  rollback; --防止在其他事务中运行本存储,先回滚之前的事务
  execute immediate 'create or replace directory IMPDIR as ''' || v_path ||
                    ''' '; --创建目录

  v_file := UTL_FILE.fopen('IMPDIR', v_filename, 'r'); --读取文件

  --导入所有数据
  loop
    UTL_FILE.get_line(v_file, v_text);
    if substr(v_text, -1, 1) <> ',' and INSTR(v_text, '[TABLE:]') = 0 then
      v_textTmp := v_text || chr(10);
      continue;
    else
      v_textTmp := v_textTmp || v_text;
    end if;
    --获取表名
    IF INSTR(v_text, '[TABLE:]') > 0 THEN
      v_textTmp := '';
      V_TABLE   := UPPER(SUBSTR(v_text, INSTR(v_text, ']', 1, 2) + 1));
      SELECT COUNT(*)
        INTO I_TABLE
        FROM all_TABLES
       WHERE all_TABLES.TABLE_NAME = V_TABLE
         AND OWNER = v_user;
      IF I_TABLE = 0 THEN
        v_sql := 'create table ' || v_user || '.' || V_TABLE || '(';
      ELSE
        v_sql := 'insert into ' || v_user || '.' || V_TABLE || '(';
      END IF;
    END IF;
    --获取字段列表
    IF INSTR(v_text, '[filed:]') > 0 THEN
      v_textTmp := '';
      select * bulk collect
        into v_fileds
        from table(splitstr(replace(v_text, '[filed:]'), v_sep));
      IF INSTR(v_sql, 'create ') > 0 then
        FOR I IN 1 .. v_fileds.COUNT
        LOOP
          V_sql := v_sql || v_fileds(i) || ',';
        END LOOP;
        V_sql := v_sql || ') ';
        execute immediate v_sql; --先创建表
        v_sql := 'INSERT INTO ' || v_user || '.' || V_TABLE || ' VALUES(';
      else
        v_filedstr := '';
        FOR I IN 1 .. v_fileds.COUNT
        LOOP
          SELECT COUNT(*)
            INTO I_TABLE
            FROM ALL_tab_columns
           WHERE TABLE_NAME = V_TABLE
             AND OWNER = v_user
             and COLUMN_NAME = UPPER(v_fileds(i));
          if I_TABLE = 0 then
            raise ex_table;
          else
            v_sql := v_sql || v_fileds(i) || ',';
            select data_type
              into v_filed
              from ALL_tab_columns
             where TABLE_NAME = V_TABLE
               AND OWNER = v_user
               and COLUMN_NAME = UPPER(v_fileds(i));
            v_filedstr := v_filedstr || v_filed || ',';
          end if;
        end loop;
        V_sql := substr(v_sql, 1,