Creating External Table DDL - Oracle 10g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Creating External Table DDL - Oracle 10g

  1. #1
    Join Date
    Jul 2009
    Posts
    9

    Creating External Table DDL - Oracle 10g

    Code:
     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:

    Code:
    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by peace2009 View Post
    I will google anyway.
    That sounds like a plan.

    Google it. Read Oracle documentation -it's free! - then come back with your doubts.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2009
    Posts
    9

    Lightbulb

    Quote Originally Posted by PAVB View Post
    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

    Code:
    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:

    Code:
    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

  4. #4
    Join Date
    Jul 2009
    Posts
    9
    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

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    Grant read permission to the directory you created. Then check

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    like this: grant read on directory emp_dir to user
    Last edited by abhi_oracle; 07-26-2009 at 02:47 PM.

  7. #7
    Join Date
    Jul 2009
    Posts
    9
    Quote Originally Posted by abhi_oracle View Post
    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

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    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

  9. #9
    Join Date
    Jul 2009
    Posts
    9
    Quote Originally Posted by abhi_oracle View Post
    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?
    Last edited by peace2009; 07-26-2009 at 03:50 PM.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by peace2009 View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width