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

Thread: How to manage two OS Text files in UTL_FILE Package simultaneously?

  1. #1
    Join Date
    Mar 2004
    Location
    INDIA
    Posts
    25

    How to manage two OS Text files in UTL_FILE Package simultaneously?

    Hi

    I am using UTL_FILE Package in order to insert rows into my table from the OS text files.

    It inserts the row in one of the column in the table successfully.
    But when I change the code in following routine in order to insert two rows from the two OS text files, it fails and no row inserted in any column of the table.

    Could someone assist what changes I will do in the following script in order to insert the rows in both the columns of the table from the two different OS Text files simultaneously?

    The UTL_FILE_DIR parameter is set to * .
    I am using Oracle 8.1.7.

    Following is the procedure, which I am trying to insert the rows in two columns at a time:
    Regards
    Chuck



    Declare

    l_file_handle1 UTL_FILE.FILE_TYPE;

    l_file_handle2 UTL_FILE.FILE_TYPE;

    l_buffer1 VARCHAR2(4000);
    l_buffer2 VARCHAR2(4000);


    BEGIN



    l_file_handle1 := UTL_FILE.FOPEN('c:\Test\Result', 'System_Name.txt', 'r', 4000);
    l_file_handle2 := UTL_FILE.FOPEN('c:\Test\Result',
    'Machine.txt', 'r', 4000);

    loop

    UTL_FILE.get_line(l_file_handle1,l_buffer1);
    UTL_FILE.get_line(l_file_handle2,l_buffer2);

    insert into test (Hostname,Machine) values(l_buffer1,l_buffer2);
    commit;
    end loop;

    exception

    when no_data_found then

    UTL_FILE.FCLOSE(l_file_handle1);
    UTL_FILE.FCLOSE(l_file_handle2);

    when others then

    if utl_file.is_open(l_file_handle1)
    then
    utl_file.fclose(l_file_handle1);
    end if;

    if utl_file.is_open(l_file_handle2)
    then
    utl_file.fclose(l_file_handle2);
    end if;

    end;

    /

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    which of the values is being inserted and which one not ?

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    IMHO, merging the two files and then loading them using SQL*Loader would be a better option. It will be more scalable than your code as it performs array inserts and will provide comprehensive errors in it's log file as to why any inserts failed. Otherwise, you're re-inventing the wheel.

    Also, aren't you concerned that the files you are receiving are logically one single table in two files (one column per file)? Can you be sure that the order in which the rows appear in each file matches exactly so that you are getting a proper row, and not actually two columns for two unrelated records. I personally wouldn't accept files constructed in this manner.

  4. #4
    Join Date
    Mar 2004
    Location
    INDIA
    Posts
    25

    UTL_FILE routine

    Thanks

    Could some one send a UTL_FILE routine (sample), in which two OS text files reads at a same time & then inserted into the table.
    This will be helpul form me.

    Regards

    Chuck

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Your exception block is catching exceptions here -- take it out and check what error is being raised.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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