{"id":709,"date":"2010-12-10T04:32:54","date_gmt":"2010-12-10T09:32:54","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=709"},"modified":"2012-11-18T20:30:30","modified_gmt":"2012-11-19T01:30:30","slug":"analyze-oracle-plsql-procedure","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/analyze-oracle-plsql-procedure\/","title":{"rendered":"Analyze Oracle PLSQL Procedure"},"content":{"rendered":"

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.<\/p>\n

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

Take this SQL for example:<\/p>\n

    \r\nCREATE OR replace PROCEDURE Db_mdw_trt_elt_prel_cond\r\n    (p_param_call IN pkg_mdw_def_general.param_call,\r\n     p_trt        IN VARCHAR2)\r\nIS\r\n  w_nom_ekapi VARCHAR2(255);\r\n  w_msg       pkg_mdw_def_general.param_msg;\r\n  trt_error EXCEPTION;\r\nBEGIN\r\n  w_nom_ekapi := 'PKG_EKI_ELT_PREL_COND';\r\n\r\n  w_code_retour := p_code_retour;\r\n\r\n  w_nom_proc := 'PKG_MDW_ELT_PREL_COND_ALIM.INIT_TCONDPREL';\r\n\r\n  IF w_existe = 'N' THEN\r\n    w_nom_proc := 'INSERT:POOLPREL';\r\n  END IF;\r\nEND db_mdw_trt_elt_prel_cond; \r\n<\/pre>\nHere is the result generated by this demo after analyze above SQL:<\/p>\n\r\nSQL Statement: sstplsql_createprocedure\r\nProcedure name: Db_mdw_trt_elt_prel_cond\r\nParameters:\r\n\tName:p_param_call\r\n\tDatatype:pkg_mdw_def_general.param_call\r\n\tIN\/OUT:1\r\n\tName:p_trt\r\n\tDatatype:VARCHAR2\r\n\tIN\/OUT:1\r\n\r\ndeclare statements: 3\r\n\r\nSQL Statement: sstplsql_vardecl\r\n\tVariable Name:w_nom_ekapi\r\n\tVariable Datatype:VARCHAR2(255)\r\nSQL Statement: sstplsql_vardecl\r\n\tVariable Name:w_msg\r\n\tVariable Datatype:pkg_mdw_def_general.param_msg\r\nSQL Statement: sstplsql_vardecl\r\n\tException:trt_error\r\n\r\nbody statements: 4\r\n\r\nSQL Statement: sstplsql_assignstmt\r\nleft: w_nom_ekapi\r\nright: 'PKG_EKI_ELT_PREL_COND'\r\n\r\nSQL Statement: sstplsql_assignstmt\r\nleft: w_code_retour\r\nright: p_code_retour\r\n\r\nSQL Statement: sstplsql_assignstmt\r\nleft: w_nom_proc\r\nright: 'PKG_MDW_ELT_PREL_COND_ALIM.INIT_TCONDPREL'\r\n\r\nSQL Statement: sstplsql_ifstmt\r\ncondition: w_existe = 'N'\r\nthen statement:\r\n\tStatement type: sstplsql_assignstmt\r\n\tStatement text: w_nom_proc := 'INSERT:POOLPREL'\r\n<\/pre>\nDownload this demo: Java Vesion<\/a>, C# demo<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n