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

2 Likes

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.

@JohnDorlon
I'm new in SQL and currently having a similar problem.

I want to create a trigger that will show which table(s) has/have been modified and who (which users) changed the data on this table.

If I understood it correctly, I need to use REF on the trigger to do this. I'm using SQL Tools 1.8 b38.

I have created a new table:

image

This is the query I used to make the table:

CREATE TABLE nag_Historic (
id NUMBER(10) NOT NULL,
tst_date DATE DEFAULT SYSDATE NOT NULL,
Ändere VARCHAR2(20) DEFAULT USER NOT NULL,
title VARCHAR2(100) NOT NULL
);

ALTER TABLE nag_Historic
ADD (
CONSTRAINT nag_pk PRIMARY KEY (id)
);

CREATE SEQUENCE nag_id START WITH 1 INCREMENT BY 1;

I have also made the trigger:

CREATE OR REPLACE TRIGGER trg_nag_histry
BEFORE INSERT OR UPDATE OR DELETE ON nag_historic
FOR EACH ROW
BEGIN
SELECT nag_ID.nextval
INTO :new.id
FROM dual;
END;

This is how I inserted the value:

INSERT INTO nag_Historic (title)
VALUES ('johan');

Now the last part of using REF to know which tables have been modified:

CREATE OR REPLACE TYPE nag_historic_type AS object(
id integer,
dateStart date,
title VARCHAR2(100)
);

create table nag_historic of nag_historic_type;

Create or replace type kunde_type as object(
id integer,
items varchar2(30),
datePurchased varchar2(20),
nag_historic_Ref REF nag_historic_type);

create table kunde of kunde_type;

SELECT REF(t)
FROM nag_historic t
WHERE id = 1;

Then it gave an error ora-00904 ref invalid identifier.
Am I wrong in using REF in this case? Thank you for your help! :slight_smile:

This is not related to the thread above. Oracle has a built-in feature called Auditing which can tell you which users modified which tables. You might want to look at that instead.

Thanks! Un-checking 'audit statements' worked!

1 Like