DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: UTL_FILE Example

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    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
  •  


Click Here to Expand Forum to Full Width