Subject: Setting events in the Oracle Toolset
Author: RBEARD
Modified: 31 Oct 95 11:51:08
==================================
Events are essentially a debugging aid for complex RDBMS problems. Sometimes,
these problems can manifest themselves when the Oracle Tools, or applications
built using the Oracle Tools, access the database.
When these problems occur, it is necessary to find their cause by creating a
trace file, triggered by the occurance of the problem. To enable this trace file
to be created, an event must be set, either at database level or by issuing an
alter session command from inside the tools.
In all cases, the trace files will be deposited in the location specified by
the init.ora parameter USER_DUMP_DEST. It should be noted that this is given a
default value under UNIX, which can be seen from inside SQL*DBA, whereas this is
not set by default in a PC/Netware environment so must be explicitly set,
otherwise no trace files will be produced. Under VMS, if USER_DUMP_DEST is not
specified, it defaults to whatever the logical ORA_DUMP is set to. If neither
are specified, you get no trace file. In all cases ensure that the write
permissions
are set correctly for the destination directory.
RDBMS
=====
Events can be set at database level by including the following line in
the init.ora file for the relevant database:
event = "604 trace name errorstack"
This will dump the errorstack to a trace file whenever the error:
ora-604 : error occurred at recursive SQL level x
is encountered.
Only one event parameter is allowed, but multiple events can be set at
database level by using ':\' to indicate a continuation line, eg.:
event = '604 trace name errorstack:\
10046 trace name context forever, level 12'
The 10046 event enables SQL statement timing, and is in effect a trace
command that gives slightly different information to the normal SQL
trace command. This event should not normally be set at instance level
as it's use can lead to excessively large trace files if not used with
great care. This is an example only.
Using the errorstack syntax, trace files can be produced for any Oracle
error prefixed by 'ORA'. There are a number of other informational
events that can be set, but these should only be done so with guidance
from Oracle Support.
Note that if more than one event is set using the above syntax, then
subsequent lines must start in the first column.
SQL*Plus
========
Inside SQL*Plus, the event can be set by issuing the following command:
SQL> alter session set events '604 trace name errorstack';
This will have the same effect as setting the event at database level
except that it will only be in effect for the length of the SQL*Plus
session.
PL/SQL
======
Refer to the section of this article which refers to the product within
which the PL/SQL is being invoked.
Forms 2.3
=========
The event can be set by creating a user defined trigger which consists
of the following statement:
alter session set events '<required event syntax>'
and then calling this trigger from a key-startup trigger using:
#exemacro exetrg <trigger_name>
Forms 3
=======
To set the event in Forms 3 it is first necessary to create a user
defined version 2 style trigger. The method for this is documented in
the Forms 3 Designers Reference in Appendix F.
This version 2 style trigger should consist of only one step which
contains the following statement:
alter session set events '<required event syntax>'
This trigger then needs to be called from a commit time trigger, for
example an on-insert trigger, using the command:
execute_trigger('<trigger name>');
To avoid altering the form that appears to be causing the problem, it
is advisable to have a separate 'dummy' form that has a single block
based on sys.dual, with a single field called dummy. This form then has
the previously described version 2 style trigger defined upon it, and
this trigger is called from an on-insert trigger defined on the block
based on sys.dual. Have a key-startup trigger which simply contains:
:dummy := 'X';
commit;
This will cause the on-insert trigger to fire which in turn will set the
event requested for the current database session.
Forms 4
=======
In Forms 4, the forms_ddl() built-in can be used to set events. This
built-in is documented in the 4.0.12 release notes. To set an event
with this built-in, include the following in a when-new-form-instance
trigger:
forms_ddl('alter session set events ''<required event syntax>''');
Note the double apostrophies either side of the <required event syntax>.
This is due to a single apostrophy being the delimiter for a string,
whilst the double apostrophies insert a single apostrophy into the
string.
Graphics 2
==========
There is no concept of sending DDL to the database in Oracle Graphics,
and hence it is impossible to set events directly. It is possible to
use trace inside Graphics as is documented in a previous bulletin. Thus,
if it becomes a requirement to use an event then it must be set either
at instance level, or in the product which calls Graphics.
Reportwriter 1.1
================
In Reportwriter 1.1, an event can be set by creating a field with the
following settings:
Name -> sql_event
Source -> &SQL alter session set events '<required event syntax>'
Group -> REPORT
Width -> 40
Ensure that the field label is deleted, that skip field is set on and
that there is no reference to this field on the text screens. When the
report is run then the event will be set, and will remain set until the
reports session is ended, ie., if the report is run in the designer then
all database actions will be checked against the event until the
designer is exitted.
Reports 2
=========
In Reports 2, an event can be set by creating a function column at
report level, with a formula of:
srw.do_sql('alter session set events ''<required event syntax>''')
and a return type of char. Ensure that this column is not displayed on
the report layout.
Precompilers
============
To set an event in the precompilers, simply include the following line
in the source code at a point before the problem arises:
#exec sql alter session set events '<required event syntax>';