SQL MENU


This section will provide a basic understanding of SQL*Menu. This document will briefly discuss the main features in designing a report. Most of the information contained in this section is DIRECTLY extracted from ``SQL*Menu User's Guide and Reference'' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the above stated manual.

SQL*Menu is a productivity tool that provides a single menu interface for running multiple data-processing tools. A SQL*Menu application acts as an ``umbrella'' under which you can integrate many different software products to provide a single solution. SQL*Menu has two main category of users: Operators and Designers. This documentation will mainly concentrate on the Designer's portion of SQL*Menu.


Overview of Menu Design

A SQL*Menu application consists of objects that you define in the SQL*Menu (Design) interface. These objects include the application itself, the menus, the menu items, and any substitution parameters and PL/SQL procedures that the menu items invoke.

The basic steps in designing a SQL*Menu applications are:

In planning the application, you should consider the following issues:


Starting and Quitting SQL*Menu (Design)

To start SQL*Menu (Design), enter the following at the operating system prompt (remember to start the xterm with -sf option).

          sqlmenu50 / -c xtermsun:sun

If you are denied access to SQL*Menu (Design) you should contact your SQL*Menu DBA (labadmin).

To exit from SQL*Menu (Design) select Action and then Quit.


Getting Help Information

Several kinds of online help are available to designers of SQL*Menu applications:

The online help system for SQL*Menu (Design) gives detailed help and general information about SQL*Menu and the design interface. Select the Help System item from the Help submenu to display the introduction, ``About the Online Help System.''. Once you have entered the online help system, you can access all other parts of the help system through its own menus. For more information on the online help system see Chapter 6 of the ``SQL*Menu User's Guide and Reference''.


Object Hierarchy

An object is a named group of data in the ORACLE database that you can copy, move, or delete as one entity in a single operation. Each type of object has default rules and values that you can override or extend for individual objects.

An object can own other objects, or belong to another object, in a hierarchy of objects. The figure below illustrates this hierarchy and shows how higher-level objects own lower-level objects.




The current context within the object hierarchy determines which instances of each type of object can be accessed at a given time. For example, in the design interface an application must be established in the current context before a menu can be defined, because every menu belongs to an application.

Substitution parameters and procedures belong directly to an application. Although they are used by menu items, they are defined independently of the menu and item context.


Object-Naming Conventions

SQL*Menu object names, like ORACLE object names, must follow these conventions:

For a complete list of Reserved Words see Chapter 8 of ``SQL*Menu User's Guide and Reference''. SQL*Menu does not distinguish between uppercase and lowercase letters in object names.


Applications

This section describes the application object in SQL*Menu.

To create a new application, from the main menu of the design interface, you select the New item from the Action submenu. In the Name Field, of the New Menu Application dialog box, you enter the name for an application you want to create.

To open an existing menu application, from the main menu of the design interface, you select the Open item from the Action submenu. In the Name Field, of the Open Menu Application dialog box, you enter the name of the application you wish to open. Press [List] to select an application name from a list of values.

When you create a new application, a default Application Definition form is completed for you. The Application Definition form shows information for the current application. To modify the Application Definition form, you select the Menu item from the main menu of the design interface and then select the Application item.

The process of defining an application consists of setting each of the application's characteristics as follows:


Menus

This section describes the menu object in SQL*Menu.

You create and modify menus in the SQL*Menu (Design) interface through the Menu Definition form or spread table. The Menu Definition form shows information for one menu at a time and the Menu Definition spread table shows information for several menus at once.

To reach the Menu Definition form or spread table, you select Menu item from the main menu of the design interface and then select the Menu item. You can toggle from form and spread table display types by pressing [Change Display type].

The process of defining a menu consists of setting each of the menu's characteristics as follows:


Menu Items

This section describes the menu item object in SQL*Menu.

You create and modify menu items in the SQL*Menu (Design) interface through the Item Definition form or spread table. The Item Definition form shows information for one item at a time and the Item Definition spread table shows information for several items at once.

To reach the Item Definition form or spread table, you select Menu item from the main menu of the design interface and then select Item. You can toggle from form and spread table display types by pressing [Change Display type].

