Home » SQL & PL/SQL » SQL & PL/SQL » Upload csv file into Oracle db
Upload csv file into Oracle db [message #338078] Sun, 03 August 2008 03:25 Go to next message
dalal
Messages: 3
Registered: August 2008
Junior Member
I have a csv file which i need to upload into a table in oracle db.
I used the below
create or replace directory my_dir as 'C:\Opendate';

CREATE TABLE generic_csv_ext1
(  C1   VARCHAR2(8),  
C2  date)
ORGANIZATION EXTERNAL  
(  TYPE ORACLE_LOADER     
DEFAULT DIRECTORY my_dir     
ACCESS PARAMETERS       
 ( records delimited BY newline        
  nobadfile         
  nologfile         
  nodiscardfile         
  fields  terminated BY ',' optionally enclosed BY '"'         
  lrtrim        
  missing field VALUES are NULL        )    
   LOCATION ('open_date.csv')  )
   REJECT LIMIT 0;

insert into suspense_data_report
( S_CIRCUITNO, S_MINCALLTIME, S_STATUSINTBMS)
select  c1 ,  c2 ,  'ACTIVE' from generic_csv_ext;

When i run the insert statement I get the error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file open_date.csv in MY_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

Anyone can help explaining the error above.
[EDITED by LF: added [code] tags]

[Updated on: Sun, 03 August 2008 04:14] by Moderator

Report message to a moderator

Re: Upload csv file into Oracle db [message #338079 is a reply to message #338078] Sun, 03 August 2008 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand to:
Quote:
file open_date.csv in MY_DIR not found

Check directory
Check file
Check permissions

And 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).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Upload csv file into Oracle db [message #338081 is a reply to message #338079] Sun, 03 August 2008 03:33 Go to previous messageGo to next message
dalal
Messages: 3
Registered: August 2008
Junior Member
The file exists in that directory in my local drive and the permissions are ok
Re: Upload csv file into Oracle db [message #338082 is a reply to message #338081] Sun, 03 August 2008 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove it.

Regards
Michel
Re: Upload csv file into Oracle db [message #338084 is a reply to message #338081] Sun, 03 August 2008 04:13 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
dalal wrote on Sun, 03 August 2008 10:33
The file exists in that directory in my local drive and the permissions are ok

It may be true - the file really can be in this directory on your local drive.

However, DIRECTORY object specifies an alias for a directory on the server file system. So, if "your local drive" is not database server's drive, you're out of luck. In other words,
create or replace directory my_dir as 'C:\Opendate';
did create a directory - NOT on your local drive, but on server.

Is this, perhaps, bothering you?

Also, there are "Spell-check message" and "Preview message" buttons. Please, use at least one of them in order to make sure that your post is correctly written. Smileys instead of column datatypes are rarely welcome.
Previous Topic: Copying a file from local drive into server
Next Topic: Invalid Number exception with the cursor
Goto Forum:
  


Current Time: Sun Dec 04 18:27:52 CST 2016

Total time taken to generate the page: 0.25748 seconds