0

I am trying to perform the following task:

create TABLE tpch10.ext_part
(
    p_partkey       NUMBER(10, 0),
    p_name          VARCHAR2(55),
    p_mfgr          CHAR(25),
    p_brand         CHAR(10),
    p_type          VARCHAR2(25),
    p_size          INTEGER,
    p_container     CHAR(10),
    p_retailprice   NUMBER,
    p_comment       VARCHAR2(23)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY DATA_PUMP_DIR
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('part10.tbl'));

But I get an error that there is no part10.tbl file in the directory DATA_PUMP_DIR.

Before that I performed usual operations:

CREATE USER user IDENTIFIED BY user;

GRANT CREATE SESSION,
      CREATE TABLE,
      UNLIMITED TABLESPACE
    TO user;

GRANT READ, write ON DIRECTORY DATA_PUMP_DIR TO user;
MT0
  • 143,790
  • 11
  • 59
  • 117
Maltem
  • 29
  • 8
  • 1
    Have you checked that the file is in the directory and the directory points to the correct directory? – MT0 Jan 09 '21 at 21:28
  • Yes. I did something like this CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\my_data'. The directories are the same and there are files in it. – Maltem Jan 09 '21 at 21:36
  • Please share the full error message. Please confirm the directory is on the database server and the OS user running the database service as read/write access to it. – Andrew Sayer Jan 09 '21 at 21:47
  • The user is SYSTEM itself so it does have r/w access to the folder. Here is an error: An error occurred while performing the operation: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file part.tbl in DATA_PUMP_DIR not found. Server is localhost so directory is there, yes. – Maltem Jan 09 '21 at 22:12
  • Aside from your actual error, this should not belong to SYSTEM. It should belong to your actual application schema. You assert that everything is correct, but oracle disagrees, and my money is on oracle. First thing I'd want to see is the output of 'select directory_path from dba_directories where directory_name='DATA_PUMP_DIR;' Next I'd want to see the contents of the returned directory_path, executed on the database server. – EdStevens Jan 10 '21 at 03:51
  • the mentioned query returns a given path 'C:\my_data'. Now how exactly can I check the contents of this path executed on the database server? – Maltem Jan 10 '21 at 15:05

1 Answers1

0

Solution: I moved the data to the users/public directory and it worked. I guess there was something wrong with permissions

Maltem
  • 29
  • 8