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?