I was using DBMS_AUTO_SQLTUNE.report_auto_tuning_task to get the results of last night's tuning task. This fails in Toad 15.1.113.1379. The database is Oracle 19.24. This works in SQL/Plus and sqlcl. It fails in SQL Developer with the same error.
This was confusing for a day.
My current solution is to run in SQL/Plus.
set lines 222 trim on pages 3333 LONG 1000000
COLUMN parameter_value FORMAT A333
COLUMN parameter_name FORMAT A333
COLUMN x FORMAT A333
SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task as x
FROM dual;
SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task as x
FROM dual
*
Error at line 5
ORA-00904: "SYS"."DBMS_AUTO_SQLTUNE": invalid identifier
Script stopped on line 5.
Are you logged into the container (not pluggable) database as SYS?
I'm logged into the non-CDB database as a user with many GRANTS, SYSTEM PRIVs, OBJECT GRANTS. I don't have SYS or SYSDBA. That shouldn't be a prerequisite.
I thought I saw somewhere on Support.Oracle.com that it was. I may be mistaken.
In any case, whatever the problem, it seems privileges/database related, and I'm not sure what it is. Toad just sends the SQL to Oracle, and Oracle and gives us the result (or the error), then we pass it along.
The only explanation that I can come up with for it working in SQL*Plus is the unlikely scenario that you are using a different Oracle client, or connected to a different database, etc.
Since you can reproduce it with SQL Developer, I'd suggest you open a case with Oracle about it. Most likely when they help you resolve it there, it'll start working again in Toad also.
Sorry I don't have anything more helpful for you.
-John
Toad is using the Oracle Instant Client 19.12. SQL Developer uses a built-in JDBC driver. Linux SQL*Plus uses the Oracle Client for Enterprise Database 19.12
The report works in SQL*Plus on my PC which is in the Oracle Instant Client 19.12.
This is another piece of information that I can send to Oracle Support.
Thank you,
-Andy