-
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]
anandkl
-
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;
Cheers!
OraKid.
-
You have posted this thread in administration forum also. I belive the policy is to post in only one forum.
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|