Synonym doesn't appear in the table liste

building semantic layer
1

Hello,

I’m creating a new universe with BO XI 3 , database Oracle 9 .

This data base has a huge number of tables, so it’s impossible to display them all in the table list. So , I created an external strategy to display only the one I need, ie only the one begining by FIN…

Then, I need another table, but only reachable by the sysdba. So, in order to reach it from my user , I created a synonym. This sounds ok because I try to reach it from and it’s working.

So I changed the SQL code in my external strategy , in order to display it in the table list. But I see nothing. I tried a workaround I ve seen here which to create a derivated table (select * from my_synonym) but this doesn’t work .

Does someone has an idea of the origin ? Bad connexion with the database? PRM problem? access right in the oracle database?

Thanks in advance

pog :switzerland: (BOB member since 2007-09-27)

2

Anyone has an idea?

Here is the code, I’ve added in the oracle.stg file (the second part of the union is not working, the part with the synonym):

SELECT
			'FINPROD','|',
			'APPS','|',
			TAB.TABLE_NAME,'|',
			TAB.COLUMN_NAME,'|',
			'','|',
			'Y','|'
			FROM USER_TAB_COLUMNS TAB
			WHERE upper(TAB.TABLE_NAME) like 'FND%'
			union 
			SELECT
			'FINPROD','|',
			'SYS','|',
			S.SYNONYM_NAME,'|',
			TAB.COLUMN_NAME,'|',
			'','|',
			'Y','|'
			FROM ALL_TAB_COLUMNS TAB, USER_SYNONYMS S
			WHERE upper(S.SYNONYM_NAME) like 'BO_%'
			and S.TABLE_NAME = TAB.TABLE_NAME

pog :switzerland: (BOB member since 2007-09-27)

3

Please do not bump your post until at least one business day has passed.

Nick Daniels :uk: (BOB member since 2002-08-15)

4

ok but I add some info about the code I use in the stg file. I think this may be an interesting information for explaining my problem. This was not only “up”

pog :switzerland: (BOB member since 2007-09-27)

5

Hello,
I’m having the same issue: synonyms aren’t showing in the table list.

Were you able to resolve it? Using BO XIR3 SP3.1

Thanks.

mactech (BOB member since 2010-10-07)

6

Are they public or private? If private, try making them public.

fader53 :uk: (BOB member since 2006-06-20)

7

In my case the origin of the problem was that I was a wrong user.

Try to add some infos if you want more help

pog :switzerland: (BOB member since 2007-09-27)

8

Hello,
The oracle.prm had to have the ‘owner’ parameter set to ‘N’ in order for the synonyms to appear in the table browser.
The synonyms are public.
Now the Designer table browser is very slow to laod the tables as now it bring in all the public synonyms (ove 16,000). My contact with the DBA group doesn’t know how to restrict/limit access to the public synonyms for a specific oracle account.
any suggestions on how to limit access to the public synonyms?

thanks.

mactech (BOB member since 2010-10-07)

9

try to use a custom strategy in designer and create a restricted query of your synonyms.
so you’ll only see the synonyms returned by the query

pog :switzerland: (BOB member since 2007-09-27)

10

Unfortunatly I don’t have access (write capability) to the …\dataacess directory, which limit a lot of what I can do.

For now my option is to find a solution with the DBA.

Thanks

mactech (BOB member since 2010-10-07)