Home » SQL & PL/SQL » SQL & PL/SQL » CSV file data into Oracle Table Using PL/SQL
CSV file data into Oracle Table Using PL/SQL [message #198477] Tue, 17 October 2006 05:43 Go to next message
kranthime_79
Messages: 2
Registered: June 2005
Location: hyderabad
Junior Member

Hi all

i want upload data of CSV file into Oracle file using PL/SQL

can anyone pls explain with example

Thanks in advance
Re: CSV file data into Oracle Table Using PL/SQL [message #198491 is a reply to message #198477] Tue, 17 October 2006 06:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
search for UTL_FILE
Re: CSV file data into Oracle Table Using PL/SQL [message #198500 is a reply to message #198477] Tue, 17 October 2006 07:16 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

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)
IS
c_var_curseur type_variable_curseur;
myfile UTL_FILE.file_type;
mybuffer varchar2(32768);
BEGIN
myfile :=
UTL_FILE.fopen (mydirectory,
myfilename ,
'r',
32768
);
do
UTL_FILE.get_line (myfile , mybuffer, 32768);
...do something...
loop until length (mybuffer) >0;
UTL_FILE.close(myfile);
END;


Re: CSV file data into Oracle Table Using PL/SQL [message #198503 is a reply to message #198477] Tue, 17 October 2006 07:32 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
Hi

The following article may be of help. It contains a simple example of using external tables.

http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11210

Razz
Previous Topic: Dynamic parenthesis in where clause Possible?
Next Topic: Calling a procedure inside a procedure [SOLVED]
Goto Forum:
  


Current Time: Sat Dec 03 04:13:29 CST 2016

Total time taken to generate the page: 0.07014 seconds