WhereScape RED includes a procedure editor which allows the maintenance of the various procedures, functions and packages within the data warehouse. The editor is invoked by double-clicking on a procedure name in the left pane or by right-clicking on the procedure name and selecting Edit the Procedure.
This section discuss some of the features of the procedure editor which includes the following:
- SQL syntax highlighting
- improved find feature (repeated find, up and down, etc.)
- toggle the display of line numbering
- a status bar that includes line number, line length, selected text length, etc.
- a context sensitive toolbar
- tab to space conversion and vice versa
- uppercase, lowercase, titlecase text conversion command
- increase/decrease test indent command
- bookmark support
A procedure can be compiled by selecting the Tools > Compile Procedures menu option. Refer to Procedure Compilation for details.
In the following sections reference is made to a selected block of text. A selected block of text is a normal windows selection where the text in question is highlighted. Normally achieved by holding down the left mouse button and moving the cursor.
Indenting code
The Tab character inserts four spaces into the text. A Shift+Tab removes four spaces.
Cut, Copy, Paste and Delete
The normal Windows cut, copy, paste and delete functions are available either through the toolbar or via the right mouse pop-up menu.
Indenting a block of text
A selected block of text can be indented by four spaces by depressing the Tab character. Each tab will indent by a further four spaces. A Shift+Tab removes four spaces from the front of each line in the selected block.
Commenting out a block of text
A selected block of text can be turned into a comment by right-clicking and selecting Comment. The editor places two dashes '--' at the front of each line in the selected block. In the same way a block of text can be uncommented by choosing the Uncomment option.
Note
Only lines that start with two dashes in the left most column can be uncommented.
Inserting Steps
The right-click menu option Insert Step inserts a code line of the format 'v_step := 1000;'. Each subsequent insert adds 100 to the step value. The Alt+Z key combination can also be used to insert a step line.
The v_step variable is used in the event of an unhandled exception. In such a case, the current v_step value is reported, and it may be possible to ascertain the code that failed based on the value of this v_step variable.
Note
If a step is inserted via this method, then the step numbering automatically resets for all steps numbering from 100 in increments of 100 when the procedure is compiled or saved.
Inserting Audit Message
The right-click menu option Insert Audit Log Call inserts several lines of code into the procedure which calls WsWrkAudit. This API is used to log messages to the audit log in the WhereScape RED scheduler. By default, the message is generated to log the current step number. It can be changed by altering the first added line. The Alt+A key combination can also be used instead of the right-click menu option.
Inserting Detail/Error Message
The right-click menu option Insert Detail/Error Log Call inserts several lines of code into the procedure which calls WsWrkError. This API is used to log messages to the detail/error log in the WhereScape RED scheduler. By default, the message is generated to log the current step number. It can be changed by altering the first added line. The Alt+D key combination can also be used instead of the right-click menu option.
Viewing other procedural code
During the editing process it is possible to pop up a window containing other procedural code. This window allows cut and paste operations. In such a way it can be used as a work area or as a source of code. Select the Tools > View Procedure or Template menu option to bring the viewer window up. A dialog will appear as follows:
There are a number of drop-down list boxes to choose from. Once an item is selected, the viewer loads the code and moves to the right side of the edit window. The various options are:
- Version: A previously saved version of this procedure. The description and time are shown.
- Procedure: Some other procedure stored within the WhereScape RED metadata.
- Template: A template procedure as defined in the WhereScape RED metadata.
- Compiled procedure: One of the currently compiled procedures existing in the database.
Once an item is chosen, the viewer appears on the right side of the editor window. The viewer or the main editor window can receive the focus by clicking the mouse within the appropriate window.
Code can be cut from one window and pasted into the other. Any changes made in the viewer window cannot be saved. An example of an active view window is as follows:
Note
Editing, deleting or compiling Locked for Edit or opened procedures/scripts
- Procedures or scripts cannot be deleted if they are Locked for Edit by any user, checked out by another user or if there is another object that has the same associated procedures or scripts.
- Saving or Compiling in the procedure or script edit window cannot be performed if the procedures or scripts become Locked for Edit by other users after the edit window has been opened.
- Procedures or scripts cannot be deleted or modified after the editor window has been opened, unless the Edit Lock has been released. Edit Locks can be released by any user in the Script or Procedure Properties screen.
- To prevent updates, deletes and modifications to certain procedures or scripts, it is best to use the Check Out functionality instead. For more information about this functionality, refer to Object Check Outs and Check Ins for details.