먼저 utl_file_dir 파라메터가 설정되어 있는지 확인해
보아야 합니다.
SELECT name, value, isses_modifiable, issys_modifiable
FROM v$parameter
WHERE name = 'utl_file_dir';
위에서 설정된 디렉토리에만 I/O가 가능합니다.
다음은 어느 책인가에 나와있는 2가지 예제입니다.
DECLARE
lv_file_id_num UTL_FILE.FILE_TYPE;
BEGIN
lv_file_id_num := UTL_FILE.FOPEN('c:\apps\loaders',
'test4.dat', 'W');
UTL_FILE.FCLOSE(lv_file_id_num);
END;
/
CREATE OR REPLACE PROCEDURE process_products
(p_directory_txt VARCHAR2 := 'c:\apps\loaders',
p_filename_txt VARCHAR2 := 'prodlist.dat',
p_string_txt VARCHAR2) IS
-- DESCRIPTION: This package processes a product data file by
-- loading all product records into the s_product table. The
-- file loaded is scanned while loaded to log each line that
-- a specified string was found on. The statistics of the load
-- are also logged.
lv_file_id_num UTL_FILE.FILE_TYPE;
lv_file_id_num_2 UTL_FILE.FILE_TYPE;
lv_output_filename_txt VARCHAR2(30);
lv_filename_txt VARCHAR2(30);
lv_error_desc_txt VARCHAR2(50);
lv_line_cnt_num PLS_INTEGER := 0;
lv_error_cnt_num PLS_INTEGER := 0;
lv_buffer_txt VARCHAR2(2000);
lv_found_bln BOOLEAN := FALSE;
-- This procedure reduces the code redundancy in the exception
-- handling process and is called by each exception condition.
PROCEDURE error_processing (p_file_num UTL_FILE.FILE_TYPE,
p_line_num PLS_INTEGER, p_error_txt VARCHAR2) IS
BEGIN
ROLLBACK;
UTL_FILE.PUT_LINE(p_file_num, 'Line: ' ||
p_line_num || ' Error: ' || p_error_txt);
UTL_FILE.PUT_LINE(p_file_num, '----------------------------');
UTL_FILE.PUT_LINE(p_file_num, 'File Process ABORTED');
UTL_FILE.FCLOSE_ALL;
END error_processing;
BEGIN
-- Opens the input data file on the server for reading.
lv_file_id_num := UTL_FILE.FOPEN(p_directory_txt,
p_filename_txt, 'R');
-- Creates the log file name by stripping the file
-- extension and adding .log on the end.
lv_output_filename_txt := SUBSTR(p_filename_txt, 1,
INSTR(p_filename_txt, '.') - 1) || '.log';
-- Opens the log file on the server for writing.
lv_file_id_num_2 := UTL_FILE.FOPEN(p_directory_txt,
lv_output_filename_txt, 'W');
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'Processing Products Log File');
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'----------------------------');
UTL_FILE.PUT_LINE(lv_file_id_num_2, 'Directory: ' ||
p_directory_txt);
UTL_FILE.PUT_LINE(lv_file_id_num_2, 'Input File: ' ||
p_filename_txt);
UTL_FILE.PUT_LINE(lv_file_id_num_2, 'Output File: ' ||
lv_output_filename_txt);
UTL_FILE.PUT_LINE(lv_file_id_num_2, 'Search String: ' ||
p_string_txt);
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'----------------------------');
LOOP
lv_buffer_txt := NULL;
-- When end of file reached, the loop is terminated.
BEGIN <<read_file>>
UTL_FILE.GET_LINE(lv_file_id_num, lv_buffer_txt);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END read_file;
lv_line_cnt_num := lv_line_cnt_num + 1;
-- If an error encountered on the insert, then the line
-- and error are logged to the log file and processing
-- continues.
BEGIN <<insert_product>>
-- When processing fixed length data files, if the spaces
-- are not trimmed off the left and right side, the spaces
-- will be part of the value inserted.
INSERT INTO s_product
(product_id, product_name, short_desc)
VALUES
(RTRIM(LTRIM(SUBSTR(lv_buffer_txt, 1, 7), ' '), ' '),
RTRIM(LTRIM(SUBSTR(lv_buffer_txt, 8, 50), ' '), ' '),
RTRIM(LTRIM(SUBSTR(lv_buffer_txt, 58), ' '), ' '));
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'Line: ' || lv_line_cnt_num || ' Error: ' ||
SUBSTR(SQLERRM,1,200));
lv_error_cnt_num := lv_error_cnt_num + 1;
END insert_product;
-- The line number is written to the log file every time
-- the search string is found.
IF INSTR(UPPER(lv_buffer_txt), UPPER(p_string_txt)) > 0 THEN
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'String Found on Line: ' || lv_line_cnt_num);
lv_found_bln := TRUE;
END IF;
END LOOP;
COMMIT; -- If the search string is not found in the file, a message is
-- written to the log file to indicate this condition.
IF NOT lv_found_bln THEN
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'The string was not found in the file.');
END IF;
-- Final processing statistics are written to the log file.
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'----------------------------');
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'Number of Total Products Processed: ' ||
TO_CHAR(lv_line_cnt_num, '999,999'));
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'Number of Products Inserted: ' ||
TO_CHAR(lv_line_cnt_num - lv_error_cnt_num, '999,999'));
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'Number of Products with Error: ' ||
TO_CHAR(lv_error_cnt_num, '999,999'));
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'----------------------------');
UTL_FILE.PUT_LINE(lv_file_id_num_2,
'File Processed Successfully');
UTL_FILE.FCLOSE_ALL;
EXCEPTION
-- If any of the UTL_FILE exceptions are raised or any errors
-- encountered, the entire process is rolled back, and the line
-- number and error number are written to the log file.
WHEN UTL_FILE.internal_error THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.INTERNAL_ERROR encountered');
WHEN UTL_FILE.invalid_filehandle THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.INVALID_FILEHANDLE encountered');
WHEN UTL_FILE.invalid_mode THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.INVALID_MODE encountered');
WHEN UTL_FILE.invalid_operation THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.INVALID_OPERATION encountered');
WHEN UTL_FILE.invalid_path THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.INVALID_PATH encountered');
WHEN UTL_FILE.read_error THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.READ_ERROR encountered');
WHEN UTL_FILE.write_error THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
'UTL_FILE.WRITE_ERROR encountered');
WHEN OTHERS THEN
error_processing(lv_file_id_num_2, lv_line_cnt_num,
SUBSTR(SQLERRM,1,200));
END process_products;
/
>>sissky 님께서 쓰시길<<
:: PL/SQL 을 이용해서 프로시져를 짜야하거덩요...
:: 음...입력받는값은 없구요..걍 프로시져안에서..SQL문을 돌려서나온값을을 TXT파일로 출력되도
:: 록 하는 거예여...
:: oracle package 안에...파일입출력에 사용되는 function들은 있던데...
:: 어케 사용해야할지 잘몰라서요.
:: 혹시..예제를 가지구 계시면 좀 보여주세요.
:: 부탁드려요...^^
:: .
:: .
:: 참 그리구...혹시 프로시져가 서버에 있다가 매일매일 특정 시간이 되면 실행되게 하는법 알구
:: 계신분 있나요?그럼 꼭꼭 연락 주세요..
:: 꼭이요~~~~~
|