UTL_FILE Example

Hi friends,
Could you please give me an example code of utl_file package with few procedures it supports.

regards
anandkl

[Edited by anandkl on 02-27-2002 at 07:33 AM]

this should help u
for lv_loc variable u hv to give the file location
there will be last of waste stuff that u hv 2 remove which are used for my purpose

PROCEDURE sp_sec_upload_accts
(
p_fname IN VARCHAR2,
p_sale_code IN VARCHAR2,
p_pool_code IN VARCHAR2,
p_pool_status IN VARCHAR2,
p_seq_no IN NUMBER,
p_session_id IN VARCHAR2,
p_cnt OUT NUMBER,
p_sum_curr_bal OUT NUMBER,
p_status OUT NUMBER,
p_err_cd OUT VARCHAR2,
p_err_msg OUT VARCHAR2
) IS

TYPE obj_account_no IS TABLE OF d_sec_account.account_no%TYPE INDEX BY BINARY_INTEGER;

lv_typ_acct_no_valid obj_account_no;
lv_typ_acct_no_invalid obj_account_no;

lv_floc VARCHAR2(1000);
lv_ftype UTL_FILE.FILE_TYPE;

lv_newline VARCHAR2(1000);
lv_acct_len NUMBER(2) := 19;

lv_cnt_valid NUMBER := 0;
lv_cnt_invalid NUMBER := 0;
lv_get_seq_cnt NUMBER := 0;
lv_cnt NUMBER := 0;
lv_duplicate_cnt NUMBER := 0;

lv_exception EXCEPTION;

BEGIN
p_err_msg := fn_get_err_msg(2) || ' for external file path.';

SELECT ref_desc INTO lv_floc FROM ref_parameter
WHERE ref_system_code = 'SEC_INTERFACE' AND ref_code = 'EXT_FILE_PATH';

p_err_msg := NULL;

lv_ftype := UTL_FILE.FOPEN(lv_floc, p_fname, 'r');

LOOP
lv_newline := NULL;

BEGIN
UTL_FILE.GET_LINE(lv_ftype,lv_newline);
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;

IF LENGTH(TRIM(lv_newline)) = lv_acct_len THEN
--check for account number and insert if they are valid
SELECT COUNT(account_no) INTO lv_cnt FROM d_sec_account
WHERE account_no = TRIM(lv_newline)
AND last_sale_code = p_sale_code
AND last_pool_code = p_pool_code
AND last_pool_status = p_pool_status;

IF lv_cnt = 1 THEN
lv_cnt_valid := lv_cnt_valid + 1;
lv_typ_acct_no_valid(lv_cnt_valid) := TRIM(lv_newline);
ELSE
lv_cnt_invalid := lv_cnt_invalid + 1;
lv_typ_acct_no_invalid(lv_cnt_invalid) := SUBSTR(TRIM(lv_newline),1,lv_acct_len);
END IF;
END IF;
END LOOP;

IF lv_typ_acct_no_valid.COUNT > 0 OR lv_typ_acct_no_invalid.COUNT > 0 THEN
-- check for record in t_sec_req_handler_hdr table if no data found for the given sequence number
SELECT COUNT(seq_no) INTO lv_get_seq_cnt FROM t_sec_req_handler_hdr
WHERE seq_no = p_seq_no;

IF lv_get_seq_cnt = 0 THEN
INSERT INTO t_sec_req_handler_hdr(seq_no, session_id, req_seq_no)
VALUES (p_seq_no, p_session_id, 0);
END IF;

-- valid accounts
FOR i IN 1..lv_typ_acct_no_valid.COUNT
LOOP
lv_duplicate_cnt := 0;

SELECT COUNT(*) INTO lv_duplicate_cnt FROM t_sec_req_handler_dtl
WHERE seq_no = p_seq_no AND account_no = lv_typ_acct_no_valid(i);

IF lv_duplicate_cnt = 0 THEN
INSERT INTO t_sec_req_handler_dtl(seq_no, account_no, validate_acct_flag)
VALUES (p_seq_no, lv_typ_acct_no_valid(i), 'Y');
END IF;
END LOOP;

-- invalid accounts
FOR i IN 1..lv_typ_acct_no_invalid.COUNT
LOOP
lv_duplicate_cnt := 0;

SELECT COUNT(*) INTO lv_duplicate_cnt FROM t_sec_req_handler_dtl
WHERE seq_no = p_seq_no AND account_no = lv_typ_acct_no_invalid(i);

IF lv_duplicate_cnt = 0 THEN
INSERT INTO t_sec_req_handler_dtl(seq_no, account_no, validate_acct_flag)
VALUES (p_seq_no, lv_typ_acct_no_invalid(i), 'N');
END IF;
END LOOP;
END IF;

IF UTL_FILE.IS_OPEN(lv_ftype) THEN
UTL_FILE.FCLOSE(lv_ftype);
END IF;

sp_sec_insert_temp_accts(p_seq_no, p_cnt, p_sum_curr_bal, p_status, p_err_cd, p_err_msg);

IF p_status <> 0 THEN
RAISE lv_exception;
END IF;

COMMIT;

p_status := 0;
p_err_cd := NULL;
p_err_msg := NULL;
EXCEPTION
WHEN lv_exception THEN
ROLLBACK;
WHEN UTL_FILE.INVALID_PATH THEN
ROLLBACK;
p_err_cd := NULL;
p_err_msg := 'Invalid Path - ' || lv_floc || ' or File - ' || p_fname;
p_status := 1;
WHEN OTHERS THEN
ROLLBACK;
IF UTL_FILE.IS_OPEN(lv_ftype) THEN
UTL_FILE.FCLOSE(lv_ftype);
END IF;
IF p_err_msg IS NULL THEN
p_err_cd := SQLCODE;
p_err_msg := SQLERRM;
ELSE
p_err_cd := NULL;
END IF;
p_status := 1;
END sp_sec_upload_accts;

You have posted this thread in administration forum also. I belive the policy is to post in only one forum.