oracle public synonym

building semantic layer
1

Hi Bob’ers,

I did a search on the list and read somewhere that BO (version 5.1) doesn’t support oracle public synonyms. I have the following questions regarding this

  • is there absolutely no work around for this, because my dba is pretty pissed :reallymad: .

  • how about version 6, does it support the public synonyms?

kind regards

Sven

blackrooster :belgium: (BOB member since 2003-08-25)

2

Have you tried it? In the past I had problems but I think they’re OK now. I’m 5.1.4.

Cindy Clayton :us: (BOB member since 2002-06-11)

3

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 :us: (BOB member since 2002-06-06)

4
  1. Open ora7EN.prm
  2. C/OWNER=Y/OWNER=N/
  3. Save

You are now ready to use public synonyms in Designer

Dennis W. Disney :us: (BOB member since 2003-09-17)

5

I should be clearer.

Change

OWNER=Y

to

OWNER=N

Dennis W. Disney :us: (BOB member since 2003-09-17)

6

Thnks for the help!!!

Sven

blackrooster :belgium: (BOB member since 2003-08-25)