I created a new database, then created a table "users". Now, I'm trying to load data to this table from a flat file called 'users.tbl', that has some records with the following format:


I'm using SQL Developer for this, i tried the following commands.

load data infile `"C:\path" 
insert into table users 
fields terminated by "|" 
lines terminated by "\r\n"; 

But the data is not loaded and no errors are shown. Do you see why its not working? Or How can i load the data in the flat file to Oracle table?


According to your question you have data in the flat file and you want to access it or can load to another table. Basically you are talking about external tables. You want to read more on it. Here is the Oracle Documentation link: External Tables Concepts.

Here is the example how I loaded your data into my external table and created another table from it.

[oracle@testsrv1 Desktop]$ cat user.tbl 
[oracle@testsrv1 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Mar 3 06:45:26 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user user1 identified by user1;

User created.

SQL> create directory user_dir as '/home/oracle/Desktop';

Directory created.

SQL> grant resource, connect to user1;

Grant succeeded.

SQL> grant read, write on directory user_dir to user1;

Grant succeeded.

SQL> conn user1/user1

SQL> create table user_load(user_id number, name varchar2(20), email varchar2(50), status varchar2(10))
organization external
(default directory user_dir
access parameters
(fields terminated by '|')

Table created.

SQL> select * from user_load;

   USER_ID NAME          EMAIL                  STATUS
---------- ----------   -------------------    ----------
     1 john         email@email.com        active

SQL> create table final as select * from user_load;

Table created

SQL> select * from final;

   USER_ID NAME          EMAIL                  STATUS
---------- ----------   -------------------    ----------
     1 john         email@email.com        active
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.