| How to load the flat files to the oracle database? [message #337884] |
Fri, 01 August 2008 08:18  |
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 #340107 is a reply to message #337930] |
Mon, 11 August 2008 08:35  |
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 #340113 is a reply to message #340109] |
Mon, 11 August 2008 08:48 |
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 #340661 is a reply to message #337884] |
Wed, 13 August 2008 13:28 |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
