I would agree with schwertner. One thing to remember is that the file must reside on the DB server and the utl_file_dir parameter of the ini file must be set to (at least) the directory where the file resides.
Here's a partial example of a procedure:
create or replace procedure Insert_Data
(p_directory IN VARCHAR2,
p_filename IN VARCHAR2) IS
v_infile Utl_File.File_type;
v_line VARCHAR2(200);
-- I would combine the DATE and Time into one Column
v_raldtdate_Time DATE;
v_atpt The_Table.atpt%TYPEl;
--the rest of the columns
...
...
BEGIN
v_infile := Utl_File.Fopen( p_directory, p_filename, 'R');
LOOP
BEGIN
<<read_file>>
UTL_FILE.get_line(v_infile
if trim(v_line) is null then
goto read_file ;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
if TRIM(UPPER( v_line) ) like '%WORLI SIGMA%' then
v_ralDtDate_Time := to_date( substr( v_line,1,8) || substr(v_line,10,8) , 'DD/MM/YYHH24:MI:SS') ;
elsif
do the other checking and parsing;
end if;
if -- some condition to check if the row is complete THEN
insert into THE_TABLE
(all the columns)
values (all the variables);
end if;
END LOOP;
Utl_File.Fclose( v_infile );
EXCEPTION
WHEN UTL_FILE.invalid_operation
Utl_File.Fclose( v_infile );
Raise_Application_Error(-2
WHEN UTL_FILE.invalid_filehandl
Utl_File.Fclose( v_infile );
Raise_Application_Error(-2
WHEN UTL_FILE.read_error THEN
Utl_File.Fclose( v_infile );
Raise_Application_Error(-2
WHEN OTHERS THEN
Utl_File.Fclose( v_infile );
Raise_Application_Error(-2
END;
Main Topics
Browse All Topics
by: schwertnerPosted on 2003-04-16 at 01:10:47ID: 8339376
It is not tough, but you have to do some work.
I will suggest to use a stored procedure and using UTL_FILE to read the inputs from the log file into PL/SQL variables. After that you can store the data in the Oracle DB using the INSERT statement. This is the best way because you have many labels in the log file which will make the usage of sql*loader utility very hard task.