I’ve done a couple of posts on how to use SQL Developer to load your Excel files to an Oracle table. However, I always wonder how many folks realize there may be a ‘better way.’ If you are loading data to Oracle on a regular basis, there’s a few things I want you to know about:
SQL*Loader is a client tool. It runs on your desktop and connects to the database over SQL*Net. It’s part of the Oracle Client installation. It reads one or more files, parses the data, and loads it to your tables. A control (CTL) file that defines how the data is to be read does most of the hard work. Actually, you’ll do most of the hard work by creating the CTL file. Are my strings single or double-quoted? What delimits a value, commas or pipes perhaps?
I think one of the reasons folks tend to stick with ‘the GUI’ is that they like running a wizard, answering a few questions, and then hitting ‘Go.’ I’m guessing you didn’t know you could use SQL Developer’s Import Data Wizard to look at your data, build the CTL file, and then give you a batch or SH file to execute to kick things off. I’m guessing that because until today, I didn’t know either 🙂
Here’s what a CTL file can look like
load data
infile 'C:\Users\jdsmith\Documents\table_exports\beer_pipe_delimited2.dsv' "STR '\r\n'"
append
into table BEER_COPY
fields terminated by '||'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
( BREWERY CHAR(4000),
STATE CHAR(4000),
COUNTRY CHAR(4000),
CITY CHAR(4000),
ID CHAR(4000)
)This file was created by SQL Developer. Now the ‘beer_pipe_delimited’ file was ALSO created by SQL Developer via the Export Wizard. Once I have a pipe-delimited file, what the ‘best’ way to load that to Oracle?
If you have access to your database server, then I’d say an External Table is probably a good way to go. But, if you’re just a ‘lowly developer’ who can’t put stuff on the server and you don’t have a SAMBA directory setup, then SQL*Loader is a nice fall-back position.
Building the SQL*Loader Control File
Once you open the Import Data wizard from your table context menu, you’ll be asked to supply the file that holds the data to be loaded.
Define the Record and String Delimiters
If you have the settings correct, you should see your records in the grid. If they look ‘funny,’ stop. Don’t pass go. Make sure the Enclosure and Delimiter settings are correct. Otherwise you’re going to have some bad data inserted, if it happens to insert at all.
If your data looks kinda ‘funny,’ then make sure your file Encoding scheme is right. If it’s set to Unicode and your file isn’t Unicode, you’ll know right away you have a problem.
Choose Your Import Method
You’ll notice that ‘Insert’, ‘Insert Script,’ and ‘Staging External Table’ are also available. Insert will work just fine, but if you’re dealing with a large amount of records, it will take much longer to run than SQL*Loader or using an External Table.
Map the Columns
The wizard defaults to loading the source columns to the target table columns. If they are named differently, you’ll just need to map them manually using the dropdown control.
Defining the SQL*Loader Options
You’ve already done the ‘hard’ work. Now you just need to tell SQL Developer where to write the CTL and batch script files to. And where you want to log the SQL*Loader session to. And how big to make the buffer and to ID the characterset.
Once you click ‘Finish,’ you won’t find your new records in your table. No, what you’ll find are the files you need to start your SQL*Loader session.
The Generated Files
You’ve already seen what the CTL file looks like. Since I’m on Windows, let’s take a look at the generated .BAT file.
sqlldr CONTROL=beer_pipe_delimited2.ctl LOG=C:\Users\jdsmith\Documents\beer_pipe_delimited2.log BAD=C:\Users\jdsmith\Documents\beer_pipe_delimited2.bad skip=1
This basically tells Windows to fire up ‘sqlldr,’ which is the EXE that runs SQL*Loader. It sends the CTL file, tells it where to LOG, where to write failed records to.
Now on my machine, I had a problem. I don’t have $ORACLE_SID setup, because Oracle isn’t running there. So to get this to work, I needed to put in my connect string. So I modified the file to
sqlldr hr/[email protected]:1521/orcl CONTROL=beer_pipe_delimited2.ctl LOG=C:\Users\jdsmith\Documents\beer_pipe_delimited2.log BAD=C:\Users\jdsmith\Documents\beer_pipe_delimited2.bad skip=1
Gotta love that password, huh? Of course you can use this batch file or create your own. The expected parameters will show if you run SQLLDR by itself.
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Aug 15 16:52:15 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct p
ath data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,part
itions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specification
s
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (D
efault FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus
able (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FA
LSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
(Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.The Output
Ok, so I have my batch file ready to go. I just double-click on it, Windows launches SQL*Loader and I sit back and watch! But it’s a very short show. It takes less than 2 seconds for Oracle to load the almost 12,000 beer records.
Table BEER_COPY: 11955 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 240120 bytes(12 rows) Read buffer bytes: 1048576 Total logical records skipped: 1 Total logical records read: 11955 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Wed Aug 15 16:55:38 2012 Run ended on Wed Aug 15 16:55:40 2012 Elapsed time was: 00:00:01.79 CPU time was: 00:00:00.31
This is a good bit faster than loading my data via the Excel importer, which is the same as doing a bunch of straight INSERTS. If you want to know why this is faster, we can go back to the docs.
A conventional path load executes SQL INSERT statements to populate tables. In contrast, a direct path load eliminates much of the database overhead by formatting data blocks and writing them directly to the database files. Direct writes operate on blocks above the high water mark and write directly to disk, bypassing the database buffer cache. Direct reads read directly from disk into the PGA, again bypassing the buffer cache.
I Realize You Probably Will Still Like the Excel Importing Better
But, I hope you also know you have alternatives available when you run into problems with Excel imports. You can easily save an Excel file to CSV and load it up as an External Table or with SQL*Loader.
Can we make our interfaces with SQL*Loader and Data Pump better? Let us know about it in our Exchange or here via the comments section.
81 Comments
hi jeff,
we have a question: we have to import many files .txt in a Oracle table. Usually we use wizard for execute a ctl file but we have to change the file name each time (both in ctl file than in the instruction file!).
Is it possible to insert a parameter to simply change the file name to import? where? and how?
thank you for your attention,
sabrina & co.
Can you please tell me how to open Data Import Wizard becuase in my SQL developer when i click on import data it is directly navigating to choose file path and not able to find Sql*plus utility.
ummm..sql*plus? We don’t look for or use sql*plus. Post a screenshot of what happens when you try to import data to a table.
Hi Jeff,
This is an awesome information.
However, I am not able to get data into my newly created table. I did everything you mentioned. I got the batch file, ctl file, log file and .sh file generated. But after this what do I do to get data into my table?
Please help…
Thanks,
Mahendra
You run SQL*Loader – which should be setup by either your .bat or .sh file. Make sure you have an $ORACLE_HOME (client) setup on your machine and that you can call the program from the command-line.
I am still unable to do it, Jeff.
Should I open .bat file and log in into SYSTEM?. I did this.
How do I make sure $ORACLE_HOME (client) is setup on my machine. I have installed Oracle 11g R2.
It would be great if you send some screenshots. Please help.
Thanks,
Mahendra
Oracle sucks… use Postgres! 🙂
No, YOU suck.
Hello Jeff,
First, thanks for the helpful article.
I am tring to use the SQL*Loader in SQL Developer but do not see the option “SQL*Loader in the “Import Method”, it only shows “Insert, Insert Script”
Do you have any idea why it is not showing in the list or how to enable it so it can be visible there?
Thanks in advance,
HD
Are you doing an Excel import? The External table and SQL Loader methods are only available when doing a delimited text or CSV file import.
I am trying to use the SQL*Loader in SQL Developer but do not see the option “SQL*Loader in the “Import Method”, it only shows “Insert, Insert Script”
I am trying to import .csv file but still, I see only 2 options “Insert, Insert Script” Please advise. Thanks
I don’t know. Based on what I just tried, a CSV dump of Employees to load into HR.Employees with version 17.3, I have Insert, Insert Script, Staging External Table, and SQL*Loader Utility available.
.
Hi Jeff,
I am using orcale Sql developer 3.0.04. Do I have any other option to automate to import data from csv file & insert into orcale Sql developer table. I have multilpe files & multiple tables. Data from fixed csv is imported in fixed tables now.
Also I can see SQL loader utility option while importing data manully, however the data is not imported & can see the control file, log file , bat file created in the same folder where the source (csv) file is. Just dont know what to do after that.
That would be really helpful if you can help me assisting how can I automate to import data into orcale Sql developer 3.0.04. May be with any other procedure.
Thanks for your support.
Hi Jeff,
Is it that I need to install Sql Loader to the system?
If no , can you please help me to automate importing data in oracle sql develpor. I need a code or a procedure so I dont need to manually right click on table to import data.
Can I have you mail ID please?
Jeff, Please assist me as I am struggling on this for a long time.
SQL*Loader is included with the Oracle Client – install that, you’ll have SQL*Loader. If you want to automate the process, you’ll be able to do that in version 4.1 with our command line interface, as the IMPORT feature now supports loading excel/csv to tables via that.
Or if you want to write a stored procedure to do that – consult the PL/SQL community on OTN.
Can I get a complete procedure to load data in oracle sql develpoer through sql loader. I am not able to find videos as well on youtube.
SQL Loader is its own program, part of the Oracle Client installation. You don’t run it through SQL Developer.
hi, I am not able to use sql loader in oracle sql developer. any suggestions?
I tried this process and tried to log in with the user – hr and password [email protected] but i keep getting tns was not give a service name, and I did set one up in the tns file. not sure what else to try. Please help?
Hi Jeff,
I have a query –
I want to load data from excel file (from 2nd worksheet & not first worksheet) into temp table. I am not sure whether we can do this with sqlldr. Can you throw some highlight?
Thanks
No, not with Excel. You’ll need to convert it to CSV, and then you can do the import via SQL Loader.
Hi Jeff,
How can i import a non delimited file into a table using sql loader?
By position, see the [DOCS]
It’s great to have the interface to build the CTL files but why not include the actual SQL*Loader EXE functionality within SQL Developer as well?
It feels like SQL Developer is only doing half the job at present
The same reason we don’t include SQL*Plus or even the database engine itself. We’re just the GUI helping you marshal the data up to the database.
Thanks! great tip, absolute legend! i would like to know if you have similar post around creating scripts to scrap table data so as to maintain real-time data in the database.
Hi jeff,
how to issue commit in control file?
Hi, have found this really useful.
After 7 years of oracle forms & report development I am finally having the fun of doing server ran procedures (to be scheduled to run over night).
From within my stored procedure (using SQL Developer) how do I call the .ctl file that will run my data import?
I think to invoke an OS program from the database, sqlldr is a client program, then you would need to write a stored java procedure. That is then invoked by a plsql wrapper procedure.
That might not be the best way though.
Hi,
Great story. My use case is somewhat different. I have exported tables from the database using SQLDelveoper 4 and I exported it on loader format and zipped. So the zip file holds 4 scripts.
I am looking for the best way to load them back in and do not seem to find clear instructions.
Any best practice?
Have you used SQL*Loader before?
I really have a doubt sir. I need to move my .csv file once it’s written in the temp table. Please clarify.!!
I’m not sure I understand your question. If you upload the data, it won’t go to a temp table – unless you mean the External Table option? If you upload as an external table, just do a CREATE TABLE AS SELECT reading from your external to the permanent table, or do an INSERT AS SELECT.
Jeff – my Oracle 11g client install does not appear to have sqlldr. Is that an optional install that I might be able to get my admins to add?
Dan
Sounds like you have the basic instant client, which doesn’t include things like sqlplus, sqlldr, impdp, etc. Yeah, you’ll need to ask an admin for help if you don’t have the privs on your machine to install stuff.
I’m not sure that this is really using a direct path load. The sqlldr help clearly states that the direct switch defaults to false:
direct — use direct path (Default FALSE)
I don’t see anywhere in the batch file or the CTL file where it is setting this switch to TRUE in order to use the direct path load as opposed to the conventional path load.
Also, according to the docs you need to ensure that the catldr.sql script has been run as well:
http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1007669
Good point. Thank you.
I used from command line the direct=TRUE option but I hadn’t executed that catldr.sql script so probably I didn’t got “direct path loading”.
Thanks! Very good article, it was very helpfull for me.
I justface this problem with the TNSNAMES so instead using yours I created the below conexion using Win CMD (it could change a little in Unix):
sqlldr userid=username/pwd@\”\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=hostname_or_IP\)\(PORT= 1521\)\)\)\(CONNECT_DATA=\(SERVER=DEDICATED\)\(SID=db_name\)\)\)\” CONTROL=my_control_file.ctl LOG=”my_log.log” BAD=”my.bad” skip=0
Greets!
So I really love the feature of having SQL Developer generate SQL*Loader control files. But when I try to get it to do anything other than a CSV file I get the “No readers registered” error. I have not been able to figure out how to resolve that one. I also am wonder how I can use this feature for fixed-width format files. Any thoughts there? By the way, I am running version 3.1.07 and plan on upgrading shortly. Thanks for the good information.
If it’s a delimited file, just change your file name extension to CSV and then use the wizard to define the proper delimiter character. Pretty sure it does not support setting up fixed width input files.
Any insight into the “No readers registered” error?
The Import Data wizard only has ‘readers’ setup for XLS, XLXS, CSV, TSV, and DSV files. If you have a delimited text file of some sort, you can just rename your file to filename.CSV, then proceed. After the file is read, you can use the wizard to change the delimiter to match your file.
When you try to open a file type that isn’t recognized, say an XML file, you’ll get the “No readers registered” error.
I have an XML file (~40MB)
How I can to import it ?
Where to find registered reader ?
SQL Developer and JDeveloper hangs on opening it.
Any suggestions ?
Who knows a right tool ?
🙂
We don’t currently have a ‘reader’ for XML in order to build IMPORT ‘jobs’ – via INSERT or SQL*Loader. It’s been requested a few times, so the odds of it happening are fairly decent.
Can you go back to your source and request the data in a delimited format instead?
Otherwise you’ll need to find another tool. There are many in the database/XML space.
You might be able to use XML DB to get the data in and then massage it to the table(s) you want – but I don’t have experience in that space.
http://www.oracle.com/technetwork/database/features/xmldb/index.html
Hi Jeff,
Apologies for continuing an old thread! Just thought I would voice a quick opinion, now that I found you delved into this issue on your blog 🙂
Although it seems like a trivial issue, I think it would be great if text files (whether CSV, or delimited in another way) would be read without having to rename the file to .csv. CSV implies the file is already delimited with commas, so it’s not intuitive to think that renaming the file to .csv (from .txt, for example) is going to work if the file is tab-delimited, for example.
What do you reckon?
Also, Excel works great for small files (<1 million rows). But in a real-life situation where a client hands you a set of text files, each a few GBs in size, you need a simple way of importing these – the SQL Developer import wizard is an ideal tool for this, but would need that little tweak to allow the reading of .txt files by default.
Cheers!
Hi Jeff Smitt!
Thanks for very interesting information!
Mahir
Thanks Mahir! You may be my first commentor from, Azerbaijan?
I think yes 🙂 I am first commentor from Azerbaijan!
Regards
Mahir M. Quluzade
http://www.mahir-quluzade.com
Great Info, Jeff! No doubt that many a DBA out there did not know that. Another reason why SQL Developer may be adopted even by regular SQL*Plus hardcore guys 🙂
Adopted? I’ll be happy if they agree to take us in as Foster kids 🙂