Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Cannot access an oracle external table. Please help.

    I have a windows environment (windows 2003 but I tried in windows 2000 too).
    I need to create some external tables for some simple csv files.
    I create the directory, I create the table but I cannot access the table. I believe there are some problems with the directpry path because the message indicates that the csv file is not recognized as being in the specified directory.
    I met the problem in the past (about 2 years ago) and I solved it by mapping the drive. Unfortunately now itr does not work anymore. I will show you the problem below. If you know what is about please help me.

    George_Assets.csv file contain a simp,le record with 4 filelds (see below). bingHP-UXB.10.20A,05A88985,BING,J01700012-BING

    1) Firstly I create the directory (being connected with sys):
    CREATE OR REPLACE DIRECTORY IntegrationTest AS 'E:\Projects\Keystone\IntegrationTest\EXTERNAL_DAT A_SOURCES'

    2) Secondly I grant rights to the user Custom_User
    GRANT READ, WRITE ON DIRECTORY IntegrationTest TO Custom_User

    3) After that, I connect to the Custom_User:
    CONNECT Custom_User/Custom_Password@Instance_db

    4) Being connected with the custom user I create the table:
    CREATE TABLE George_Assets (ASSET_DESCRIPTION CHAR(100),
    SERIAL_NUMBER CHAR(50),
    SYSTEM_DOMAIN_NAME CHAR(50),
    JTAG_DOMAIN CHAR (50))
    ORGANIZATION EXTERNAL
    (TYPE oracle_loader DEFAULT DIRECTORY IntegrationTest
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE
    badfile IntegrationTest: 'George_Assets.bad'
    logfile IntegrationTest: 'George_Assets.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (ASSET_DESCRIPTION,SERIAL_NUMBER,SYSTEM_DOMAIN_NAM E,JTAG_DOMAIN)
    )
    LOCATION ('George_Assets.csv')
    );

    I also try with Location IntegrationTest: 'George_Assets.csv'.

    5) Finally, I try to access the table
    select * from George_Assets

    All the possible combination ended the same with the following errors:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP_04063: unable to open log file George_Assets.log
    OS error the system cannot find the path specified
    ORA-06512: at "SYS.ORACLE_LOADER", line 19

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The external table must reside on the database server, they are not allowed to be on a client machine.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    thanks

    Quote Originally Posted by beilstwh
    The external table must reside on the database server, they are not allowed to be on a client machine.
    Thank you very much.
    This was the problem indeed.
    The external files must reside on the computer where the database server is.

Posting Permissions

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