Subject: Setting events in the Oracle Toolset Type: BULLETIN Creation Date: 30-AUG-1994 PURPOSE To describe setting events in the Oracle Toolset SCOPE & APPLICATION For users requiring to set events to obtain more diagnostic information. RELATED DOCUMENTS [NOTE:75713.1] Customer Information for setting numeric events 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 receive 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-00604 : error occurred at recursive SQL level x is encountered. If multiple events are to be set they must either be set using a continuation character, or should be grouped together on successive lines. To set using the continuation character at the database level use ':\' 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. To set on successive lines eg.: event="604 trace name errorstack" event="10046 trace name context forever, level 12" 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 '' and then calling this trigger from a key-startup trigger using: #exemacro exetrg 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 '' This trigger then needs to be called from a commit time trigger, for example an on-insert trigger, using the command: execute_trigger(''); 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 '''''); Note the double apostrophes on either side of the . This is due to a single apostrophe being the delimiter for a string, while the double apostrophes insert a single apostrophe 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 '' 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 exited. 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 ''''') 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 '';