DBMS_JAVA requirement for schema browser table columns

Toad for Oracle - Beta
1

Howdy!

In 17.0.294.1777, I'm seeing occasional ORA-00904 "SYS"."DBMS_JAVA"."LONGNAME": invalid identifier errors in the SB/Tables->Columns tab.

Our standard is to lock down that package since the zero-day in Oracle 11.2... :frowning: This would be per CIS Oracle Server 19c DB Unified Auditing v1.1.0 | Tenable® and the accompanying CIS link that I don't have a login to access.

Thoughts?

Thanks!
Rich

2

Hi Rich,

When a connection is first made, in the giant pl/sql block that Toad runs, we select from ALL_OBJECTS looking for DBMS_JAVA and DBMS_DEBUG. So if they aren't found due to lack of privs, Toad won't use them.

That said, the places we might use DBMS_JAVA.LONGNAME are:

on F4, when trying to figure out what you've F4'd on.
in SB, if you go to Java tab
SB RHS Grants tab of java objects
SB RHS script tab of java objects

If you can post the SQL that you're seeing next time it happens, I can tell you exactly where it comes from. But I don't know why you're seeing it in SB/Table-Columns.

3

I see that some of these places in our source reference dbms_java.longname even if our prior query to all_object said you don't have it.

I'll log this to go through it carefully tomorrow/next week.

-John

1 Like
4

I'll grab the Spool for you tonight or tomorrow. Right now, it's BurgerTime™ (the food, not the '80s arcade game).

Thanks John!

1 Like
5

Here's what causes the error:

-- Timestamp: 18:38:52.848
Select o.*, SYS.DBMS_JAVA.LONGNAME(OBJECT_NAME) longname
from  sys.DBA_OBJECTS o
where o.owner = :own
and o.object_type = 'JAVA SOURCE'
and o.object_name = :OneObjectName
order by o.object_name;
-- ORA-00904: "SYS"."DBMS_JAVA"."LONGNAME": invalid identifier
:own(VARCHAR[2],IN)='RR' 
:OneObjectName(VARCHAR[17],IN)='RRR#RRRRRRRRR#RRR' 
-- Elapsed Time: 0.332 seconds
-- Rows fetched: 0

I guess the weird thing is that the :OneObjectName object is a trigger (of course the object names have been obfuscated). I replaced the letters and numbers with "R", but left the "#" in where it appears in the actual object name (not my idea to have that character in an object name).

Let me know if there's more info needed!

Thanks!
Rich

6

I see what's happening.

First of all, we should not be running this query at that time (longname or no longname). So that's a bug right there. It's running when we try to load the script for a trigger. I'll make a change to fix that tomorrow.

If you uncheck Options -> Schema Browser -> Data Tab -> Highlight columns populated by sequence/trigger pair, then it won't run, unless you go to the SB-Tables-RHS triggers tab.

But the interesting thing is that we are checking our internal HasJava property, which means that DBMS_JAVA is in all_objects for you, which - I would think - would let you call dbms_java.longname. Because the ALL_ views are all about privileges, right? Weird.

Edit: I see what's wrong with that too. We're actually querying DBA_OBJECTS in that pl/sql block when a connection is first made. Can you find it? It was sneaky. :man_facepalming:t2:

-John

1 Like
7

The "Highlight columns..." toggle does the trick! A Toad option I'm reasonably comfortable saying I have not used in the past 23+ years. :smiley:

This issue doesn't actually affect me personally because I'll just grant execute on DBMS_JAVA to my personal user. :innocent: I'm thinking down the road to GA, where my users will start hitting it and I'll get the support tickets.

Ahh, were you catching the public synonym in the ALL_ view???

Rich

8

I was building some SQL text using all_objects with the intent of running it using execute immediate, but at the last second, I ran the string through a proc that swaps out all_objects for dba_objects if the user can select from dba_objects. Good way to see if the package exists. Not so good for seeing if the user can execute it.

1 Like