create a remote external tables

Question

I have the following infrastructure:

Local machine with Windows OS, named A, with Oracle 12c installed. Remote machine with Windows OS, named B, mapped into machine A.

I am trying to create an external table on B, "AS SELECT" from an internal table from Oracle (machine A).

For this:

1. I created a directory in Oracle:

   create or replace directory external_test_dir as 'h:\external_test_dir';

2. I created an internal tabel in Oracle:

   create table TEST_INT (id number, text varchar2(100));

3. Insert values in TEST_INT table:

   insert into TEST_INT values (1, 'test 1');

   insert into TEST_INT values (2, 'test 2');

4. Try to create the external table TEST_EXT as select from TEST_INT table on a remote location.

And I receive the following error message:

Error starting at line 58 in command:
create table TEST_EXT
organization external 
  (
      type ORACLE_DATAPUMP
      default directory EXTERNAL_TEST_DIR 
      location ('TEST_EXT_FILE.csv')
   )
   parallel 2
   as
   select *
    from TEST_INT
Error at Command Line:58 Column:1
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04001: error opening file h:\external_test_dir\TEST_EXT_2480_11532.log
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.

Can anyone tell me what should I do to solve this error, please?

PS: I tried same example with an external table on same machine with DB Server and it works just fine.

Thanks,

Answer 1

You need to make sure Oracle has read/write access to EXTERNAL_TEST_DIR because it has to be able to write out a log to that same directory. Refer to this link for more details.