|Re: CSV file data into Oracle Table Using PL/SQL [message #198500 is a reply to message #198477]
||Tue, 17 October 2006 07:16
Registered: October 2006
Location: Courbevoie, France
if you use Oracle 9.2 or above, you have more choices:
- SQL Loader, needs to run a program from the OS
- Load via pl/sql (what you ask for)
- Declare the csv file as an external table and do an:
insert into dest_table select * from your_external_table;
Which may be the easiest and fastest way to do it. Just search for external table in this forum for more help.
Look at http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm
for the documentation for utl_file.
To load file from pl/sql, you need to declare the directories you want to read from:
CREATE DIRECTORY log_dir AS '/appl/gl/log';
GRANT READ ON DIRECTORY log_dir TO the_user;
(look at the doc, ask to your dba).
Then, you can write a procedure like this one (you have to adapt to your goal...):
PROCEDURE load(mydirectory IN VARCHAR2, myfilename IN VARCHAR2)
UTL_FILE.get_line (myfile , mybuffer, 32768);
loop until length (mybuffer) >0;