Results 1 to 4 of 4

Thread: Utl_file

  1. #1
    Join Date
    Dec 2003
    Location
    Ogden Utah
    Posts
    34

    Smile Unanswered: Utl_file

    I have a file that contains the following information:
    FJA|328450|768492324|101.38|20041022
    FJA|328450|770809973|325.51|20041022

    I am trying to read it using the UTL_FILE package (I have to use this package...I am not allowed to use the external table feature or old sql loader) and I cannot get it to read the file.....the script below only picks up the 328450 and 768492324 values and that is it. Suggestions or experiences greatly appreciated.


    DECLARE
    TYPE list IS TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;
    a_field list;
    fh UTL_FILE.FILE_TYPE;
    text_out VARCHAR2(2000);
    file_exists BOOLEAN;
    file_length NUMBER;
    file_size NUMBER;
    -- ---------------------------------
    -- Variables to keep track of the
    -- file delimiters.
    -- ---------------------------------
    lv_pipe1 NUMBER;
    lv_pipe2 NUMBER;
    lv_pipe3 NUMBER;
    lv_pipe4 NUMBER;
    -- ----------------------------------
    -- Variables to hold the output being
    -- read from the invoice txt file.
    -- ----------------------------------
    lv_org VARCHAR2(3);
    lv_vendor_number VARCHAR2(6);
    lv_invoice_number VARCHAR2(22);
    lv_invoice_amount NUMBER;
    lv_invoice_date VARCHAR2(10);
    lv_invoice_paid CHAR(1) := 'y';
    -- ----------------------------------
    -- Variables that hold the import
    -- errors.
    -- ----------------------------------
    lv_error_msg VARCHAR2(200);
    lv_error_num VARCHAR2(10);
    lv_field_count NUMBER;
    number_count NUMBER;
    BEGIN
    -- --------------------
    -- Get file attributes
    -- --------------------
    UTL_FILE.FGETATTR('DAT_DIR','external_invoice.txt' ,file_exists,file_length,file_size);
    fh := UTL_FILE.FOPEN('DAT_DIR','external_invoice.txt','r ');
    UTL_FILE.get_line(fh,text_out);
    FOR i IN 1..file_length LOOP
    -- ---------------------
    -- Get lines from file
    -- ---------------------
    -- -------------------------------------------
    -- Get the position of delimiters and pull
    -- data and assigning to the corresponding
    -- variables.
    -- -------------------------------------------
    BEGIN
    a_field(i) := SUBSTR(text_out, INSTR(text_out, '|' ,1 ,i)+1,
    (INSTR(text_out,'|' ,1 ,i+1)- INSTR(text_out, '|' ,1 ,i)-1));
    lv_pipe1 := INSTR(text_out, '|' ,1 , 1);
    lv_pipe2 := INSTR(text_out, '|' ,1 , 2);
    END;
    DBMS_OUTPUT.PUT_LINE(a_field(i));
    DBMS_OUTPUT.PUT_LINE(lv_pipe1||' '||lv_pipe2);
    DBMS_OUTPUT.PUT_LINE(file_length);
    END LOOP;
    EXCEPTION WHEN NO_DATA_FOUND THEN
    UTL_FILE.FCLOSE(fh);
    END;
    /
    mvilla

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >and I cannot get it to read the file.....the script below only picks up the 328450 and 768492324 values
    The statement above is self-contradictory.
    IMO, getting ANY values out of the file, "proves" UTL_FILE is working, plain and simple.
    Try the following modified code:
    Code:
     FOR i IN 1..file_length LOOP
    -- ---------------------
    -- Get lines from file
    -- ---------------------
    -- -------------------------------------------
    -- Get the position of delimiters and pull
    -- data and assigning to the corresponding
    -- variables.
    -- -------------------------------------------
    BEGIN
    DBMS_OUTPUT.PUT_LINE(text_out);
    a_field(i) := SUBSTR(text_out, INSTR(text_out, '|' ,1 ,i)+1,
    (INSTR(text_out,'|' ,1 ,i+1)- INSTR(text_out, '|' ,1 ,i)-1));
    lv_pipe1 := INSTR(text_out, '|' ,1 , 1);
    lv_pipe2 := INSTR(text_out, '|' ,1 , 2);
    END;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Location
    Ogden Utah
    Posts
    34

    Smile Utl_file

    Hello and thanks for your reply...
    IMO, getting ANY values out of the file, "proves" UTL_FILE is working, plain and simple.

    >>You are correct as I did not post the questions correctly. I am able to open the file, read it and select output from it ....it is the SUBSTR AND INSTR function that I do no believe I have the syntax right the capture each corresponding field so I can then insert it to an oracle table.

    >>>Yes You are correct ...,,
    lv_pipe1 := INSTR(text_out, '|' ,1 , 1);
    lv_pipe2 := INSTR(text_out, '|' ,1 , 2);

    This part is contradictory and it was placed only to check why the syntax below that I do not feel I am constructing it properly.

    a_field(i) := SUBSTR(text_out, INSTR(text_out, '|' ,1 ,i)+1,
    (INSTR(text_out,'|' ,1 ,i+1)- INSTR(text_out, '|' ,1 ,i)-1));

    ================================================== =
    Below is the original procedure that I wrote and it works ok however, I want to combine the INSTR AND SUBSTR functions to replace the syntax below the comments below. ----

    -- -------------------------------------------
    -- Get the position of delimiters and pull
    -- data and assigning to the corresponding
    -- variables.
    -- -------------------------------------------



    CREATE OR REPLACE PROCEDURE cc_invoice_load_sp
    IS

    ex_ccinvoice_pk EXCEPTION;
    PRAGMA EXCEPTION_INIT(ex_ccinvoice_pk, -0001);

    fh UTL_FILE.FILE_TYPE;
    text_out VARCHAR2(2000);
    file_exists BOOLEAN;
    file_length NUMBER;
    file_size NUMBER;

    -- ---------------------------------
    -- Variables to keep track of the
    -- file delimiters.
    -- ---------------------------------

    lv_pipe1 NUMBER;
    lv_pipe2 NUMBER;
    lv_pipe3 NUMBER;
    lv_pipe4 NUMBER;

    -- ----------------------------------
    -- Variables to hold the output being
    -- read from the invoice txt file.
    -- ----------------------------------

    lv_org VARCHAR2(3);
    lv_vendor_number VARCHAR2(6);
    lv_invoice_number VARCHAR2(22);
    lv_invoice_amount NUMBER;
    lv_invoice_date VARCHAR2(10);
    lv_invoice_paid CHAR(1) := 'y';

    BEGIN

    UTL_FILE.FGETATTR('DAT_DIR','external_invoice.txt' ,file_exists,file_length,file_size);
    fh := UTL_FILE.FOPEN('DAT_DIR','external_invoice.txt','r ');

    FOR i IN 1..file_length LOOP

    -- ---------------------
    -- Get lines from file
    -- ---------------------

    UTL_FILE.get_line(fh,text_out);

    -- -------------------------------------------
    -- Get the position of delimiters and pull
    -- data and assigning to the corresponding
    -- variables.
    -- -------------------------------------------

    lv_pipe1 := INSTR(text_out, '|' ,1 , 1);
    lv_pipe2 := INSTR(text_out, '|' ,1 , 2);
    lv_pipe3 := INSTR(text_out, '|' ,1 , 3);
    lv_pipe4 := INSTR(text_out, '|' ,1 , 4);

    lv_org := SUBSTR(text_out, 1, lv_pipe1-1);
    lv_vendor_number := SUBSTR(text_out,lv_pipe1+1, lv_pipe2-lv_pipe1-1);
    lv_invoice_number := SUBSTR(text_out,lv_pipe2+1, lv_pipe3-lv_pipe2-1);
    lv_invoice_amount := SUBSTR(text_out,lv_pipe3+1, lv_pipe4-lv_pipe3-1);
    lv_invoice_date := SUBSTR(text_out,lv_pipe4+1);



    -- ---------------------------
    -- Load the cc_invoice table.
    -- ---------------------------

    INSERT INTO cc_invoice
    VALUES ( lv_org,
    lv_vendor_number,
    lv_invoice_number,
    lv_invoice_amount,
    TO_DATE(lv_invoice_date,'yymmdd'),
    lv_invoice_paid);


    END LOOP;

    EXCEPTION

    WHEN no_data_found THEN
    UTL_FILE.FCLOSE(fh);

    WHEN ex_ccinvoice_pk THEN
    DBMS_OUTPUT.PUT_LINE(SYSDATE ||' : '||'PK error WHEN loading the cc_invoice table');
    END;
    /
    mvilla

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    First make it work, then make it fancy.
    I'm sure that if you include enough DBMS_OUTPUT.PUT_LINE statements
    in your code to actually see what values are being returned,
    that you'll be able to get the code to do what you want.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •