7
DBMS_DEBUG

DBMS_DEBUG is a PL/SQL API to the PL/SQL debugger layer, Probe, in the Oracle server.

This API is primarily intended to implement server-side debuggers, and it provides a way to debug server-side PL/SQL program units.

Using DBMS_DEBUG

In order to debug server-side code, it is necessary to have two database sessions: one session to run the code in debug-mode (the target session), and a second session to supervise the target session (the debug session).

The target session becomes available for debugging by making initializing calls with DBMS_DEBUG. This marks the session, so that the PL/SQL interpreter runs in debug-mode and generates debug events. As debug events are generated, they are posted from the session. In most cases, debug events require return notification: the interpreter pauses awaiting a reply.

Meanwhile, the debug session must also initialize itself using DBMS_DEBUG: This tells it what target session to supervise. The debug session may then call entrypoints in DBMS_DEBUG to read events which were posted from the target session and to communicate with the target session.

DBMS_DEBUG does not provide any interface to the PL/SQL compiler; however, it does depend on debug information optionally generated by the compiler. Without debug information, it is not possible to look at or modify the values of parameters or variables. There are two ways to ensure that debug information is generated: through a session switch, or through individual recompilation.

To set the session switch, enter the following statement:

ALTER SESSION SET PLSQL_DEBUG = true;

This instructs the compiler to generate debug information for the remainder of the session. It does not recompile any existing PL/SQL.

To generate debug information for existing PL/SQL code, use one of the following statements (the second recompiles a package or type body):

ALTER [PROCEDURE | FUNCTION | PACKAGE | TRIGGER | TYPE] <name> COMPILE DEBUG;
ALTER [PACKAGE | TYPE] <name> COMPILE DEBUG BODY;

Figure 7-1 Target Session

Figure 7-2 Debug Session

Figure 7-2 Debug Session (Cont.)

Control of the Interpreter

The interpreter pauses execution at the following times:

1. At startup of the interpreter, so that any deferred breakpoints may be installed prior to execution.

2. At any line containing an enabled breakpoint.

3. At any line where an interesting event occurs. The set of interesting events is specified by the flags passed to DBMS_DEBUG.CONTINUE in the breakflags parameter.

Usage Notes

Session Termination

There is no event for session termination. Therefore, it is the responsibility of the debug session to check and make sure that the target session has not ended. A call to DBMS_DEBUG.SYNCHRONIZE after the target session has ended causes the debug session to hang until it times out.

Deferred Operations

The diagram suggests that it is possible to set breakpoints prior to having a target session. This is true. In this case, Probe caches the breakpoint request and transmits it to the target session at first synchronization. However, if a breakpoint request is deferred in this fashion, then:

• SET_BREAKPOINT does not set the breakpoint number (it can be obtained later from SHOW_BREAKPOINTS if necessary).

• SET_BREAKPOINT does not validate the breakpoint request. If the requested source line does not exist, then an error silently occurs at synchronization, and no breakpoint is set.

Diagnostic Output

To debug Probe, there are diagnostics parameters to some of the calls in DBMS_DEBUG. These parameters specify whether to place diagnostic output in the RDBMS tracefile. If output to the RDBMS tracefile is disabled, then these parameters have no effect.

Types

PROGRAM_INFO

This type specifies a program location. It is a line number in a program unit.This is used for stack backtraces and for setting and examining breakpoints.The read-only fields are currently ignored by Probe for breakpoint operations. They are set by Probe only for stack backtraces.

EntrypointName	Null, unless this is a nested procedure or function.
LibunitType	Disambiguate among objects that share the same namespace (for example, procedure and package specifications). See the Libunit Types for more information.

TYPE program_info IS RECORD
(
    -- The following fields are used when setting a breakpoint
    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, 
    EntrypointName   VARCHAR2(30)
);

RUNTIME_INFO

This type gives context information about the running program.

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
);

BREAKPOINT_INFO

This type gives information about a breakpoint, such as its current status and the program unit in which it was placed.

