Query Formatting in Interactive SQL
Brought to you by the Oracle Rdb Server Technology Group
Interactive SQL was never designed to be a report writer. However, it has many features that can be used easily and quickly to write tools and reports, enabling you to avoid writing a more extensive program. This article looks at several features in interactive SQL that can be used to format output.
This clause is available for domains and columns and provides an editing format used by the SELECT statement in interactive SQL. The different editing characters are described in the Oracle Rdb7 SQL Reference Manual.
The edit characters described for time fields in the current Oracle Rdb7 SQL Reference Manual are not currently implemented for Oracle Rdb7. These are the H, R, P, Q, *, % and : format characters. However, these characters are fully supported in Oracle Rdb8.
By applying different edit strings in view definitions, a variety of formatting options can be provided to interactive users. However, this is simplified in Oracle Rdb8, which extends the editing facility by allowing ad hoc edit strings in the SELECT statement. The EDIT USING clause can apply the same style edit strings to columns and other value expressions. If you have a domain with an EDIT STRING you can use its name as shorthand, as shown in this next example:
This clause is also available for domains and columns and provides the header for each column reported by the SELECT statement. The header can be up to three lines long.
When many columns are displayed, they may not fit on a single row of the report. In this case, SQL will wrap the columns to the next line, usually making the output hard to read. Use the SET LINE LENGTH statement to widen the length used by SQL (which defaults to 80 columns). You can specify a numeric value up to 512.
You might also notice that long text columns will be wrapped and a >> placed at the front of the continued segment. These are not characters in your string! I recommend using EDIT STRING 'T(20)' or something similar to have Rdb wrap the text at word space boundaries. This makes text descriptions easier to read.
Most FETCH, PRINT, SELECT and CALL statements display headers and data in interactive SQL. The CALL statement reports the values of OUT and INOUT parameters. Every INSERT, SELECT, UPDATE and DELETE statement reports an informational row count when it completes.
Because some customers would like to suppress these displays, Oracle Rdb Release 7.0.2 adds a SET DISPLAY command to interactive SQL. This command currently provides three clauses to enable and disable various parts of the formatting.
The SHOW DISPLAY statement will report the current settings for these items, as in the following example.
to view a larger version.
Technical Tips: Displaying Data from Compound Statements
The PRINT statement is only available in interactive SQL. However, many programmers would like to use it from a multistatement or stored procedure so that information can be displayed.
There are two ways to implement similar functionality: use an external procedure to interface to the 3GL routine (such as the OpenVMS runtime library routine LIB$PUT_OUTPUT), or use the SQL TRACE statement.
Using the TRACE statement is the easiest, but requires some extra steps. First, make sure that SET FLAGS 'TRACE' is executed before the procedure is first executed. This statement enables the TRACE statement, which is usually ignored. If necessary, you can define the logical name RDMS$SET_FLAGS to TRACE before you run SQL.
By default, the prefix "~Xt:" is added to each line so that the TRACE lines can be easily located in a debug log file. However, you can use SET FLAGS 'NOPREFIX' to remove this from the output. Note that it also affects the BLR and MBLR output.