CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
C.Z
C.Z

Reputation: 1

read and insert data from text file to database table using oracle SQL Plus

I really need your help I am always work on SQL server, but now I am working on something else and that why I need your help. I m working on (Oracle SQL plus), I have a text file lets say the name test.txt and just I want to upload data from this file to database table using SQL plus

lets say the text file data:

001,mike,1-1-2018

002,jon,20-12-2017

003,bill 25-5-2018

how to write a code pl/sql on sql plus to upload the data from the text file to the table on my data base?? usually on SQL server I use Bulk insert, here what the methods?

I tried many from the internet but not solved.

Please help me Thanks a lot

Upvotes: 1

Views: 14228

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

If the text file is on the same machine you're running SQL*Plus from, you can use the SQL*Loader utility.

As a simple example, lets say your table is:

create table your_table (id number, name varchar2(10), some_date date);

And you have a text file data.txt containing what you showed, but with a comma added on the third line:

001,mike,1-1-2018
002,jon,20-12-2017
003,bill,25-5-2018

You can create a basic SQL*Loader control file in the same directory, called say your_table.ctl, with something like:

LOAD DATA
INFILE 'data.txt'
APPEND
INTO TABLE your_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  ID,
  NAME,
  SOME_DATE DATE "DD-MM-YYYY"
)

Look at the documentation to see what all those mean, particularly what APPEND means; you may want to TRUNCATE instead - but be careful with that.

Then run SQL*Loader from the command line (not from within SQL*Plus), using the same credentials and connect string you normally use to connect to the database:

sqlldr userid=usr/pwd@tns control=your_table.ctl

Once that has completed - assuming there are no errors reported on console ro in the log file it creates - then querying your table will show:

select * from your_table;

        ID NAME       SOME_DATE 
---------- ---------- ----------
         1 mike       2018-01-01
         2 jon        2017-12-20
         3 bill       2018-05-25

There are lots of other options and capabilities, but that might cover what you need at the moment.

Upvotes: 3

Related Questions