Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: Syonyms with Oracle

This question is answered.


Permlink Replies: 18 - Pages: 2 [ 1 2 | Next ] - Last Post: Oct 31, 2008 5:02 PM Last Post By: Roger Bjarevall
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Syonyms with Oracle
Posted: Feb 4, 2008 6:48 PM
 
   thread.click_reply Reply
Why can't I see synonyms for a schema in dbvisualizer?  I can see them when I log into pl/sql developer, I can select * from them, I just can't see them in the Object view.  It's a huge pain.

Anyone else experience this?
Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Feb 4, 2008 9:19 PM    global.in_response_to.tooltip in response to: Jamie McIlroy
 
   thread.click_reply Reply
Jamie,

(Assuming you are using the DbVis Free edition or the "generic" database profile).

I just tried and I see them in the object tree. If you are running DbVisualizer Personal make sure you have chosen "Oracle" as the Database Type and that the "Profile" field in the Connection tab status bar is "oracle" once connected.

If you are using DbVisualizer Free then try add a driver property with the name "includeSynonyms" and set its value to "true". Re-connect and explore the objects tree again.

Regards

Roger
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 4, 2008 11:09 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
I'm using DBVis personal (bought it 2 weeks ago).  I set the profile to oracle (version 1.25) and still can't see them...any other thoughts?  If this doesn't work, I'm probably going to ask for a refund from the company.
Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Feb 4, 2008 11:30 PM    global.in_response_to.tooltip in response to: Jamie McIlroy
 
   thread.click_reply Reply
Jamie,

Please post or email a screenshot.

Regards

Roger
support@minq.se
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 4, 2008 11:53 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
 global.attachment database type.gif (36.5 KB )
 global.attachment dbvis issue.gif (44.8 KB )
 global.attachment profile.gif (43.3 KB )
you'll note in dbvis_issue.gif...the select is from the synonym and it's returning results but the syn doesn't show in the object view.
Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Feb 5, 2008 8:25 AM    global.in_response_to.tooltip in response to: Jamie McIlroy
 
   thread.click_reply Reply
Jamie,

This is the SQL DbVis execute to get information about all synonyms in a schema:

SELECT
s.owner "Synonym Owner",
s.synonym_name "Synonym Name",
s.table_owner "Object Owner",
s.table_name "Object Name",
decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type",
s.db_link "DB Link"
FROM
all_synonyms s,
all_objects o
WHERE
(s.owner = '$owner')
and s.table_name = o.object_name
and s.table_owner = o.owner
and o.object_type not in ('PACKAGE BODY')
order by 2

You need to change $owner to the actual schema. I assume you get zero hits when executing this?

What happen if you execute the following simplified SQL:

SELECT
*
FROM
all_synonyms
WHERE
owner = '$owner'

Regards

Roger
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 5, 2008 4:05 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
First query:
... Physical database connection acquired for: Man Fin Recon
08:06:07 \[SELECT - 0 row(s), 0.000 secs] Empty result set fetched
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.100/0.000 sec \[0 successful, 1 warnings, 0 errors]

Second query:
Returns the synonym that I can 'select *' from

Is this what you expected?

Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Feb 6, 2008 1:02 PM    global.in_response_to.tooltip in response to: Jamie McIlroy
 
   thread.click_reply Reply
 global.attachment oracle.xml (227.6 KB )
Jamie,

Thanks!

The result is somewhat strange since the synonym show in the all_synonyms system table but not in all_objects (which should keep information about all objects including synonyms).

I have attached a modified oracle.xml file that hopefully solves this. Save it as:

DBVIS-HOME\resources\profiles\oracle.xml

Please let me know the result.

Regards

Roger
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 6, 2008 4:29 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Progress....
I can see the synonyms...but when I try to view the data, on the 'data' tab, I get:

An error occurred while performing the operation:
You probably don't have the required privileges to view this object

thoughts?
Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Feb 7, 2008 10:27 AM    global.in_response_to.tooltip in response to: Jamie McIlroy
 
   thread.click_reply Reply
Jamie,

Post a screenshot of the Synonyms listing and the one of the Data tab.

You may also enable debug mode. This will then reveal what SQL DbVis execute to get the data for the Synonym.

Read more in:

http://www.minq.se/products/dbvis/faq.html#6.11

Regards

Roger
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 7, 2008 3:03 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
 global.attachment datatab.gif (39.7 KB )
 global.attachment synonyms.gif (37.7 KB )
Here are the files you request
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 7, 2008 3:06 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
debug output:
\[cleared]
07:07:45 \[DEBUG Thread-18 L.execute] Executing...
07:07:45 \[DEBUG Thread-18 L.setCurrentCatalog] DbConnection='Man Fin Recon' Catalog='null' Schema='null' NewCatalog='null'
07:07:45 \[DEBUG Thread-18 C.invoke] RootConnection: OracleConnection.prepareStatement("select * from ZE_DATA.MAN_FINANCIAL_DAILY_SETTLEMENT")
07:07:45 \[DEBUG Thread-18 L.prepareStatement] SQL: select * from ZE_DATA.MAN_FINANCIAL_DAILY_SETTLEMENT
07:07:45 \[DEBUG Thread-18 C.invoke] RootConnection: OracleConnection.nativeSQL("select * from ZE_DATA.MAN_FINANCIAL_DAILY_SETTLEMENT")
07:07:45 \[DEBUG Thread-18 B.invoke] RootConnection: OraclePreparedStatement.execute()
07:07:45 \[DEBUG AWT-EventQueue-1 C.?] DbConnection='Man Fin Recon' Catalog='null' Schema='ZE_DATA' Table='MAN_FINANCIAL_DAILY_SETTLEMENT'

Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Feb 12, 2008 4:10 PM    global.in_response_to.tooltip in response to: Jamie McIlroy
 
   thread.click_reply Reply
 global.attachment oracle.xml (227.6 KB )
Jamie,

The problem is accessing objects via DB link synonyms. I have attached a new profile that may solve this.

Just save it as:

DBVIS-HOME/resources/profiles/oracle.xml

Regards

Roger
Jamie McIlroy

Posts: 10
Registered: 01/30/08
Re: Syonyms with Oracle
Posted: Feb 15, 2008 10:31 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Fixed!!!
Thanks Roger!
Roger Bjarevall


Posts: 5,221
Registered: 12/17/04
Re: Syonyms with Oracle
Posted: Mar 16, 2008 11:35 PM    global.in_response_to.tooltip in response to: Jamie McIlroy
Correct
   thread.click_reply Reply
Jamie,

The synonyms fix is now included in the 6.0.9 version.

http://www.minq.se/products/dbvis

Regards

Roger

Point your RSS reader here for a feed of the latest messages in all forums