ORA-02083:database name has illegal character '.'

Recently I was asked for help in an issue which is related to ASCP.

In OLTP instance, from OM Order Administrator Primary responsibility
1.Open ATP Inquiry Form
2.Give the Item name and Qty
3.click on the Global Availability Button.
4.Following Error message appears and user is unable to view Global Availability:

ORA-02083:database name has illegal character '.'
ORA-06512:at "SYS.DBMS_SYS_SQL", line 909
ORA-06512:at "SYS.DBMS_SQL", line 39
ORA-06512:at "APPS.MSC_SCH_WB", line 842

Official documentation states the cause of ORA-02083 as:

The database name supplied contains an invalid character. For example, there can be no periods, semicolons, single quotes, double quotes, blanks, or non-printable characters in the database name.


First I checked for the global name of OLTP and ASCP instances:
OLTP:
SQL> select * from global_name;
oltp11i.production.example.com

The global name still had the domain for production. So I changed it to:

SQL> alter database rename global name to oltp11i.development.example.com;
Database Altered
SQL> select * from global_name;
oltp11i.development.example.com

ASCP:
SQL>select * from global_name;
ascp11i

If you query dba_db_links table, you'll find that it automatically attaches the domain name derived from global name of the instance.

I dropped and recreated the ASCP hookup DB links without domain name.
ASCP Instance:
SQL> drop database link ASCP2OLTP.DEVELOPMENT.EXAMPLE.COM;
SQL> create database link ASCP2OLTP connect to apps identified by apps using 'oltp11i';

OLTP Instance:
SQL>drop database link OLTP2ASCP.DEVELOPMENT.EXAMPLE.COM;
SQL> create database link OLTP2ASCP connect to apps identified by apps using 'ascp11i';

I checked for the DB link names in MRP_AP_APPS_INSTANCES:

SQL> select M2A_DBLINK,A2M_DBLINK from mrp_ap_apps_instances
2 /

M2A_DBLINK A2M_DBLINK
--------------------------------- --------------------------------
ASCP2OLTP.DEVELOPMENT.EXAMPLE.COM OLTP2ASCP.DEVELOPMENT.EXAMPLE.COM

The DB links had been specified with domain name. I removed the domain names altogether, as the '.' (period) is not allowed in the base db name by the following statement.

SQL> update mrp_ap_apps_instances
2 set m2a_dblink='ASCP2OLTP',A2M_DBLINK='OLTP2ASCP';
1 row updated.

SQL> COMMIT;
Commit complete.

SQL> select M2A_DBLINK,A2M_DBLINK from mrp_ap_apps_instances
2 /

M2A_DBLINK A2M_DBLINK
--------------------------- -----------------------
ASCP2OLTP OLTP2ASCP

After these changes the error changed to this:

ORA-02019: connection description for remote database not found
ORA-06512: at "APPS.MSC_ATP_PROC", line 3519
ORA-06512: at line 1
ORA-06512: at "APPS.MSC_ATPSOURCES_NEW", line 53
ORA-06512: at "APPS.MSC_SCH_WB", line 660
ORA-06512: at "APPS.ORDER_SCH_WB", line 54

------------------------------------------------
FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-02019.

Tracing the error from forms side or the session from DB side is not logging this error. Still investigating this one.

Akhilesh solved this one for me. This is what he did:

On OLTP instance:

Checked the value of profile option MRP:ATP Database Link and it was oltp2ascp.development.example.com. So we removed the domain name and made it oltp2ascp

On ASCP Instance:

Logged in with Advanced Planning Administrator responsibility
Admin > Instances

The DB link values in the fields "From Source to APS" and "From APS to Source" had the dblink with domain name. Removed the domain name. Saved.

Voila, the issue is solved. Error message no longer appears and Global Availability form opens on clicking the button.