Toad Data Point - Select any dictionary - Oracle DB permission

Toad Data Point & Toad Intelligence Central
1

Why does Data Point not display procedures without ‘SELECT ANY DICTIONARY’ DB permission, but Toad for Oracle will display them.

Is there an option in Data Point that I’m missing?

2

They should display them. How are you connecting to Oracle in TDP? ODBC, OCI or Direct Connect? You should be using an OCI client type connection in order to have all of the features for Oracle. Please check and if you are not sure please post a screen shot of your connection properties.

3

Here’s the connection properties.

As I said, I’m able to see all the procedures on Toad for Oracle – just not Toad Data Point.
20140117_toad_data_point_screenshot.docx (37.5 KB)

4

Also - I’m on Toad Data Point 3.2.0.916 - Base Edition

5

Please execute this SQL in the editor and enter Schema name when prompted. Are the procedure names list in result set?

SELECT o.object_name,
o.object_type,
DECODE (o.status, ‘VALID’, ‘true’, ‘false’) valid,
o.last_ddl_time,
o.object_id,
o.created,
NVL (
(SELECT DECODE (NVL (c.debuginfo, ‘F’),
‘T’, ‘true’,
‘F’, ‘false’)
FROM sys.all_probe_objects c
WHERE c.object_id = o.object_id),
‘false’)
debuginfo,
(SELECT NVL (p.AUTHID, ‘CURRENT_USER’) AUTHID
FROM SYS.dba_procedures p
WHERE owner = :f1 AND object_name(+) = o.object_name
GROUP BY object_name, AUTHID)
AUTHID,
STATUS
FROM SYS.dba_objects o
WHERE o.owner = :f1 AND object_type = ‘PROCEDURE’
ORDER BY 2, 1

6

That returned 121 rows (Both Toad Data Point & Toad for Oracle

7

121 rows
20140117_toad_data_point_screenshot_B.docx.doc (179 KB)

8

Can you try executing this query and see if the results are the same or different>

SELECT o.object_name,

o.object_type,

DECODE (o.status, ‘VALID’, ‘true’, ‘false’) valid,

o.last_ddl_time,

o.object_id,

o.created,

NVL (

(SELECT DECODE (NVL (c.debuginfo, ‘F’),

‘T’, ‘true’,

‘F’, ‘false’)

FROM sys.all_probe_objects c

WHERE c.object_id = o.object_id),

‘false’)

debuginfo,

(SELECT NVL (p.AUTHID, ‘CURRENT_USER’) AUTHID

FROM SYS.all_procedures p

WHERE owner = :f1 AND object_name(+) = o.object_name

GROUP BY object_name, AUTHID)

AUTHID,

STATUS

FROM SYS.all_objects o

WHERE o.owner = :f1 AND object_type = ‘PROCEDURE’

ORDER BY 2, 1

9

That only returned the one procedure shown in the previous screenshot from Toad Data Point.

Both Toad Data Point & Toad for Oracle returned only the one row when running the query.

10

Okay. I have attached a sqldictionary.xml file. Go to your About box and click the Application Data Dir. Place this file in this directory and restart TDP. See if that resolves your issue.
SqlDictionary.xml (552 Bytes)

11

Sorry - that didn’t make a difference
20140127_toad_data_point_screenshot_A.docx.doc (231 KB)

12

I pulled the query out of the XML, and ran it manually.

It returned all the procedures.

When I added a where clause for the specific schema, it returned 121 rows - the correct number.

13

So it sounds like we are good. You put the SqlDictionary.xml file in the app data dir, correct? That file tells the app to use a different query than what was compiled.

14

Sorry, I didn't this post. Go to your About box and select the App data dir. can you see the SqlDictionary.xml file there. It should look like this.

sqldictionary.png
sqldictionary.png847×578 101 KB

15

See the attachment - 20140127_toad_data_point_screenshot_A.docx.doc from 1:43 today.

The top shows the directory that was opened when I clicked application_directory.

It shows the new xml file, but the object-explorer still only shows one procedure.

I only see all the procedures when I remove the select statement from the xml file, and run it manuall.

It looks like the xml select does return all the procedures, but the application does not appear to be executing the select from the xml file.

16

Go to the Tools menu and select ‘Execution Trace | To Ouput’. Then go to the views menu and choose the Ouput window.

Now connect to Oracle and go to the proceedure tab. While you are doing this you should see what SQL is being executed in the output window. Please find the procedure SQL and see which one is being executed. I assume it is the old one but this is the only way to really confirm what the app is using.

17

Here’s the trace.

It’s still only displaying the one procedure. I’m assuming it just isn’t looking at the new xml file.
20140129_toad_data_point_screenshot_A.docx (11 KB)

18

This doesn’t make sense. The app is using the correct query now. And there is no caching of these objects.

Let’s try this. We are going to try renaming your app data dir.

  1. Go to the about box and go to the app data dir.

  2. Leave the windows explorer window open and close down TDP.

  3. Rename the directory.

  4. Start TDP and then shut it down. This will create new directory and base files such as settings. xml. We still need your connections and changed SQL before we test.

  5. Go back to the old dir and copy over the connections.xml and sqldictionary.xml file into the new app data dir.

  6. Now start up TDP and try the procedures again.

19

Still no sucess. I’ve screenshot’d the following steps. I’ll attach seperately:

B: About screen w/ procedure showing only 1

C: Application data directory in windows explorer

D: Notepad of xml file

E: Code from xml file - run manually

Did I do it right?
20140129_toad_data_point_screenshot_B.doc (276 KB)

20

Here’s the combined screenshots
20140129_toad_data_point_screenshot_B-E.doc (712 KB)