OraFAQ Forum: Server Utilities » Please explain SQL* Loader with simple example

Home » RDBMS Server » Server Utilities » Please explain SQL* Loader with simple example

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please explain SQL* Loader with simple example [message #608936]

Thu, 27 February 2014 04:33

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

I am unable to understand SQL * Loader, please simplify anyone with example, suppose I have .txt file on the our system in D drive then how can .txt file data transfer in our database employee table, need example because I could not understood from helping links docs.oracle.com

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #608941 is a reply to message #608936]

Thu, 27 February 2014 05:03

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As you have a TXT file, you have to create a control file first. It would read that TXT file and load data into the EMPLOYEE table.

Here's a simple example based on Scott's DEPT table (I created an empty one, called DEPT_TEST).

Control file:

load data
infile 'm:\a1_maknuto\dept_data.txt'
into table dept_test
replace
fields terminated by ','
trailing nullcols
  (
   deptno,
   dname,
   loc
  )

Input file (dept_data.txt):

1,Accounting,Zagreb
2,Sales,Zadar
3,Operations,Split

Loading session:

M:\a1_maknuto>sqlldr scott/tiger@ora10 control=test11.ctl log=test11.log

SQL*Loader: Release 11.2.0.1.0 - Production on ╚et Vel 27 11:59:23 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

What have we done:

M:\a1_maknuto>sqlplus scott/tiger@ora10

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 11:59:27 2014

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> select * From dept_test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 Accounting     Zagreb
         2 Sales          Zadar
         3 Operations     Split

SQL>

I believe that you should take some time and carefully read SQL*Loader documentation. Basics are simple, but they require some reading anyway. For complex stuff return back here, someone will assist.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #608946 is a reply to message #608941]

Thu, 27 February 2014 05:35

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Many thanks Littlefoot, please tell me one question more, I have oracle 10g then I can practice there for sql*loader

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #608947 is a reply to message #608946]

Thu, 27 February 2014 05:37

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Certainly.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609013 is a reply to message #608947]

Fri, 28 February 2014 00:44

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Please let me know that is I can write it on SQL Plus editor as
SQL>load data
SQL>infile 'm:\a1_maknuto\dept_data.txt'
SQL>into table dept_test
SQL>replace
SQL>fields terminated by ','
SQL>trailing nullcols
(
deptno,
dname,
loc
);

if not then where I can write above code, please confirm me.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609014 is a reply to message #609013]

Fri, 28 February 2014 00:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Control file is a "file", it is to be created with any text editor and saved into a directory on your disk.

You didn't read the documentation, did you?

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609043 is a reply to message #609014]

Fri, 28 February 2014 04:45

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

I could unable to understand above statement after that carefully study of SQL*Loader documentation
Please guide me about loading session, where I will execute it, can I execute on sql plus editor or anywhere others.

Please enhance me about Loading session: What have we done: Connected to: That where I will execute, can I execute it on sql plus editor

[EDITED by LF: removed invalid quotes]

[Updated on: Fri, 28 February 2014 04:48] by Moderator

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609044 is a reply to message #609043]

Fri, 28 February 2014 04:48

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Run SQL*Loader from operating system's command prompt.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609046 is a reply to message #609044]

Fri, 28 February 2014 05:06

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

how to Run SQL*Loader from operating system's command prompt, I have no idea,
Please tell me process to Run SQL*Loader from operating system's command prompt on 10g sql plus editor

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609048 is a reply to message #609046]

Fri, 28 February 2014 05:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Do you know what "operating system command prompt is"? It has nothing to do with SQL*Plus.

As you didn't specify your operating system, here's how it goes on my Windows 7: click Start - All programs - Accessories - Command prompt. A black window with white letters appears. It displays prompt, such as M:\> (M is disk letter, followed by :\>. This is not any kind of a smiley).

Now you run SQL*Loader:

M:\>sqlldr ...

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609050 is a reply to message #609048]

Fri, 28 February 2014 05:35

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Thank you very much LF, I understood it, But I have one confusion more here please help me I have created control file: on named control_file.text in our disc, then it will be write or wrong.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609052 is a reply to message #609050]

Fri, 28 February 2014 05:38

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Its name doesn't matter; you just have to specify it as I did:

sqlldr scott/tiger@ora10 control=control_file.text ...

I prefer keeping the .CTL extension (so that I *know* what these files represent).

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609053 is a reply to message #609052]

Fri, 28 February 2014 05:52

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Thank you LF for supporting lot of.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609072 is a reply to message #609053]

Fri, 28 February 2014 23:59

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

My window version is
Microsoft windows[Version 6.1.7601]

When we try to like
E:\>SQL-Loder>sqlldr hr/hr@ora10
then get problems 'E:\>SQL-Loder>' is not recognized as an internal or external commond.
operable program or batch file.

Please help me.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609073 is a reply to message #609072]

Sat, 01 March 2014 00:07

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What is this, painted in red?

E:\>SQL-Loder>sqlldr hr/hr@ora10

[Updated on: Sat, 01 March 2014 00:08]

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609074 is a reply to message #609073]

Sat, 01 March 2014 00:22

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

SQL-Loder is a folder in E: drive where I can save text file data & ctl file data.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609075 is a reply to message #609074]

Sat, 01 March 2014 00:32

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Huh, it is kind of difficult to assist as you seem to know nothing about operating system's command prompt, what it is and how to use it (not to mention SQL*Loader). Do you know how to navigate through the file system using command prompt?

Could you, please, describe - STEP BY STEP - what you did and how? What did you type and how Windows responded?

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609078 is a reply to message #609075]

Sat, 01 March 2014 01:04

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

For Loading session: I give commond as
E:\SQL-Loder>sqlldr hr/hr@ora10 control=test11.ctl log=test11.log
Where E: is our PC drive name and within its SQL-Loder folder
SQL-Loder is folder name where we stored named of test11.ctl file and dept_data.txt file

Also I have created table named: dept_test on the sql plus editor means database

Now I want to fetch data from dept_data.txt file into table dept_test

But when used commond prompt
E:\SQL-Loder>sqlldr hr/hr@ora10 control=test11.ctl log=test11.log
then get problems 'E:\>SQL-Loder>' is not recognized as an internal or external commond.
operable program or batch file.

Also I could not get why are we using sqlldr, what's its means and one more what it is log=test11.log I am unknow about two parameter
Please explain me about it.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609079 is a reply to message #609078]

Sat, 01 March 2014 01:18

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You didn't read SQL*Loader documentation, did you?

I asked you a question which you didn't answer. It was (once again): Do you know how to navigate through the file system using command prompt? Your answer is ...?

When you start command prompt, which disk are you on? C? Do you know how to move to E disk? Enter the SQL-Loder directory?

C:\>                           --> you're on disk C
C:\>e:                         --> go to disk E

E:\>cd sql-loder               --> enter sql-loder directory

E:\SQL-Loder>sqlldr ...        --> run the command (sqlldr in this case)

Quote:

Also I could not get why are we using sqlldr, what's its means

Tell me that you are joking! We are discussing SQL*Loader for two days, and now you ask me what it is used for?!? Go away, start reading documentation, don't come back until you know the basics.

This is pointless ...

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609080 is a reply to message #609079]

Sat, 01 March 2014 01:38

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

I am not joking here LF and I give you respect lot, I am serious to learn SQL* Loder, I have read SQL* Loder documentation but some point I could not understand from there that's why we had to join orafaq to put up my query on the forum.
I am not kidding here, you are honorable person and if you want to me go away from here then I will go out from here.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609081 is a reply to message #609080]

Sat, 01 March 2014 01:59

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Hello, LF - I'll have a try, I think you missed something.

Aaditya, you MUST be accurate. Use copy-paste to transfer details from your command prompt to your OraFAQ post. For example, you posted this:

Quote:

But when used commond prompt
E:\SQL-Loder>sqlldr hr/hr@ora10 control=test11.ctl log=test11.log
then get problems 'E:\>SQL-Loder>' is not recognized as an internal or external commond.
operable program or batch file.

which is impossible. Look at the characters I have high-lighted in red, it is impossible to get that error from that command. The right angle bracket character, this one: > is nit permitted by the Windows command interpreter as part of a directory or file name. It is permitted as part of prompt. So what did you actually do? Copy/paste the whole command prompt session, and use [code] tags to format it for display

You might also want to consider attending an introductory course in using Windows.

--update: typo

[Updated on: Sat, 01 March 2014 02:00]

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609083 is a reply to message #609081]

Sat, 01 March 2014 03:49

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Thank you John for giving discussion opportunity again here.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609090 is a reply to message #609083]

Sat, 01 March 2014 07:17

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I didn't mean that you should leave the forum, but to take some time (at least a few hours) and study documentation. It is pointless to discuss anything about SQL*Loader (please, spell it correctly, its name is NOT "Loder" but "Loader") if you don't even know what is its purpose. You can't expect anyone here to provide that kind of assistance - you must do something by yourself. As I said - basics is on you. Then, if you still have questions, ask.

Another part of your problem is that you don't know how to use Windows' command prompt. There are some commands that let you change disks, enter certain directories, copy files, etc. etc., all of that by typing those commands - no mouse can be used here. It is not your fault - nobody taught you that because GUI is now "in", not many people actually type commands. HOWEVER, as you chose to use Oracle, you have to get used to communicate with it using different tools. SQL*Plus is one of them. SQL Developer is a GUI tool that enables you to do the same as with SQL*Plus, just in a user-friendly manner. Forms lets you create front-end applications that will be used by end users. There are many other Oracle products I didn't even mention; your current task is to use SQL*Loader.

It requires you to know basic basics of the operating system command prompt (as you invoke it from command prompt) as well as basics of SQL*Loader itself.

Once again: forum is not a training course. You'll have to do that elsewhere. We can assist, but not the way you expect it now. I tried to point out what you have to do in both command prompt as well as SQL*Loader (control file, loading seesion ...) but that obviously wasn't enough. I don't know what would you want me to do; I can't go to Delhi and actually *show* you how to find command prompt, how to type commands, why is "E:\>SQL-Loder> not recognized as ...", how to create control file and so forth. There are just too many basic things that you don't know, and until you learn them, you can't expect any progress.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609100 is a reply to message #609078]

Sat, 01 March 2014 13:12

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

aaditya321 wrote on Fri, 28 February 2014 23:04

For Loading session: I give commond as
E:\SQL-Loder>sqlldr hr/hr@ora10 control=test11.ctl log=test11.log
Where E: is our PC drive name and within its SQL-Loder folder
SQL-Loder is folder name where we stored named of test11.ctl file and dept_data.txt file

Also I have created table named: dept_test on the sql plus editor means database

Now I want to fetch data from dept_data.txt file into table dept_test

But when used commond prompt
E:\SQL-Loder>sqlldr hr/hr@ora10 control=test11.ctl log=test11.log
then get problems 'E:\>SQL-Loder>' is not recognized as an internal or external commond.
operable program or batch file.

Also I could not get why are we using sqlldr, what's its means and one more what it is log=test11.log I am unknow about two parameter
Please explain me about it.

SQL*Loader is an Oracle utility that is used for loading data from text files into Oracle tables. It can be run from the operating system or from SQL*Plus using the HOST command. There is a file named sqlldr.exe that is in a bin sub-directory of the Oracle files on your operating system. From an operating system (command) prompt in that directory, you can type sqlldr to run SQL*Loader. If you are in a different directory and your path is not set to look in the bin directory, then you need to give the complete path that the sqlldr.exe file is in. So, from the operating system you would type:

your_Oracle_path\bin\sqlldr

You can also run SQL*Loader from the command prompt of SQL*Plus by typing:

host sqlldr

By default, when you log in to SQL*Plus, you should be in the correct directory, so you do not need to worry about the path. Also, this can eliminate some potential problems with permissions, so I usually prefer to run SQL*Loader from SQL*Plus. You might trying loggin in to SQL*Plus and type just:

host sqlldr

and see what you get. I believe it should list the possible parameters, such as your username and password and control file.

I also prefer to create the control file from SQL*Plus to avoid any problems with directories or permissions. If you do not create your control file from SQL*Plus in the default directory, then you will need to specify the complete path that your control file is in. You can specify the data file in either your sqlldr command line or in the control file. Once again, if not using the default directory, then you must specify the complete directory path where your data file is. So, a sample command to run SQL*Loader from SQL*Plus would be:

host oracle_path/bin/sqlldr username/password@database_name control=control_file_path/control_file_name data=data_file_path/data_file_name

You would of course substitute the actual paths and values for oracle_path and username and password and database_name and control_file_path and control_file_name and data_file_path and data_file_name.

In your case it would be something like:

host sqlldr hr/hr@ora10 control="E:\SQL-Loder\test11.ctl" data="E:\SQL-Loder\your_data_file.txt" log=test11.log

Note that you need to not have any spaces on either side of the = sign and if you have any spaces or special characters in your path or file names, then you need to enclose them in double quotes.

The log file records the results of your SQL*Loader run, so if it does not load the data as expected, then you can check the log to see what went wrong.

[edit: moved misplaced quotation mark]

[Updated on: Sun, 02 March 2014 11:24]

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609103 is a reply to message #609100]

Sun, 02 March 2014 02:23

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Very nice exlaination , but if i want to upload multiple text files at the same time into oracle database, is there any feature available.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609104 is a reply to message #609103]

Sun, 02 March 2014 02:27

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

If you look at the syntax of the DATA= argument, you will see how to specify several files.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609109 is a reply to message #609104]

Sun, 02 March 2014 11:32

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

thanks john but can i get some examples, all i need id choose a folder of .csv or txt files and upload it in database. i know many tecnologies like c# provide this kind of thing but not sure in oracle.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609111 is a reply to message #609103]

Sun, 02 March 2014 11:35

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

m.abdulhaq wrote on Sun, 02 March 2014 00:23

Very nice exlaination , but if i want to upload multiple text files at the same time into oracle database, is there any feature available.

You can specify multiple data files in the DATA parameter of the sqlldr command line, as described in the following section of the online documentation:

http://docs.oracle.com/cd/E16655_01/server.121/e17639/ldr_params.htm#SUTIL1023

or you can specify multiple data files using one INFILE line per file in the control file, as described in the following section of the online documentation:

http://docs.oracle.com/cd/E16655_01/server.121/e17639/ldr_control_file.htm#SUTIL1065

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609113 is a reply to message #609109]

Sun, 02 March 2014 11:42

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

m.abdulhaq wrote on Sun, 02 March 2014 09:32

thanks john but can i get some examples, all i need id choose a folder of .csv or txt files and upload it in database. i know many tecnologies like c# provide this kind of thing but not sure in oracle.

DATA="your_path_name\your_folder_name\*.csv"

or

DATA="your_path_name\your_folder_name\*.txt"

for example:

data="E:\SQL-Loder\*.txt"

This assumes that you want to load all of the text files in that directory into the same table.

Report message to a moderator

Re: Please explain SQL* Loader with simple example [message #609206 is a reply to message #609113]

Mon, 03 March 2014 22:31

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Thanks for supporting all guys here who had given good example. And now my problems has resolved.

Report message to a moderator

Previous Topic:	Problem in sql loader
Next Topic:	EXTERNAL TABLE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 18:49:44 CST 2025