I have made a change to my underlying Oracle DB to swap a view for a synonym (for performance reasons). The synonym has the same name as the view had. When I refresh the LLBL Project I get a message stating the the entity mapping has been changed to the renamed view.

I have two questions: 1. Can I change that mapping to the new synonym without deleting and re-adding the entity (which would result in a loss of my custom relationships, etc.)? 2. Where does the synonym appear in the catalog? I retrieved Tables, Views, SPs, and Custom Properties but I can't see the synonym anywhere.

Synonyms are resolved to the table /view it represents. If that table or view is in a reachable schema, i.e. in the schema set you included in your project, the table or view the synonym represents is used, otherwise a table/view is added as if it is located in the schema, but in fact you reach it through the synonym.

Synonyms of elements in the same schema is not effective. I understand from your text that you added a synonym of a view to the same schema?

Otis wrote:

Synonyms of elements in the same schema is not effective. I understand from your text that you added a synonym of a view to the same schema?

The original view was to a table in a different schema on another server, and there were issues with indexing (hence the change). The new synonym is for that same table which is in another schema, and on another server; so, based on what you say, I should see my synonym as if it were a table?

ChicagoKiwi wrote:

Otis wrote:

Synonyms of elements in the same schema is not effective. I understand from your text that you added a synonym of a view to the same schema?

The original view was to a table in a different schema on another server, and there were issues with indexing (hence the change). The new synonym is for that same table which is in another schema, and on another server; so, based on what you say, I should see my synonym as if it were a table?

Yes. In my test project I have a synonym in HR to SCOTT.EMP. When I create a project for the HR schema I get an 'EMP' table, as if it appears in HR.

Ok. I'll talk to the DBA about what he may need to do to expose the synonym further (I presume it's a rights issue). Any thoughts on my first question of how to move which catalog item my entity is mapped to?

ChicagoKiwi wrote:

Ok. I'll talk to the DBA about what he may need to do to expose the synonym further (I presume it's a rights issue). Any thoughts on my first question of how to move which catalog item my entity is mapped to?

That's not possible at the moment in the designer. You can do that in a plugin though, if you really need to. I think you can also do a trick (of course by keeping backups of projects after refresh)

• make the view return a completely different resultset
• refresh
• the entity now is mapped to the view/table of the synonym
• restore the original query in the view
• refresh again. It now should be ok.

My DBA is a little mystified as to why I can't see the synonym. He's tried creating a public synonym as well as a private one to no avail.

The details of the synonym are: private synonym on ServerA in SchemaA pointing via a link to a view on ServerB in SchemaA (the schemas on the two servers have identical names, but are obviously different schemas, in different Oracle instances); the view on ServerB/SchemaA points to another synonym on ServerB in SchemaB which in turn points to a view on ServerB/SchemaB. To confuse matters further the view on ServerB/SchemaA has the same name and result set as the original view the synonym replaces on ServerA/SchemaA

Could you give me some more details on how LLBLGen Pro determines that available synonyms and perhaps I can work out from that why it's not appearing?

ChicagoKiwi wrote:

My DBA is a little mystified as to why I can't see the synonym. He's tried creating a public synonym as well as a private one to no avail.

The details of the synonym are: private synonym on ServerA in SchemaA pointing via a link to a view on ServerB in SchemaA (the schemas on the two servers have identical names, but are obviously different schemas, in different Oracle instances); the view on ServerB/SchemaA points to another synonym on ServerB in SchemaB which in turn points to a view on ServerB/SchemaB. To confuse matters further the view on ServerB/SchemaA has the same name and result set as the original view the synonym replaces on ServerA/SchemaA

Could you give me some more details on how LLBLGen Pro determines that available synonyms and perhaps I can work out from that why it's not appearing?

Get all table synonyms:

SELECT * FROM ALL_SYNONYMS 
WHERE
TABLE_NAME IN
(
    SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=ALL_SYNONYMS.TABLE_OWNER AND 
        TABLE_NAME=ALL_SYNONYMS.TABLE_NAME
)
AND OWNER='<currentschema>'

'<currentschema>' is for example 'SCOTT'.

get all view synonyms:

 SELECT * FROM ALL_SYNONYMS 
 WHERE
 TABLE_NAME IN
 (
        SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER=ALL_SYNONYMS.TABLE_OWNER AND 
        VIEW_NAME=ALL_SYNONYMS.TABLE_NAME
)
AND OWNER='<currentschema>'

It then retrieves the fields for these retrieved synonyms, using 'TABLE_OWNER' and 'TABLE_NAME' to find the original table/view

Per found table /view synonym, this is executed:

 SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT
 FROM       ALL_TAB_COLUMNS WHERE OWNER=:schemaOwner AND TABLE_NAME = :tableName
 ORDER BY TABLE_NAME ASC, COLUMN_ID ASC

':schemaOwner' is a parameter, and for example 'SCOTT', and :tableName for example 'EMP'

If no rows are returned, the synonym is skipped. Then, all populated syn.view/table object is moved to table/view stores as normal table/view.

Thanks for the detailed information. Because the view that my synonym is based on is not available in all_views (it's only accessible via a link to another Oracle instance) the synonym was not returned. By creating a view in my schema of the same name, and having the same result set as the remote view I was able to get the synonym to appear.

Cool!