0

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?

2 Answers 2

2

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

1
  • Thanks for the suggestion. I have looked at this option.But, since we have a better control of things from Oracle side, a solution within PL/SQL or the driver settings would be highly beneficial. Moreover, a View would be required for several tables and schemas which are newly created/altered, which makes it less feasible to suit our expectation. I believe you understand. Further, it is not even the column that we are updating that has this problem Apr 25, 2019 at 17:35
0

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.

2
  • Thanks for your response. But, I'm not sure if I understand completely your solution. PL/SQL is Oracle's procedural language and the procedure i mentioned is being run from Oracle. Moreover, the Postgres procedure you posted doesn't have the right syntax. Apr 25, 2019 at 17:25
  • @KaushikNayak Instead of passing an update statement over a link, pass the values you want to change to a Postgres procedure. That can work on the table without Oracle without Oracle knowing anything about the length of the names. I don't work with Postgres so you will have to correct the syntax.
    – kevinskio
    Apr 25, 2019 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.