TYPE breakpoint_info IS RECORD
(
   -- These fields are duplicates of 'program_info':
   Name        VARCHAR2(30),
   Owner       VARCHAR2(30),
   DbLink      VARCHAR2(30),
   Line#       BINARY_INTEGER,
   LibunitType BINARY_INTEGER,
   Status      BINARY_INTEGER   -- see breakpoint_status_* below
);

INDEX_TABLE

This type is used by GET_INDEXES to return the available indexes for an indexed table.

TYPE index_table IS table of BINARY_INTEGER INDEX BY BINARY_INTEGER;

BACKTRACE_TABLE

This type is used by PRINT_BACKTRACE.

TYPE backtrace_table IS TABLE OF program_info INDEX BY BINARY_INTEGER;

BREAKPOINT_TABLE

This type is used by SHOW_BREAKPOINTS.

TYPE breakpoint_table IS TABLE OF breakpoint_info INDEX BY BINARY_INTEGER;

VC2_TABLE

This type is used by SHOW_SOURCE.

TYPE vc2_table IS TABLE OF VARCHAR2(90) INDEX BY BINARY_INTEGER;

Constants

A breakpoint status may have these values:

breakpoint_status_unused

Breakpoint is not in use.

Otherwise, the status is a mask of the following values:

breakpoint_status_active	A line breakpoint.
breakpoints_status_disabled	Breakpoint is currently disabled.
breakpoint_status_remote	A 'shadow' breakpoint (a local representation of a remote breakpoint).

NAMESPACES

Program units on the server reside in different namespaces.When setting a breakpoint, it is necessary to specify the desired namespace.

1. Namespace_cursor contains cursors (anonymous blocks).

2. Namespace_pgkspec_or_toplevel contains:
   • Package specifications.

   • Procedures and functions that are not nested inside other packages, procedures, or functions.

   • Object types.

3. Namespace_pkg_body contains package bodies and type bodies.

4. Namespace_trigger contains triggers.

Libunit Types

These values are used to disambiguate among objects in a given namespace. These constants are used in PROGRAM_INFO when Probe is giving a stack backtrace.

LibunitType_cursor
LibunitType_procedure
LibunitType_function
LibunitType_package
LibunitType_package_body
LibunitType_trigger
LibunitType_Unknown

Breakflags

These are values to use for the breakflags parameter to CONTINUE, in order to tell Probe what events are of interest to the client. These flags may be combined.

break_next_line	Break at next source line (step over calls).
break_any_call	Break at next source line (step into calls).
break_any_return	Break after returning from current entrypoint (skip over any entrypoints called from the current routine).
break_return	Break the next time an entrypoint gets ready to return. (This includes entrypoints called from the current one. If interpreter is running Proc1, which calls Proc2, then break_return stops at the end of Proc2.)
break_exception	Break when an exception is raised.
break_handler	Break when an exception handler is executed.
abort_execution	Stop execution and force an 'exit' event as soon as DBMS_DEBUG.CONTINUE is called.

Information Flags

These are flags which may be passed as the info_requested parameter to SYNCHRONIZE, CONTINUE, and GET_RUNTIME_INFO.

info_getStackDepth	Get the current depth of the stack.
info_getBreakpoint	Get the breakpoint number.
info_getLineinfo	Get program unit information.

Reasons for Suspension

After CONTINUE is run, the program either runs to completion or breaks on some line.

reason_none
reason_interpreter_starting	Interpreter is starting.
reason_breakpoint	Hit a breakpoint.
reason_enter	Procedure entry.
reason_return	Procedure is about to return.
reason_finish	Procedure is finished.
reason_line	Reached a new line.
reason_interrupt	An interrupt occurred.
reason_exception	An exception was raised.
reason_exit	Interpreter is exiting (old form).
reason_knl_exit	Kernel is exiting.
reason_handler	Start exception-handler.
reason_timeout	A timeout occurred.
reason_instantiate	Instantiation block.
reason_abort	Interpreter is aborting.

Error Codes

These values are returned by the various functions called in the debug session (SYNCHRONIZE, CONTINUE, SET_BREAKPOINT, and so on). If PL/SQL exceptions worked across client/server and server/server boundaries, then these would all be exceptions rather than error codes.

success

Normal termination.

