把Oracle数据库表内容导出文件

1、运行日志表和序列

create table ODS_DATA_EXTRACT_LOG
(
  LOG_ID        NUMBER,
  TABLE_NAME    VARCHAR2(30),
  JOB_STATUS    CHAR(1),
  JOB_RUN_DATE  DATE,
  ERROR_MESSAGE VARCHAR2(300),
  SQL_TEXT      CLOB
);
create sequence SEQ_ODS_DATA_EXTRACT_LOG;

JOB_STATUS字段说明:
I: 初始化状态,存储过程执行后立即记录
P: 准备好查询SQL后,准备执行SQL语句
S: 数据导出成功,SQL_TEXT字段中有运行的SQL语句
E: 数据导出失败,ERROR_MESSAGE为出错的文本

2、创建导出文件夹、并分配权限

create or replace directory ods_data as '/ods_data/temp';
grant read, write on directory ods_data to db_user;

3、存储过程代码
按照表的修改日期提出数据,假定所有表的最后修改日期字段为ETL_DT

create or replace procedure extract_table_to_file(
  in_table_name IN VARCHAR2,
  in_etl_dt1 IN DATE DEFAULT SYSDATE,
  in_etl_dt2 IN DATE DEFAULT SYSDATE
)
IS
  v_sql VARCHAR2(30000);
  v_fields VARCHAR2(30000);
  v_row_string VARCHAR2(30000);
  v_header VARCHAR2(30000);
  v_column_count NUMBER;

  rc_row sys_refcursor;

  v_table_name VARCHAR2(30);
  v_today VARCHAR2(10);
  v_now VARCHAR2(20);
  v_output_file VARCHAR2(50);
  v_file_handle  utl_file.file_type;

  v_error_message ods_data_extract_log.error_message%TYPE;
BEGIN
  v_table_name := upper(in_table_name);

  INSERT INTO ods_data_extract_log(
    log_id, table_name, job_status, job_run_date, error_message, sql_text
  ) VALUES (
    SEQ_ods_data_extract_log.NEXTVAL,
    v_table_name,
    'I',
    SYSDATE,
    NULL,
    NULL
  );
  COMMIT;

  v_today := to_char(SYSDATE, 'yyyymmdd');
  v_now   := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');

-- 生成执行的sql语句
  v_column_count := 0;
  FOR i IN (
    SELECT column_name, data_type
    FROM all_tab_columns
    WHERE table_name = v_table_name
    ORDER BY column_name
  ) LOOP
-- 带/字符的为特殊字段,ODS不需要,不提取这部分字段
    IF INSTR(i.column_name, '/') = 0 THEN
      IF i.data_type = 'DATE' THEN
        v_fields := v_fields || 'to_char(' || i.column_name || ' ,''yyyy-mm-dd hh24:mi:ss'') ' || '|| ''|'' || ';
      ELSE
        v_fields := v_fields || i.column_name || '|| ''|'' || ';
        v_header := v_header || i.column_name || '|';
      END IF;
      v_column_count := v_column_count + 1;
    END IF;
  END loop;

  IF (v_column_count = 0) THEN
    INSERT INTO ods_data_extract_log (
      log_id, table_name, job_status, job_run_date, error_message, sql_text
    ) VALUES (
      SEQ_ods_data_extract_log.NEXTVAL,
      v_table_name,
      'E',
      SYSDATE,
      'Table does not exist: ' || v_table_name,
      NULL
    );
    COMMIT;
    RETURN;
  END IF;

-- 去掉字符后面的分隔符
  v_fields := rtrim(v_fields, '|| ''|'' ||')  || ' as row_string ';
  v_header := rtrim(v_header, '|');

  v_sql := 'select '
    || v_fields
    || ' from '
    || v_table_name;
    || ' where etl_dt >= trunc(:1) and etl_dt <= trunc(:2)';

--  DBMS_OUTPUT.put_line(v_sql);

  v_output_file := v_table_name || '-' || v_today || '.txt';
--默认一行最长为1024个字符,必须修改
  v_file_handle := utl_file.fopen('ODS_DATA' , v_output_file, 'W', 30000);

-- 写文件标题头
  utl_file.put_line(v_file_handle, v_header);  

  INSERT INTO ods_data_extract_log (
    log_id, table_name, job_status, job_run_date, error_message, sql_text
  ) VALUES (
    SEQ_ods_data_extract_log.NEXTVAL,
    v_table_name,
    'P',
    SYSDATE,
    NULL,
    v_sql
  );
  COMMIT;

--执行动态sql,结果输出到文件中
  BEGIN
    OPEN rc_row FOR v_sql USING trunc(in_etl_dt1), trunc(in_etl_dt2);
    LOOP
      FETCH rc_row INTO v_row_string;
      EXIT WHEN rc_row%NOTFOUND;

      utl_file.put_line(v_file_handle, v_row_string);
    END LOOP;

    CLOSE rc_row;
    utl_file.fclose(v_file_handle);

--写控制文件
    v_output_file := v_table_name || '.ctl';
    v_file_handle := utl_file.fopen('ODS_DATA' , v_output_file, 'W');
    utl_file.put_line(v_file_handle, v_table_name || ' ' || v_now);
    utl_file.fclose(v_file_handle);

    INSERT INTO ods_data_extract_log (
      log_id, table_name, job_status, job_run_date, error_message, sql_text
    ) VALUES (
      SEQ_ods_data_extract_log.NEXTVAL,
      v_table_name,
      'S',
      SYSDATE,
      NULL,
      NULL
    );
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      v_error_message := substr(SQLERRM, 1, 300);
      INSERT INTO ods_data_extract_log (
        log_id, table_name, job_status, job_run_date, error_message, sql_text
      ) VALUES (
        SEQ_ods_data_extract_log.NEXTVAL,
        v_table_name,
        'E',
        SYSDATE,
        v_error_message,
        NULL
      );
      COMMIT;
  END;
end extract_table_to_file;

Comments are closed.