Using the dblink extension, this is possible.
Just create a connection inside your function, supplying the database name:
SELECT dblink_connect('conn', format('hostaddr=127.0.0.1 port=5435 dbname=%I user=test password=test', 'foreign_test'));
SELECT dblink_exec('conn', 'CREATE TABLE t (id integer)');
Note that using the ...
I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there.
If you ...
Basically, dblink uses a libpq-style connection info string.
Add sslmode=require to your connection string or a more restrictive setting to disallow unencrypted transfers. More details in the manual here.
I would also advice not to have the password in your SQL statement at all. SQL statements may get logged. Or backups are stored in an insecure location ......
As you have nothing dynamically built in your query, you don't have to use EXECUTE at all.
Inside the function body, you can simply do an INSERT if temp_table already exists:
INSERT INTO temp_table (fielda, fieldb, fielbc)
SELECT a, b, c
FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw',
E'SELECT fielda, fieldb, ...
postgres_fdw is definitely the better way. As you have read-only access to the production database, you can setup a FDW on your test (secondary) database provided you have superuser access to that (secondary) server. Or at least someone who can install the extension and setup the foreign server. Once those two things are there, you can proceed with a regular ...
Depending on the DB2 platform, it may indeed be possible to call an Oracle (or other database) stored procedure from a DB2 stored procedure. With DB2 for Linux, Unix, and Windows the process of setting up communication with a foreign data source is called federation. You can read more about this in the manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/...
Here is what I think is happening. I don't think it is a bug.
You have a view. That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)
It's not necessarily the number of transactions, but the timeout. The parameter distrubuted_lock_timeout is set to 60 seconds by default. The purpose of this parameter is to avoid having distributed transactions in a long running wait status while something else is performing work on that row; the transaction will wait 60 seconds, then Oracle kills it.
What you described here is absolutely normal and intended. This feature is called a global database link:
It is a "side effect" when for example Oracle Internet Directory is in use, I have seen this at a few companies who have a lot of databases.
More and better ...
Community Wiki answer collecting answers left in question comments
The ODBC driver needs to be on the computer that connects to the Oracle server not on the Oracle server itself. Plus: you can't use dblink for this, Postgres' DBLink can only connect to other Postgres servers. But you can use a foreign data wrapper. You only need the ...
What about remote database side, what does it have to do?
There must have Oracle Net installed on both local and remote database servers.
And the user being used should have CREATE SESSION system privilege on the remote database.
On fixed user link, local database need to know ID and password set. Is the ID is a usual account with which someone can ...
Answer provided by John Eisbrener in a comment
The effective equivalent is a Database Link. Sadly there's not an identical equivalent to what OPENROWSET does in one Oracle command or statement. You have to declare the db link first before accessing it. If you don't want to edit a tnsnames.ora file, you can create the dblink using an in-line statement, ...
No. You'll need to create a new private database link. Public database links (and synonyms) are owned by a different user than private database links (and synonyms) and you can't alter an object to change the owner.
Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.
You cannot create Materialized View from Synonym. I don't know why would you like to that but we create materialized ...
That's exactly what foreign data wrappers and foreign tables were created for:
You create a foreign server pointing to the source server, and then a foreign table that enables access to that remote server.
Some examples for that are in the description of the Postgres foreign data wrapper:
The solution that worked for me was to use Advanced Security, with netmgr or directly edit the sqlnet.ora by adding the parameters:
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
Once this is done it is necessary to restart the listener, for ...
To understand what's going on, first check what is being done on the remote:
This does return a record, not two integers. When the dblink() call reports
ERROR: remote query result rowtype does not match the specified FROM clause rowtype
it's because locally you defined the row type as x(a int, b int), but that ...
Creating a view in the Postgres database that assigns shorter names to the columns in question might be an option. It will not affect dependencies on the Postgres side while providing comfortably short column names to Oracle.
Your real problem is not about storing the password in a table securely, but the need for repeatedly changing and recreating the dblink.
Yes, you can store a password in a wallet, and no, you will not be able to retrieve the actual password from the wallet.
To store a password securely in a table, you can encrypt it (e.g. with DBMS_CRYPTO) and store the ...
I do not think that is possible.
Someone or something needs access to the Filesystem to read the tnsnames.ora.
When using the DB-link oracle will get the definition for RDBX from the tnsnames.ora file. This is transparent to the user, so the user will not notice. As @EdStevens suggested I would try to solve the political issues and work with the DBA.
You can configure a timeout for a query to a remote server. The documentation is here
( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 )
You can change this on a database with this script which sets a 100 second timeout
USE AdventureWorks2012 ;
What needs to be done depends on where the connection initiative is done. If the initiative is done from the Oracle side, you could use dg4odbc and configure it with the odbc driver and connection details for the Postgress database.
dg4odbc sets up a special kind of listener process that you refer to using a regular tns alias. This listener process has to ...
The comment from @a_horse_with_no_name helped me solve the issue i.e., the extension was not installed in the right schema when attempting to restore the DB. When moved to the right schema the restore finished with no errors.
I now see that Balasz has provided a solution, too. My solution works if the name of the database is FooB, and I see now that that isn't the case here. So Balas will be right. But I leave this post because it seems not to be widely known that this works.
From Administrator's Guide, 31 Distributed Database Concepts,
Global Name as a Loopback Database ...
Oracle has spoken:
Distributed Query Returns Different Results on Consecutive Runs when
no Changes Exist in the Base Tables. (Doc ID 561471.1)
...skip some more writing...
Bug.611416 SELECT AFTER UPDATE AND COMMIT DOESN'T SHOW UPDATED COLUMNS
...skip some more writing...