Displaying and fixing compilation errors on a SQLTXPLAIN package

Displaying and fixing compilation errors on a SQLTXPLAIN package

on executing SQLT 

Reference  - 

https://carlos-sierra.net/2013/06/19/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.

1 2 3 4 5 6 7

... 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;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

$ 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;