Carlos Sierra's Tools and Tips

Starting on SQLT/TRCA release 11.4.5.4 both tools switched PL/SQL compilation mode of their libraries from INTERPRETED to NATIVE. This change was introduced to reduce overhead in PL/SQL code used by these two tools.

Some customers report that installing SQLT or TRCA they get some PL/SQL errors similar to the ones below. That means those systems are not correctly setup to benefit of the NATIVE compilation mode for PL/SQL. If you get similar errors you have two options: configure your system so it can benefit of this PL/SQL optimization, or revert to use INTERPRETED mode for SQLT/TRCA.

Typical errors observed:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00923: native compilation failed: C compilation or linking
 failed:spdzj.c:Could not invoke program
 /opt/Prod/SUNW/bin/cc:No such file or directory

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [spdzj.c:child process terminated due
 to signal:]

If you want to revert to use PL/SQL compilation mode of INTERPRETED on SQLT follow these steps:

1. Modify sqlt/install/sqcommon1.sql removing the two dashes “–” at the beginning of this line (27): –DEF plsql_code_type = ‘INTERPRETED’;
2. Re-install SQLT by executing sqlt/install/sqcreate.sql
3. If problem persists perform a fresh install executing sqlt/install/sqdrop.sql followed by sqlt/install/sqcreate.sql

Be aware that you may have to perform these steps above every time you upgrade your SQLT tool until you address the root cause of your PL/SQL configuration issue.

To perform the same on TRCA look for script trca/install/tacommon1.sql and modify its line 27 removing the two dashes “–” at the beginning of the line, so it becomes: DEF plsql_code_type = ‘INTERPRETED’;. Then proceed to install the tool with trca/install/tacreate.sql as usual.