Question. Access view from Oracle DB in PostgreSQL and Insert into table in Oracle DB from PostgreSQL

Question

For a long time I have been working only with Oracle Databases and I haven't had much contact with PostgreSQL.

So now, I have a few questions for people who are closer to Postgres.

1. Is it possible to create a connection from Postgres to Oracle (oracle_fdw?) and perform selects on views in a different schema than the one you connected to?
2. Is it possible to create a connection from Postgres to Oracle (oracle_fdw?) and perform inserts on tables in the same schema as the one you connected to?

Answer 1

Ad 1:

Yes, certainly. Just define the foreign table as

CREATE FOREIGN TABLE view_1_r (...) SERVER ...
   OPTIONS (table 'VIEW_1', schema 'USERB');

Ad 2:

Yes, certainly. Just define a foreign table on the Oracle table and insert into it. Note that bulk inserts work, but won't perform well, since there will be a round trip between PostgreSQL and Oracle for each row inserted.

---

Both questions indicate a general confusion between a) the Oracle user that you use to establish the connection and b) the schema of the table or view that you want to access. These things are independent: The latter is determined by the schema option of the foreign table definition, while the former is determined by the user mapping.