The process of defining a menu item consists of setting each of the menu item's characteristics as follows:


SQL*Menu Command Types

This section describes the seven command types available in SQL*Menu. To use these commands, enter the command type number into the Command Type field of the Item Definition form or spread table and then enter an appropriate command in the Command Line scroll region.


Type 1: Invoking a Submenu

Command type 1 lets you attach a submenu to a menu item. When an operator selects that item, SQL*Menu displays the menu specified in the item's Command Line.

In the pull-down and bar menu display styles, the submenu cannot be above the current menu in the same branch of the menu tree. In general, you should make your menu tree a simple hierarchy.


Type 2 and 3: Executing Operating System Commands

Command types 2 and 3 pass the value of the Command Line to the operating system for processing. After executing the command, type 2 returns to SQL*Menu immediately. Type 3 pauses after executing the command and waits for the operator to press a key before returning to SQL*Menu.

These command types can be used to invoke other software products, such as SQL*ReportWriter, SQL*Forms, and SQL*Plus. If SQL*Forms or SQL*Plus is linked with SQL*Menu, however, command type 4 or 5 will invoke the linked program more quickly.


Type 4: Invoking SQL*Forms

SQL*Forms and SQL*Menu are both linked together when SQL*Menu is installed. If this option is selected, SQL*Menu uses menu command type 4 to invoke a form without a new database login. This invocation of a form is much quicker than invocation by command type 2 or 3.

The Command Line scroll region contains any information for the SQL*Forms login, just as if you were invoking SQL*Forms from the operating system level. Values such as username, password, command file, and arguments can be passed by using substituion parameters, for which the user must supply values. For example, type ``RUNFORM30 EMP &UN/&PW'' into the Command Line scroll region.

SQL*Forms (Run Form) command line switches -c,-e,-i,-r and -w are not supported in command type 4 when SQL*Forms and SQL*Menu are linked. To specify one of these switches, you can use command type 2 or 3.

SQL*Menu preserves any global variables defined by SQL*Forms when using command type 4 (only if SQL*Forms is linked with SQL*Menu). Thus, forms can define global variables and then refer back to them.


Type 5: Invoking SQL*Plus

SQL*Plus and SQL*Menu are both linked together when SQL*Menu is installed. If this option is selected, SQL*Menu uses menu command type 5 to invoke SQL*Plus without a new database login and without loading SQL*Plus into memory. Command type 5 invokes SQL*Plus significantly faster then command type 2 or 3.

The Command Line scroll region contains any information for the SQL*Plus login, just as if you were invoking SQL*Plus from the operating system level. Values such as username, password, command file, and arguments can be passed by using substituion parameters, for which the user must supply values. For example, type ``SQLPLUS &UN/&PW @JOBS'' into the Command Line scroll region.


Type 6: Executing Macro Commands

To use SQL*Menu macro commands, enter 6 into the Command Type field of the Item Definition form or spread table and enter one or more macros into the Command Line scroll region.

The following macros are available in SQL*Menu:

          APLMENU     DISP        NEWUSER        RIGHT
          APLPARM     DOWN        NODISP         SHOWBGM
          ASSIGN      EXIT        NXTFLD         SHOWKEYS
          BGMn        HELP        OSCMD          SUSPEND
          CHRMODE     LEFT        OSCMD1         TRMNATE
          CLRFLD      MAINMENU    PRVFLD         UP
          DEBUG       MENUPARM    PRVMENU        WHERE
          DELCHR      NEWAPL      REDISP

Some of these macros are discussed later in this chapter. For more information on these macros see Chapter 11 of ``SQL*Menu User's Guide and Reference''.


Type 7: Executing PL/SQL Commands

To execute a PL/SQL command, enter 7 into the Command Type field of the Item Definition form or spread table and enter the command into the Command Line scroll region. The command text can be either a procedure name or the text for an unnamed procedure. PL/SQL packaged procedures are available for SQL*Menu and SQL*Forms.

Some of the PL/SQL packaged procedures are discussed later in this chapter. For more information on PL/SQL packaged procedures see Chapter 10 of ``SQL*Menu User's Guide and Reference''. PL/SQL is discussed in another chapter in this manual.


