Oracle DBMS_DEBUG
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This package API is primarily intended to implement server-side debuggers and provides a way to debug server-side PL/SQL program units. The name of the debugger is PROBE.
AUTHID DEFINER
Constants
Name Data Type Value
 Break Flags
break_exception PLS_INTEGER 2
break_any_call PLS_INTEGER 12
break_return PLS_INTEGER 16
break_next_line PLS_INTEGER 32
break_control_c PLS_INTEGER 256
break_any_return PLS_INTEGER 512
break_handler PLS_INTEGER 2048
break_RPC PLS_INTEGER 4096
abort_exception PLS_INTEGER 8192
 Breakpoints
breakpoint_status_unused PLS_INTEGER 0
breakpoint_status_active PLS_INTEGER 1
breakpoint_status_active2 PLS_INTEGER 2
breakpoint_status_disabled PLS_INTEGER 4
breakpoint_status_remote PLS_INTEGER 8
 Client Side Error Codes
error_pbrun_mismatch PLS_INTEGER 9
error_no_rph PLS_INTEGER 10
error_probe_invalid PLS_INTEGER 20
error_upierr PLS_INTEGER 21
error_noasync PLS_INTEGER 22
error_nologon PLS_INTEGER 23
error_reinit PLS_INTEGER 24
error_unrecognized PLS_INTEGER 25
error_synch PLS_INTEGER 26
error_incompatible PLS_INTEGER 30
 Cursor Flags
cflags_open PLS_INTEGER 1
cflags_found PLS_INTEGER 2
cflags_notfound PLS_INTEGER 4
cflags_uninitialized PLS_INTEGER 32
cflags_recursive PLS_INTEGER 128
cflags_refcur_bind PLS_INTEGER 1024
cflags_dynamic_sql PLS_INTEGER 2048
cflags_first_iter PLS_INTEGER 4096
cflags_dynamic_open PLS_INTEGER 16384
 Error Codes
success PLS_INTEGER 0
error_bogus_frame PLS_INTEGER 1
error_no_debug_info PLS_INTEGER 2
error_no_such_object PLS_INTEGER 3
error_unknown_type PLS_INTEGER 4
error_unimplemented PLS_INTEGER 17
error_indexed_table PLS_INTEGER 18
error_illegal_index PLS_INTEGER 19
error_deferred PLS_INTEGER 27
error_exception PLS_INTEGER 28
error_communication PLS_INTEGER 29
error_timeout PLS_INTEGER 31
error_nullvalue PLS_INTEGER 32
error_nullcollection PLS_INTEGER 40
 Libunit Types
LibunitType_cursor PLS_INTEGER 0
LibunitType_procedure PLS_INTEGER 7
LibunitType_function PLS_INTEGER 8
LibunitType_package PLS_INTEGER 9
LibunitType_package_body PLS_INTEGER 11
LibunitType_trigger PLS_INTEGER 12
LibunitType_Unknown PLS_INTEGER -1
 Statuses returned by set_value
error_illegal_value PLS_INTEGER 5
error_illegal_null PLS_INTEGER 6
error_value_malformed PLS_INTEGER 7
error_other PLS_INTEGER 8
error_name_incomplete PLS_INTEGER 11
 Statuses returned by the breakpoint functions
error_illegal_line PLS_INTEGER 12
error_no_such_breakpt PLS_INTEGER 13
error_idle_breakpt PLS_INTEGER 14
error_stale_breakpt PLS_INTEGER 15
error_bad_handle PLS_INTEGER 16
 Information Flags
info_getStackDepth PLS_INTEGER 2
info_getBreakpoint PLS_INTEGER 4
info_getLineinfo PLS_INTEGER 8
info_getOerInfo (Probe 2.4) PLS_INTEGER 32
 Reasons Flags
