How do I access the AST (abstract syntax tree) for a PL/SQL stored procedure?

Question

When Oracle compiles a stored procedure, it stores the AST for the procedure in DIANA format.

• how can I access this AST?
• are there built-in tools for processing this AST?

Answer 1

There is an undocumented package DUMPDIANA that is meant to dump the Diana in a human-readable format.

The file $ORACLE_HOME\rdbms\admin\dumpdian.sql says "Documentation is available in /vobs/plsql/notes/dumpdiana.txt". I cannot find that file, and without it we can only guess at the meaning of some parameters. Basic usage of DUMPDIANA is as follows:

SQL> show user
USER is "SYS"
SQL> @?\rdbms\admin\dumpdian

Library created.


Package created.


Package body created.

create or replace procedure hello_world
  2  as
  3  begin
  4  dbms_output.put_line('hello world');
  5* end;


Procedure created.

SQL> set serveroutput on
SQL> execute sys.DUMPDIANA.dump('HELLO_WORLD');
user: SYS

PL/SQL procedure successfully completed.

At this point a pair of files should have been created in the folder $ORACLE_BASE/diag/rdbms/orcl12c/orcl12c/trace. The two files seem to follow the naming convention:

orcl12c_ora_{PROCESS}.trc
orcl12c_ora_{PROCESS.trm

Where the trc file is a human readable version of the corresponding trm file, and {PROCESS} is the operating system process ID. To find this use the following query from the same session:

select p.spid from v$session s,v$process p 
where s.paddr = p.addr
and s.sid = sys_context('USERENV','SID');

For example if the session ID was 8861 then from a bash shell you can view the results using:

vim $ORACLE_BASE/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_8861.trc

The result is interesting... if not particularly intuitive! For example here is a snippet of the file produced. Note the HELLO_WORLD string literal.

PD1(2):D_COMP_U  [
    L_SRCPOS : row 1 col 1
    A_CONTEX :
PD2(2):      D_CONTEX  [
          L_SRCPOS : row 1 col 1
          AS_LIST :  < >
      ]
    A_UNIT_B :
PD3(2):      D_S_BODY  [
          L_SRCPOS : row 1 col 1
          A_D_ :
PD4(2):            DI_PROC  [
                L_SRCPOS : row 1 col 11
                L_SYMREP : HELLO_WORLD,
                S_SPEC : PD5^(2),
                S_BODY : PD8^(2),

A couple of notes. I've run this as SYS, which as we know is not a good practice, this is no reason I know of why you shouldn't grant privileges on DUMPDIANA to a normal user. All the procedures you dump go into the same file - if you delete that file, it stops working, and you'll need to start a new session. If it stops working, starting a new session sometimes seems to fix the problem.