Thread: Oracle Directory problem

Actually i am working on some project which require external file link with oracle, so i tried the following.

7369,smith,clerk,20
7499,allen,salesman,30
7521,ward,salesman,30
7566,jones,manager,20
7654,martin,salesman,30

is stored as demo1.dat in c:external directory.

Using sys create or replace directory dat_dir as 'c:external';

grant read on directory dat_dir to scott; are executed.

Connect scott/tiger;

create table ext_tab2 ( empno char(4), ename char(20), job char(20), deptno char(2)) organization external (type oracle_loader default directory ext access parameters ( records delimited by newline fields terminated by 0x'09' missing field values are null (empno, ename, job, deptno)) location ('demo1.dat')) parallel reject limit 10

select * from ext_tab2;

here its giving the result as no rows created, and creating bad file and 2 log files .

If i view bad file its shows the same content as in demo.dat, & a log file contains error as "too larger to fit in to column". But column size is correct.

Can any one help me solving this.

Thank you in advance.

Create directory mydir as 'd:\';
-----------------------------------------
grant read,write on directory mydir to debasis;
-----------------------------------------
create table extemp1
(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) )
organization external ( type oracle_loader default directory mydir
access parameters ( records delimited by newline skip 1 fields terminated by ','
( empno char, ename char, job char, mgr char, hiredate char, sal char, comm char, deptno char ))
location('data.lst') )
reject limit 1 ;




try the above code,it will solve your problem