Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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