UTL_FILE Package

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

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

** 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;

THANKS !!!!:)

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

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;

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

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)

thank u very much for u r reply