Toad World® Forums

Ora-00904 ref invalid identifier with Toad 12.7.0.121

I am trying to access the Script tab of tables in Toad (version 12.7.0.121) and getting ora-00904 ref invalid identifier. The database is 19c.

I don't see this error with 12.2 version of database.
Is there any setting that need to be set in Toad?

Is there any setting that need to be set in Toad?

No, but maybe this isn't something that's supported in your version of Toad, which came out long before Oracle 19c.

Go to main menu: database -> Spool SQL -> Spool to screen. THen make the error happen again.

Then post the spool sql output here.

The forum is not allowing to add the spool output.

This is part of the spool output:

Session: SYS@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1@example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SDB1)))
Timestamp: 17:00:32.171
Select *
from sys.dba_obj_audit_opts
where (SUBSTRB (alt, 1, 1) in ('-', 'A', 'S')) and
((alt <> '-/-') or (aud <> '-/-') or (com <> '-/-') or
(del <> '-/-') or (gra <> '-/-') or (ind <> '-/-') or
(ins <> '-/-') or (loc <> '-/-') or (ren <> '-/-') or
(sel <> '-/-') or (upd <> '-/-') or (ref <> '-/-') or
(exe <> '-/-') or (rea <> '-/-') or (wri <> '-/-') or (fbk <> '-/-'))
and object_type in ('TABLE','INDEXTYPE','TYPE')
and owner = :own
and OBJECT_NAME = :OneObjectName
Error: ORA-00904: "REF": invalid identifier

Oh, that.

Yes, Oracle got rid of the "REF" column in DBA_OBJ_AUDIT_OPTS in 19c. The current version of Toad does not include this column in the SQL.

You can avoid the error by unchecking auditing in the script options.

-John

Thank you so much, that worked!!

You're welcome. You may also find some problems with getting temporary tablespace sizes in pluggable databases in 19c. Oracle changed that too. It's also fixed in the newest Toad version.

Thanks for letting me know about that too. Will try to get the new Toad version.

Could you please give me the new Toad version number which has all the fixes?

The current version is 13.2.
You can keep your old version and the new one installed at the same time, just incase you need the old version for anything.

Sure, that helps.

Thank you once again!

1 Like

Hi!
We have the same problem when loading a package/procedure from Schema Browser to Editor.
We use Toad 13.2.0.258 and Oracle 19c. Problem is the same, the ref column in user_obj_audit_opts is gone in 19c and Toad is trying to query it when loading object to editor.
Any suggestions how to solve?
(I have changed the user_obj_audit_opt view as a workaround in our test database, but this it not a permanent solution of course. Should be fixed in Toad)
Select *
from sys.user_obj_audit_opts
where (SUBSTRB (alt, 1, 1) in ('-', 'A', 'S')) and
((alt <> '-/-') or (aud <> '-/-') or (com <> '-/-') or
(del <> '-/-') or (gra <> '-/-') or (ind <> '-/-') or
(ins <> '-/-') or (loc <> '-/-') or (ren <> '-/-') or
(sel <> '-/-') or (upd <> '-/-') or
(ref <> '-/-') or
(exe <> '-/-') or (rea <> '-/-') or (wri <> '-/-') or (fbk <> '-/-'))
and OBJECT_NAME = :OneObjectName;

What is your database's compatible parameter set to?

If it's set to anything less than 19, Toad 13.2 will still query the REF column. This will be fixed soon for 13.3.

Regarding sending packages to editor....if you just double-click the package in the Schema Browser, we send the package to editor without running the audit query. I think you must be using "create script".

To use "create script" to send to send a package to editor w/o running the audit query:

  • Click the "Create script" toolbar button
  • In the dialog that appears, Set "Output" to one of the editor options.
  • Go to the "Script Options" tab
  • Uncheck "Audit statements"
  • Set other options as desired, then OK

Hi,
Thanks for your quick reply!
Compatible was set to 12.2.0.
When I change it to 19.0.0 the problem is gone!!
So I guess we have to workaround until 13.3 then.
Do you know when it will be released?

"f you just double-click the package in the Schema Browser, we send the package to editor without running the audit query"

I double-clik the package name in schema editor, and it runs the audit query , for sure. That was how I got the audit query, using Spool sql.

/jio

I don't have a release date for 13.3, but it just got started in beta less than a month ago, so it might be a while.

Weird. There must be some option involved somewhere. I definitely do not see the audit query on double-click.

Another workaround to send to editor: would be in schema browser, click the 2nd toolbar button on the right hand side source tab.