Problems with Synonyms

[CD12]

Hi,
I have a table on a different database than my main database and via a database link and synonym I can access it from my main database in sqlplus, by e.g.

SELECT count(*) FROM table_on_secondary_db;

As soon as I try to access it within a Begin - End block, like:

DECLARE
  counter   NUMBER;
BEGIN
  SELECT count(*)   INTO counter
  FROM table_on_secondary_db;
END;

I receive the error:
ORA-02083: database name has illegal character '-'

On both databases I have the same user, what is wrong, why doesn't it work?

Looking for help
CD12

 

[karluk]

According to the documentation, ORA-02083 indicates that GLOBAL_NAMES contains an invalid database name. In this case the bad character is a hyphen. Underscores are allowed, but not hyphens.

You should verify this by querying global_names. According to Metalink, the only way to fix this error is by updating global_names with a command like

UPDATE GLOBAL_NAME
SET GLOBAL_NAME = '<A VALID GLOBAL_NAME>';

I don't know your query works in SQL*Plus, but not in PL/SQL.

 

[CD12]

Thank you karluk,
your information brought me on the right track, now it is working.
CD12