A public synonym has been created for a table.
If I create a Derived Table in the Universe and have the sql as:
SELECT * FROM TABLE1
then the derived table returns the column definitions.
However, if I go to Insert-Tables, I can’t find (browse) this table1. It just doesn’t appear.
If I browse to the OWNER.TABLE1 then I can find it but then if I rename this table and remove the owner name it can’t find the table.
Also, if I run SELECT * FROM TABLE1 in the oracle client it runs fine.
Your question is not clear but here is what i understood.
You have a global synonym that you are using first to create a derived table but when you fire a select * from table1(where table1 is ur derived table), then it retuns you the column definitions of that global synonym.
And when you try to Insert this global synonym to your universe schema then you can’t find it unless you add the ownername.globalsynonym.
Assuming my understanding is correct.
Could you please check the priviledges on the original table?? I guess a public synonym is given to it just keep the original table secure.
Please provide more information for your problem. so that it helps us in having a better understanding of it.
I am building a Universe and in it is a table. Up until now I’ve been using the full qualified name, ie, DW.CUSTOMERS
I have now been told that a public synonym (this is Oracle by the way) exists for this table so I shouldn’t use the fullly qualified name - I should use the synonym.
To check that I have access to the synonym, I logon to TOAD and I can run: SELECT * FROM CUSTOMERS and it returns the records correctly.
I now go into my Universe and I r-click and ‘rename table’ the table DW.CUSTOMERS and for the OWNER I remove the words DW and leave it blank. If i then refresh structure it tells me that it can’t find the table.
If I r-click and to to ‘Tables’ and I browse the owners and the tables I cannot find the synoynm CUSTOMERS in order to add it into the Universe.
However, if I - as a test - create a derived table and type SELECT * FROM CUSTOMERS then this works fine. So this leads me to believe there is some issue somewhere within the Universe but I don’t know where.
So, all I want to do is to ‘Insert - Table’ and browse to the synonym CUSTOMERS and add it in but I can’t because it’s not there.
This a bit clearer? Any ideas?
Regards the PRM file, what about it? There is no mention of owner name/qualifer name - should I add this as an entry or something?
Thanks for the description.
To see the PRM file , follow below instructions:
File --> Parameters
test the connection…
Click on DETAILS.
In the instruction you will see the path for the prm file used in the connection server folder.
Open the PRM file in NotePad.
Now check these parameter entries:
OWNER NAME
QUALIFIER NAME
and what are the values set into that(i.e. Y or N)
Now take the backup of this oracle.prm file(copy it and save in any other location) and in the original one change the OWNER parameter to N and save the file.
Close the designer and open it again and reimport the universe and then check inserting tables in the table browser.
Hello,
I followed the instructions to change Oracle.prm file and can see the public synonyms- Great!
Now the table browser takes close to 30 minutes to return any tables before it took seconds for the browser to list the tables.
Any suggestions to ensure it still take second to return the tables?
For the same account in Toad it takes not even a second to list all the public synonyms. Both are using native connectors.