Statuses returned by GET_VALUE and SET_VALUE:

error_bogus_frame	No such entrypoint on the stack.
error_no_debug_info	Program was compiled without debug symbols.
error_no_such_object	No such variable or parameter.
error_unknown_type	Debug information is unreadable.
error_indexed_table	Returned by GET_VALUE if the object is a table, but no index was provided.
error_illegal_index	No such element exists in the collection.
error_nullcollection	Table is atomically null.
error_nullvalue	Value is null.

Statuses returned by SET_VALUE:

error_illegal_value	Constraint violation.
error_illegal_null	Constraint violation.
error_value_malformed	Unable to decipher the given value.
error_other	Some other error.
error_name_incomplete	Name did not resolve to a scalar.

Statuses returned by the breakpoint functions:

error_no_such_breakpt	No such breakpoint.
error_idle_breakpt	Cannot enable or disable an unused breakpoint.
error_bad_handle	Unable to set breakpoint in given program (non-existent or security violation).

General error codes (returned by many of the DBMS_DEBUG subprograms):

error_unimplemented	Functionality is not yet implemented.
error_deferred	No program running; operation deferred.
error_exception	An exception was raised in the DBMS_DEBUG or Probe packages on the server.
error_communication	Some error other than a timeout occurred.
error_timeout	Timout occurred.

Exceptions

illegal_init

DEBUG_ON was called prior to INITIALIZE.

The following exceptions are raised by procedure SELF_CHECK:

pipe_creation_failure	Could not create a pipe.
pipe_send_failure	Could not write data to the pipe.
pipe_receive_failure	Could not read data from the pipe.
pipe_datatype_mismatch	Datatype in the pipe was wrong.
pipe_data_error	Data got garbled in the pipe.

Variables

default_timeout

The timeout value (used by both sessions).The smallest possible timeout is 1 second. If this value is set to 0, then a large value (3600) is used.

Summary of Subprograms

Table 7-1 DBMS_DEBUG Package Subprograms

PROBE_VERSION procedure

SELF_CHECK procedure

SET_TIMEOUT function

INITIALIZE function

DEBUG_ON procedure

DEBUG_OFF procedure

ATTACH_SESSION procedure

SYNCHRONIZE function

SHOW_SOURCE procedure

PRINT_BACKTRACE 
procedure

CONTINUE function

SET_BREAKPOINT function

DELETE_BREAKPOINT 
function

DISABLE_BREAKPOINT 
function

ENABLE_BREAKPOINT 
function

SHOW_BREAKPOINTS 
procedure

GET_VALUE function

SET_VALUE function

DETACH_SESSION procedure

GET_RUNTIME_INFO 
function

GET_INDEXES function

EXECUTE procedure

Common Section

These following subprograms may be called in either the target or the debug session:

• PROBE_VERSION procedure

• SELF_CHECK procedure

• SET_TIMEOUT function

PROBE_VERSION procedure

This procedure returns the version number of DBMS_DEBUG on the server.

Syntax

DBMS_DEBUG.PROBE_VERSION (
   major out BINARY_INTEGER,
   minor out BINARY_INTEGER); 

Parameters

Table 7-2 PROBE_VERSION Procedure Parameters

major

minor

SELF_CHECK procedure

This procedure performs an internal consistency check. SELF_CHECK also runs a communications test to ensure that the Probe processes are able to communicate.

If SELF_CHECK does not return successfully, then an incorrect version of DBMS_DEBUG was probably installed on this server. The solution is to install the correct version (pbload.sql loads DBMS_DEBUG and the other relevant packages).

Syntax

DBMS_DEBUG.SELF_CHECK (
   timeout IN binary_integer := 60);

Parameters

Table 7-3 SELF_CHECK Procedure Parameters

timeout

Exceptions

Table 7-4 SELF_CHECK Procedure Exceptions

OER-6516

pipe_creation_failure

pipe_send_failure

pipe_receive_failure

pipe_datatype_mismatch

pipe_data_error

All of these exceptions are fatal. They indicate a serious problem with Probe that prevents it from working correctly.

SET_TIMEOUT function

