by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_플랫 파일 언로드




1. 플랫 파일 언로드

  • EXP/IMP 또는 EXPDP/IMPDP 를 사용하지 않고, 서로 다른 시스템간(오라클을 사용하는) 데이터를 옮길 때 유용
  • 오라클 사용자 패키지(unloader)를 이용해 SQLLDR 포멧으로 text 파일 생성
  • UTL_FILE 패키지를 이용
  • LOB 데이터 Export 불가
  • VARCHAR2(4000)까지 지원
  • 레코드의 종료는 | 을 사용 (옵션으로 지정 가능)

2. 관련 스크립트


-- 다운로드 주소 : http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D6551135514501758779&p_cat=unloader15a.sql&p_company=822925097021874
--  FILE:       unloader.sql
--
--  AUTHOR:     Tom Kyte, Expert one-on-one Oracle
--              Andy Rivenes
--  DATE:       Unknown
--
--  DESCRIPTION:
--              Script to unload table data into SQL*Loader format
--              with an appropriate control file.
--
--
--  REQUIREMENTS:
--              Requires UTL_FILE support. The init.ora parameter
--              utl_file must be set to a valid directory.
--              Requires access to dbms_sql and the ability to create
--              this package.
--
--              Note: In 10g UTL_FILE_DIR has been deprecated in favor
--                    of directories.
--
--              As SYSDBA run:
--                CREATE DIRECTORY unloader AS '/u01/app/oracle/unloaddir';
--                GRANT READ, WRITE ON DIRECTORY unloaddir TO unload_user;
--
--
--
--  MODIFICATIONS:
--    Ver. 1.1,  03/31/2003, AR, Modified to perform data type checking,
--               added error messages for unsupported types. Enabled
--               exception handling - was disabled in Tom's version.
--    Ver. 1.2,  05/01/03, AR, Added a "bad" file to the SQLLDR control file.
--    Ver. 1.3,  05/02/03, AR, Added exception to catch utl_file invalid path/file
--               since this will probably be a common error.
--    Ver. 1.4,  09/19/05, AR, Added ability to output a "column header" line.
--    Ver. 1.4a, 09/21/2005, AR, Initial script creation
--    Ver. 1.4b, 10/17/2005, AR, Added "remove" function to support directories.
--    Ver. 1.4c, 10/20/2005, AR, Fixed to handle running as another user (e.g. 
--                              uga account).
--    Ver. 1.4d, 10/21/2005, AR, Corrected directory output in the .ctl file so
--                              that the database directory is translated into
--                              its OS equivalent.
--    Ver. 1.4e, 11/16/2005, AR, Added option to not create a control file.
--    Ver. 1.5,  06/14/2006, AR, Added all 1.4 changes. Both dump_ctl and run have
--                               been overloaded.
--
--  USAGE:
--    To unload using UTL_FILE_DIR:
--
--      SET LINESIZE 150;
--      SET SERVEROUTPUT on SIZE 1000000 FORMAT WRAPPED;
--      --
--      declare
--        --
--        l_rows    number;
--        --
--        begin
--          l_rows := unloader.run
--                    ( p_query      => 'select * from APP_COLUMN_METADATA',
--                      p_tname      => 'TABLE_NAME',
--                      p_mode       => 'truncate',
--                      p_dir        => '/u01/app/oracle/admin/SID/utlfile',
--                      p_filename   => 'unload_file',
--                      p_separator  => ',',
--                      p_enclosure  => '"',
--                      p_terminator => '|'
--                      p_ctl        => 'YES',
--                      p_header     => 'NO' );
--        --
--        dbms_output.put_line( to_char(l_rows) ||
--                              ' rows extracted to ascii file' );
--        --
--      end;
--      /
--
--    To unload using a database directory:
--
--      SET LINESIZE 150;
--      SET SERVEROUTPUT on SIZE 1000000 FORMAT WRAPPED;
--      --
--      declare
--        --
--        l_rows    number;
--        --
--        begin
--          l_rows := unloader.run
--                    ( p_cols       => '*',
--                      p_town       => 'OWNER',
--                      p_tname      => 'TABLE_NAME',
--                      p_mode       => 'truncate',
--                      p_dbdir      => 'dbdir',
--                      p_filename   => 'unload_file',
--                      p_separator  => ',',
--                      p_enclosure  => '"',
--                      p_terminator => '|'
--                      p_ctl        => 'YES',
--                      p_header     => 'NO' );
--        --
--        dbms_output.put_line( to_char(l_rows) ||
--                              ' rows extracted to ascii file' );
--        --
--      end;
--      /
--
--    or to remove a file (expects a database directory):
--
--      SELECT unloader.remove('TOWN','FNAME.dat') FROM dual;
--      SELECT unloader.remove('TOWN','FNAME.ctl') FROM dual;
--
--
SET LINESIZE 150;
SET SERVEROUTPUT on SIZE 1000000 FORMAT TRUNCATED;
--
--
set echo on;
--
create or replace package unloader
as
    function run( p_query      in varchar2 default NULL,
                  p_cols       in varchar2 default '*',
                  p_town       in varchar2 default USER,
                  p_tname      in varchar2,
                  p_mode       in varchar2 default 'REPLACE',
                  p_dir        in varchar2,
                  p_filename   in varchar2,
                  p_separator  in varchar2 default ',',
                  p_enclosure  in varchar2 default '"',
                  p_terminator in varchar2 default '|',
                  p_ctl        in varchar2 default 'YES',
                  p_header     in varchar2 default 'NO' )
    return number;
    --
    function run( p_query      in varchar2 default NULL,
                  p_cols       in varchar2 default '*',
                  p_town       in varchar2 default USER,
                  p_tname      in varchar2,
                  p_mode       in varchar2 default 'REPLACE',
                  p_dbdir      in varchar2,
                  p_filename   in varchar2,
                  p_separator  in varchar2 default ',',
                  p_enclosure  in varchar2 default '"',
                  p_terminator in varchar2 default '|',
                  p_ctl        in varchar2 default 'YES',
                  p_header     in varchar2 default 'NO' )
    return number;
    --
    function remove( p_dbdir      in varchar2,
                     p_filename   in varchar2)
    return number;
    --
    PROCEDURE version;
    --
    PROCEDURE help;
