Creating External Table DDL - Oracle 10g

[peace2009]

 CREATE TABLE oldemp ( fname char(25), lname char(25) )
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY emp_dir
     ACCESS PARAMETERS
     (RECORDS DELIMINATED BT NEWLINE
      NOBADFILE
      NOLOGFILE
      FIELDS TERMINATED BY ',' (fname POSITION (1:20) CHAR, lname POSITION (22:41) CHAR)
     )
     LOCATION ('emp.dat') )
     PARALLEL 5
     REJECT LIMIT 200;

1. Can you please explain this part:

ACCESS PARAMETERS
     (RECORDS DELIMINATED BT NEWLINE
      NOBADFILE
      NOLOGFILE
      FIELDS TERMINATED BY ',' (fname POSITION (1:20) CHAR, lname POSITION (22:41) CHAR)
     )

2. Can you please explain what PARALLEL 5 is? and Why do we need it?

I will google anyway. Your help would not harm anyway

[PAVB]

I will google anyway.

That sounds like a plan.

Google it. Read Oracle documentation -it's free! - then come back with your doubts.

[peace2009]

That sounds like a plan.

Google it. Read Oracle documentation -it's free! - then come back with your doubts.

I got the answers. However, i'm facing another problem. I did

create or replace directory emp_dir
as 'F:\emp_dir';

The folder is created in F:\....It did not show up even though the transaction succeeded...I have all grants i need.

I'm running Win Vista x32...

After the above query, i did:

drop table emp_data;

CREATE TABLE emp_data
  (first_name  VARCHAR2(20)
  ,last_name   VARCHAR2(20)
  , email     VARCHAR2(30)
  )
ORGANIZATION EXTERNAL
(
 TYPE oracle_loader
 DEFAULT DIRECTORY emp_dir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  NOBADFILE
  NOLOGFILE
  FIELDS
  ( first_name POSITION ( 1:20) CHAR
  , last_name POSITION (22:41) CHAR
  ,  email   POSITION (43:72) CHAR )
 )
 LOCATION ('emp.dat') ) ;

The table creation succeeded, but when i do select * from emp_data;

Error starting at line 1 in command:
select * from emp_data
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp.dat in EMP_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.

Any help will be appreciated....Thanks

[peace2009]

SQL> SELECT *
FROM dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS EMP_DIR
F:\emp_dir

SYS SUBDIR
D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep

SYS XMLDIR
D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\

dba_directories records shows that i got emp_dir i just created, but i can't see it in my F: drive

[abhi_oracle]

Grant read permission to the directory you created. Then check

[abhi_oracle]

like this: grant read on directory emp_dir to user

[peace2009]

like this: grant read on directory emp_dir to user

It did not help....I even did

grant read, write on directory emp_dir to scott;

Didn't help too

[abhi_oracle]

What did you mean by "dba_directories records shows that i got emp_dir i just created, but i can't see it in my F: drive" .. you gotta create that directory yourself ... go to F drive and create a physical folder with that name

[peace2009]

What did you mean by "dba_directories records shows that i got emp_dir i just created, but i can't see it in my F: drive" .. you gotta create that directory yourself ... go to F drive and create a physical folder with that name

Good. It worked. However, if i'm a remote user and i have been granted create directory privilege, how can i manage creating the folder and file manually! I might not have access to the PC or Unix Server Drive to create the directory by myself. Any idea?

[PAVB]

if i'm a remote user and i have been granted create directory privilege, how can i manage creating the folder and file manually! I might not have access to the PC or Unix Server Drive to create the directory by myself.

That would mean you are not a DBA, create directory priv was granted in error to you and, you should ask a DBA for help.