This function sets the timeout value and returns the new timeout value.

Syntax

DBMS_DEBUG.SET_TIMEOUT (
   timeout BINARY_INTEGER) 
  RETURN BINARY_INTEGER;

Parameters

Table 7-5 SET_TIMEOUT Function Parameters

timeout

TARGET SESSION Section

The following subprograms are run in the target session (the session that is to be debugged):

• INITIALIZE function

• DEBUG_ON procedure

• DEBUG_OFF procedure

INITIALIZE function

This function initializes the target session for debugging.

Syntax

DBMS_DEBUG.INITIALIZE (
   debug_session_id  IN VARCHAR2       := NULL, 
   diagnostics       IN BINARY_INTEGER := 0)
  RETURN VARCHAR2; 

Parameters

Table 7-6 INITIALIZE Function Parameters

debug_session_id

diagnostics

Returns

The newly-registered debug session ID (debugID)

DEBUG_ON procedure

This procedure marks the target session so that all PL/SQL is run in debug mode. This must be done before any debugging can take place.

Syntax

DBMS_DEBUG.DEBUG_ON (
   no_client_side_plsql_engine BOOLEAN := TRUE,
   immediate                   BOOLEAN := FALSE); 

Parameters

Table 7-7 DEBUG_ON Procedure Parameters

no_client_side_plsql_
engine

immediate

DEBUG_OFF procedure

This procedure notifies the target session that debugging should no longer take place in that session. It is not necessary to call this function before ending the session.

Syntax

DBMS_DEBUG.DEBUG_OFF;

Parameters

None.

Usage Notes

The server does not handle this entrypoint specially. Therefore, it attempts to debug this entrypoint.

Debug Session Section

The following subprograms should be run in the debug session only:

• ATTACH_SESSION procedure

• SYNCHRONIZE function

• SHOW_SOURCE procedure

• PRINT_BACKTRACE procedure

• CONTINUE function

• SET_BREAKPOINT function

• DELETE_BREAKPOINT function

• DISABLE_BREAKPOINT function

• ENABLE_BREAKPOINT function

• SHOW_BREAKPOINTS procedure

• GET_VALUE function

• SET_VALUE function

• DETACH_SESSION procedure

• GET_RUNTIME_INFO function

• GET_INDEXES function

• EXECUTE procedure

ATTACH_SESSION procedure

This procedure notifies the debug session about the target program.

Syntax

DBMS_DEBUG.ATTACH_SESSION (
   debug_session_id  IN VARCHAR2,
   diagnostics       IN BINARY_INTEGER := 0); 

Parameters

Table 7-8 ATTACH_SESSION Procedure Parameters

debug_session_id

diagnostics

SYNCHRONIZE function

This function waits until the target program signals an event. If info_requested is not NULL, then it calls GET_RUNTIME_INFO.

Syntax

DBMS_DEBUG.SYNCHRONIZE (
   run_info       OUT  runtime_info,
   info_requested IN   BINARY_INTEGER := NULL)
  RETURN BINARY_INTEGER;

Parameters

Table 7-9 SYNCHRONIZE Function Parameters

run_info

info_requested

Returns

Table 7-10 SYNCHRONIZE Function Returns

success

error_timeout

error_communication

SHOW_SOURCE procedure

The best way to get the source code (for a program that is being run) is to use SQL. For example:

DECLARE
	   info DBMS_DEBUG.runtime_info;
BEGIN
   -- call DBMS_DEBUG.SYNCHRONIZE, CONTINUE,
   -- or GET_RUNTIME_INFO to fill in 'info'
   SELECT text INTO <buffer> FROM all_source
   WHERE owner = info.Program.Owner
     AND name  = info.Program.Name
     AND line  = info.Line#;
END;

However, this does not work for non-persistent programs (for example, anonymous blocks and trigger invocation blocks). For non-persistent programs, call SHOW_SOURCE. There are two flavors: one returns an indexed table of source lines, and the other returns a packed (and formatted) buffer.

There are two overloaded SHOW_SOURCE procedures.

Syntax

DBMS_DEBUG.SHOW_SOURCE (
   first_line  IN   BINARY_INTEGER,
   last_line   IN   BINARY_INTEGER,
   source      OUT  vc2_table);

