| h4. unloader 패키지 생성 {code:sql} create or replace package unloader -- AUTHID CURRENT_USER -- SQL Injection 공격을 피하기위해 반드시 명시 as -- /* Function run – unloads data from any query into a file -- and creates a control file to reload that -- data into another table -- -- p_query = SQL query to "unload". May be virtually any query. -- p_tname = Table to load into. Will be put into control file. -- p_mode = REPLACE | APPEND | TRUNCATE – how to reload the data -- p_dir = directory we will write the ctl and dat file to. -- p_filename = name of file to write to. I will add .ctl and .dat -- to this name -- p_separator = field delimiter. I default this to a comma. -- p_enclosure = what each field will be wrapped in -- p_terminator = end of line character. We use this so we can unload -- and reload data with newlines in it. I default to -- " | \n" (a pipe and a newline together) and " | \r\n" on NT. -- You need only to override this if you believe your -- data will have that sequence in it. I ALWAYS add the -- OS "end of line" marker to this sequence, you should not -- */ -- function run( p_query in varchar2, -- 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 ' | ' ) -- return number; -- end; -- / -- create or replace package body unloader -- as -- -- -- g_theCursor integer default dbms_sql.open_cursor; -- g_descTbl dbms_sql.desc_tab; -- G_DESCTBL은 DBMS_SQL.DESCRIBE 호출의 출력을 유지하는 plsql테이블 g_nl varchar2(2) default chr(10); -- 개행문자 -- -- function to_hex( p_str in varchar2 ) return varchar2 -- 16진수를 문자로 변환한느데 사용하는 함수 is -- begin -- return to_char( ascii(p_str), 'fm0x' ); -- end; -- -- function is_windows return boolean -- 윈도우즈 플랫폼 여부에 따라 TRUE,FALSE를 반환하는 함수 is -- l_cfiles varchar2(4000); -- l_dummy number; -- begin -- if (dbms_utility.get_parameter_value( 'control_files', l_dummy, l_cfiles )>0) -- control_files파라미터를 조회하고 \를 찾으면 윈도우임 then -- return instr( l_cfiles, '\' ) > 0; -- else -- return FALSE; -- end if; -- end; -- -- procedure dump_ctl( p_dir in varchar2, -- DBMS_SQL.DESCRIBE_COLUMNS로 생성한 DESCRIBE테이블을 사용해서 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) := chr(10); -- -- begin -- if ( is_windows ) -- then -- l_str := chr(13) | chr(10); -- end if; -- -- l_output := utl_file.fopen( p_dir, p_filename | '.ctl', 'w' ); -- -- utl_file.put_line( l_output, 'load data' ); -- utl_file.put_line( l_output, 'infile ''' | -- p_filename | '.dat'' "str x''' | -- utl_raw.cast_to_raw( p_terminator | -- l_str ) | '''"' ); -- 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, '(' ); -- -- 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 ); -- 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 run( p_query in varchar2, -- RUN이라는 이름의 메인함수 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 ' | ' ) return number -- is -- 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 -- NLS_DATE_FORMAT을 변수에 저장 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 -- 오류가 발생했을때 NLS_DAE_FORMAT을 재설정 하기위해 예외처리 블록 설정 '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. -- */ -- begin -- /* -- Parse and describe the query. We reset the -- 파싱을 수행하고 쿼리를 정의 descTbl to an empty table so .count on it -- will be reliable. -- */ -- dbms_sql.parse( g_theCursor, p_query, dbms_sql.native ); -- g_descTbl := l_descTbl; -- 글로벌 테이블ㅇ르 재설정 dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl ); -- -- /* -- Create a control file to reload this data -- into the desired table. -- */ -- dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator, -- 한번수행하고 나면 실제로 컨트롤 파일에 생성하기위해 DUMP_CTL호출 p_enclosure, p_terminator ); -- -- /* -- 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); -- 모든컬럼에 VARCHAR2(4000)으로 저장함으로써 모든 NUMBER,DATE,RAW end loop; -- 등의 데이터타입은 VARCHAR2로 변환 -- /* -- 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 ); -- 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 -- DATE포맷을 이전 값으로 설정 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 others then -- execute immediate -- 'alter session set nls_date_format=''' | l_datefmt | ''''; -- RAISE; -- end; -- end run; -- -- -- end unloader; -- / -- set serveroutput on -- create or replace directory my_dir as '/tmp'; -- declare -- l_rows number; -- begin -- l_rows := unloader.run -- ( p_query => 'select * from scott.emp order by empno', -- 먼저 scott.emp에대한 select 권한부여 p_tname => 'emp', -- p_mode => 'replace', -- p_dir => 'MY_DIR', -- p_filename => 'emp', -- p_separator => ',', -- p_enclosure => '"', -- p_terminator => '~' ); -- -- dbms_output.put_line( to_char(l_rows) | -- ' rows extracted to ascii file' ); -- end; -- / -- |
|---|
|
|h4. 컨트롤파일
{code:sql}
load data
infile 'emp.dat' "str x'7E0A'" -- SQLLDR의 STR 속성 사용
into table emp --
replace --
fields terminated by X'2C enclosed by X'22' --구분자 문자와 인클로저 문자 사용
( --
EMPNO char(44), --
ENAME char(20), --
JOB char(18), --
MGR char(44), --
HIREDATE date 'ddmmyyyyhh24miss', -- NLS문제를 피하고, 날짜필드의 시간 컴포넌트 유지하기위해
SAL char(44), --
COMM char(44), --
DEPTNO char(44) --
) --