Analyze Oracle PLSQL Procedure

This demo illustrate how to using General SQL Parser to analyze Oracle PL/SQL procedure, find all elements in a PLSQL procedure such as procedure name, parameters, declared variable and all statements inside this procedure. This demo can be modified to meet you own need.

You can also analyze PL/SQL function, trigger, package, package body, type body and fetch detailed information you need.

Take this SQL for example:

CREATE OR replace PROCEDURE Db_mdw_trt_elt_prel_cond
    (p_param_call IN pkg_mdw_def_general.param_call,
     p_trt        IN VARCHAR2)
IS
  w_nom_ekapi VARCHAR2(255);
  w_msg       pkg_mdw_def_general.param_msg;
  trt_error EXCEPTION;
BEGIN
  w_nom_ekapi := 'PKG_EKI_ELT_PREL_COND';

  w_code_retour := p_code_retour;

  w_nom_proc := 'PKG_MDW_ELT_PREL_COND_ALIM.INIT_TCONDPREL';

  IF w_existe = 'N' THEN
    w_nom_proc := 'INSERT:POOLPREL';
  END IF;
END db_mdw_trt_elt_prel_cond; 

Here is the result generated by this demo after analyze above SQL:

SQL Statement: sstplsql_createprocedure
Procedure name: Db_mdw_trt_elt_prel_cond
Parameters:
	Name:p_param_call
	Datatype:pkg_mdw_def_general.param_call
	IN/OUT:1
	Name:p_trt
	Datatype:VARCHAR2
	IN/OUT:1

declare statements: 3

SQL Statement: sstplsql_vardecl
	Variable Name:w_nom_ekapi
	Variable Datatype:VARCHAR2(255)
SQL Statement: sstplsql_vardecl
	Variable Name:w_msg
	Variable Datatype:pkg_mdw_def_general.param_msg
SQL Statement: sstplsql_vardecl
	Exception:trt_error

body statements: 4

SQL Statement: sstplsql_assignstmt
left: w_nom_ekapi
right: 'PKG_EKI_ELT_PREL_COND'

SQL Statement: sstplsql_assignstmt
left: w_code_retour
right: p_code_retour

SQL Statement: sstplsql_assignstmt
left: w_nom_proc
right: 'PKG_MDW_ELT_PREL_COND_ALIM.INIT_TCONDPREL'

SQL Statement: sstplsql_ifstmt
condition: w_existe = 'N'
then statement:
	Statement type: sstplsql_assignstmt
	Statement text: w_nom_proc := 'INSERT:POOLPREL'

Download this demo: Java Vesion, C# demo