reason_none PLS_INTEGER 0
reason_interpreter_starting PLS_INTEGER 2
reason_breakpoint PLS_INTEGER 3
reason_enter PLS_INTEGER 6
reason_return PLS_INTEGER 7
reason_finish PLS_INTEGER 8
reason_line PLS_INTEGER 9
reason_interrupt PLS_INTEGER 10
reason_exception PLS_INTEGER 11
reason_ICD_call PLS_INTEGER 12
reason_ICD_return PLS_INTEGER 13
reason_exit PLS_INTEGER 15
reason_handler PLS_INTEGER 16
reason_timeout PLS_INTEGER 17
reason_instantiate PLS_INTEGER 20
reason_abort PLS_INTEGER 21
reason_knl_exit PLS_INTEGER 25
 Namespaces
namespace_cursor PLS_INTEGER 0
namespace_pkgspec_or_toplevel PLS_INTEGER 1
namespace_pkg_body PLS_INTEGER 2
namespace_trigger PLS_INTEGER 3
namespace_none PLS_INTEGER 255
 Other
full_diagnostics PLS_INTEGER 1
 Timeout Options
retry_on_timeout PLS_INTEGER 0
continue_on_timeout PLS_INTEGER 1
nodebug_on_timeout PLS_INTEGER 2
abort_on_timeout PLS_INTEGER 3
 Not Yet Supported
reason_sql PLS_INTEGER 4
reason_rpc PLS_INTEGER 14
reason_rpc PLS_INTEGER 18
reason_unhandled PLS_INTEGER 19
 Public Variables
default_timeout (60 minutes) PLS_INTEGER 3600
diagnostic_level PLS_INTEGER 0
Data Types -- the following fields are used when setting a breakpoint
TYPE program_info IS RECORD (
Namespace BINARY_INTEGER, -- See 'NAMESPACES' section below.
Name      VARCHAR2(30),   -- name of the program unit
Owner     VARCHAR2(30),   -- owner of the program unit
Dblink    VARCHAR2(30),   -- database link, if remote
Line#     BINARY_INTEGER,

-- read-only fields (set by Probe when doing a stack backtrace)
LibunitType    BINARY_INTEGER, -- ie. kglobtyp
EntrypointName VARCHAR2(512));
/

TYPE runtime_info IS RECORD (
Line#            BINARY_INTEGER, -- (duplicate of program.line#)
Terminated       BINARY_INTEGER, -- has the program terminated?
Breakpoint       BINARY_INTEGER, -- breakpoint number
StackDepth       BINARY_INTEGER, -- number of frames on the stack
InterpreterDepth BINARY_INTEGER, -- <reserved field>
Reason           BINARY_INTEGER, -- reason for suspension
Program          program_info,   -- source location
oer              PLS_INTEGER);   -- OER (exception), if any for Probe 2.4
/

-- these fields are duplicates of 'program_info'
TYPE breakpoint_info IS RECORD (
Name        VARCHAR2(30),
Owner       VARCHAR2(30),
DbLink      VARCHAR2(30),
Line#       BINARY_INTEGER,
LibunitType BINARY_INTEGER,
Status      BINARY_INTEGER); -- see breakpoint_status below
/

TYPE index_table IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

TYPE backtrace_table IS TABLE OF program_info INDEX BY BINARY_INTEGER;

TYPE breakpoint_table IS TABLE OF breakpoint_info INDEX BY BINARY_INTEGER;

TYPE vc2_table IS TABLE OF VARCHAR2(90) index by BINARY_INTEGER;

