Upload csv file into Oracle db [message #338078] |
Sun, 03 August 2008 03:25  |
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 #338084 is a reply to message #338081] |
Sun, 03 August 2008 04:13  |
 |
Littlefoot
Messages: 21823 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.
|
|
|