The use of private synonyms is considered a feature by most. 8) That way the DBA can create specific synonyms for a user account that will only be used by BusinessObjects. Designer will work faster, as there will be fewer tables / views included in the list of possible universe elements.
Even if it doesn’t currently work with public synonyms, you can adjust the strategy files so that it will. For example, the following code is found in the stora7en.txt file in the Oracle directory:
[STRATEGY]
TYPE=OBJECT
NAME= External Strategy: Classes and Objects
[SQL]
SQL=SELECT
U1.table_name,'|',
U1.column_name,'|',
translate(initcap(U1.table_name),'_',' '),'|',
translate(initcap(U1.column_name),'_',' '),'|',
U1.table_name||'.'||U1.column_name,'|',
' ','|',
decode(SUBSTR(U1.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|',
SUBSTR(U2.comments,1,474),'|',
'O','|'
FROM USER_TAB_COLUMNS U1,USER_COL_COMMENTS U2
WHERE
U1.table_name=U2.table_name
and U1.column_name=U2.column_name
UNION
SELECT
S.SYNONYM_NAME,'|',
U1.column_name,'|',
translate(initcap(S.SYNONYM_NAME),'_',' '),'|',
translate(initcap(U1.column_name),'_',' '),'|',
S.SYNONYM_NAME||'.'||U1.column_name,'|',
' ','|',
decode(SUBSTR(U1.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|',
SUBSTR(U2.comments,1,474),'|',
'O','|'
FROM ALL_TAB_COLUMNS U1, ALL_COL_COMMENTS U2, ALL_OBJECTS O, USER_SYNONYMS S
WHERE
S.table_owner=O.owner
AND S.table_name=O.object_name
AND (O.OBJECT_TYPE='TABLE' OR O.OBJECT_TYPE='VIEW')
AND O.owner=U1.owner
AND O.object_name=U1.table_name
AND U1.owner=U2.owner
AND U1.table_name=U2.table_name
AND U1.column_name=U2.column_name
;
The first portion of the query finds all user owned objects (private tables and views) and the next obtains a list of user synonym references. Simply change USER_SYNONYMS to ALL_SYNONYMS and you will get every synonym created that the particular user has access to. It’s not that difficult, and this change only needs to be made on Designer computers. I don’t believe it has to be rolled out to user computers as the structure is, by that time, contained within the universe.
Dave Rathbun
(BOB member since 2002-06-06)