CODEWITHSUNDEEP
plsql
vasanti
vasanti

Reputation: 11

Import CSV to Oracle database

Can someone help me with plsql stored procedure of importing a .CSV file into an Oracle table? Wanted to create a procedure which will accept file name and table name.

Note: I am using UTF-8 file format

Upvotes: 1

Views: 16265

Answers (3)

ASz
ASz

Reputation: 119

I think the easy's way is using external tables. This is the object who is treatment like a table but he read data from file. To achive this u need privilege CREATE ANY DIRECTORY. So first create directort:

CREATE OR REPLACE DIRECTORY imp_data AS 'c:\tmp\';

or any other path directory on your server. Next you need to grant it

GRANT READ, WRITE ON DIRECTORY imp_data TO PUBLIC;

now is the best part :)

CREATE TABLE imp_data (
  ID NUMBER(20),
  NAME VARCHAR2(50),
  CITY VARCHAR2(200)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER --this is the read-file driver
  DEFAULT DIRECTORY imp_data
  ACCESS PARAMETERS
    RECORDS DELIMITED BY NEWLINE
    BADFILE 'bad'
    LOGFILE 'log'
    FIELDS TERMINATED BY ',' -- CSV terminate mark
    (ID, NAME, CITY)) -- the order of the columns in the file
  LOCATION ('dane.csv')) -- name of file
 PARALLEL 5
 REJECT LIMIT 200; -- numbers of bad row before break import

Upvotes: 0

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

A pl/sql procedure which is stored in the database is not able to operate on your desktop. You may

  • use SQL*Loader utility for this purpose
  • write a program which will read and parse the file and insert data in your table
  • send the file to a place which can be accessed by your database and use e.g. external table or write some procedure to read the file (like get_file in one of answers)

Upvotes: 1

Renato Afonso
Renato Afonso

Reputation: 654

You have to create a directory, and point it to the folder where you have the file. (you need the permissions to create that)

create or replace procedure get_file (p_dir varchar2, p_file varchar2) 
is
 line varchar2(4000);
v_file utl_file.file_type;
begin
    v_file := utl_file.fopen(p_dir,p_file,'R');
    loop
         utl_file.get_line(v_file,line);
        -- insert code here
         dbms_output.put_line(line);
    end loop;
    exception when others then
           -- The only way to know when we reach the end of the file is to get and exception
           utl_file.fclose(v_file);
end;

Upvotes: 0

Related Questions