read data fro a flat file into oracle table using utl file package

Posted on 2007-09-27
Last Modified: 2013-12-19
I wanted to read the data from the flat file into an oracle table I want to use Utl file package because each time I will be getting a new input file ...............I have devised a way to capture the new file name so my safest bet is to use plsql procedure to fetch the flat file insert the data into a table using utl file package can somone help me
Question by:koolbinu
    LVL 27

    Accepted Solution

    Your case can be easily handled by oracle supplied utilities like sqlloader, external tables etc. They are optimized to run in faster ways. Read about them.
    If you have a specific reason to use UTL_FILE, read the below syntax directions.

    SQL> declare
      2   l_file utl_file.file_type;
      3   l_rec varchar2(40);
      4  begin
      5   l_file := utl_file.fopen('MYDIR', 'test.txt', 'r');
      6   loop
      7   begin
      8    utl_file.get_line(l_file, l_rec);
      9    insert into a1 values(l_rec);
     10   exception
     11    when no_data_found then
     12     exit;
     13   end;
     14   end loop;
     15   utl_file.fclose(l_file);
     16  end;
     17  /

    PL/SQL procedure successfully completed.

    SQL> select * from a1;

    first line from file
    second line from file
    another line from file
    last line from file
    LVL 27

    Expert Comment

    LVL 47

    Expert Comment

    procedure YOUR_PROC   is

       sasha SYS.UTL_FILE.FILE_TYPE;

        sanjeev   VARCHAR2(1800);


      sasha := UTL_File.Fopen('D:\staff\cv','56789.txt', 'r');



    -- FORMAT THE DATA in/from buffer sanjeev and insert in Oracle DB using INSERT statement

    END LOOP              
    WHEN no_data_found THEN  
    WHEN  UTL_File.invalid_filehandle   THEN ... UTL_File.Fclose(sasha);  
          --   invalid_filehandle - not a valid file handle
    WHEN  UTL_File.read_error   THEN ... UTL_File.Fclose(sasha);
           -- OS error occurred during read
    WHEN  UTL_File.invalid_operation   THEN ... UTL_File.Fclose(sasha);                    

    end  YOUR_PROC ;

    Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed.
    Accessible directories must be specified in the
    instance parameter initialization file (INIT.ORA).

    Specify the accessible directories for the UTL_FILE
    functions in the initialization file
    using the UTL_FILE_DIR parameter.
    For example:
    UTL_FILE_DIR = <directory name>
    like in
    Starting from 9i you can use Oracle directories instead:

    create or replace directory XMLDIR as 'E:\ICW_TASKS\XML\SOURCE\';
    grant read on directory xmldir to my_user with grant option;
    LVL 47

    Expert Comment

    external tables approach:

    reate directory med as 'c:\med' ; -- on main
    GRANT READ  ON DIRECTORY med TO lsmedical;
    GRANT WRITE ON DIRECTORY med TO lsmedical;

    drop table lsmedical.USERMEDBASIC_EXT;
    create table lsmedical.USERMEDBASIC_EXT (
                       PZN  VARCHAR2(33),
                       NAME VARCHAR2(111)   )
        organization external  
        (type oracle_loader  
         default directory med
         access parameters (records delimited by newline
         fields terminated by '|')
         location ('med-de-formatted.txt','med-de-insulin-formatted.txt',

    Now you can select from theExternal table.

    Author Comment

    Thanks a lot for all your help the reason for using utl_file package is to automate the whole system ifI use orcle loader(external table) i have to specifty the name of the file in location
     (location ('med-de-formatted.txt','med-de-insulin-formatted.txt',
     'med-us-formatted.txt','med-us-insulin-formatted.txt')); )
    or the infile :name_of_data_file  over here the name of the file is dynamic so I am going for utl file.I devised a way to capture the name of the file from the front end and store it into an oracle table use this as a reference to
    call ult_file package .........................Please do suggest me by any chance can I use sql loader or external file ie changing the name of the input data file ...........based on the name of the file stored in the new table (captured from front end asp,net and stored in oracle table using stored proc)

    Expert Comment

    Just to add some complexity... How to read a remote location file. Lets assume that the file is not present in the database system. It is avialble in some other server.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now