Parameters

Table 7-11 SHOW_SOURCE Procedure Parameters

first_line

last_line

source

Returns

An indexed table of source-lines. The source lines are stored starting at first_line. If any error occurs, then the table is empty.

Usage Notes

This second overloading of SHOW_SOURCE returns the source in a formatted buffer, complete with line-numbers. It is faster than the indexed table version, but it does not guarantee to fetch all the source.

If the source does not fit in bufferlength (buflen), then additional pieces can be retrieved using the GET_MORE_SOURCE procedure (pieces returns the number of additional pieces that need to be retrieved).

Syntax

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);  

Parameters

Table 7-12 SHOW_SOURCE Procedure Parameters

first_line

last_line

window

print_arrow

buffer

buflen

pieces

PRINT_BACKTRACE procedure

This procedure prints a backtrace listing of the current execution stack. This should only be called if a program is currently running.

There are two overloaded PRINT_BACKTRACE procedures.

Syntax

DBMS_DEBUG.PRINT_BACKTRACE (
  listing IN OUT VARCHAR2); 

Parameters

Table 7-13 PRINT_BACKTRACE Procedure Parameters

listing

Syntax

DBMS_DEBUG.PRINT_BACKTRACE (
   backtrace OUT backtrace_table); 

Parameters

Table 7-14 PRINT_BACKTRACE Procedure Parameters

backtrace

CONTINUE function

This function passes the given breakflags (a mask of the events that are of interest) to Probe in the target process. It tells Probe to continue execution of the target process, and it waits until the target process runs to completion or signals an event.

If info_requested is not NULL, then calls GET_RUNTIME_INFO.

Syntax

DBMS_DEBUG.CONTINUE (
   run_info       IN OUT runtime_info,
   breakflags     IN     BINARY_INTEGER,
   info_requested IN     BINARY_INTEGER := NULL)
  RETURN BINARY_INTEGER;

Parameters

Table 7-15 CONTINUE Function Parameters

run_info

breakflags

info_requested

Returns

Table 7-16 CONTINUE Function Returns

success

error_timeout

error_communication

SET_BREAKPOINT function

This function sets a breakpoint in a program unit, which persists for the current session. Execution pauses if the target program reaches the breakpoint.

Syntax

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;

Parameters

Table 7-17 SET_BREAKPOINT Function Parameters

program

line#

breakpoint#

fuzzy

iterations

Returns

Table 7-18 SET_BREAKPOINT Function Returns

success

error_illegal_line

error_bad_handle

DELETE_BREAKPOINT function

This function deletes a breakpoint.

Syntax

DBMS_DEBUG.DELETE_BREAKPOINT (
   breakpoint IN BINARY_INTEGER)
  RETURN BINARY_INTEGER;

Parameters

Table 7-19 DELETE_BREAKPOINT Function Parameters

breakpoint

Returns

Table 7-20 DELETE_BREAKPOINT Function Returns

success

error_no_such_breakpt

error_idle_breakpt

error_stale_breakpt

DISABLE_BREAKPOINT function

This function makes an existing breakpoint inactive, but it leaves it in place.

Syntax

DBMS_DEBUG.DISABLE_BREAKPOINT (
   breakpoint IN BINARY_INTEGER)
  RETURN BINARY_INTEGER; 

Parameters

Table 7-21 DISABLE_BREAKPOINT Function Parameters

breakpoint

Returns

Table 7-22 DISABLE_BREAKPOINT Function Returns

success

error_no_such_breakpt

error_idle_breakpt

ENABLE_BREAKPOINT function

This function is the reverse of disabling. This enables a previously disabled breakpoint.

Syntax

DBMS_DEBUG.ENABLE_BREAKPOINT (
   breakpoint IN BINARY_INTEGER)
  RETURN BINARY_INTEGER;

Parameters

Table 7-23 ENABLE_BREAKPOINT Function Parameters

breakpoint

Returns

Table 7-24 ENABLE_BREAKPOINT Function Returns

success

error_no_such_breakpt

error_idle_breakpt

