Home » RDBMS Server » Server Utilities » How to load the flat files to the oracle database? (Oracle 10g)
How to load the flat files to the oracle database? [message #337884] Fri, 01 August 2008 08:18 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi all,
Here i need a clear step by step that , how to load the flat files by using sql*loader? I have gone through many articles but still no luck can anyone guide me through clear and simple steps.
pls....
Your fast responce is appreciated..

Thanks and Regards..
Hammer..
Re: How to load the flat files to the oracle database? [message #337887 is a reply to message #337884] Fri, 01 August 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm#i436326

Regards
Michel
Re: How to load the flat files to the oracle database? [message #337888 is a reply to message #337884] Fri, 01 August 2008 08:24 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Many articles? Your search criteria must be flawed. You can try here (only one eample, even if outdated):

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/part2.htm#436160
Re: How to load the flat files to the oracle database? [message #337889 is a reply to message #337884] Fri, 01 August 2008 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm
Re: How to load the flat files to the oracle database? [message #337894 is a reply to message #337888] Fri, 01 August 2008 08:35 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi ,
Can you explain in steps so that i can try it now.


Thanks and Regards ,
Hammer.
Re: How to load the flat files to the oracle database? [message #337895 is a reply to message #337894] Fri, 01 August 2008 08:37 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
aviva4500 wrote on Fri, 01 August 2008 09:35
Hi ,
Can you explain in steps so that i can try it now.


Thanks and Regards ,
Hammer.


It's all included in the links. Read on.
Re: How to load the flat files to the oracle database? [message #337897 is a reply to message #337894] Fri, 01 August 2008 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
aviva4500 wrote on Fri, 01 August 2008 06:35
Hi ,
Can you explain in steps so that i can try it now.


Thanks and Regards ,
Hammer.


It is explained in the Fine Utilities Manual pointed to by the URL I posted.

If you can not understand the words in the manual why should I waste my time & effort posting the same words here for you to NOT understand here again/still?

You're On Your Own (YOYO)!
Re: How to load the flat files to the oracle database? [message #337930 is a reply to message #337894] Fri, 01 August 2008 10:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Step 1:
Create a table to load your data into.

Step 2:
Create a SQL*Loader control file.
You do this just like you would create a .sql script, except that you give it a .ctl extension instead. The contents of your SQL*Loader control file is dependent on what your flat file looks like, for example whether it is delimited or fixed position.

Step 3:
Load your data by running SQL*Loader.
SQL*Loader can be run from the operating system or from SQL*Plus using the HOST command. For example, you can run the following substituting your actual values for your_username and your_password and your_control_file and whatever you want your_log to be.

SQL> HOST SQLLDR your_username/your_password CONTROL=your_control_file.ctl LOG=your_log.log

Step 4:
Check the results.
Select from your table and see what your get.

Step 5:
Diagnose problems.
If you don't see what you expect in step 4, then:
SQL> EDIT your_log.log
and read what it says. Make appropriate changes and try again.


Without knowing your table structure or what your flat file looks like, we cannot tell you what your SQL*Loader control file should look like. If you read the SQL*Loader section of the Utilities Guide in the online documentation, there is a separate section for the control file that tells you what to put in it, with many examples. You need to show some effort on your own and post what you have tried and the results that you have gotten.

Re: How to load the flat files to the oracle database? [message #340107 is a reply to message #337930] Mon, 11 August 2008 08:35 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Barbara Boehmer,
Below are the steps what i have done in my database.

1. created new folder in my c drive as:
C:\oracle\xtrn_data

2. created a file employee.csv using above data (from this artical) to this directory
C:\oracle\xtrn_data\employee.csv

3. connected as sys and created xtern_data_dir directory object:

SQL> conn sys as sysdba;
Enter password: ***
Connected.
SQL> create or replace Directory xtern_data_dir as 'C:\oracle\xtrn_data';

Directory created.

4. Issued grant read and write privileges to directory object to Scott.

SQL> grant read, write on directory xtern_data_dir to Scott;

Grant succeeded.

5. connected as Scott and created external table:

SQL> create table xtern_empl_rpt
2 ( empl_id varchar2(3),
3 last_name varchar2(50),
4 first_name varchar2(50),
5 ssn varchar2(9),
6 email_addr varchar2(100),
7 years_of_service number(2,0)
8 )
9 organization external
10 ( default directory xtern_data_dir
11 access parameters
12 ( records delimited by newline
13 fields terminated by ','
14 )
15 location ('employee_report.csv')
16 );
Table created.

I have error when i issue select command.

select * from xtern_empl_rpt;
Error at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
kup-04063:unable to open log file xtern_empl_rpt_32230.log
os error No such file or directory
ora-06512:at "sys.oracle_loader",line 19

I think i have not created the log file.
Can you explain where to create the log file in steps.


Thanks & Regards
Hammer.



[Updated on: Mon, 11 August 2008 08:37]

Report message to a moderator

Re: How to load the flat files to the oracle database? [message #340109 is a reply to message #340107] Mon, 11 August 2008 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add "nologfile" in access parameters.

Regards
Michel
Re: How to load the flat files to the oracle database? [message #340113 is a reply to message #340109] Mon, 11 August 2008 08:48 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Thanks for your reply.For the below query where to add the nolog files.

create table xtern_empl_rpt
2 ( empl_id varchar2(3),
3 last_name varchar2(50),
4 first_name varchar2(50),
5 ssn varchar2(9),
6 email_addr varchar2(100),
7 years_of_service number(2,0)
8 )
9 organization external
10 ( default directory xtern_data_dir
11 access parameters
12 ( records delimited by newline
13 fields terminated by ','
14 )
15 location ('employee_report.csv')
16 );

Thanks & Regards
Hammer.

Re: How to load the flat files to the oracle database? [message #340116 is a reply to message #340113] Mon, 11 August 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 11 August 2008 15:42
Add "nologfile" in access parameters.

Regards
Michel

You can also check it in documentation.

Regards
Michel

Re: How to load the flat files to the oracle database? [message #340661 is a reply to message #337884] Wed, 13 August 2008 13:28 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Now I tried differently but now getting new error.


My date file is

12, Research, "Saratoga"
10, "Accounting", Cleveland
11, "Art", Salem
13, Finance, Dubai
21, Sales, India
22, Sales, Australia
42, "Int'l", "San Fran"
---------------------------------------------------------
Table:

CREATE TABLE testdept
(deptnum NUMBER(2) NOT NULL,
dname VARCHAR2(14),
loc VARCHAR2(13));
------------------------------------------------

Control file is

LOAD DATA
INFILE 'c:\exp.dat'
INTO TABLE testdept
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(deptnum, dname, loc)
--------------------------------------------------------

When I use

c:\>sqlldr username/password@DB control ='exp.ctl'
I am getting the following error.

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


Thanks & Regards
Hammer
Re: How to load the flat files to the oracle database? [message #340667 is a reply to message #340661] Wed, 13 August 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
%ORACLE_HOME%\bin\sqlldr ...

Regards
Michel
Re: How to load the flat files to the oracle database? [message #340669 is a reply to message #337884] Wed, 13 August 2008 13:48 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
The system cannot find the path specified.The steps which I have done is correct if not can you correct me where i left something.



Thanks & Regards
Hammer.
Re: How to load the flat files to the oracle database? [message #340673 is a reply to message #340669] Wed, 13 August 2008 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't set ORACLE_HOME.

Regards
Michel
Re: How to load the flat files to the oracle database? [message #340679 is a reply to message #337884] Wed, 13 August 2008 14:14 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Can you explain the steps,how to set the oracle_home.

Thanks in Advance


Thanks & Regards
Hammer.
Re: How to load the flat files to the oracle database? [message #340690 is a reply to message #340679] Wed, 13 August 2008 14:56 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set ORACLE_HOME=...

Of course, you must have previously install Oracle.

Regards
Michel
Previous Topic: How to read content from a datapump file
Next Topic: how to upload (export) the text file to database?
Goto Forum:
  


Current Time: Sat Dec 03 04:15:43 CST 2016

Total time taken to generate the page: 0.08285 seconds