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"|