end;
/
--
SET ARRAYSIZE 1;
SHOW ERRORS;
--
--
create or replace package body unloader
as
  --
  g_theCursor     integer default dbms_sql.open_cursor;
  g_descTbl       dbms_sql.desc_tab;
  g_nl            varchar2(2) default chr(10);
  --
  g_version_txt   VARCHAR2(60)
        := 'unloader - Version 1.5, June 14, 2006';
  --
  --
  function to_hex( p_str in varchar2 ) return varchar2
  is
    begin
      return to_char( ascii(p_str), 'fm0x' );
    end;
  --
  --
  -- dump_ctl using UTL_FILE_DIR
  --
  procedure  dump_ctl( p_dir        in varchar2, 
                       p_filename   in varchar2, 
                       p_tname      in varchar2, 
                       p_mode       in varchar2, 
                       p_separator  in varchar2, 
                       p_enclosure  in varchar2,
                       p_terminator in varchar2 )
  is
    l_output        utl_file.file_type;
    l_sep           varchar2(5);
    l_str           varchar2(5);
    l_path          varchar2(5);
  begin
    --
    -- Set file directory separator
    --
    if ( p_dir like '%\%' ) 
    then
      -- Windows platforms --
      l_str := chr(13) || chr(10);
      if ( p_dir not like '%\' AND p_filename not like '\%' )
      then
        l_path := '\';
      end if;
    else
      l_str := chr(10);
      if ( p_dir not like '%/' AND p_filename not like '/%' )
      then
        l_path := '/';
      end if;
    end if;
    --
    -- Open output file for control file
    --
    l_output := utl_file.fopen( p_dir, p_filename || '.ctl', 'w' );
    --
    -- Output control file DDL lines
    --
    utl_file.put_line( l_output, 'load data' );
    utl_file.put_line( l_output, 'infile ''' || p_dir || l_path || 
				  p_filename || '.dat'' "str x''' || 
                                  utl_raw.cast_to_raw( p_terminator ||
				  l_str ) || '''"' );
    utl_file.put_line( l_output, 'badfile ''' || p_dir || l_path || 
                                  p_filename || '.bad'' ' );
    utl_file.put_line( l_output, 'into table ' || p_tname );
    utl_file.put_line( l_output, p_mode );
    utl_file.put_line( l_output, 'fields terminated by X''' || 
                                  to_hex(p_separator) || 
                                 ''' enclosed by X''' || 
                                  to_hex(p_enclosure) || ''' ' );
    utl_file.put_line( l_output, '(' );
    --
    -- Output column specifications
    --
    for i in 1 .. g_descTbl.count
    loop
      if ( g_descTbl(i).col_type = 12 )
      then
        utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 
                      ' date ''ddmmyyyyhh24miss'' '); 
      else
        utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 
                      ' char(' || 
                      to_char(g_descTbl(i).col_max_len*2) ||' )' );
      end if;
      l_sep := ','||g_nl ;
    end loop;
    utl_file.put_line( l_output, g_nl || ')' );
    utl_file.fclose( l_output );
  --
  -- Exception handler
  --
  exception
    when utl_file.invalid_path then
      dbms_output.put_line('Invalid path name specified for ctl file');
    when others then
      dbms_output.put_line('Error creating ctl file');
      RAISE;
  end;
  --
  -- dump_ctl using database directory
  --
  procedure  dump_ctl( p_dbdir      in varchar2, 
                       p_filename   in varchar2,
                       p_tname      in varchar2, 
                       p_mode       in varchar2, 
                       p_separator  in varchar2, 
                       p_enclosure  in varchar2,
                       p_terminator in varchar2 )
  is
    l_output        utl_file.file_type;
    l_sep           varchar2(5);
    l_str           varchar2(5);
    l_path          varchar2(5);
    l_dir           varchar2(100);
  begin
    --
    -- Set file directory from db directory
    --
    select directory_path
      into l_dir
      from all_directories
    where directory_name = p_dbdir
      and owner = 'SYS';
    --
    -- Set file directory separator
    --
    if ( l_dir like '%\%' ) 
    then
      -- Windows platforms --
      l_str := chr(13) || chr(10);
      if ( l_dir not like '%\' AND p_filename not like '\%' )
      then
        l_path := '\';
      end if;
    else
      l_str := chr(10);
      if ( l_dir not like '%/' AND p_filename not like '/%' )
      then
        l_path := '/';
      end if;
    end if;
    --
    -- Open output file for control file
    --
    l_output := utl_file.fopen( p_dbdir, p_filename || '.ctl', 'w' );
    --
    -- Output control file DDL lines
    --
    utl_file.put_line( l_output, 'load data' );
    utl_file.put_line( l_output, 'infile ''' || l_dir || l_path || 
                                  p_filename || '.dat'' "str x''' || 
                                  utl_raw.cast_to_raw( p_terminator ||
                                  l_str ) || '''"' );
    utl_file.put_line( l_output, 'badfile ''' || l_dir || l_path || 
                                  p_filename || '.bad'' ' );
    utl_file.put_line( l_output, 'into table ' || p_tname );
    utl_file.put_line( l_output, p_mode );
    utl_file.put_line( l_output, 'fields terminated by X''' || 
                                  to_hex(p_separator) || 
                                 ''' enclosed by X''' || 
                                  to_hex(p_enclosure) || ''' ' );
    utl_file.put_line( l_output, '(' );
    --
    -- Output column specifications
    --
    for i in 1 .. g_descTbl.count
    loop
      if ( g_descTbl(i).col_type = 12 )
      then
        utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 
                      ' date ''ddmmyyyyhh24miss'' '); 
      else
        utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 
                      ' char(' || 
                      to_char(g_descTbl(i).col_max_len*2) ||' )' );
      end if;
      l_sep := ','||g_nl ;
    end loop;
    utl_file.put_line( l_output, g_nl || ')' );
    utl_file.fclose( l_output );
  --
  -- Exception handler
  --
  exception
    when utl_file.invalid_path then
      dbms_output.put_line('Invalid path name specified for ctl file');
    when others then
      dbms_output.put_line('Error creating ctl file');
      RAISE;
  end;
  --
  --
  function quote(p_str in varchar2, p_enclosure in varchar2) 
    return varchar2
  is
  begin
    return p_enclosure || 
           replace( p_str, p_enclosure, p_enclosure||p_enclosure ) || 
           p_enclosure;
  end;
  --
  --
  function getColName(p_col in varchar2, p_own in varchar2, p_tab in varchar2, 
                      debug_opt in varchar2 default 'N') 
    return varchar2
  is
    l_col                 VARCHAR2(4000);
  begin
    DECLARE
      var_ddl_stmt          VARCHAR2(4000);    -- DDL statment holder
      cursor_name           INTEGER;           -- Dynamic SQL cursor holder
      var_ret_cd            INTEGER;           -- Dynamic SQL return code
      v_comment             VARCHAR2(100);
    BEGIN
      var_ddl_stmt := 'SELECT comments FROM all_col_comments WHERE table_name = '''||p_tab||
                      ''' AND owner = '''||p_own||''' AND column_name = '''||p_col||'''';
      IF UPPER(debug_opt) = 'Y' THEN
        dbms_output.put_line(var_ddl_stmt);
      END IF;
      cursor_name := DBMS_SQL.OPEN_CURSOR;
      --DDL statements are executed by the parse call, which
      --performs the implied commit
      DBMS_SQL.PARSE(cursor_name, var_ddl_stmt, DBMS_SQL.NATIVE);
      DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, v_comment, 100);
      var_ret_cd := DBMS_SQL.EXECUTE(cursor_name);
      --
      loop
        exit when ( dbms_sql.fetch_rows(cursor_name) <= 0 );
        DBMS_SQL.COLUMN_VALUE(cursor_name, 1, v_comment );
      end loop;
      --
      IF ( v_comment IS NULL ) THEN
        l_col := p_col;
      ELSE
        l_col := v_comment;
      END IF;
      DBMS_SQL.CLOSE_CURSOR(cursor_name);
      --
    EXCEPTION
      WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(cursor_name) THEN
          DBMS_SQL.CLOSE_CURSOR(cursor_name);
        END IF;
        return p_col;
    END;
    --
    return l_col;
  end;
  --
  -- Uses UTL_FILE_DIR 
  --
  function run( p_query      in varchar2 default NULL,
                p_cols       in varchar2 default '*',
                p_town       in varchar2 default USER,
                p_tname      in varchar2,
                p_mode       in varchar2 default 'REPLACE',
                p_dir        in varchar2,
                p_filename   in varchar2,
                p_separator  in varchar2 default ',',
                p_enclosure  in varchar2 default '"',
                p_terminator in varchar2 default '|',
                p_ctl        in varchar2 default 'YES',
                p_header     in varchar2 default 'NO' ) return number
  is
    l_query         varchar2(4000);
    l_output        utl_file.file_type;
    l_columnValue   varchar2(4000);
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
    l_line          long;
    l_datefmt       varchar2(255);
    l_descTbl       dbms_sql.desc_tab;
  begin
    select value 
      into l_datefmt 
      from nls_session_parameters 
     where parameter = 'NLS_DATE_FORMAT';
     --
     -- Set the date format to a big numeric string. Avoids
     -- all NLS issues and saves both the time and date.
     --
    execute immediate 
      'alter session set nls_date_format=''ddmmyyyyhh24miss'' ';
    --
    -- Set up an exception block so that in the event of any
    -- error, we can at least reset the date format back.
    --
    declare
      invalid_type EXCEPTION;
    begin
      -- 
      -- Parse and describe the query. We reset the 
      -- descTbl to an empty table so .count on it 
      -- will be reliable.
      --
      if p_query is NULL then
        l_query := 'select '||p_cols||' from '||p_town||'.'||p_tname;
      else
        l_query := p_query;
      end if;
      --
      dbms_sql.parse( g_theCursor, l_query, dbms_sql.native );
      g_descTbl := l_descTbl;
      dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
      --
      -- Verify that the table contains supported columns - currently
      -- LOBs are not supported.
      --
      for i in 1 .. g_descTbl.count loop
        IF (g_descTbl(i).col_type = 1) OR (g_descTbl(i).col_type = 2)
            OR (g_descTbl(i).col_type = 12) OR (g_descTbl(i).col_type = 96)
            OR (g_descTbl(i).col_type = 8) OR (g_descTbl(i).col_type = 23) THEN
          NULL;
        ELSE
          RAISE invalid_type;
        END IF;
      end loop;
      --
      -- Create a control file to reload this data
      -- into the desired table.
      --
      IF p_ctl = 'YES' THEN
        dump_ctl( p_dir=>p_dir,
                  p_filename=>p_filename,
                  p_tname=>p_tname,
                  p_mode=>p_mode,
                  p_separator=>p_separator, 
                  p_enclosure=>p_enclosure,
                  p_terminator=>p_terminator );
      END IF;
      --
      -- Bind every single column to a varchar2(4000). We don't care 
      -- if we are fetching a number or a date or whatever.
      -- Everything can be a string.
      --
      for i in 1 .. l_colCnt loop
        dbms_sql.define_column( g_theCursor, i, l_columnValue, 4000 );
      end loop;
      --
      -- Run the query - ignore the output of execute. It is only 
      -- valid when the DML is an insert/update or delete. 
      --
      l_cnt := dbms_sql.execute(g_theCursor);
      --
      -- Open the file to write output to and then write the
      -- delimited data to it.  
      --
      l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w', 32760 );
      --
      -- Output a column header. This version uses table column comments if they
      -- exist, otherwise it defaults to the actual table column name.
      --
      IF p_header = 'YES' THEN
        l_separator := '';
        l_line := '';
        for i in 1 .. g_descTbl.count
        loop
          l_line := l_line || l_separator || 
                    quote( getColName(g_descTbl(i).col_name, p_town, p_tname), p_enclosure );
          l_separator := p_separator;
        end loop;
        utl_file.put_line( l_output, l_line );
      END IF;
      --
      -- Output data
      --
      loop
        exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 );
        l_separator := '';
        l_line := null;
        for i in 1 .. l_colCnt loop
          dbms_sql.column_value( g_theCursor, i, 
                                 l_columnValue );
          l_line := l_line || l_separator || 
                    quote( l_columnValue, p_enclosure );
          l_separator := p_separator;
        end loop;
        l_line := l_line || p_terminator;
        utl_file.put_line( l_output, l_line );
        l_cnt := l_cnt+1;
      end loop;
      utl_file.fclose( l_output );
      --
      -- Now reset the date format and return the number of rows
      -- written to the output file.
      --
      execute immediate 
        'alter session set nls_date_format=''' || l_datefmt || '''';
      --
      return l_cnt;
    exception
      --
      -- In the event of ANY error, reset the data format and
      -- re-raise the error.
      --
      when invalid_type then
        execute immediate 
          'alter session set nls_date_format=''' || l_datefmt || '''';
        --
        dbms_output.put_line('Error - Table: '||p_tname||' contains an unsupported column type');
        dbms_output.put_line('Table is being skipped');
        --
        return 0;
      when utl_file.invalid_path then
        execute immediate 
          'alter session set nls_date_format=''' || l_datefmt || '''';
        --
        dbms_output.put_line('Invalid path name specified for dat file');
        --
        return 0;
      when others then
        execute immediate 
          'alter session set nls_date_format=''' || l_datefmt || '''';
        --
        RAISE;
        --
        return 0;
    end;
  end run;
  --
  -- Uses database directory
  --
  function run( p_query      in varchar2 default NULL,
                p_cols       in varchar2 default '*',
                p_town       in varchar2 default USER,
                p_tname      in varchar2,
                p_mode       in varchar2 default 'REPLACE',
                p_dbdir      in varchar2,
                p_filename   in varchar2,
                p_separator  in varchar2 default ',',
                p_enclosure  in varchar2 default '"',
                p_terminator in varchar2 default '|',
                p_ctl        in varchar2 default 'YES',
                p_header     in varchar2 default 'NO' ) return number
  is
    l_query         varchar2(4000);
    l_output        utl_file.file_type;
    l_columnValue   varchar2(4000);
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
    l_line          long;
    l_datefmt       varchar2(255);
    l_descTbl       dbms_sql.desc_tab;
  begin
    select value 
      into l_datefmt 
      from nls_session_parameters 
     where parameter = 'NLS_DATE_FORMAT';
     --
     -- Set the date format to a big numeric string. Avoids
     -- all NLS issues and saves both the time and date.
     --
    execute immediate 
      'alter session set nls_date_format=''ddmmyyyyhh24miss'' ';
    --
    -- Set up an exception block so that in the event of any
    -- error, we can at least reset the date format back.
    --
    declare
      invalid_type EXCEPTION;
    begin
      -- 
      -- Parse and describe the query. We reset the 
      -- descTbl to an empty table so .count on it 
      -- will be reliable.
      --
      if p_query is NULL then
        l_query := 'select '||p_cols||' from '||p_town||'.'||p_tname;
      else
        l_query := p_query;
      end if;
      --
      --     
      -- dbms_output.put_line('Query: '||l_query);
      --
      --
      dbms_sql.parse( g_theCursor, l_query, dbms_sql.native );
      g_descTbl := l_descTbl;
      dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
      --
      -- Verify that the table contains supported columns - currently
      -- LOBs are not supported.
      --
      for i in 1 .. g_descTbl.count loop
        IF (g_descTbl(i).col_type = 1) OR (g_descTbl(i).col_type = 2)
            OR (g_descTbl(i).col_type = 12) OR (g_descTbl(i).col_type = 96)
            OR (g_descTbl(i).col_type = 8) OR (g_descTbl(i).col_type = 23) THEN
          NULL;
        ELSE
          RAISE invalid_type;
        END IF;
      end loop;
      --
      -- Create a control file to reload this data
      -- into the desired table.
      --
      dbms_output.put_line('Create the control file');
      --
      IF p_ctl = 'YES' THEN
        dump_ctl( p_dbdir=>p_dbdir,
                  p_filename=>p_filename,
                  p_tname=>p_tname,
                  p_mode=>p_mode,
                  p_separator=>p_separator, 
                  p_enclosure=>p_enclosure,
                  p_terminator=>p_terminator );
      END IF;
      --
      -- Bind every single column to a varchar2(4000). We don't care 
      -- if we are fetching a number or a date or whatever.
      -- Everything can be a string.
      --
      for i in 1 .. l_colCnt loop
        dbms_sql.define_column( g_theCursor, i, l_columnValue, 4000 );
      end loop;
      --
      -- Run the query - ignore the output of execute. It is only 
      -- valid when the DML is an insert/update or delete. 
      --
      l_cnt := dbms_sql.execute(g_theCursor);
      --
      -- Open the file to write output to and then write the
      -- delimited data to it.  
      --
      l_output := utl_file.fopen( p_dbdir, p_filename || '.dat', 'w', 32760 );
      --
      -- Output a column header. This version uses table column comments if they
      -- exist, otherwise it defaults to the actual table column name.
      --
      IF p_header = 'YES' THEN
        l_separator := '';
        l_line := '';
        for i in 1 .. g_descTbl.count
        loop
          l_line := l_line || l_separator || 
                    quote( getColName(g_descTbl(i).col_name, p_town, p_tname), p_enclosure );
          l_separator := p_separator;
        end loop;
        utl_file.put_line( l_output, l_line );
      END IF;
      --
      -- Output data
      --
      loop
        exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 );
        l_separator := '';
        l_line := null;
        for i in 1 .. l_colCnt loop
          dbms_sql.column_value( g_theCursor, i, 
                                 l_columnValue );
          l_line := l_line || l_separator || 
                    quote( l_columnValue, p_enclosure );
          l_separator := p_separator;
        end loop;
        l_line := l_line || p_terminator;
        utl_file.put_line( l_output, l_line );
        l_cnt := l_cnt+1;
      end loop;
      utl_file.fclose( l_output );
      --
      -- Now reset the date format and return the number of rows
      -- written to the output file.
      --
      execute immediate 
        'alter session set nls_date_format=''' || l_datefmt || '''';
      --
      return l_cnt;
    exception
      --
      -- In the event of ANY error, reset the data format and
      -- re-raise the error.
      --
      when invalid_type then
        execute immediate 
          'alter session set nls_date_format=''' || l_datefmt || '''';
        --
        dbms_output.put_line('Error - Table: '||p_tname||' contains an unsupported column type');
        dbms_output.put_line('Table is being skipped');
        --
        return 0;
      when utl_file.invalid_path then
        execute immediate 
          'alter session set nls_date_format=''' || l_datefmt || '''';
        --
        dbms_output.put_line('Invalid path name specified for dat file');
        --
        return 0;
      when others then
        execute immediate 
          'alter session set nls_date_format=''' || l_datefmt || '''';
        --
        RAISE;
        --
        return 0;
    end;
  end run;
  --
  --
  function remove( p_dbdir      in varchar2,
                   p_filename   in varchar2) return number
  is
  begin
    begin
      utl_file.fremove( p_dbdir, p_filename );
      return 0;
    exception
      when utl_file.invalid_path then
        dbms_output.put_line('Invalid path name specified for file '||p_filename);
        return 1;
      when utl_file.delete_failed then
        dbms_output.put_line('Delete failed for file: '||p_filename);
        return 1;
      when others then
        dbms_output.put_line('Error during remove of file: '||p_filename);
        return 1;
    end;
  end remove;
  --
  --
  PROCEDURE version
  IS
  -- 
  BEGIN
    IF LENGTH(g_version_txt) > 0 THEN
      dbms_output.put_line(' ');
      dbms_output.put_line(g_version_txt);
    END IF;
  -- 
  END version;
  --
  --
  PROCEDURE help 
  IS
  -- 
  -- Lists help menu
  --
  BEGIN
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE(g_version_txt);
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Function run - Unloads data from any query into a file, and creates a');
    DBMS_OUTPUT.PUT_LINE('               control file to reload this data into another table.');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('p_query      = SQL query to ''unload''. May be virtually any query.');
    DBMS_OUTPUT.PUT_LINE(' OR');
    DBMS_OUTPUT.PUT_LINE('p_cols       = Columns to ''unload''. Defaults to ''*'' for all columns.');
    DBMS_OUTPUT.PUT_LINE('p_town       = Owner of table to unload.');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('p_tname      = Table to unload. Will also be put into the control file for load
ing.');
    DBMS_OUTPUT.PUT_LINE('p_mode       = REPLACE|APPEND|TRUNCATE - how to reload the data.');
    DBMS_OUTPUT.PUT_LINE('p_dir        = Directory we will write the .ctl and .dat file to.');
    DBMS_OUTPUT.PUT_LINE(' OR');
    DBMS_OUTPUT.PUT_LINE('p_dbdir      = Database directory name we will write the .ctl and .dat file to.
');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('p_filename   = Name of file to write to. I will add .ctl and .dat to this name.
');
    DBMS_OUTPUT.PUT_LINE('p_separator  = Field delimiter. I default this to a comma.');
    DBMS_OUTPUT.PUT_LINE('p_enclosure  = What each field will be wrapped in.');
    DBMS_OUTPUT.PUT_LINE('p_terminator = End of line character. We use this so we can unload and');
    DBMS_OUTPUT.PUT_LINE('               reload data with newlines in it. I default to ''|\n'' (a');
    DBMS_OUTPUT.PUT_LINE('               pipe and a newline together), ''|\r\n'' on NT. You need');
    DBMS_OUTPUT.PUT_LINE('               only to override this if you believe your data will');
    DBMS_OUTPUT.PUT_LINE('               have this sequence in it. I ALWAYS add the OS ''end of');
    DBMS_OUTPUT.PUT_LINE('               line'' marker to this sequence, you should not.');
    DBMS_OUTPUT.PUT_LINE('p_ctl        = YES - Default, output a control file.');
    DBMS_OUTPUT.PUT_LINE('                NO - No control file.');
    DBMS_OUTPUT.PUT_LINE('p_header     = YES - Output a column header line in the .dat file.');
    DBMS_OUTPUT.PUT_LINE('                NO - Default, no header.');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('NOTE: In SQL*Plus set the following for best results:');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('      SET SERVEROUTPUT ON SIZE 1000000 FORMAT TRUNCATED');
  END help;
  --
  --
end unloader;
/
--
SET ARRAYSIZE 1;
SHOW ERRORS;

3. 테스트

SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

테이블이 생성되었습니다.

-- 패키지 실행
SET LINESIZE 150;
SET SERVEROUTPUT on SIZE 1000000 FORMAT WRAPPED;

DECLARE
    L_ROWS   NUMBER;
BEGIN
    L_ROWS    :=
        UNLOADER.RUN(P_COLS             => '*'
                   , P_TNAME            => 'emp'
                   , P_MODE             => 'TRUNCATE'
                   , P_DBDIR            => 'dir2'
                   , P_FILENAME         => 'flat_test'
                   , P_SEPARATOR        => ','
                   , P_ENCLOSURE        => '"'
                   , P_TERMINATOR       => '|'
                   , P_CTL              => 'YES'
                   , P_HEADER           => 'NO');
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(L_ROWS) || ' rows extracted to ascii file');
END;

Create the control file
14 rows extracted to ascii file

PL/SQL 처리가 정상적으로 완료되었습니다.


-- CTL 파일과 데이터가 아래와 같이 생성됨
[oracle@mydream demo_dir]$ cat flat_test.ctl
load data
infile '/app/demo_dir/flat_test.dat' "str x'7C0A'"
badfile '/app/demo_dir/flat_test.bad'
into table emp
TRUNCATE
fields terminated by X'2c' enclosed by X'22'
(
EMPNO char(44 ),
ENAME char(20 ),
JOB char(18 ),
MGR char(44 ),
HIREDATE date 'ddmmyyyyhh24miss' ,
SAL char(44 ),
COMM char(44 ),
DEPTNO char(44 )
)

[oracle@mydream demo_dir]$ cat flat_test.dat
"7369","SMITH","CLERK","7902","17121980000000","800","","20"|
"7499","ALLEN","SALESMAN","7698","20021981000000","1600","300","30"|
"7521","WARD","SALESMAN","7698","22021981000000","1250","500","30"|
"7566","JONES","MANAGER","7839","02041981000000","2975","","20"|
"7654","MARTIN","SALESMAN","7698","28091981000000","1250","1400","30"|
"7698","BLAKE","MANAGER","7839","01051981000000","2850","","30"|
"7782","CLARK","MANAGER","7839","09061981000000","2450","","10"|
"7788","SCOTT","ANALYST","7566","19041987000000","3000","","20"|
"7839","KING","PRESIDENT","","17111981000000","5000","","10"|
"7844","TURNER","SALESMAN","7698","08091981000000","1500","0","30"|
"7876","ADAMS","CLERK","7788","23051987000000","1100","","20"|
"7900","JAMES","CLERK","7698","03121981000000","950","","30"|
"7902","FORD","ANALYST","7566","03121981000000","3000","","20"|
"7934","MILLER","CLERK","7782","23011982000000","1300","","10"|

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.