OraFAQ Forum: Server Utilities » how to create control file to insert data in our database

Home » RDBMS Server » Server Utilities » how to create control file to insert data in our database (oracle 11g,version:11.1.0,windows XP2)

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

how to create control file to insert data in our database [message #536537]

Wed, 21 December 2011 06:52

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

hi,
could someone help me how to create control file and how to load the data through command window in our database using sql * loader.
iam having structure in my database and .csv file in my desktop.

[MERGED by LF]

[Updated on: Thu, 22 December 2011 00:11] by Moderator

Report message to a moderator

Re: how to create control file to insert data in our database [message #536538 is a reply to message #536537]

Wed, 21 December 2011 06:53

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Read this: http://www.orafaq.com/wiki/SQL*Loader_FAQ

If you get stuck, post more detail is my advice.

Report message to a moderator

Re: how to create control file to insert data in our database [message #536543 is a reply to message #536538]

Wed, 21 December 2011 07:05

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

You have to go with sql loader or external table to load into table.

Report message to a moderator

Re: how to create control file to insert data in our database [message #536546 is a reply to message #536543]

Wed, 21 December 2011 07:10

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Did you read the question?
Q: how to do this in sqlloader?
A: use sqlloader

Report message to a moderator

Re: how to create control file to insert data in our database [message #536547 is a reply to message #536546]

Wed, 21 December 2011 07:13

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Once more a silly answer from him as 95% of them.

Regards
Michel

Report message to a moderator

problem in loading data from sql* loader [message #536631 is a reply to message #536537]

Wed, 21 December 2011 23:43

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

hi,
could some one help me out of this error and the error is when iam trying to load the data through command window it is througing
error is

'sqlldr' is not recognized as an internal or external command,
operable program or batch file

but i created control file and .csv file and i kept in c drive

while iam excuting through command window the command iam using is

c:\sqlldr blackbeauty/MyCrackc@Live control=policies_update.ctl skip = 1

Report message to a moderator

Re: problem in loading data from sql* loader [message #536635 is a reply to message #536631]

Thu, 22 December 2011 00:08

flyboy
Messages: 1903
Registered: November 2006

Senior Member

The error you are facing is quite self-explanatory, is not it?

In Windows, most files contain extension. E.g. EXE for executable or BAT for batch files.
Are you sure, that file sqlldr (without extensions) is placed in the root directory of C drive?

I just wonder why you do not continue in the thread about the same topic (http://www.orafaq.com/forum/m/536537/96705/#msg_536537)
or why you did not put any feedback there (yes, I consider it impolite).

Report message to a moderator

Re: problem in loading data from sql* loader [message #536675 is a reply to message #536631]

Thu, 22 December 2011 03:14

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

Senior Member

Search for your sqlldr.exe file and either run the command from that directory or use the full directory path in the command or put the directory in your operating system default paths. You also need to do the same with your control file. Also, do not use any spaces on either side of the equal sign in "skip=1".

Report message to a moderator

Re: problem in loading data from sql* loader [message #536834 is a reply to message #536675]

Fri, 23 December 2011 04:59

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

hi,
could help me how to find sqlldr.exe file is present or not,if
sqlldr.exe is not found then sql loader does it work or not

Regards,
vvr

Report message to a moderator

Re: problem in loading data from sql* loader [message #536840 is a reply to message #536834]

Fri, 23 December 2011 05:11

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Type sqlldr in command prompt? if you get manual, you have else not.

Report message to a moderator

Re: problem in loading data from sql* loader [message #536841 is a reply to message #536834]

Fri, 23 December 2011 05:12

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In the same way you search any file with the explorer!

Regards
Michel

Report message to a moderator

Re: problem in loading data from sql* loader [message #536845 is a reply to message #536840]

Fri, 23 December 2011 05:33

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

The file is not located in my system,then how can i use the property of sql*loader and where i can get this sqlldr.exe file.
because i have almost 3 lakhs of records to load,could you please help me how to overcome this problem.

Report message to a moderator

Re: problem in loading data from sql* loader [message #536847 is a reply to message #536845]

Fri, 23 December 2011 06:14

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Can you attach the sample file with few records?
Do you have privilges for creating the external directory?

Report message to a moderator

Re: problem in loading data from sql* loader [message #536848 is a reply to message #536847]

Fri, 23 December 2011 06:19

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

yes i have privilges for creating the external directory,but i dont know how o create

Report message to a moderator

Re: problem in loading data from sql* loader [message #536851 is a reply to message #536848]

Fri, 23 December 2011 06:29

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Code: [Select all] [Show/ hide]

create or replace directory dir_temp as 'c:\oracle\mk';

grant read, write on directory dir_temp to <<user_name>>

select * from all_directories where directory_path='c:\oracle\mk'

Report message to a moderator

Re: problem in loading data from sql* loader [message #536858 is a reply to message #536851]

Fri, 23 December 2011 06:56

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

As you said i created directory,then how should i opproach
i searched in google that sqlldr.exe file will be loaded at the time of instillation,i mean it depends upon type of instillation.
and if u can u can send me the sample script to load the data from that i willopproach

Regards,
vvr

[Updated on: Sat, 24 December 2011 00:50] by Moderator

Report message to a moderator

Re: problem in loading data from sql* loader [message #536862 is a reply to message #536858]

Fri, 23 December 2011 07:02

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Atttach the sample data,which table to load with table script.

Report message to a moderator

Re: problem in loading data from sql* loader [message #536867 is a reply to message #536858]

Fri, 23 December 2011 08:09

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

You don't need sqlldr to create a control file. Use your favorite editor to create the control file YOURSELF. don't make someone else do it for you.
There are samples all over the internet. Do note though that without sqlldr, you will not be able to use it, so I do not see the point of either someone creating one for you or you creating it yourself.

Report message to a moderator

Re: problem in loading data from sql* loader [message #536868 is a reply to message #536848]

Fri, 23 December 2011 08:11

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

vickyminugmailcom wrote on Fri, 23 December 2011 07:19

yes i have privilges for creating the external directory,but i dont know how o create

Then use external tables, but based on your questions, this is way beyond your comprehension. Plus, you would need access to putting the data file on the server, not the client.

Report message to a moderator

Re: problem in loading data from sql* loader [message #536873 is a reply to message #536868]

Fri, 23 December 2011 10:38

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

Senior Member

Let's start by confirming whether you have SQL*Loader or not for sure. Since you are using Windows, using your mouse, put the arrow on the start button in the lower left corner of your screen, then click the left mouse button, move your arrow to all programs, then click the left mouse button again, move your arrow to accessories, then click the left button again, move your arrow to command prompt, then click your left button again. From the DOS command prompt window go to the root directory by typing cd\ and pressing the enter key. Then search for any sqlldr files in any sub-directories by typing:

dir sqlldr.*/s

Then press the enter key. Wait for it to return to the c:\> prompt so that you know it has finished. Then post here what it displayed inbetween. Please copy and paste, if you can, instead of typing. If you left click in the upper left corner of the DOS command prompt window, you should be able to select edit and use the features to mark and copy, then paste it here, using ctrl-v. Please post the whole thing from the cd\ command to the return to the c:\> prompt.

Note that this is not the only method of searching for the file, but it is one that checks all directories and produces output that can be copied and pasted easily.

Report message to a moderator

Re: problem in loading data from sql* loader [message #536918 is a reply to message #536862]

Sat, 24 December 2011 00:13

vickyminugmailcom
Messages: 54
Registered: October 2011
Location: banglore

Member

Thank you for your guidence in this finally i re-installed oracle in my system and loaded successfully
once again thank you so much

Regards,
vvr

[Updated on: Sat, 24 December 2011 00:49] by Moderator

Report message to a moderator

Previous Topic:	Parallel slaves on import
Next Topic:	Downgrade from Oracle11g to Oracle9i via exp/imp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 19:51:00 CDT 2025