Ora-00904 ref invalid identifier

cvanbiljon · November 20, 2019, 6:36am

Hi,
I am have toad 13.2.0.258 running on 18c client. When I use schema browser to view views or mviews I get ora-00904 ref invalid identifier.
Any suggestions? The database i am viewing is 19c. That cannot be the issue because other people in the group can see the views. I am leaning towards my setting. Any help appreciated.

cvanbiljon · November 20, 2019, 6:56am

FYI I copied another users setting and it fixed it. I still do not know whet setting, but its working.

JohnDorlon · November 20, 2019, 1:03pm

If it happens again, go to main menu: Database->Spool SQL->Spool to screen. That will capture the SQLs that Toad executes. Then you'll be able to see where the error comes from.

dennis.horn · March 3, 2020, 8:19am

Hi.
I get the same error, whenever I open a package from schema browser in the editor. The statement causing problems:

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
ERROR: ORA-00904: "REF": ungültige ID

Do you know, wich option is causing the problems? The error seems only to occour on Oracle 19 Databases.

Additional Information:
Oracle Database Version: 19
Oracle Client Version: 12, 32 Bit
Toad Version: 13.1.1.5

JohnDorlon · March 3, 2020, 1:18pm

Oracle 19 changed some things that made this query no longer valid.

This is fixed in the current version of Toad.

Uncheck here, that should fix it.

dennis.horn · March 3, 2020, 1:54pm

Thanks for the fast reply!

Unchecking this option helped when exporting the DDL. But the error also occurs, when double-clicking the the package to open it in the Editor and everytime you compile a package.
Is there an option to turn off audit statements everywhere?

JohnDorlon · March 3, 2020, 2:11pm

I can't get that query to fire when I double-click a package in the schema browser. For me, it doesn't even get near that code with the audit statements.

Can you let me know your exact steps?

dennis.horn · March 6, 2020, 12:31pm

That's strange.. I navigate to the packages in the Schema Browser and double click on the package name. The Editor opens and the error message pops up:

After closing it, the error appears three or four times more. Same thing when I compile a package in the Editor.

Does this help? I could try to make a video next monday, if this would help?

dennis.horn · March 6, 2020, 12:37pm

I just tried a few more different objects. Same behaviour with procedures and functions, that open in the editor (but the error gets displayed only twice). When double clicking a view to get into "Alter View"-Dialog the error gets displayed once.
It doesn't happen when double clicking on tables and opening the "Alter Table"-Dialog.

JohnDorlon · March 6, 2020, 6:18pm

Do you use Team Coding? If so, uncheck this option:

dennis.horn · March 9, 2020, 9:10am

That's it! Awesome, I unchecked the option and now it works. Thank you for the fast support!

kristi.richardson · March 11, 2020, 7:29pm

I get this error as well. I upgraded toad to 13.2.0.258 and am running against a 19c database. I get the following error when trying to script tables and views:

ORA-00904: "REF": invalid identifier

We are not using Team coding

JohnDorlon · March 11, 2020, 7:39pm

Uncheck this. Repeat for Views and any other object type that you get the error on.

kristi.richardson · March 11, 2020, 7:46pm

Thanks. Had to change to tabbed view to find that option but its working now.

JohnDorlon · March 11, 2020, 7:47pm

FYI, you can also get to it from the right hand side script tab.

akellasandhya78 · November 9, 2022, 3:56am

Hi experts, I am using toad 13.1.0 and facing the same error when trying to compile oracle package and the the database version is 19c.

For package i dont this script option to uncheck the audit statements.

Please help to fix this error for me.

JohnDorlon · November 9, 2022, 12:53pm

Scroll all the way up and see my first reply about how to turn on spool sql. Do that and then you can find the SQL that is causing the error. Then you can post it here and maybe I can provide a solution.

akellasandhya78 · November 9, 2022, 1:29pm

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:28.514
Select object_name, object_type, status, owner
from SYS.DBA_OBJECTS
where owner = :own
AND OBJECT_TYPE = 'PACKAGE BODY'
and object_name = :OneObjectName
order by object_name;
:own(VARCHAR[4],IN)='APPS'
:OneObjectName(VARCHAR[28],IN)='XXAYM_CS_SERVICEREQUEST_CUHK'

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:28.729
Select s.*
FROM SYS.DBA_SYNONYMS s
WHERE ((s.OWNER = :own) OR ((s.TABLE_OWNER = :own) or (s.table_owner is null) and s.OWNER = 'PUBLIC'))
and S.TABLE_NAME = :OneObjectName
and s.db_link is null
order by s.owner, s.synonym_name;
:own(VARCHAR[4],IN)='APPS'
:OneObjectName(VARCHAR[28],IN)='XXAYM_CS_SERVICEREQUEST_CUHK'

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:28.934
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;
:OneObjectName(VARCHAR[28],IN)='XXAYM_CS_SERVICEREQUEST_CUHK'

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:29.159
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
Error: ORA-00904: "REF": invalid identifier

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:30.688
Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t;
:nm(VARCHAR[28],IN/OUT)='XXAYM_CS_SERVICEREQUEST_CUHK'
:t(VARCHAR[12],IN/OUT)='PACKAGE BODY'

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:30.893
Select line, position, SUBSTR (text, 1, 200) text
FROM SYS.user_errors
WHERE name = :oname AND TYPE = :otype
ORDER BY sequence;
:oname(VARCHAR[28],IN/OUT)='XXAYM_CS_SERVICEREQUEST_CUHK'
:otype(VARCHAR[12],IN/OUT)='PACKAGE BODY'

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:31.153
Select object_name, object_type, status, owner
from SYS.DBA_OBJECTS
where owner = :own
AND OBJECT_TYPE = 'PACKAGE BODY'
and object_name = :OneObjectName
order by object_name;
:own(VARCHAR[4],IN)='APPS'
:OneObjectName(VARCHAR[28],IN)='XXAYM_CS_SERVICEREQUEST_CUHK'

-- Session: APPS@NEW_DEV
-- Timestamp: 17:27:31.368
Select s.*
FROM SYS.DBA_SYNONYMS s
WHERE ((s.OWNER = :own) OR ((s.TABLE_OWNER = :own) or (s.table_owner is null) and s.OWNER = 'PUBLIC'))
and S.TABLE_NAME = :OneObjectName
and s.db_link is null
order by s.owner, s.synonym_name;
:own(VARCHAR[4],IN)='APPS'
:OneObjectName(VARCHAR[28],IN)='XXAYM_CS_SERVICEREQUEST_CUHK'

akellasandhya78 · November 9, 2022, 1:30pm

Hey John, I have post the spooled text.
Please have a look.

JohnDorlon · November 9, 2022, 1:36pm

This is the problem SQL.

If you are in the Schema Browser, turn off this option (It's on the procedures tab for me, but do it on procedures, packages, and functions). If you are not in the Schema Browser, I think I need to see some screen shots of Toad right before the error is shown