SHOW_BREAKPOINTS procedure

This procedure returns a listing of the current breakpoints. There are two overloaded SHOW_BREAKPOINTS procedures.

Syntax

DBMS_DEBUG.SHOW_BREAKPOINTS (
   listing    IN OUT VARCHAR2);

Parameters

Table 7-25 SHOW_BREAKPOINTS Procedure Parameters

listing

Syntax

DBMS_DEBUG.SHOW_BREAKPOINTS (
   listing  OUT breakpoint_table);

Parameters

Table 7-26 SHOW_BREAKPOINTS Procedure Parameters

listing

GET_VALUE function

This function gets a value from the currently-running program. There are two overloaded GET_VALUE functions.

Syntax

DBMS_DEBUG.GET_VALUE (
   variable_name  IN  VARCHAR2,
   frame#         IN  BINARY_INTEGER,
   scalar_value   OUT VARCHAR2,
   format         IN  VARCHAR2 := NULL)
  RETURN BINARY_INTEGER;

Parameters

Table 7-27 GET_VALUE Function Parameters

variable_name

frame#

scalar_value

format

Returns

Table 7-28 GET_VALUE Function Returns

success

error_bogus_frame

error_no_debug_info

error_no_such_object

error_unknown_type

error_nullvalue

error_indexed_table

This form of GET_VALUE is for fetching package variables. Instead of a frame#, it takes a handle, which describes the package containing the variable.

Syntax

DBMS_DEBUG.GET_VALUE (
   variable_name  IN  VARCHAR2,
   handle         IN  program_info,
   scalar_value   OUT VARCHAR2,
   format         IN  VARCHAR2 := NULL)
  RETURN BINARY_INTEGER;

Parameters

Table 7-29 GET_VALUE Function Parameters

variable_name

handle

scalar_value

format

Returns

Table 7-30 GET_VALUE Function Returns

error_no_such_object

error_indexed_table

Example

This example illustrates how to get the value with a given package PACK in schema SCOTT, containing variable VAR:

DECLARE
   handle     dbms_debug.program_info;
   resultbuf  VARCHAR2(500);
   retval     BINARY_INTEGER;
BEGIN
   handle.Owner     := 'SCOTT';
   handle.Name      := 'PACK';
   handle.namespace := dbms_debug.namespace_pkgspec_or_toplevel;
   retval           := dbms_debug.get_value('VAR', handle, resultbuf, NULL);
END;

SET_VALUE function

This function sets a value in the currently-running program. There are two overloaded SET_VALUE functions.

Syntax

DBMS_DEBUG.SET_VALUE (
   frame#               IN binary_integer,
   assignment_statement IN varchar2) 
  RETURN BINARY_INTEGER;

Parameters

Table 7-31 SET_VALUE Function Parameters

frame#

assignment_statement

Returns

Table 7-32 SET_VALUE Function Returns

success

error_illegal_value

error_illegal_null

error_value_malformed

error_name_incomplete

This form of SET_VALUE sets the value of a package variable.

Syntax

DBMS_DEBUG.SET_VALUE (
   handle               IN program_info,
   assignment_statement IN VARCHAR2) 
  RETURN BINARY_INTEGER;  

Parameters

Table 7-33 SET_VALUE Function Parameters

handle

assignment_statement

Table 7-34 SET_VALUE Function Returns

error_no_such_object

In some cases, the PL/SQL compiler uses temporaries to access package variables, and Probe does not guarantee to update such temporaries. It is possible, although unlikely, that modification to a package variable using SET_VALUE might not take effect for a line or two.

Example

To set the value of SCOTT.PACK.var to 6:

DECLARE
   handle  dbms_debug.program_info;
   retval  BINARY_INTEGER;
BEGIN
   handle.Owner     := 'SCOTT';
   handle.Name      := 'PACK';
   handle.namespace := dbms_debug.namespace_pkgspec_or_toplevel;
   retval           := dbms_debug.set_value(handle, 'var := 6;');
END;

DETACH_SESSION procedure

This procedure stops debugging the target program. This procedure may be called at any time, but it does not notify the target session that the debug session is detaching itself, and it does not abort execution of the target session. Therefore, care should be taken to ensure that the target session does not hang itself.

Syntax

DBMS_DEBUG.DETACH_SESSION;  

Parameters

None.

GET_RUNTIME_INFO function

This function returns information about the current program. It is only needed if the info_requested parameter to SYNCHRONIZE or CONTINUE was set to 0.

Syntax

DBMS_DEBUG.GET_RUNTIME_INFO (
   info_requested  IN  BINARY_INTEGER,
   run_info        OUT runtime_info)
  RETURN BINARY_INTEGER; 

Parameters

Table 7-35 GET_RUNTIME_INFO Function Parameters

info_requested

run_info

GET_INDEXES function

Given a name of a variable or parameter, this function returns the set of its indexes, if it is an indexed table. An error is returned if it is not an indexed table.

Syntax

DBMS_DEBUG.GET_INDEXES (
   varname   IN  VARCHAR2,
   frame#    IN  BINARY_INTEGER,
   handle    IN  program_info,
   entries   OUT index_table) 
  RETURN BINARY_INTEGER;

Parameters

Table 7-36 GET_INDEXES Function Parameters

varname

frame#

handle

entries

Returns

Table 7-37 GET_INDEXES Function Returns

error_no_such_object

EXECUTE procedure

This procedure executes SQL or PL/SQL code in the target session. The target session is assumed to be waiting at a breakpoint (or other event). The call to DBMS_DEBUG.EXECUTE occurs in the debug session, which then asks the target session to execute the code.

Syntax

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);

Parameters

Table 7-38 EXECUTE Procedure Parameters

what

frame#

bind_results

results

errm

Example 1

This example executes a SQL statement. It returns no results.

DECLARE
   coll sys.dbms_debug_vc2coll; -- results (unused)
   errm VARCHAR2(100);          
BEGIN
   dbms_debug.execute('insert into emp(ename,empno,deptno) ' ||
                      'values(''LJE'', 1, 1)',
                      -1, 0, coll, errm);
END;

Example 2

This example executes a PL/SQL block, and it returns no results. The block is an autonomous transaction, which means that the value inserted into the table becomes visible in the debug session.

DECLARE
   coll sys.dbms_debug_vc2coll;
   errm VARCHAR2(100);
BEGIN
   dbms_debug.execute(
       'DECLARE PRAGMA autonomous_transaction; ' ||
       'BEGIN ' ||
       '   insert into emp(ename, empno, deptno) ' ||
       '   values(''LJE'', 1, 1); ' ||
       ' COMMIT; ' ||
       'END;',
       -1, 0, coll, errm);
END;

Example 3

This example executes a PL/SQL block, and it returns some results.

DECLARE
   coll sys.dbms_debug_vc2coll;
   errm VARCHAR2(100);
BEGIN
   dbms_debug.execute(
      'DECLARE ' ||
      '   pp SYS.dbms_debug_vc2coll := SYS.dbms_debug_vc2coll(); ' ||
      '   x  PLS_INTEGER; ' ||
      '   i  PLS_INTEGER := 1; ' ||
      'BEGIN ' ||
      '   SELECT COUNT(*) INTO x FROM emp; ' ||
      '   pp.EXTEND(x * 6); ' ||
      '   FOR c IN (SELECT * FROM emp) LOOP ' ||
      '      pp(i) := ''Ename: '' || c.ename; i := i+1; ' ||
      '      pp(i) := ''Empno: '' || c.empno; i := i+1; ' ||
      '      pp(i) := ''Job:   '' || c.job;   i := i+1; ' ||
      '      pp(i) := ''Mgr:   '' || c.mgr;   i := i+1; ' ||
      '      pp(i) := ''Sal:   '' || c.sal;   i := i+1; ' ||
      '      pp(i) := null;                   i := i+1; ' ||
      '   END LOOP; ' ||
      '   :1 := pp;' ||
      'END;',
       -1, 1, coll, errm);
   each := coll.FIRST;
   WHILE (each IS NOT NULL) LOOP
      dosomething(coll(each));
      each := coll.NEXT(each);
   END LOOP;
END;