Oracle foreign data wrapper

Question

I have master-slave architecture. On the slave I have an Oracle database with two schemas, e.g. TEST1 and TEST2.

I have all objects (e.g. EMPLOYEES) stored in schema TEST1, and user TEST1 (or admin) has given read only privileges on TEST1.EMPLOYEES to TEST2, so when I use TEST1.EMPLOYEES in a query on the Oracle database I can access its data.

How can I implement the same using Oracle foreign data wrapper in postgres 9.5 because I have credentials for TEST2 and not TEST1? When I try to access the foreign table it give an error saying that TEST2.EMPLOYEES does not exist.

Laurenz Albe · Accepted Answer · 2017-08-10 18:54:52Z

1

You can easily do that if you define the user mapping with the credentials of user TEST2 and the foreign table with the schema option, i.e.

CREATE FOREIGN TABLE ... OPTIONS (schema 'TEST1', table 'EMPLOYEES');

answered Aug 10, 2017 at 18:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mahesh Hadawale Over a year ago

Your answer helped me... My issue resolved.. Thx a lot

wildplasser Over a year ago

@LaurenzAlbe : in postgres-9.4, these options were named schema_name and table_name (I don't know if these keywords have been renamed inlater versions)

Laurenz Albe Over a year ago

@wildplasser: You are mistaken. The names of the options are not ordained by PostgreSQL, they are defined by the foreign data wrapper. The main reason why names in oracle_fdw and postgres_fdw differ is that I wrote oracle_fdw first, so I couldn't copy behaviour...

Collectives™ on Stack Overflow

Oracle foreign data wrapper

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related