Displaying and fixing compilation errors on a SQLTXPLAIN package
If you are installing SQLTXPLAIN and for some reason you get a PL/SQL compilation error in a SQLT log like the one below, chances are the SHOW ERRORS command on SQL*Plus won’t show the actual error. This is because the installation connects as SYS and the packages are owned by SQLTXADMIN. In most cases the cause of the error is missing a GRANT or a SYNONYM in libraries called by SQLT like DBMS_METADATA or UTL_FILE.
... creating package body for SQLT$R Warning: Package Body created with compilation errors. No errors. ... SELECT column_value libraries FROM TABLE(SQLTXADMIN.sqlt$r.libraries_versions) ERROR at line 1: ORA-04063: package body "SQLTXADMIN.SQLT$R" has errors
How to display PL/SQL package compilation error on SQLTXPLAIN
You may need to unlock the schema owner of SQLT packages and compile the invalid object connecting as this SQLTXADMIN account, then lock it back. By following steps below you will get to see the actual compile error. Then proceed to fix it and recompile the invalid package body. If it is a missing GRANT/SYNONYM you may want to create the GRANT EXECUTE of the SYS library to SQLTXADMIN then create a SYNONYM with same name (not a PUBLIC SYNONYM). Ex: GRANT EXECUTE ON SYS.DBMS_METADATA TO SQLTXADMIN; CREATE SYNONYM SQLTXADMIN.DBMS_METADATA FOR SYS.DBMS_METADATA;
$ cd sqlt/install $ sqlplus / AS SYSDBA -- SQL> SELECT object_name, object_type FROM dba_objects WHERE owner = 'SQLTXADMIN' AND object_type LIKE 'PACKAGE%' AND status = 'INVALID'; SQL> START sqcommon1.sql SQL> GRANT CREATE SESSION TO sqltxadmin; SQL> ALTER USER sqltxadmin IDENTIFIED BY &&password. ACCOUNT UNLOCK; SQL> CONN sqltxadmin/&&password.; -- -- compile invalid packages as per query output above -- SQL> ALTER PACKAGE sqlt$m COMPILE; SQL> ALTER PACKAGE sqlt$r COMPILE; -- -- fix root cause of error -- SQL> CONN / AS SYSDBA SQL> REVOKE CREATE SESSION FROM sqltxadmin; SQL> ALTER USER sqltxadmin PASSWORD EXPIRE ACCOUNT LOCK;
Hi Carlos, i have in my prod database following 2 SQLTXADMIN package bodies invalid SQLT$D and SQLT$T, sowsErrors for PACKAGE BODY SQLTXADMIN.SQLT$D:
LINE/COL ERROR
——– —————————————————————–
3093/5 PL/SQL: SQL Statement ignored
3093/17 PL/SQL: ORA-00980: synonym translation is no longer valid
3145/5 PL/SQL: SQL Statement ignored
3145/12 PL/SQL: ORA-00980: synonym translation is no longer valid
3149/5 PL/SQL: SQL Statement ignored
3149/17 PL/SQL: ORA-00980: synonym translation is no longer valid
3195/5 PL/SQL: SQL Statement ignored
even when tracing my session there is no helpful info what synonym is failing, could you please advise.
br Daniel Jelev
Daniel Jelev
October 23, 2020 at 6:19 am
Try first uninstalling, then installing latest version. If still the same then contact Oracle Support. I no longer support SQLT myself. You did not specify your DB version.
Carlos Sierra
October 23, 2020 at 7:02 am