Home » RDBMS Server » Server Utilities » how to create control file to insert data in our database (oracle 11g,version:11.1.0,windows XP2)
how to create control file to insert data in our database [message #536537] Wed, 21 December 2011 06:52 Go to next message
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

You have to go with sql loader or external table to load into table.
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you read the question?
Q: how to do this in sqlloader?
A: use sqlloader
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once more a silly answer from him as 95% of them.

Regards
Michel
problem in loading data from sql* loader [message #536631 is a reply to message #536537] Wed, 21 December 2011 23:43 Go to previous messageGo to next message
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

Re: problem in loading data from sql* loader [message #536635 is a reply to message #536631] Thu, 22 December 2011 00:08 Go to previous messageGo to next message
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).
Re: problem in loading data from sql* loader [message #536675 is a reply to message #536631] Thu, 22 December 2011 03:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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".
Re: problem in loading data from sql* loader [message #536834 is a reply to message #536675] Fri, 23 December 2011 04:59 Go to previous messageGo to next message
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
Re: problem in loading data from sql* loader [message #536840 is a reply to message #536834] Fri, 23 December 2011 05:11 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Type sqlldr in command prompt? if you get manual, you have else not.
Re: problem in loading data from sql* loader [message #536841 is a reply to message #536834] Fri, 23 December 2011 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the same way you search any file with the explorer!

Regards
Michel
Re: problem in loading data from sql* loader [message #536845 is a reply to message #536840] Fri, 23 December 2011 05:33 Go to previous messageGo to next message
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.


Re: problem in loading data from sql* loader [message #536847 is a reply to message #536845] Fri, 23 December 2011 06:14 Go to previous messageGo to next message
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?
Re: problem in loading data from sql* loader [message #536848 is a reply to message #536847] Fri, 23 December 2011 06:19 Go to previous messageGo to next message
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
Re: problem in loading data from sql* loader [message #536851 is a reply to message #536848] Fri, 23 December 2011 06:29 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

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'
Re: problem in loading data from sql* loader [message #536858 is a reply to message #536851] Fri, 23 December 2011 06:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Atttach the sample data,which table to load with table script.
Re: problem in loading data from sql* loader [message #536867 is a reply to message #536858] Fri, 23 December 2011 08:09 Go to previous messageGo to next message
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.
Re: problem in loading data from sql* loader [message #536868 is a reply to message #536848] Fri, 23 December 2011 08:11 Go to previous messageGo to next message
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.
Re: problem in loading data from sql* loader [message #536873 is a reply to message #536868] Fri, 23 December 2011 10:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.

Re: problem in loading data from sql* loader [message #536918 is a reply to message #536862] Sat, 24 December 2011 00:13 Go to previous message
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:
  


Current Time: Thu Mar 28 16:58:58 CDT 2024