Substitution Parameters

This section describes the substitution parameter object in SQL*Menu. A substitution parameter is identified by a two-character code that can stand for a value in the command line or in a procedure.

You create and modify substitution parameters in the SQL*Menu (Design) interface through the Parameter Definition form or spread table. The Parameter Definition form shows information for one parameter at a time and the Parameter Definition spread table shows information for several parameters at once.

To reach the Parameter Definition form or spread table, you select Menu item from the main menu of the design interface and then select Parameter item. You can toggle from form and spread table display types by pressing [Change Display type].

The process of defining a parameter consists of setting each of the parameter's characteristics as follows:

SQL*Menu lets you construct flexible, general-purpose menus through the use of substitution parameters. For example, you can create a menu item that invokes SQL*Forms, using a substitution parameter that prompts for the name of the form the operator wants to run.

You can associate any substitution parameter with any menu in an application, making a menu parameter. When an operator selects that menu in the full-screen display style, the Enter Parameter Values dialog box prompts the operator to enter the exact value. Note that menu parameters are only useful for full-screen menus.

You can also associate a substitution parameter with a menu item, making an item parameter. When an operator selects that item, the Enter Parameter Values dialog box prompts for the exact value.

Five predefined substitution parameters, which are always known to SQL*Menu, are &UN, &PW, &AD, &SO, and &TT. They contain the following values:

&UN the current username
&PW the current password
&AD the directory where the current application library file is stored
&SO the current menu item (``selected option'')
&TT the terminal type you used to log in to SQL*Menu

After processing each menu parameter in the command, SQL*Menu checks the item parameter list. If the list is not empty, SQL*Menu activates the Enter Parameter Values dialog box and the operator can enter a value. Otherwise, SQL*Menu executes the command after substituting the menu parameter values in the command string.


Procedures

This section describes the procedure object in SQL*Menu. A procedure is a set of instructions that an application stores for repeated execution. Typically, a procedure consists of SQL statements and PL/SQL commands.

You create and modify procedures in the SQL*Menu (Design) interface through the Procedure Definition form or spread table. The Procedure Definition form shows information for one procedure at a time and the Procedure Definition spread table shows information for several procedures at once.

To reach the Procedure Definition form or spread table, you select Procedure item from the main menu of the design interface. You can toggle from form and spread table display types by pressing [Change Display type].

The process of defining a procedure consists of setting each of the procedure's characteristics as follows:


Packaged Procedure Overview

A packaged procedure is a named PL/SQL command that allows designers to specify functions normally performed by function keys. You can invoke packaged procedures in a menu item's command line or in a PL/SQL procedure.

A packaged procedure can fail for two reasons:

Certain functions have no meaning in some menus or states of SQL*Menu. Similarly, their corresponding packaged procedures have no meaning in the same states.

SQL*Menu packaged procedures can execute in the following states of SQL*Menu (Run Menu):

SQL*Menu (Run Menu):

SQL*Menu provides the following PL/SQL functions that you can use to trap the success or failure of packaged procedures:

          MENU_SUCCESS       FORM_SUCCESS        ERROR_TYPE
          MENU_FAILURE       FORM_FAILURE        ERROR_CODE
          MENU_FATAL         FORM_FATAL          ERROR_TEXT

Each of these functions tests the value of a specific error variable that is set whenever SQL*Menu executes a packaged procedure.

The error variables record the following information:

Because each packaged procedure sets the return values of these functions when it executes, the functions only report information on the most recently executed packaged procedure. Other actions such as pressing a function key can also set these return values, so you should always test the results immediately after a packaged procedure executes.

You can alter the flow of processing by testing the value of any of the error variables through the appropriate PL/SQL function, and then raising an exception or taking the appropriate action.

SQL*Menu Packaged Procedures with its syntax, the equivalent macro, and the SQL*Menu states in which it is valid are specified below. Failure conditions are also specified. For a complete list of SQL*Menu Packaged Procedures see Chapter 10 of ``SQL*Menu User's Guide and Reference''.