Topic: SQL*Plus and PL/SQL >> urgent help for EXternal table
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: urgent help for EXternal table |
| ashish |
Posted: Mar 07, 2008 09:18:34 PM |
Total Post: 14
Joined: Dec, 2007
|
hello friends i m hving promlem in fatching records from EXTERNAL TABLE.
For this i have did...
1. create directory....
SQL> create directory emp_dir as '\I:\flat_files';
Directory created.
2. then i create external table.....
SQL> create table oldemp(empno number,empname char(20),birthdate date)
organization external(type oracle_loader default directory emp_dir access
parameters( records delimited by newline badfile 'bad_emp' logfile 'log_emp' fields terminated by ',' (empno char,empname char,birthdate char
date_format date mask "dd-mon-yyyy")) location('emp1.txt')) parallel 5 reject limit 200;
Table created.
3. describe table
SQL> desc oldemp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
EMPNAME CHAR(20)
BIRTHDATE DATE
4. but when i m tring to fatch data it give error:
SQL> select * from oldemp;
select * from oldemp
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file log_emp.log
OS error The device does not recognize the command.
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1.
------------------------------------
i hv already write 2 records in text file. so iwas expecting that two result.
|
|
|
 Garlapati |
| Posted: Mar 08, 2008 05:22:02 AM |  |
|
Total Post: 30
Joined: Apr, 2007
|
create table oldemp(empno number,empname char(20),birthdate date)
organization external
(type oracle_loader
default directory OSP_DIR
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('emp1.txt')
)
try with this script it works fine..
i think there is a mistake in date format in u r script which is not matching
datacatrige error means .
if the flat file is not in that directory then it occurs
first check wether the flat file is exist or not.
and remove the part
(empno char,
empname char,
birthdate char,
date_format date mask 'dd-mon-yyyy')
u r putting docuble code in date format it is not right
it will execute but at retrivation time it fire an error.
i think it is not Necessary to create log and bad files
try with this...
all the best
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
