1748027 Members
4635 Online
108757 Solutions
New Discussion юеВ

UTL_fILE

 
Rajkumar_3
Regular Advisor

UTL_fILE

Hai,

I have a text file which contains rows upto 1M.
Instead of using SQLLOADER I want to use UTL_FILE concept to insert those records into the
different oracle tables.

Can any one have an idea ,If have please provide an example..

Thanks for the Advance in Help..

Regards
Rajkumar
Oracle DBA
6 REPLIES 6
harry d brown jr
Honored Contributor

Re: UTL_fILE

Take a look at this:

http://www.elementkjournals.com/dbm/0005/dbm0053.htm

Live Free or Die
Steve Slade
Frequent Advisor

Re: UTL_fILE

Hi,

I have never done any file reading using UTL_FILE, although I have used to write out to files. As Oracle features go, it is pretty poor: it has poor error handling, and is pretty slow - especially compared to SQL-LOADER.

Good Luck

Steve
If at first you do not succeed. Destroy all evidence that you even attempted.
Andreas D. Skjervold
Honored Contributor

Re: UTL_fILE

Hi

check the enclosed note from Oracle Metalink. Here you'll find how to set up a function to read and write from/to files:

To read from files, check the following section of the note:
-- Open the same file to read from
file_handle :=
UTL_FILE.FOPEN('/tmp','myfile.txt','R');

-- Read a line from the file.
UTL_FILE.GET_LINE (file_handle, retrieved_buffer);

-- Print fetched line out to the SQL*PLUS prompt.
DBMS_OUTPUT.PUT_LINE(retrieved_buffer);

-- CLose the file.
UTL_FILE.FCLOSE(file_handle);

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Andreas D. Skjervold
Honored Contributor

Re: UTL_fILE

SQL*Loader, specially using direct path, loads fast but within sqlloader, you cannot add logic such as "if the row exists then update else insert".
If this is what you need to do, then you will use the UTL_FILE package.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Alexander M. Ermes
Honored Contributor

Re: UTL_fILE

Hi there.
Go for the sqlloader.
Faster, easier to handle.
If you read the manual, check for ctl files
( param files for sqlloader ).
There you can set conditions for the load.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Rajkumar_3
Regular Advisor

Re: UTL_fILE

Hai All,

Thank you for your reples..

I will try using these options which i have recieved from you all and i will let you know the result..

Thanks & Regards
Rajkumar
Oracle DBA