• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7791
  • Last Modified:

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

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
0
koolbinu
Asked:
koolbinu
1 Solution
 
sujith80Commented:
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;

VAL
----------------------------------------
first line from file
second line from file
another line from file
last line from file
0
 
schwertnerCommented:
procedure YOUR_PROC   is

   sasha SYS.UTL_FILE.FILE_TYPE;

    sanjeev   VARCHAR2(1800);

begin

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

LOOP

UTL_File.get_line(sasha,sanjeev);

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

END LOOP              
               
EXCEPTION
WHEN no_data_found THEN  
UTL_File.Fclose(sasha);                      
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 ;


REMARK
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
UTL_FILE_DIR = C:\DATA\ORACLE\DATAFILES
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;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
schwertnerCommented:
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',
               'med-us-formatted.txt','med-us-insulin-formatted.txt'));

Now you can select from theExternal table.
0
 
koolbinuAuthor Commented:
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 asp.net 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)
0
 
arin_12Commented:
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.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now