Simple PL/SQL debug package (kind a port of JavaScript debug package), because sometimes you just want to watch application's flow online.
Warning: Screencap with previous version of API!!! New screencap coming soon.
- SQL*Plus and SQLcl
- namespace filtering
- specify during
init_session
,init_persistent
orset_filter
- filter defined as list of SQL
like
expressions delimited by,
- only filtered namespace debug messages are stored/output
- specify during
- time diffs computed by debug namespace instance
- persistent storage of debug (actually just side effect)
- namespace filtering
- SQLcl specific features
- ANSI colors
- online watching
Creates debug
object for debug namespace
- all messages logged using log
method are in this debug namespace. Log can be filtered for specific namespace/namespaces.
Parameters
- namespace - debug namesapce.
Best practice: Prefix with your package name.
- value - value to be logged. You are responsible for conversion into
varchar2
Logs
This is simplest sample use of debug.
- Start debugging current session
SQL> @module/script/debug_this
Debug this session and add it to debug group (optionally)
>> Enter debug group [null]:
>> Enter namespace filter [*]:
>> Enter colors [256] (NO | 16 | 256):
.. Setting debug on for this session and optionally adding it into debug group. (debug_group="", filter="*", colors="256")
done
- Create simple PL/SQL block
SQL> declare
2 d debug := new debug('my-test'); -- create debug object with namespace "my-test"
3 begin
4 d.log('Hello World!'); -- log some debug message
5 end;
6 /
my-test Hello World! +0ms << this is output from debug [namesapce] [message] [offset from last debug message in namespace]
Creates debug group, which can aggregate debug messages from multiple sessions. Initializes debug with persistence into table, so it can be watched online from other session. Multisession debugging and watching (e.g.: server + workers, async using jobs, multiple sessions, ..) requires persistence.
Params
- filter
like
expressions without escape (sorry), separated by,
*
- all namespaces will be enabled (default)
- description - just some description
SQL> var debug_group number
SQL> exec :debug_group := debug_adm.create_group;
Drops debug group and debug log mesasges. Effectively stops debugging and watching in all sessions in group.
exec debug_adm.drop_group(:debug_group);
Params
- debug_group - debug group identifier
Returns
Y
- if debug group existsN
- otherwise
select debug_adm.group_exists(:debug_group);
Starts debug in this session only or add this session to existing debug group (if debug_group parameter is passed)
Warning: Debug not in group cannot be watched from other session
Parameters
- debug_group - debug group identifier. see
debug_adm.create_group
- filter
- like expressions without escape (sorry), separated by ,
-
-
- all namespaces will be enabled (default)
-
- colors - colors settings for namespace "coloring" in ANSI terminals. Use debug_format.COLORS_NO if your terminal does not support ANSI colors
- debug_format.COLORS_NO - no colors
- debug_format.COLORS_16 - 16 colors
- debug_format.COLORS_256 - 256 colors
Sample usage
SQL> exec debug_adm.debug_this;
SQL> declare
2 d debug := new debug('my-test');
3 begin
4 d.log('Hello World!');
5 end;
6 /
my-test Hello World! +0ms
Add other session, identified by sessionId (sys_context('userEnv','sessionId'
or v$session.audsid
) to debug group.
Parameters
- debug_group - debug group identifier. see
debug_adm.create_group
- sessionId - sessionId of session to be added to group (
sys_context('userEnv','sessionId'
orv$session.audsid
) - filter
- like expressions without escape (sorry), separated by ,
-
-
- all namespaces will be enabled (default)
-
Sample usage
Session to be debugged.
SQL> set srveroutput on size unlimited
SQL> exec dbms_output.put_line(sys_context('userEnv','sessionId'));
1710605
SQL> exec my_server_test;
From other session, watching debug messages from debugged session.
SQL> var debug_group number
SQL> exec :debug_group := debug_adm.create_group;
SQL> column debug_group new_value debug_group
SQL> select :debug_group as debug_group from dual;
SQL> exec debug_adm.debug_other(:debug_group, 1710605);
SQL> script watch_group.js &&debug_group
Pause debug for session and/or debug group.
Parameters
- debug_group - debug group to be paused. see
debug_adm.create_group
- sessionId - sessionId of session to be paused
One session can be in more groups, so
- if called with
debug_group
then stops debugging of whole debug group - if called with
sessionId
then stops debugging of session withsessionId
in all debug groups - if called with both
debug_group
andsessionId
then stops debugging of session withsessionId
in specified debug group
Sample usage
SQL> exec debug_adm.pause_debug(3);
Resume debug for session and/or debug group.
Parameters
- debug_group - debug group to be paused. see
debug_adm.create_group
- sessionId - sessionId of session to be paused
see debug_adm.pause_debug
for detailed info about values of parameters
Purge messages from log for session and/or debug group.
- debug_group - debug group, which log message to be purged. see
debug_adm.create_group
- sessionId - sessionId of session, which log message to be purged
see debug_adm.pause_debug
for detailed info about values of parameters
Change namespace filter session and/or debug group.
- filter - new value for filter - see
debug_adm.debug_this
- debug_group - debug group, which log message to be purged
- sessionId - sessionId of session, which log message to be purged
see debug_adm.pause_debug
for detailed info about values of parameters
Connect as DBA or privileged user (SYS
is the best) and
Create schema for debug as configured in package.sql
SQL> @create configured <environment>
Or create in interactive mode
SQL> @create manual <environment>
Choose from >environment< values
development
- for development of debug package - schema receives more privilegesproduction
- if you just want to use debug
Or you may wish to install debug in already existing schema. Then use the grant.sql
script to grant privileges required by debug package.
Pass environment parameter to grant development
or production
privileges.
SQL> @grant <packageSchema> <environment>
Again either configured or manual. And it drops cascade. So be carefull. You have been warned
SQL> @drop configured
or
SQL> @drop manual
You can install either from some privileged deployer user or from within "target" schema.
Use this script to change current_schema
SQL> @set_current_schema <target_schema>
Installs module in current_schema
. (see set_current_schema
). Can be installed as
- public - grants required privileges on module API to
PUBLIC
(see/module/api/grant_public.sql
)
SQL> @install public
- peer - sometimes you may want to use package only by schema, where it is deployed - then install it as peer package
SQL> @install peer
Drops all objects created by install.
SQL> @uninstall
When you want to use debug from other schemas, you have basically 2 options
- either reference objects granted to
PUBLIC
with qualified name (<schema>.<object>
) - or create synonyms and simplify everything (upgrades, move to other schema, use other debug package, ...)
These scripts will help you with latter, by either creating or dropping synonyms for debug package API in that schema.
Creates depenency from reference owner.
SQL> conn <some_schema>
SQL> @set_dependency_ref_owner <schema_where_debug_is_installed>
Removes depenency from reference owner.
SQL> conn <some_schema>
SQL> @unset_dependency_ref_owner <schema_where_debug_is_installed>