Support Article
Synonyms Not working for Oracle Data base.
SA-21908
Summary
The user has two different databases. The first is DB1-PegaDatabase, which has pega_data schema. The second is DB2-sample database, which has SOR schema and contains a table called sample_ClaimFile. The user created a DB link for DB1 to access tables in DB2 and then created a synonym in pega_data schema as follows:
CREATE OR REPLACE SYNONYM sample_ClaimFile FOR sample.sample_ClaimFile@DB_LinkName.
The table can be accessed with no problem if the user selects* from pega_Data.sample_ClaimFile but whenthe user tries to map the table, the pega_data schema and table name are saved as "sample_claimfile".
Pega is throwing a TestConnectivity error.
Error Messages
Connection Problem: Class Sample-FW-CLASSFW-Data-BatchSample-AAA-Claim is mapped to table PEGA_DATA.Sample_CLAIM_FILE_REPORT which contains no columns.
Steps to Reproduce
1. Have two different data base. example : "DB1"- Current Pega database and "DB2" - SOR database.
2. DB1 has the "pega_data" schema and DB2 has the "sample "schema
3. "sample" schema has a table called "sample_ClaimFile".
4. Create a DB link for the DB1 to access tables in DB2.
5. Now create a synonym in Pega_Data schema as given below.
CREATE OR REPLACE SYNONYM sample_ClaimFile FOR sample.sample_ClaimFile@DB_LinkName.
6. Now execute the below statement. It will be working fine.
select * from pega_Data.sample_ClaimFile;
7. Create a database table mapping from Pega. try to map the table in pega_data schema and table name as "sample_claimfile". When you try to do a test connectivity, this will throw an error.
Root Cause
This is an issue in the custom application code or rules. The user didn't add the connection property "includeSynonyms=true" while setting the datasource.
Resolution
Add the 'Views' as an alternate approach in place of using 'synonyms'.
Published May 8, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.