TYPE oer_table IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
Dependencies
DBMS_CRYPTO DBMS_STANDARD PBUTL
DBMS_DEBUG_VC2COLL PBREAK PLITBLM
DBMS_PIPE PBRPH UTL_ENCODE
DBMS_SESSION PBSDE UTL_RAW
Documented Yes: Packages and Types Reference
Exceptions
Name Reason
desupported -
illegal_init DEBUG_ON called prior to INITIALIZE
no_target_program target is not running
pipe_creation_failure can't create a secure pipe
pipe_data_error message garbled across pipe
pipe_datatype_mismatch datatype mismatch in pipe
pipe_receive_failure can't receive from the pipe
pipe_send_failure can't send to the pipe
target_error problem in target session
unimplemented not yet implemented
First Available prior to 8.1.6
Flow Chart                                    Target Session
                                   **************

                     +--------------------------------------+
                     | Initialize session for debugging,    |
                     | and generate/specify unique debugID. |
                     | DBMS_DEBUG.initialize()              |
                     +--------------------------------------+
                                          |
      +---------------------------------->|
      |                                   V
      |              +--------------------+-----------------+
      |              V                    |                 V
      |   +-----------------------+       |      +------------------------+
      |   | Start debugging       |       |      | Stop debugging         |
      |   | DBMS_DEBUG.debug_on() |       |      | DBMS_DEBUG.debug_off() |
      |   +-----------------------+       |      +------------------------+
      |               V                   V                 V
      |              +--------------------+-----------------+
      |                                   V
      |                      +-------------------------+
      |                      | Execute PL/SQL programs |
      |                      +-------------------------+
      |                                   V
      +-----------------------------------+


                                    Debug Session
                                    *************
                            +-----------------------------+
      Input:                |           Initialize        |
        debugID    from --> | DBMS_DEBUG.attach_session() |
        target session      +-----------------------------+
                                            |
      +------------------------------------>|
      |                +-------------------<+<------------------+
      |                |                    |                   |
      |                |   +--------------------------------+   |
      |                |   | Manipulate breakpoints         |   |
      |                |   | DBMS_DEBUG.set_breakpoint()    |   |
      |                |   | DBMS_DEBUG.delete_breakpoint() |   |
      |                |   | DBMS_DEBUG.disable_breakpoint()|   |
      |                |   | DBMS_DEBUG.enable_breakpoint() |   |
      |                |   | DBMS_DEBUG.show_breakpoints()  |   |
      |                |   +--------------------------------+   |
      |                |                    V                   |
      |                +------------------->+>------------------+
      |                                     |
      |                                     V
      |                 +--------------------------------------+
      |                 | Read first event from target session |
      |                 | DBMS_DEBUG.synchronize()             |
      |                 +--------------------------------------+
      |                           |
      |   +---------------------->|
      |   |   +------------------>|
      |   |   |                   V
      |   |   |                   +----------------------->+
      |   |   |                   |                        |
      |   |   |                   V                        |
      |   |   |   +------------------------------+         |
      |   |   |   | Show stack                   |-------->+
      |   |   |   | DBMS_DEBUG.print_backtrace() |         V
      |   |   |   +------------------------------+         |
      |   |   |                                            |
      |   |   |                  +------------------------<+
      |   |   |                  V                         V
      |   |   |   +------------------------+               |
      |   |   |   | Get/set values         |               |
      |   |   |   | DBMS_DEBUG.get_value() |-------------->+
      |   |   |   | DBMS_DEBUG.set_value() |               V
      |   |   |   +------------------------+               |
      |   |   |                                            |
      |   |   |               +---------------------------<+
      |   |   |               V                            V
      |   |   |   +------------------------+               |
      |   |   |   | Manipulate breakpoints |-------------->+
      |   |   |   +------------------------+               V
      |   |   |                                            |
      |   |   |                +--------------------------<+
      |   |   |                V                           V
      |   |   |   +--------------------------------+       |
      |   |   |   | Show source                    |------>+
      |   |   |   | DBMS_DEBUG.show_source()       |       V
      |   |   |   | DBMS_DEBUG.show_frame_source() |       |
      |   |   |   +--------------------------------+       |
      |   |   |                                            |
      |   |   +----------------------<+-------------------<+
      |   |                           |
      |   |                           V
      |   |   +--------------------------------------------+
      |   |   | Continue execution and wait for next event |
      |   |   | DBMS_DEBUG.continue()                      |
      |   |   +--------------------------------------------+
      |   |
      |   |       +---------------------------------------+
      |   +- No - | Program terminated?                   |
      |           | (event is DBMS_DEBUG.reason_knl_exit) |
      |           +---------------------------------------+
      |                           |
      |                          Yes
      +---------------------------+
       next program to debug      V
                                  |
                    +-----------------------------+
                    | Detach session              |
                    | DBMS_DEBUG.detach_session() |
                    +-----------------------------+
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmspb.sql
Subprograms
ABORT GET_ENCODED_STACK_FOR_CLIENT SELF_CHECK
ATTACH_SESSION GET_INDEXES SET_BREAKPOINT
CONTINUE GET_LINE_MAP SET_DIAGNOSTIC_LEVEL
DEBUG_OFF GET_MORE_SOURCE SET_OER_BREAKPOINT
DEBUG_ON GET_RUNTIME_INFO SET_TIMEOUT
DELETE_BREAKPOINT GET_TIMEOUT SET_TIMEOUT_BEHAVIOUR
DELETE_OER_BREAKPOINT GET_TIMEOUT_BEHAVIOUR SET_VALUE
DETACH_SESSION GET_VALUE SHOW_BREAKPOINTS
DISABLE_BREAKPOINT INITIALIZE SHOW_FRAME_SOURCE
ENABLE_BREAKPOINT PING SHOW_SOURCE
EXECUTE PRINT_BACKTRACE SYNCHRONIZE
GET_DIAGNOSTIC_LEVEL PRINT_INSTANTIATIONS TARGET_PROGRAM_RUNNING
GET_ENCODED_PKGVARS_FOR_CLIENT PROBE_VERSION Demo
 
