Issue with Identifiers in Postgres greater than Oracle's limit while connecting over ODBC (DBLINK)

Question

We have a database link from Oracle 12.1 to Postgres via psqlODBC Driver. There is a problem while running few queries/statements when the Postgres identifiers, like the length of a column name exceeds 30 characters, which is the maximum limit of column names until Oracle 12.1.

ORA-04052: error occurred when looking up remote object myschema.mytesttable@ORA_TO_PGLINK ORA-01948: identifier's name length (32) exceeds maximum (30) ORA-06512: at line 13 04052. 00000 - "error occurred when looking up remote object %s%s%s%s%s" *Cause: An error occurred when trying to look up a remote object. *Action: Fix the error. Make sure the remote database system has run CATRPC.SQL to create necessary views used for querying or looking up objects stored in the database.

This error occurs even if the statement does not refer the column explicitly. The statement we use basically updates another column which has a normal length(<= 30 characters) through an update statement over PL/SQL.

BEGIN
  UPDATE "mytesttable"@ORA_TO_PGLINK
     SET "col" = 10
   WHERE "col" = 1;
END;

Here the col is not the one whose length is greater than 30 characters in Postgres. Also, the statement works fine while running as a normal SQL statement, ie. without an anonymous block or inside a procedure.

Now, of course we can fix this issue by renaming the column name in Postgres to have a length <= 30 characters. But, we do not want to do that because it has several other dependancies. As far as Oracle db is concerned, the update is part of a PL/SQL procedure and can't be run as a single statement directly. What is the solution for this? Is there a workaround or some settings of the driver be modified?

Answer 1

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.

dbfiddle

Answer 2

You should write a procedure on the Postgres database which takes as the parameters the column and row you want to change and call that from the Oracle database.

On the Oracle side

IF vOperation = 'UPDATE'
THEN
UPDATE_MYTESTTABLE(10,1)@ORA_TO_PGLINK;
END IF;

On the Postgres side something like

PROCEDURE UPDATE_MYTESTTABLE(col1_in NUMBER, col2_in NUMBER) IS
BEGIN
UPDATE mytesttable
     SET "col" = col1_in
   WHERE "col" = col2_in;
END UPDATE_MYTESTTABLE;

A quick look shows me that you should check the Postgres syntax, it's not quite PL/SQL.