Compiling a stored subprogram involves fixing any syntax errors in the code. You might need to do additional debugging to ensure that the subprogram works correctly, performs well, and recovers from errors. Such debugging might involve:
Adding extra output statements to verify execution progress and check data values at certain points within the subprogram.
Running a separate debugger to analyze execution in greater detail.
Topics:
PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code. Because PL/Scope is a compiler-driven tool, you use it through interactive development environments (such as SQL Developer and JDeveloper), rather than directly.
PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.
The PL/SQL hierarchical profiler reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls. It accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes information such as number of calls to the subprogram, time spent in the subprogram itself, time spent in the subprogram's subtree (that is, in its descendent subprograms), and detailed parent-children information.
You can browse the generated HTML reports in any browser. The browser's navigational capabilities, combined with well chosen links, provide a powerful way to analyze performance of large applications, improve application performance, and lower development costs.
You can also debug stored subprograms and triggers using the Oracle package DBMS_OUTPUT. Put PUT and PUT_LINE statements in your code to output the value of variables and expressions to your terminal.
This section describes the privilege model that applies to debugging PL/SQL and Java code running within the database. This model applies whether you are using SQL Developer, Oracle JDeveloper, or any of the various third-party PL/SQL or Java development environments, and it affects both the DBMS_DEBUG and DBMS_DEBUG_JDWP APIs.
For a session to connect to a debugger, the effective user at the time of the connect operation must have the DEBUG CONNECT SESSION system privilege. This effective user might be the owner of a DR subprogram involved in making the connect call.
When a debugger becomes connected to a session, the session login user and the enabled session-level roles are fixed as the privilege environment for that debugging connection. Any DEBUG or EXECUTE privileges needed for debugging must be granted to that combination of user and roles.
To be able to display and change Java public variables or variables declared in a PL/SQL package specification, the debugging connection must be granted either EXECUTE or DEBUG privilege on the relevant code.
To be able to either display and change private variables or breakpoint and execute code lines step by step, the debugging connection must be granted DEBUG privilege on the relevant code
|
Caution: TheDEBUG privilege allows a debugging session to do anything that the subprogram being debugged could have done if that action had been included in its code. |
In addition to these privilege requirements, the ability to stop on individual code lines and debugger access to variables are allowed only in code compiled with debug information generated. Use the PL/SQL compilation parameter PLSQL_DEBUG and the DEBUG keyword on statements such as ALTER PACKAGE to control whether the PL/SQL compiler includes debug information in its results. If not, variables are not accessible, and neither stepping nor breakpoints stop on code lines. The PL/SQL compiler never generates debug information for code hidden with the PL/SQL wrap utility.
|
See Also: Oracle Database PL/SQL Language Reference for information about thewrap utility |
The DEBUG ANY PROCEDURE system privilege is equivalent to the DEBUG privilege granted on all objects in the database. Objects owned by SYS are included if the value of the O7_DICTIONARY_ACCESSIBILITY parameter is TRUE.
A debug role mechanism is available to carry privileges needed for debugging that are not normally enabled in the session. See the documentation on the DBMS_DEBUG and DBMS_DEBUG_JDWP packages for details on how to specify a debug role and any necessary related password.
The JAVADEBUGPRIV role carries the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges. Grant it only with the care those privileges warrant.
|
Caution: GrantingDEBUG ANY PROCEDURE privilege, or granting DEBUG privilege on any object owned by SYS, means granting complete rights to the database. |
If you are writing code for part of a debugger, you might need to use packages such as DBMS_DEBUG_JDWP or DBMS_DEBUG.
The DBMS_DEBUG_JDWP package provides a framework for multi-language debugging that is expected to supersede the DBMS_DEBUG package over time. It is especially useful for programs that combine PL/SQL and Java.