Query Formatting in Interactive SQL

Brought to you by the Oracle Rdb Server Technology Group

Ian Smith ian.e.smith@oracle.com
Consulting Engineer
Oracle Corporation
New England Development Center

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.

• EDIT STRING

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.

                                     
                                       
SQL> create domain MONEY integer(2) EDIT STRING '$$$,$$$,$$9.99';
                                    
                                  

to view a larger version.

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:

                                     
                                       
SQL> select employee_id,
                                       
cont>       avg(salary_amount) as "Avg Salary" edit using MONEY
                                       
cont> from salary_history
                                       
cont> where salary_end is null; -- current jobs
                                    
                                  

to view a larger version.

• QUERY HEADER

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.

                                     
                                       
SQL> create table SALARY_HISTORY (
                                       
cont> ...
                                       
cont> salary_amount MONEY query header is 'Salary'/'Amount',
                                       
cont> ...);
 
                                    
                                  

to view a larger version.

• SET LINE LENGTH

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.

• SET DISPLAY statement (new for Rdb 7.0.2)

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 EDIT STRING option enables the use of column edit strings to format values for SELECT. Use NO EDIT STRING to disable the use of the columns edit strings.

• The QUERY HEADER option enables the printed header generated by the SELECT, CALL, FETCH and PRINT statements. Use NO QUERY HEADER to disable this header.

• The ROW COUNTER option enables the total count reported by SELECT, DELETE, INSERT and UPDATE statements. Use NO ROW COUNTER to disable the trailing count message.

The SHOW DISPLAY statement will report the current settings for these items, as in the following example.

                                     
 

                                       
SQL> create domain MONEY integer(2) edit string '$$$,$$9.99';
                                       
SQL> create table TEMP_EMP (id integer, sal MONEY);
                                       
SQL>
                                       
SQL> select * from work_status;
  
                                      
STATUS_CODE   STATUS_NAME   STATUS_TYPE
  
                                      
0             INACTIVE      RECORD EXPIRED
  
                                      
1             ACTIVE        FULL TIME
  
                                      
2             ACTIVE        PART TIME
  
                                      
3 rows selected
                                       
SQL>
                                       
SQL>
                                        
                                           
set display no row counter;
                                        
                                      
                                       
SQL>
                                       
SQL> show display
  
                                      
Output of the query header is enabled
  
                                      
Output of the row counter is disabled

  
                                      
Output using edit strings is enabled
  
                                      
HELP page length is set to 24 lines
  
                                      
Line length is set to 132 bytes 
                                       
SQL>
                                       
SQL> select * from work_status;
  
                                      
STATUS_CODE   STATUS_NAME   STATUS_TYPE
  
                                      
0             INACTIVE      RECORD EXPIRED
  
                                      
1             ACTIVE        FULL TIME
  
                                      
2             ACTIVE        PART TIME
                                       
SQL> insert into TEMP_EMP (id) values (0);
                                       
SQL>
                                    
                                  

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.