Oracle DBMS_DEGUG
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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_any_return PLS_INTEGER 512
break_handler PLS_INTEGER 2048
abort_execution 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_DEBUG_VC2COLL PBREAK PBUTL
DBMS_PIPE PBRPH PLITBLM
DBMS_SESSION PBSDE UTL_RAW
DBMS_STANDARD    
Documented Yes
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
Aborts the currently-executing program dbms_debug.abort RETURN BINARY_INTEGER;
SELECT dbms_debug.abort
FROM dual;
 
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_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_BEHAVIOUR
Return the current timeout behaviour dbms_debug.get_timeout_behaviour RETURN BINARY_INTEGER;
SELECT dbms_debug.get_timeout_behaviour
FROM dual;
 
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;
/
 
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.

The default is 0.
dbms_debug.set_timeout(timeout BINARY_INTEGER)
RETURN BINARY_INTEGER;
set serveoutput on

DECLARE
 i PLS_INTEGER;
BEGIN
  i := dbms_debug.set_timeout(20);
  dbms_output.put_line(i);
END;
/
 
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
http://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
DBMS_ALERT
DBMS_DEBUG_JDWP
DBMS_DEBUG_JDWP_CUSTOM
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved