DBMS_DEBUG, 2 of 2

Summary of DBMS_DEBUG Subprograms

Table 10-1 DBMS_DEBUG Package Subprograms

Subprogram	Description
PROBE_VERSION Procedure	Returns the version number of `DBMS_DEBUG` on the server.
SELF_CHECK Procedure	Performs an internal consistency check.
SET_TIMEOUT Function	Sets the timeout value.
INITIALIZE Function	Sets debugID in target session.
DEBUG_ON Procedure	Turns debug-mode on.
DEBUG_OFF Procedure	Turns debug-mode off.
ATTACH_SESSION Procedure	Notifies the debug session about the target debugID.
SYNCHRONIZE Function	Waits for program to start running.
SHOW_SOURCE Procedure	Fetches program source.
PRINT_BACKTRACE Procedure	Prints a stack backtrace.
CONTINUE Function	Continues execution of the target program.
SET_BREAKPOINT Function	Sets a breakpoint in a program unit.
DELETE_BREAKPOINT Function	Deletes a breakpoint.
DISABLE_BREAKPOINT Function	Disables a breakpoint.
ENABLE_BREAKPOINT Function	Activates an existing breakpoint.
SHOW_BREAKPOINTS Procedure	Returns a listing of the current breakpoints.
GET_VALUE Function	Gets a value from the currently-running program.
SET_VALUE Function	Sets a value in the currently-running program.
DETACH_SESSION Procedure	Stops debugging the target program.
GET_RUNTIME_INFO Function	Returns information about the current program.
GET_INDEXES Function	Returns the set of indexes for an indexed table.
EXECUTE Procedure	Executes SQL or PL/SQL in the target session.

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 10-2 PROBE_VERSION Procedure Parameters

Parameter	Description
major	Major version number.
minor	Minor version number: increments as functionality is added.

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 10-3 SELF_CHECK Procedure Parameters

Parameter	Description
timeout	The timeout to use for the communication test. Default is 60 seconds.

Exceptions

Table 10-4 SELF_CHECK Procedure Exceptions

Exception	Description
OER-6516	Probe version is inconsistent.
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.

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 10-5 SET_TIMEOUT Function Parameters

Parameter	Description
timeout	The timeout to use for communication between the target and debug sessions.

TARGET SESSION Section

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

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 10-6 INITIALIZE Function Parameters

Parameter Description

Parameter	Description
debug_session_id	Name of session ID. If `NULL`, then a unique ID is generated.
diagnostics	Indicates whether to dump diagnostic output to the tracefile. 0 = (default) no diagnostics 1 = print diagnostics

debug_session_id

Name of session ID. If NULL, then a unique ID is generated.

diagnostics

Indicates whether to dump diagnostic output to the tracefile.

0 = (default) no diagnostics

1 = print 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 10-7 DEBUG_ON Procedure Parameters

Parameter	Description
no_client_side_plsql_engine	Should be left to its default value unless the debugging session is taking place from a client-side PL/SQL engine.
immediate	If this is `TRUE`, then the interpreter immediately switches itself into debug-mode, instead of continuing in regular mode for the duration of the call.

Caution:

There must be a debug session waiting if immediate is TRUE.

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;

Usage Notes

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

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 10-8 ATTACH_SESSION Procedure Parameters

Parameter	Description
debug_session_id	Debug ID from a call to `INITIALIZE` in target session.
diagnostics	Generate diagnostic output if nonzero.

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 10-9 SYNCHRONIZE Function Parameters

Parameter Description

Parameter	Description
run_info	Structure in which to write information about the program. By default, this includes information about what program is running and at which line execution has paused.
info_requested	Optional bit-field in which to request information other than the default (which is `info_getStackDepth` + `info_getLineInfo`). 0 means that no information is requested at all. See "Information Flags".

run_info

Structure in which to write information about the program. By default, this includes information about what program is running and at which line execution has paused.

info_requested

Optional bit-field in which to request information other than the default (which is info_getStackDepth + info_getLineInfo). 0 means that no information is requested at all.

See "Information Flags".

Returns

Table 10-10 SYNCHRONIZE Function Returns

Return	Description
success
error_timeout	Timed out before the program started execution.
error_communication	Other communication error.

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 nonpersistent programs (for example, anonymous blocks and trigger invocation blocks). For nonpersistent 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 10-11 SHOW_SOURCE Procedure Parameters

Parameter	Description
first_line	Line number of first line to fetch. (PL/SQL programs always start at line 1 and have no holes.)
last_line	Line number of last line to fetch. No lines are fetched past the end of the program.
source	The resulting table, which may be indexed by line#.

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 10-12 SHOW_SOURCE Procedure Parameters

Parameter	Description
first_line	Smallest line-number to print.
last_line	Largest line-number to print.
window	'Window' of lines (the number of lines around the current source line).
print_arrow	Nonzero means to print an arrow before the current line.
buffer	Buffer in which to place the source listing.
buflen	Length of buffer.
pieces	Set to nonzero if not all the source could be placed into the given buffer.

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 10-13 PRINT_BACKTRACE Procedure Parameters

Parameter	Description
listing	A formatted character buffer with embedded newlines.

Syntax

DBMS_DEBUG.PRINT_BACKTRACE (
   backtrace OUT backtrace_table);

Parameters

Table 10-14 PRINT_BACKTRACE Procedure Parameters

Parameter	Description
backtrace	1-based indexed table of backtrace entries. The currently-running procedure is the last entry in the table (that is, the frame numbering is the same as that used by `GET_VALUE`). Entry 1 is the oldest procedure on the stack.

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 10-15 CONTINUE Function Parameters

Parameter	Description
run_info	Information about the state of the program.
breakflags	Mask of events that are of interest. See "Breakflags" .
info_requested	Which information should be returned in `run_info` when the program stops. See "Information Flags".

Returns

Table 10-16 CONTINUE Function Returns

Return	Description
success
error_timeout	Timed out before the program started running.
error_communication	Other communication error.

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 10-17 SET_BREAKPOINT Function Parameters

Parameter	Description
program	Information about the program unit in which the breakpoint is to be set. (In version 2.1 and later, the namespace, name, owner, and dblink may be set to `NULL`, in which case the breakpoint is placed in the currently-running program unit.)
line#	Line at which the breakpoint is to be set.
breakpoint#	On successful completion, contains the unique breakpoint number by which to refer to the breakpoint.
fuzzy	Only applicable if there is no executable code at the specified line: 0 means return `error_illegal_line`. 1 means search forward for an adjacent line at which to place the breakpoint. -1 means search backward for an adjacent line at which to place the breakpoint.
iterations	Number of times to wait before signalling this breakpoint.

Note:

The fuzzy and iterations parameters are not yet implemented.

Returns

Table 10-18 SET_BREAKPOINT Function Returns

Return	Description
success
error_illegal_line	Cannot set a breakpoint at that line.
error_bad_handle	No such program unit exists.

DELETE_BREAKPOINT Function

This function deletes a breakpoint.

Syntax

DBMS_DEBUG.DELETE_BREAKPOINT (
   breakpoint IN BINARY_INTEGER)
  RETURN BINARY_INTEGER;

Parameters

Table 10-19 DELETE_BREAKPOINT Function Parameters

Parameter	Description
breakpoint	Breakpoint number from a previous call to `SET_BREAKPOINT`.

Returns

Table 10-20 DELETE_BREAKPOINT Function Returns

Return	Description
success
error_no_such_breakpt	No such breakpoint exists.
error_idle_breakpt	Cannot delete an unused breakpoint.
error_stale_breakpt	The program unit was redefined since the breakpoint was set.

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 10-21 DISABLE_BREAKPOINT Function Parameters

Parameter	Description
breakpoint	Breakpoint number from a previous call to `SET_BREAKPOINT`.

Returns

Table 10-22 DISABLE_BREAKPOINT Function Returns

Returns	Description
success
error_no_such_breakpt	No such breakpoint exists.
error_idle_breakpt	Cannot disable an unused breakpoint.

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 10-23 ENABLE_BREAKPOINT Function Parameters

Parameter	Description
breakpoint	Breakpoint number from a previous call to `SET_BREAKPOINT`.

Returns

Table 10-24 ENABLE_BREAKPOINT Function Returns

Return	Description
success
error_no_such_breakpt	No such breakpoint exists.
error_idle_breakpt	Cannot enable an unused breakpoint.

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 10-25 SHOW_BREAKPOINTS Procedure Parameters

Parameter	Description
listing	A formatted buffer (including newlines) of the breakpoints.

Syntax

DBMS_DEBUG.SHOW_BREAKPOINTS (
   listing  OUT breakpoint_table);

Parameters

Table 10-26 SHOW_BREAKPOINTS Procedure Parameters

Parameter	Description
listing	Indexed table of breakpoint entries. The breakpoint number is indicated by the index into the table. Breakpoint numbers start at 1 and are reused when deleted.

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 10-27 GET_VALUE Function Parameters

Parameter	Description
variable_name	Name of the variable or parameter.
frame#	Frame in which it lives; 0 means the current procedure.
scalar_value	Value.
format	Optional date format to use, if meaningful.

Returns

Table 10-28 GET_VALUE Function Returns

Return	Description
success
error_bogus_frame	Frame does not exist.
error_no_debug_info	Entrypoint has no debug information.
error_no_such_object	variable_name does not exist in frame#.
error_unknown_type	The type information in the debug information is illegible.
error_nullvalue	Value is `NULL`.
error_indexed_table	The object is a table, but no index was provided.

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 10-29 GET_VALUE Function Parameters

Parameter	Description
variable_name	Name of the variable or parameter.
handle	Description of the package containing the variable.
scalar_value	Value.
format	Optional date format to use, if meaningful.

Returns

Table 10-30 GET_VALUE Function Returns

Return	Description
error_no_such_object	Either: - Package does not exist. - Package is not instantiated. - User does not have privileges to debug the package. - Object does not exist in the package.
error_indexed_table	The object is a table, but no index was provided.

Return

Description

error_no_such_object

Either:

- Package does not exist.

- Package is not instantiated.

- User does not have privileges to debug the package.

- Object does not exist in the package.

error_indexed_table

The object is a table, but no index was provided.

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 10-31 SET_VALUE Function Parameters

Parameter	Description
frame#	Frame in which the value is to be set; 0 means the currently executing frame.
assignment_statement	An assignment statement (which must be legal PL/SQL) to run in order to set the value. For example, 'x := 3;'. Only scalar values are supported in this release. The right side of the assignment statement must be a scalar.

Parameter

Description

frame#

Frame in which the value is to be set; 0 means the currently executing frame.

assignment_statement

An assignment statement (which must be legal PL/SQL) to run in order to set the value. For example, 'x := 3;'.

Only scalar values are supported in this release. The right side of the assignment statement must be a scalar.

Returns

Table 10-32 SET_VALUE Function Returns

Return	Description
success	-
error_illegal_value	Not possible to set it to that value.
error_illegal_null	Cannot set to `NULL` because object type specifies it as 'not null'.
error_value_malformed	Value is not a scalar.
error_name_incomplete	The assignment statement does not resolve to a scalar. For example, 'x := 3;', if x is a record.

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 10-33 SET_VALUE Function Parameters

Parameter	Description
handle	Description of the package containing the variable.
assignment_statement	An assignment statement (which must be legal PL/SQL) to run in order to set the value. For example, 'x := 3;'. Only scalar values are supported in this release. The right side of the assignment statement must be a scalar.

Parameter

Description

handle

Description of the package containing the variable.

assignment_statement

An assignment statement (which must be legal PL/SQL) to run in order to set the value. For example, 'x := 3;'.

Only scalar values are supported in this release. The right side of the assignment statement must be a scalar.

Table 10-34 SET_VALUE Function Returns

Return	Description
error_no_such_object	Either: - Package does not exist. - Package is not instantiated. - User does not have privileges to debug the package. - Object does not exist in the package.

Return

Description

error_no_such_object

Either:

- Package does not exist.

- Package is not instantiated.

- User does not have privileges to debug the package.

- Object does not exist in the package.

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;

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.

Note:

This is currently only used by client-side PL/SQL.

Syntax

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

Parameters

Table 10-35 GET_RUNTIME_INFO Function Parameters

Parameter	Description
info_requested	Which information should be returned in `run_info` when the program stops. See "Information Flags".
run_info	Information about the state of the program.

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 10-36 GET_INDEXES Function Parameters

Parameter	Description
varname	Name of the variable to get index information about.
frame#	Number of frame in which the variable or parameter resides; `NULL` for a package variable.
handle	Package description, if object is a package variable.
entries	1-based table of the indexes. If non-`NULL`, then `entries`(1) contains the first index of the table, `entries`(2) contains the second index, and so on.

Returns

Table 10-37 GET_INDEXES Function Returns

Return	Description
error_no_such_object	Either: - The package does not exist. - The package is not instantiated. - The user does not have privileges to debug the package. - The object does not exist in the package.

Return

Description

error_no_such_object

Either:

- The package does not exist.

- The package is not instantiated.

- The user does not have privileges to debug the package.

- The object does not exist in the package.

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 10-38 EXECUTE Procedure Parameters

Parameter	Description
what	SQL or PL/SQL source to execute.
frame#	The context in which to execute the code. Only -1 (global context) is supported at this time.
bind_results	Whether the source wants to bind to `results` in order to return values from the target session. 0 = No 1 = Yes
results	Collection in which to place results, if `bind_results` is not 0.
errm	Error message, if an error occurred; otherwise, `NULL`.

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;

PRINT_INSTANTIATIONS Procedure

This procedure returns a list of the packages that have been instantiated in the current session.

Syntax

DBMS_DEBUG.PRINT_INSTANTIATIONS (
   pkgs  IN OUT NOCOPY backtrace_table, 
   flags  IN BINARY_INTEGER);

Parameters

Table 10-39 PRINT_INSTANTIATIONS Procedure Parameters

Parameter Description

Parameter	Description
`pkgs (OUT)`	The instantiated packages
`flags`	Bitmask of options: 1 - show specs 2 - show bodies 4 - show local instantiations 8 - show remote instantiations (NYI) 16 - do a fast job. The routine does not test whether debug information exists or whether the libunit is shrink-wrapped.

pkgs (OUT)

The instantiated packages

flags

Bitmask of options:

1 - show specs
2 - show bodies
4 - show local instantiations
8 - show remote instantiations (NYI)
16 - do a fast job. The routine does not test whether debug information exists or whether the libunit is shrink-wrapped.

Exceptions

no_target_program - target session is not currently executing

Usage Notes

On return, pkgs contains a program_info for each instantiation. The valid fields are: Namespace, Name, Owner, and LibunitType.

In addition, Line# contains a bitmask of:

1 - the libunit contains debug info
2 - the libunit is shrink-wrapped

TARGET_PROGRAM_RUNNING Procedure

This procedure returns TRUE if the target session is currently executing a stored procedure, or FALSE if it is not.

Syntax

FUNCTION target_program_running RETURN BOOLEAN;

PING Procedure

This procedure pings the target session, to prevent it from timing out. Use this procedure when execution is suspended in the target session, for example at a breakpoint.

If the timeout_behavior is set to retry_on_timeout then this procedure is not necessary.

Syntax

DBMS_DEBUG.PING;

Exceptions

Oracle will display the no_target_program exception if there is no target program or if the target session is not currently waiting for input from the debug session.

Timeout Options

Timeout options for the target session are registered with the target session by calling set_timeout_behavior.

retry_on_timeout - Retry. Timeout has no effect. This is like setting the timeout to an infinitely large value.
continue_on_timeout - Continue execution, using same event flags.
nodebug_on_timeout - Turn debug-mode OFF (in other words, call debug_off) and then continue execution. No more events will be generated by this target session unless it is re-initialized by calling debug_on.
abort_on_timeout - Continue execution, using the abort_execution flag, which should cause the program to abort immediately. The session remains in debug-mode.

retry_on_timeout CONSTANT BINARY_INTEGER:= 0;

continue_on_timeout CONSTANT BINARY_INTEGER:= 1;

nodebug_on_timeout CONSTANT BINARY_INTEGER:= 2;

abort_on_timeout CONSTANT BINARY_INTEGER:= 3;

SET_TIMEOUT_BEHAVIOR Procedure

This procedure tells Probe what to do with the target session when a timeout occurs. This call is made in the target session.

Syntax

DBMS_DEBUG.SET_TIMEOUT_BEHAVIOR (
   behavior IN PLS_INTEGER);

Parameters

Table 10-40 SET_TIMEOUT_BEHAVIOR Procedure Parameters

Parameter	Description
`behavior` - One of the following:
`retry_on_timeout`	Retry. Timeout has no effect. This is like setting the timeout to an infinitely large value.
`continue_on_timeout`	Continue execution, using same event flags.
`nodebug_on_timeout`	Turn debug-mode OFF (in other words, call `debug_off`) and continue execution. No more events will be generated by this target session unless it is re-initialized by calling `debug_on.`
`abort_on_timeout`	Continue execution, using the `abort_execution` flag, which should cause the program to abort immediately. The session remains in debug-mode.

Exceptions

unimplemented - the requested behavior is not recognized

Usage Notes

The default behavior (if this procedure is not called) is continue_on_timeout, since it allows a debugger client to reestablish control (at the next event) but does not cause the target session to hang indefinitely.

GET_TIMEOUT_BEHAVIOR Function

This procedure returns the current timeout behavior. This call is made in the target session.

Syntax

DBMS_DEBUG.GET_TIMEOUT_BEHAVIOR (
RETURN BINARY_INTEGER;

Information Flags

info_getOerInfo CONSTANT PLS_INTEGER:= 32;

Reasons

reason_oer_breakpoint CONSTANT BINARY_INTEGER:= 26;

RUNTIME_INFO

Runtime_info gives context information about the running program.

Probe v2.4:

Added OER. It gets set if info_getOerInfo is set. The OER is a positive number. It can be translated into SQLCODE by translating 1403 to 100, 6510 to 1, and negating any other value.

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 
Following fields were added in Probe v2.4 oer              PLS_INTEGER       OER 
(exception), if any 
   );

oer_table

Used by show_breakpoints

TYPE oer_table IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

- SET_OER_BREAKPOINT

Set a breakpoint on an OER. The breakpoint persists for the session (or until deleted), as with code breakpoints.

Parameters

Table 10-41

Parameter	Description
`oer`	The OER (a 4-byte positive number).

Returns

success

Usage Notes

Less functionality is supported on OER breakpoints than on code breakpoints. In particular, note that:

No "breakpoint number" is returned - the number of the OER is used instead. Thus it is impossible to set duplicate breakpoints on a given OER (it is a no-op).
It is not possible to disable an OER breakpoint (although clients are free to simulate this by deleting it).
OER breakpoints are deleted using delete_oer_breakpoint.

SET_OER_BREAKPOINT Function

This function sets an OER breakpoint.

Syntax

DBMS_DEBUG.SET_OER_BREAKPOINT (
   oer  IN PLS_INTEGER) 
RETURN PLS_INTEGER;

Parameters

Table 10-42 SET_OER_BREAKPOINT Function Parameters

Parameter	Description
`oer`	The OER (positive 4-byte number) to delete.

Returns

success

error_no_such_breakpt - no such OER breakpoint exists

DELETE_OER_BREAKPOINT Function

This function deletes an OER breakpoint.

Syntax

DBMS_DEBUG.DELETE_OER_BREAKPOINT (
   oer  IN PLS_INTEGER) 
RETURN PLS_INTEGER;

SHOW_BREAKPOINTS Procedure

Syntax

DBMS_DEBUG.SHOW_BREAKPOINTS (
   code_breakpoints  OUT breakpoint_table, 
   oer_breakpoints   OUT oer_table);

Parameters

Table 10-43 SHOW_BREAKPOINTS Procedure Parameters

Parameter	Description
`code_breakpoints`	The indexed table of breakpoint entries, indexed by breakpoint number.
`oer_breakpoints`	The indexed table of OER breakpoints, indexed by OER.

code_breakpoints - indexed table of breakpoint entries, indexed by breakpoint number.
oer_breakpoints - indexed table of OER breakpoints, indexed by OER.
PROCEDURE show_breakpoints (code_breakpoints OUT breakpoint_table, oer_breakpoints OUT oer_table);