PL/SQL Analyze Code

PL/SQL Analyze code PPT. Presentation shared by Shashank Barki as part of Ask Tom Office Hours hosted and presented by Steven Feuerstein.

Transcript

1. PL/SQL Analyze Code Shashank Barki / @shankbarki
2. • Senior principal data engineer at Manhattan associates • 13 years of industry experience as developer, cloud architect and data analyst • PL/SQL evangelist • Life is easy (or tough) when you support Arsenal FC, read PG Wodehouse and eat Mangoes (! wrong season !) Shashank Barki / @shankbarki
3. Agenda 1. Code composition 2. Tracking Code 3. Tracking propagating exceptions 4. PL/SQL DBMS_METADATA 5. PL/Scope tool 6. Using DBMS_UTILITY.FORMAT_CALL_STACK
4. Code composition – • Variables • Parameters • Iterations • Number of executions • Sub program calls • Bulk operations ? • Nested calls PL/SQL Code composition
5. Our buddies – • USER_ARGUMENTS • USER_OBJECTS • USER_SOURCE • USER_PROCEDURES • USER_OBJECT_SIZE • USER_DEPENDENCIES Tracking coding information
6. Tracking coding information - DEMO
7. • The call stack information lets us to determine the stages of program execution. • A program call stack includes the nested information of the referenced subprograms. • DBMS_UTILITY.FORMAT_CALL_STACK function or UTL_CALL_STACK • Issue with the FORMAT_CALL_STACK output is the inability to extract the drill-down up to local routine information and use it for debugging tasks. Tracking coding information (contd.)
8. • UTL_CALL_STACK gives a structured call stack as well as error stack. • From 12C onwards • The structured call stack output allows developers to extract the information and use it for debug purposes. Tracking coding information (contd.)
9. Tracking coding information - DEMO
10. Tracking propagating exceptions in code • Move away from SQLERRM and SQLCODE !! • PL/SQL programs invoke routines, and it may happen that a routine call fails with an exception. • The exception propagates to the calling block and traverses through multiple blocks in search of its handler. To track the path of propagating exceptions – 1. FORMAT_ERROR_BACKTRACE function from DBMS_UTILITY 2. UTL_CALL_STACK package
11. Tracing Error - DEMO
12. USER_IDENTIFIERS • PL/SQL program code is made up of literals, identifiers, delimiters, and comments. • All items that are declared in a PL/SQL program as variables, cursors, constants, and subprogram names are identifiers. • Identifiers can be reserved words (such as BEGIN and END), predefined (declared globally within STANDARD package), or quoted. • USER_IDENTIFIERS • The USER_IDENTIFIERS dictionary view reports the usage of identifiers in a PL/SQL program unit. • The view includes information about an identifier's name, its type, and usage by line number in a PL/SQL program.
13. PL/Scope tool • Capture the use of identifiers in a PL/SQL program. • The identifier details are stored in the SYSAUX tablespace • Queried from the USER_IDENTIFIERS dictionary view • PL/Scope tool from SQL Developer. PLSCOPE_SETTINGS PARAMETER • The compilation parameter PLSCOPE_SETTINGS enables the collection of identifier data in a PL/SQL program. • By default, the parameter is disabled (IDENTIFIERS:NONE). • It should be set to IDENTIFIERS:ALL in order to enable identifier tracking.
14. PL/Scope tool - DEMO
15. DBMS_METADATA package • Introduced in Oracle 9i. • The API enables the extraction of object metadata from database dictionaries that can be optionally manipulated and re-executed on a database server. • The package is owned by SYS whose public synonym is used by all users. • A user with SELECT_CATALOG_ROLE can directly access the DBMS_METADATA package.
16. dbms_metadata - DEMO
17. Resources https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm #BABFBDDG https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope. htm#g1010526 Advanced Oracle PL/SQL Developer's Guide - Saurabh K. Gupta
18. THANK YOU Merci ! Danke Shashank Barki / @shankbarki