Is there support for Oracle External tables?

[rdnkrkmz]

When we tried to retrieve external table from Oracle, we failed. Is not there support Oracle External tables?

Thanks Much

[laurenz]

There is no specific support for Oracle external tables, but I didn't expect that anything special would be necessary.

Perhaps you had best describe what exactly you did (the SQL statements) and what response you got (including the complete error messages, if there are any).

[rdnkrkmz]

I will provide test case, and logs in a few days, sorry.

[philflorent]

Hi,

It should work "out of the box" since oracle-fdw natively has the capabilities an OCI program linked with Oracle Instant Client libraries has. And in fact it works, test case :

-- oracle side

CREATE OR REPLACE DIRECTORY tmp AS '/tmp';

CREATE TABLE exttable(

  exttable_id      VARCHAR2(5),

  exttable_val      VARCHAR2(50)

)

ORGANIZATION EXTERNAL (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY tmp

  ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    (

      exttable_id      CHAR(5),

      exttable_val      CHAR(50)

    )

  )

  LOCATION ('exttable1.txt')

)

REJECT LIMIT UNLIMITED;

SQL> !cat /tmp/exttable1.txt

00000,OK

SQL> select * from exttable;

EXTTA EXTTABLE_VAL

----- --------------------------------------------------

00000 OK

-- pg side

select oracle_diag();

                                                         oracle_diag

-----------------------------------------------------------------------------------------------------------------------------

oracle_fdw 2.6.0, PostgreSQL 15.3 (Debian 15.3-0+deb12u1), Oracle client 19.21.0.0.0, ORACLE_HOME=/opt/oracle/instantclient

(1 ligne)

import foreign schema "XXXXXXX" LIMIT TO (exttable) FROM server oradb into orcl;

IMPORT FOREIGN SCHEMA

table orcl.exttable;

exttable_id | exttable_val

-------------+--------------

00000       | OK

(1 ligne)

Best regards, Phil

[rdnkrkmz]

What I tried in advance and did not help.

1. create viewfor external table on oracle side and put its name in PostgreSQL FDW TABLE_NAME option.
2. disable parallel on external table.

I can send you any debug/trace if you want.

Diag Info psql> select sch_oracle_fdw.oracle_diag(); oracle_fdw 2.6.0, PostgreSQL 14.7, Oracle client 21.11.0.0.0, ORACLE_HOME=/usr/lib/oracle/21/client64

==================================== on Oracle side ====================================

create EXTERNAL table

CREATE TABLE HR.EX_TABLE
ORGANIZATION EXTERNAL
    (TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY ASMDIR -- like +DATA/ext_tables
     ACCESS PARAMETERS (LOGFILE LINUXFSDIR : tbl_ext) -- like /tmp/
     LOCATION ('tblext.01.dmp',
               'tblext.02.dmp',
               'tblext.03.dmp',
               'tblext.04.dmp',
               'tblext.05.dmp',
               'tblext.06.dmp',
               'tblext.07.dmp',
               'tblext.08.dmp',
               'tblext.09.dmp',
               'tblext.10.dmp',
               'tblext.11.dmp',
               'tblext.12.dmp',
               'tblext.13.dmp',
               'tblext.14.dmp',
               'tblext.15.dmp',
               'tblext.16.dmp',
               'tblext.17.dmp',
               'tblext.18.dmp',
               'tblext.19.dmp',
               'tblext.20.dmp',
               'tblext.21.dmp',
               'tblext.22.dmp',
               'tblext.23.dmp',
               'tblext.24.dmp',
               'tblext.25.dmp',
               'tblext.26.dmp',
               'tblext.27.dmp',
               'tblext.28.dmp',
               'tblext.29.dmp',
               'tblext.30.dmp',
               'tblext.31.dmp',
               'tblext.32.dmp'))
    PARALLEL 32
AS
    SELECT *
      FROM HR.NORMAL_TABLE AS OF SCN 1500000000000;

we can see data on Oracle SELECT * FROM HR.EX_TABLE;

==================================== Jump to PostgreSQL ====================================

create FOREIGN table in PostgreSQL

CREATE FOREIGN TABLE hr.f_ex_table(
    column01 numeric NULL,
    column02 numeric NULL,
    column03 numeric NULL,
    column04 numeric NULL,
    column05 numeric NULL,
    column06 character varying(10) NULL COLLATE pg_catalog."default",
    column07 character varying(1) NULL COLLATE pg_catalog."default",
    column08 numeric NULL,
    column09 numeric NULL,
    column10 timestamp without time zone NULL,
    column11 timestamp without time zone NULL,
    column12 numeric NULL,
    column13 numeric NULL,
    column14 character varying(50) NULL COLLATE pg_catalog."default",
    column15 character varying(7) NULL COLLATE pg_catalog."default",
    column16 numeric NULL,
    column17 numeric NULL,
    column18 character varying(1) NULL COLLATE pg_catalog."default",
    column19 timestamp without time zone NULL,
    column20 character varying(1) NULL COLLATE pg_catalog."default",
    column21 character varying(300) NULL COLLATE pg_catalog."default",
    column22 timestamp without time zone NULL
)
    SERVER proddb
    OPTIONS (schema 'HR', table 'EX_TABLE');