ABORT
Aborts the currently-executing program dbms_debug.abort RETURN BINARY_INTEGER;
SELECT dbms_debug.abort
FROM dual;

  ABORT
-------
     17
 
ATTACH_SESSION
Sets the debug session target and collection level for debugging dbms_debug.attach_session(
debug_session_id IN VARCHAR2,
diagnostics      IN BINARY_INTEGER := 0); -- 0=no tracefile output, 1=minimal output
See DBMS_DEBUG Demo Below
 
CONTINUE
Continue execution of the target program dbms_debug.continue(
run_info       IN OUT runtime_info,
breakflags     IN     BINARY_INTEGER,
info_requested IN     BINARY_INTEGER := NULL)
RETURN BINARY_INTEGER;
TBD
 
DEBUG_OFF
Turns off debugging dbms_debug.debug_off;
See DBMS_DEBUG Demo Below
 
DEBUG_ON
Begin debugging dbms_debug.debug_on(
no_client_side_plsql_engine IN BOOLEAN := TRUE,
immediate                   IN BOOLEAN := FALSE);
See DBMS_DEBUG Demo Below
 
DELETE_BREAKPOINT
Deletes a breakpoint dbms_debug.delete_breakpoint((breakpoint IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
 
DELETE_OER_BREAKPOINT
Deletes an Oracle Error (OER) breakpoint dbms_debug.delete_oer_breakpoint(oer IN PLS_INTEGER)
RETURN PLS_INTEGER;
TBD
 
DETACH_SESSION
Stop debugging and detach from the currently attached session dbms_debug.detach_session;
exec dbms_debug.detach_session;
 
DISABLE_BREAKPOINT
Inactivates an existing breakpoint dbms_debug.disable_breakpoint(breakpoint IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
 
ENABLE_BREAKPOINT
Enables an existing, but disabled breakpoint dbms_debug.enable_breakpoint(breakpoint IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
 
EXECUTE
Execute some SQL or PL/SQL code in target session. Program is assumed to be suspended in the target session awaiting commands. dbms_debug.execute(
what         IN            VARCHAR2,
frame#       IN            BINARY_INTEGER,
bind_results IN            BINARY_INTEGER,
results      IN OUT NOCOPY dbms_debug_vc2coll,
errm         IN OUT NOCOPY VARCHAR2);
TBD
 
GET_DIAGNOSTIC_LEVEL
Returns the currently set diagnostic level dbms_debug.get_diagnostic_level RETURN BINARY_INTEGER;
SELECT dbms_debug.get_diagnostic_level
FROM dual;

GET_DIAGNOSTIC_LEVEL
--------------------
                   0

exec dbms_debug.set_diagnostic_level(2);

SELECT dbms_debug.get_diagnostic_level
FROM dual;

GET_DIAGNOSTIC_LEVEL
--------------------
                   2
 
GET_ENCODED_PKGVARS_FOR_CLIENT
Similar to the get_encoded_stack_for_client

Overload 1		 dbms_debug.get_encoded_pkgvars_for_client(
handle        IN     program_info,
flags         IN     BINARY_INTEGER,
pbrun_version IN     BINARY_INTEGER,
status        IN OUT BINARY_INTEGER,
tidl_version     OUT BINARY_INTEGER,
tidl_buf      IN OUT RAW);
TBD
Overload 2 dbms_debug.get_encoded_pkgvars_for_client(
handle        IN     program_info,
flags         IN     BINARY_INTEGER,
pbrun_version IN     BINARY_INTEGER,
status        IN OUT BINARY_INTEGER,
tidl_buf      IN OUT VARCHAR2,
tidl_version     OUT BINARY_INTEGER);
TBD
 
GET_ENCODED_STACK_FOR_CLIENT
Return an encoded form of one or more stack frames. Only useful if the caller is client-side PL/SQL

Overload 1		 dbms_debug.get_encoded_stack_for_client(
start_frame       IN     BINARY_INTEGER,
frame_count       IN     BINARY_INTEGER,
flags             IN     BINARY_INTEGER,
max_string_length IN     BINARY_INTEGER,
max_index_values  IN     BINARY_INTEGER,
pbrun_version     IN     BINARY_INTEGER,
tidl_version         OUT BINARY_INTEGER,
tidl_buf          IN OUT RAW;
TBD
Overload 2 dbms_debug.get_encoded_stack_for_client(
start_frame       IN     BINARY_INTEGER,
frame_count       IN     BINARY_INTEGER,
flags             IN     BINARY_INTEGER,
max_string_length IN     BINARY_INTEGER,
max_index_values  IN     BINARY_INTEGER,
pbrun_version     IN     BINARY_INTEGER,
tidl_buf          IN OUT VARCHAR2,
tidl_version         OUT BINARY_INTEGER);
TBD
 
GET_INDEXES
Given a name (of a variable or parameter), return the set of its indexes if it is an indexed table. Error if it is not an indexed table dbms_debug.get_indexes(
varname IN  VARCHAR2,
frame#  IN  BINARY_INTEGER,
handle  IN  program_info,
entries OUT index_table)
RETURN BINARY_INTEGER;
TBD
 
GET_LINE_MAP
Finds program unit and returns highest source line number, number of entry points, and a line map that allows to determine which lines are executable (or, to be precise, whether user can install a break-point or step on that line) dbms_debug.get_line_map(
program                IN  program_info,
maxline                OUT BINARY_INTEGER,
number_of_entry_points OUT BINARY_INTEGER,
linemap                OUT RAW)
RETURN BINARY_INTEGER;
TBD
 
GET_MORE_SOURCE
When source doesn't fit in buffer provided in the "show_source" procedure, this procedure provides additional source dbms_debug.get_more_source(
buffer IN OUT VARCHAR2,
buflen IN     BINARY_INTEGER,
piece# IN     BINARY_INTEGER);
TBD
 
GET_RUNTIME_INFO
Returns information about the current program. It is only needed if the 'info_requested' parameter to synchronize or continue was set to 0 dbms_debug.get_runtime_info(
info_requested IN  BINARY_INTEGER,
run_info       OUT runtime_info) -- success, error_timeout, or error_communication
RETURN BINARY_INTEGER;
TBD
 
GET_TIMEOUT
Returns the currently set timeout value dbms_debug.get_timeout RETURN BINARY_INTEGER;
SELECT dbms_debug.get_timeout
FROM dual;

GET_TIMEOUT
-----------
       3600
 
GET_TIMEOUT_BEHAVIOUR
Return the current timeout behaviour dbms_debug.get_timeout_behaviour RETURN BINARY_INTEGER;
SELECT dbms_debug.get_timeout_behaviour
FROM dual;

GET_TIMEOUT_BEHAVIOUR
---------------------
                    1
 
GET_VALUE
Returns a value from the currently-executing  program

Overload 1		 dbms_debug.get_value(
variable_name IN  VARCHAR2,
frame#        IN  BINARY_INTEGER,
scalar_value  OUT VARCHAR2,
format        IN  VARCHAR2 := NULL)
RETURN BINARY_INTEGER;
TBD
Overload 2 dbms_debug.get_value(
variable_name IN  VARCHAR2,
handle        IN  program_info,
scalar_value  OUT VARCHAR2,
format        IN  VARCHAR2 := NULL)
RETURN BINARY_INTEGER;
TBD
 
INITIALIZE
Initializes a debug session dbms_debug.initialize(
debug_session_id IN VARCHAR2       := NULL,
diagnostics      IN BINARY_INTEGER := 0,
debug_role       IN VARCHAR2       := NULL,
debug_role_pwd   IN VARCHAR2       := NULL)
RETURN VARCHAR2;
See DBMS_DEBUG Demo Below
 
PING
Pings the target session to prevent it from timing out. Intended for use when execution is suspended by a breakpoint. dbms_debug.ping;
exec dbms_debug.ping;
 
PRINT_BACKTRACE
Prints a backtrace listing of the current execution stack. Only be called if a program is currently executing.
Overload 1		 dbms_debug.print_backtrace(listing IN OUT VARCHAR2);
TBD
Overload 2 dbms_debug.print_backtrace(backtrace OUT backtrace_table);
TBD
 
PRINT_INSTANTIATIONS
Returns a listing of the packages that have been instantiated in the current session dbms_debug.print_instantiations(
pkgs  IN OUT NOCOPY backtrace_table,
flags IN            BINARY_INTEGER);
TBD
 
PROBE_VERSION
Currently installed Probe version dbms_debug.probe_version(
major OUT BINARY_INTEGER,
minor OUT BINARY_INTEGER);
set serveroutput on

DECLARE
 majver PLS_INTEGER;
 minver PLS_INTEGER;
BEGIN
  dbms_debug.probe_version(majver, minver);
  dbms_output.put_line(majver);
  dbms_output.put_line(minver);
END;
/
2
5
 
SELF_CHECK
Performs an internal consistency check dbms_debug.self_check(timeout IN BINARY_INTEGER := 60);
exec dbms_debug.self_check(120);
 
SET_BREAKPOINT
Sets a breakpoint in a program unit, which persists for the current session. Execution will pause if the target program reaches the breakpoint. dbms_debug.set_breakpoint(
program     IN  program_info,
line#       IN  BINARY_INTEGER,
breakpoint# OUT BINARY_INTEGER,
fuzzy       IN  BINARY_INTEGER := 0,
iterations  IN  BINARY_INTEGER := 0)
RETURN BINARY_INTEGER;
TBD
 
SET_DIAGNOSTIC_LEVEL
Sets the diagnostic output level. Unless otherwise instructed ... should be 0 dbms_debug.set_diagnostic_level(dlevel IN BINARY_INTEGER);
exec dbms_debug.set_diagnostic_level(0);
 
SET_OER_BREAKPOINT
Sets a breakpoint on an Oracle Exception. The breakpoint persists for the session (or until deleted), as with code breakpoints. dbms_debug.set_oer_breakpoint(oer IN PLS_INTEGER)
RETURN PLS_INTEGER;
TBD
 
SET_TIMEOUT
Sets the timeout value: If zero uses the default

The default is 3600.		 dbms_debug.set_timeout(timeout IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
SELECT dbms_debug.get_timeout
FROM dual;

GET_TIMEOUT
-----------
       3600

SELECT dbms_debug.set_timeout(2400)
FROM dual;

DBMS_DEBUG.SET_TIMEOUT(2400)
----------------------------
                        2400

SELECT dbms_debug.get_timeout
FROM dual;

GET_TIMEOUT
-----------
       2400
 
SET_TIMEOUT_BEHAVIOUR
Tell Probe what to do with the target session when a timeout occurs dbms_debug.set_timeout_behaviour(behaviour IN PLS_INTEGER);
exec dbms_debug.set_timeout_behaviour(dbms_debug.abort_on_timeout);
 
SET_VALUE
Sets a value in the currently-executing program

Overload 1		 dbms_debug.attach_session(
frame#               IN BINARY_INTEGER,
assignment_statement IN VARCHAR2)
RETURN BINARY_INTEGER;
TBD
Overload 2 dbms_debug.attach_session(
handle               IN program_info,
assignment_statement IN VARCHAR2)
RETURN BINARY_INTEGER;
TBD
 
SHOW_BREAKPOINTS
Return a listing of the current breakpoints

Overload 1		 dbms_debug.show_breakpoints(listing IN OUT VARCHAR2);
TBD
Overload 2 dbms_debug.show_breakpoints(listing OUT breakpoint_table);
TBD
Overload 3 dbms_debug.show_breakpoints(
code_breakpoints OUT breakpoint_table,
oer_breakpoints  OUT oer_table);
TBD
 
SHOW_FRAME_SOURCE
The only time where this is used is when backtrace shows an anonymous unit is executing at a given frame position and source is required for viewing and possibly setting a breakpoint. dbms_debug.show_frame_source(
first_line IN         BINARY_INTEGER,
last_line  IN         BINARY_INTEGER,
source IN  OUT NOCOPY vc2_table,
frame_num  IN         BINARY_INTEGER);
TBD
 
SHOW_SOURCE
For anonymous blocks and triggers at run-time

Overload 1		 dbms_debug.show_source(first_line IN BINARY_INTEGER,
last_line IN         BINARY_INTEGER,
source IN OUT NOCOPY vc2_table);
TBD
Overload 2 dbms_debug.show_source(
first_line  IN     BINARY_INTEGER,
last_line   IN     BINARY_INTEGER,
window      IN     BINARY_INTEGER,
print_arrow IN     BINARY_INTEGER,
buffer      IN OUT VARCHAR2,
buflen      IN     BINARY_INTEGER,
pieces         OUT BINARY_INTEGER);
TBD
 
SYNCHRONIZE
Waits until the target program signals an event then calls get_runtime_info dbms_debug.synchronize(
run_info       OUT runtime_info,
info_requested IN  BINARY_INTEGER := NULL)
RETURN BINARY_INTEGER;
SQL> DECLARE
  2   rinfo dbms_debug.runtime_info;
  3   retVal PLS_INTEGER;
  4  BEGIN
  5    retVal := dbms_debug.synchronize(rinfo);
  6    dbms_output.put_line(retVal);
  7  END;
  8  /
29

PL/SQL procedure successfully completed.
 
TARGET_PROGRAM_RUNNING
Returns TRUE if the target session is currently executing a stored procedure: FALSE if it is not dbms_debug.target_program_running RETURN BOOLEAN;
TBD
 
Demo Code
 
Debug Session Target Session
-- create an intersession alert
DECLARE
 msg VARCHAR2(30);
 x   PLS_INTEGER;
BEGIN
  dbms_alert.register('debug_alert');
  dbms_alert.waitone('debug_alert', msg, x);
  dbms_output.put_line(msg);
  dbms_debug.attach_session(msg);
END;
/
  -- create a procedure in debug mode
CREATE OR REPLACE PROCEDURE testproc
AUTHID CURRENT_USER IS
 sid servers.srvr_id%TYPE;
 lat servers.latitude%TYPE;

 CURSOR dbcur IS
 SELECT srvr_id, latitude
 FROM servers
 WHERE rownum < 4;
BEGIN
  FOR dbrec IN dbcur LOOP
    sid := dbrec.srvr_id;
    lat := dbrec.latitude;
  END LOOP;
END testproc;
/

desc dba_plsql_object_settings

col plsql_debug format a15

SELECT DISTINCT name, plsql_debug
FROM user_plsql_object_settings
ORDER BY 1;

ALTER PROCEDURE testproc COMPILE DEBUG;
or
ALTER SESSION SET PLSQL_DEBUG = TRUE;
ALTER PROCEDURE testproc COMPILE;

-- expect an error message

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

-- expect an error message

SELECT DISTINCT name, plsql_debug
FROM user_plsql_object_settings
ORDER BY 1;

set serveroutput on

DECLARE
 sess_id VARCHAR2(30);
 PROCEDURE display_id(sid IN VARCHAR2)IS
  PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
   dbms_output.put_line(sess_id);
 END;
BEGIN
  sess_id := dbms_debug.initialize;
  display_id(sess_id);

  dbms_alert.signal('debug_alert', sess_id);

  dbms_debug.debug_on;

  testproc;

  dbms_debug.debug_off;
END;
/
-- manage breakpoints
exec dbms_debug.set_breakpoint(
exec dbms_debug.delete_breakpoint(
exec dbms_debug.disable_breakpoint(
exec dbms_debug.enable_breakpoint(
exec dbms_debug.show_breakpoints(

- read first event from target session
SQL> DECLARE
  2   rinfo dbms_debug.runtime_info;
  3   retVal PLS_INTEGER;
  4  BEGIN
  5    retVal := dbms_debug.synchronize(rinfo);
  6    dbms_output.put_line(retVal);
  7  END;
  8  /
29

PL/SQL procedure successfully completed.
set serveroutput on

DECLARE
  info       dbms_debug.program_info;
  bnumber    BINARY_INTEGER;
  return_int BINARY_INTEGER;
BEGIN
 info.namespace := NULL;
 info.name := NULL;
 info.owner := NULL;
 info.dblink := NULL;
 info.line# := 8;
 return_int:= dbms_debug.set_breakpoint(info,8,bnumber);

  IF return_int != dbms_debug.success THEN
    dbms_output.put_line('Error ' || return_int);
  ELSE
    dbms_output.put_line(bnumber);
  END IF;
END;
/
 
Demo 1: Target Session Demo
OTN Forums
https://forums.oracle.com/forums/thread.jspa? threadID=1052907&tstart=0		 BEGIN
  dbms_debug.attach_session('001E00850001');
END;
/

--6 sync
DECLARE
 runinfo dbms_debug.runtime_info;
 retval  BINARY_INTEGER;
BEGIN
  retval := dbms_debug.synchronize(runinfo, 0 + dbms_debug.info_getstackdepth +
    dbms_debug.info_getbreakpoint + dbms_debug.info_getlineinfo + 0);
  IF retval = dbms_debug.success THEN
    dbms_output.put_line('Sync Successful');
  ELSE
    dbms_output.put_line('Sync Failure ' || retval);
  END IF;
END;

--7 execute sql
DECLARE
 P_ONE NUMBER := 2;
 P_TWO NUMBER := 3;
BEGIN
  add_numbers(P_ONE => P_ONE, P_TWO => P_TWO);
END;
/

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_ALERT
DBMS_DEBUG_JDWP
DBMS_DEBUG_JDWP_CUSTOM
PBRPH
PBSDE
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx