Home » SQL & PL/SQL » SQL & PL/SQL » How to load the flat files data to database? (Oracle 10G)
How to load the flat files data to database? [message #360669] Sat, 22 November 2008 00:41 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi all,
I would like to know how to load the flat files by using sql*loader? I have gone through many articles but still no luck .I have tried the below steps so far.But i get the following error.

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

2. created a file employee.csv using above data (from this artical) to this directory
C:\TEST\xtrn_data\LOAD.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:\TEST\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 ('LOAD.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



Thanks & Regards
Hammer.





Re: How to load the flat files data to database? [message #360670 is a reply to message #360669] Sat, 22 November 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
os error No such file or directory

I think it is clear.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to load the flat files data to database? [message #360673 is a reply to message #360670] Sat, 22 November 2008 01:09 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Is this directory on the same system as the database server?

Can the OS user that runs the Oracle software write to this directory?
Re: How to load the flat files data to database? [message #360681 is a reply to message #360669] Sat, 22 November 2008 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that Oracle database isn't installed on your PC. Is it?

Quote:

1. created new folder in my c drive as:
C:\TEST\xtrn_data
This is a folder on your PC.

Quote:

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:\TEST\xtrn_data';
This is a directory object on a DATABASE SERVER (not on your PC!)

Quote:
No such file or directory
This is because Oracle couldn't find the CSV file in "c:\temp\xtrn_data" directory on a server.

If I was right, you could try to repeat what you've done if you have access to a database server. If not (which wouldn't be a surprise), you'll have to ask DBA to assist (create a directory, enable access to it).

Or, install Oracle client onto your PC and use SQL*Loader (not external tables feature!) and load the CSV file.
Re: How to load the flat files data to database? [message #360684 is a reply to message #360681] Sat, 22 November 2008 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually the error is on the log file:
Quote:
unable to open log file xtern_empl_rpt_32230.log

But this does not change anything.

Regards
Michel
Re: How to load the flat files data to database? [message #362425 is a reply to message #360681] Tue, 02 December 2008 04:20 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi,
You are correct absolutely i didn't created the file in the database server.But now i have created it in the same server .But i get the below error.
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line

Thanks & Regards
Hammer
Re: How to load the flat files data to database? [message #362426 is a reply to message #362425] Tue, 02 December 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-30653: reject limit reached
 *Cause:  the reject limit has been reached.
 *Action: Either cleanse the data, or increase the reject limit.

Regards
Michel
Re: How to load the flat files data to database? [message #362438 is a reply to message #362426] Tue, 02 December 2008 05:18 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
I have cleansed the data now for test i have only 5 records.But it is still showing the same..

Thanks & Regards
Hammer
Re: How to load the flat files data to database? [message #362441 is a reply to message #362438] Tue, 02 December 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a log file and a bad file to your access parameters then you will know why.

Regards
Michel
Re: How to load the flat files data to database? [message #362544 is a reply to message #362441] Tue, 02 December 2008 23:38 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Thanks a lot it got worked...You have been very helpfull.




Thanks & Regards
Hammer.
Re: How to load the flat files data to database? [message #362546 is a reply to message #362544] Tue, 02 December 2008 23:55 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi,
I have one more clarification if in a folder I have 10 files how can i append to the same table.For example in the previous post i have one file called "load.csv" if i have more data files how can i automize all the files in that particular folder.



Thanks & Regards,
Hammer.
Re: How to load the flat files data to database? [message #362562 is a reply to message #362546] Wed, 03 December 2008 00:54 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"location" parameter can contain a list of files.

Regards
Michel
Previous Topic: How to send email through PL/SQL
Next Topic: LONG DATA TYPE
Goto Forum:
  


Current Time: Mon Dec 05 10:50:26 CST 2016

Total time taken to generate the page: 0.05760 seconds