select data on PostgreSQL select* from hr.f_ex_table limit 10;

ERROR:  ORA-06564: object ASMDIR does not existerror describing remote table: OCIStmtExecute failed to describe table 

ERROR:  error describing remote table: OCIStmtExecute failed to describe table
SQL state: HV00M
Detail: ORA-06564: object ASMDIR does not exist

[philflorent]

Hi, As you can see in my previous example, it can work. It's not directly an oracle-fdw issue since you obtain an Oracle error, ORA-6564, and oracle-fdw reports it. Seems the connection user does not have the necessary privileges on the asmdir directory. Is your connection user the owner of the table and, if not, does he have the select privilege and the necessary privileges on the directory ? A "grant select" is not enough, direct privileges on the directory are mandatory. Can you test you can remotely select from this table from another Oracle database via database link ? If not, oracle-fdw won't do better, e.g https://stackoverflow.com/questions/40800551/external-table-via-dblink Best regards, Phil

[laurenz]

I don't have the faintest clue. Is there an object named ASMDIR in the Oracle database? That looks like a bug or limitation in Oracle. You could ask Oracle support why an OCIStmtExecute() call with OCI_DESCRIBE_ONLY as the last argument (mode) causes that error on an external table.

[philflorent]

It can be an Oracle bug, some very old versions have bugs with directories. But the error message can be accurate : perhaps the connection user does not have the necessary privileges on the directory and cannot see it.

I am unable to reproduce the issue if my connection user has the required privileges, whatever the access method in use (oracle_loader or datapump)

But if I revoke read/write on the directory object at Oracle side (11.2.0.4) I obtain the ORA-06564 error:

table orcl2.ext_pa_pat; ERREUR: error describing remote table: OCIStmtExecute failed to describe table DÉTAIL : ORA-06564: objet TMP inexistant

The error is not oracle_fdw specific, it's easy to reproduce the issue with an Oracle database link. We lack crucial information on the user that is used for the user mapping and its read/write privileges on asmdir directory. It's strange but "read" is not enough even for a select, you obtain an ORA-29400/KUP-04074 if "write" is missing. Here it seems read and writes privileges are missing though.

[rdnkrkmz]

Hi @philflorent @laurenz, I am sorry I took your time because of Oracle's unclear error messages.

Like @philflorent said, it is a permission issue with fdw_user and directories used. So here is process of minimal grant to things work.

Initial error

ERROR:  ORA-06564: object ASMDIR does not existerror describing remote table: OCIStmtExecute failed to describe table 

ERROR:  error describing remote table: OCIStmtExecute failed to describe table
SQL state: HV00M
Detail: ORA-06564: object ASMDIR does not exist

After granting READ on ASMDIR to fdw_user grant read on directory ASMDIR to fdw_user;

ERROR:  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object LINUXFSDIR not founderror executing query: OCIStmtExecute failed to execute remote query 

ERROR:  error executing query: OCIStmtExecute failed to execute remote query
SQL state: HV00L
Detail: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object LINUXFSDIR not found

Yet it is not clear that this is a permission error. After granting READon log directory to fdw_user, picture is clear now. Obviously seems a permission error. Oracle finally says that :( grant read on directory LINUXFSDIR to fdw_user;

ERROR:  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04074: no write access to directory object LINUXFSDIRerror executing query: OCIStmtExecute failed to execute remote query 

ERROR:  error executing query: OCIStmtExecute failed to execute remote query
SQL state: HV00L
Detail: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04074: no write access to directory object LINUXFSDIR

After granting WRITEon log directory to fdw_user, works perfect. grant WRITE on directory LINUXFSDIR to fdw_user;

[philflorent]

Hi, Thanks for your feedback. I agree error messages are strange and inconsistent in this case. It's also strange to need a "write" privilege to read something. That said, being able to read directly an Oracle dumpfile located in a secure directory on the Oracle server from PostgreSQL via oracle_fdw is really something impressive. At some point, PostgreSQL had dump_fdw for that but sadly it's not maintained anymore.
Best regards, Phil

[laurenz]

Yes, thanks for the detailed account. That is going to help others with the same problem.