Oracle 11g – Remove Global Name from DB Links

dblinkfunctionsoracle-11g-r2remote

I am trying to call remote function via DB LINKS.

However, My global name contains the string ON and I believe it causes an error.

The Error I have is …

SQL> var tmp varchar2(2);
SQL> execute :tmp := IF_TRX_SKY.CREATEWITH@SKYLBS_LINK2.I-ON.NET('a', 1, 2, 't', 't', 'k', 'm', 'r', 'u', 's', 's', 't', 'k', '00', sysdate, 1);
BEGIN :tmp := IF_TRX_SKY.CREATEWITH@SKYLBS_LINK2.I-ON.NET('a', 1, 2, 't', 't', 'k', 'm', 'r', 'u', 's', 's', 't', 'k', '00', sysdate, 1); END;

                                                   *
ERROR at line 1:
ORA-06550: line 1, column 52:
PLS-00103: Encountered the symbol "ON" when expecting one of the following:
( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current max min prior sql stddev sum
variance execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted SQ

I assume that if I removed .I-ON.NET part, it would work.

How can I remove/escape this? Oracle documentation states that GLOBAL_NAME is required for replication. Then how can I call this remote function without removing the GLOBAL_NAME?

Here is my create statement to create DB LINKS

SQL> CREATE PUBLIC DATABASE LINK SKYLBS_LINK2                     
  2  CONNECT TO ICE
  3  IDENTIFIED BY ICE1234
  4  USING 'ORCL';

Database link created.

And this is setting what I have

NAME                             TYPE        VALUE                 
---------------------------- ----------- ----------------
global_names                    boolean      FALSE

FYI, I can not change config files or reboot database, since I don't have permissions.

Summary:

  • How to remove GLOBAL_NAME from DB LINKS which I never write in my create statement.
  • How to execute remote function which contains a forbidden string

Best Answer

To remove DB_DOMAIN from DB_LINKS.

ALTER SYSTEM SET global_names = false SCOPE = SPFILE;
ALTER SYSTEM SET db_domain = '' SCOPE = SPFILE;
UPDATE GLOBAL_NAME SET GLOBAL_NAME = [Name without db_domain];

then restart, DB and Listener.

However, I still do not know how to use this with domain.