# Database Discussions > Oracle >  UTL_FILE Package

## susheela

Hi,

I am looking for some code. Pls help me if you can. I want to write PL/SQL procedures(I have to..recommended) instead of SQL Loader.

Retrieving data from Flat Files (CSV) and inserting into Oracle 9i Data Base using UTL_FILE Package.

Example Data : 123, abc

This data should be inserted in oracle data base table as seperate fields (code, name)


Thanks

----------


## skhanal

Can you use SQL Loader?. It is much faster and is built for data loading.

----------


## YuckFou

** As of 8.0.6, you can have a maximum of 50 files open simultaneously.
** As of 9.0.2, UTL_FILE allows file system access for directories
** created as database objects.  See the CREATE DIRECTORY command.
** Directory object names are case sensitive and must match exactly
** the NAME string in ALL_DIRECTORIES.  The LOCATION parameter may be
** either a directory string from the UTL_FILE_DIR init.ora parameter
** or a directory object name.

1.
*--example with utl_file* 
DECLARE
  f Utl_File.file_type;
  buffer VARCHAR2(4000);
BEGIN
  f := Utl_File.fopen('DIRECTORY_DIR','pokus.txt','r');
  IF Utl_File.is_open(f) THEN
    Dbms_Output.put_line('is open');
    BEGIN      
      WHILE 1=1 LOOP        
        Utl_File.get_line(f,buffer); 
        -- now you have to parse the buffer and insert in your table
        Dbms_Output.put_line(buffer);
      END LOOP;
      EXCEPTION
        WHEN No_Data_Found THEN 
          Dbms_Output.put_line('End of file');
        WHEN Others THEN
          Dbms_Output.put_line(SQLERRM);
    END;
  ELSE
    Dbms_Output.put_line('is close');
END IF;  
  Utl_File.fclose(f);
  Dbms_Output.put_line('end proc');
EXCEPTION
  WHEN Others THEN NULL;
  Dbms_Output.put_line(SQLERRM);
END;

2.
*-- example with linked textfile* 
/*
create table scott.test (val varchar2(4000))
organization external
(
  type oracle_loader 
  default directory DIRECTORY_DIR
  access parameters (records delimited by newline fields terminated by ';')
  location ('pokus.txt')
);
*/
-- now i can use only select from linked table to get values from my text file..
SELECT * FROM scott.test;

----------


## susheela

THANKS !!!! :Smilie:

----------


## susheela

Hi,

I got values using UTL_FILE..

There are list of values in the line..

i.e.. 123,abc,eee,..

now how to put all these values in array variables and insert in oracle db..

It will be helpful if you reply to this too...


Thanks

----------


## YuckFou

here you are...
---------------------
DECLARE
  v_comma     NUMBER;
  v_start     NUMBER := 1;
  v_aline     VARCHAR2(100);
  v_empno     NUMBER;
  v_ename     VARCHAR2(10);
  v_sal       NUMBER;
BEGIN
  v_aline := '1,John,100';
  -- parse the input line to extract column info
  v_comma := INSTR(v_aline, ',' , v_start, 1);
  v_empno := To_Number(SUBSTR(v_aline, v_start, v_comma - v_start));
  v_start := v_comma + 1;
  v_comma := INSTR(v_aline, ',' , v_start, 1);
  v_ename := Trim(SUBSTR(v_aline, v_start, v_comma - v_start));
  v_start := v_comma + 1;
  v_sal   := To_Number(SUBSTR(v_aline, v_start, LENGTH(v_aline)));
  -- print values
  Dbms_Output.put_line('VALUES ARE ...'||v_empno||'*'||v_ename||'*'||v_sal);
  --INSERT INTO  SalariedEmployee (empno,ename,sal) VALUES (v_empno,v_ename,v_sal);
END;
---------------------
But, I find it easier to use external table, where you can just select values ...
create table external_table
( empno  number,        ename  varchar2(10),        sal    number )
organization external
(
type oracle_loader 
default directory DIRECTORY_DIR
access parameters (records delimited by newline fields terminated by ',')
location ('pokus.txt')
);
insert into SalariedEmployee (empno,ename,sal) select empno,ename,sal from external_table;

----------


## nikki_g15r

HAI
   i am looking for some code. Pls help me if you can. I want to write PL/SQL procedures  using utl_file i must transfer the os files from one location to another

----------


## vinnuk3

Hi,
There are two predifned procedures in UTL_FILE Package to move the files from one location to another.

utl_file.fcopy (
 src_location                       in                 varchar2        ,
 src_filename                       in                 varchar2        ,
 dest_location                      in                 varchar2        ,
 dest_filename                      in                 varchar2        ,
 start_line                         in           binary_integer default,
 end_line                           in           binary_integer default
);

procedure frename (
 src_location                       in                 varchar2        ,
 src_filename                       in                 varchar2        ,
 dest_location                      in                 varchar2        ,
 dest_filename                      in                 varchar2        ,
 overwrite                          in                  boolean default
);

These two procedures are different with each other.
FCOPY : It will keep the source file as it is and it creates one more file in destination folder (COPY and PASTE Job).

FRENAME : It will not keep the original file (CUT and PASTE job)

----------


## nikki_g15r

thank u very much for u r reply

----------

