1  SET_DISPLAY
   Controls the output of header information. Use the SHOW DISPLAY
   statement to view the current settings.
 

2  Environment
   You can use the SET DISPLAY statement in interactive SQL only.
 

2  Format

 SET DISPLAY ---+-+-+-------+-+-> COMMENT     --+-------+-+-->
                | | +-> NO -+ +-> EDIT STRING  -+       | |
                | |           +-> QUERY HEADER -+       | |
                | |           +-> ROW COUNTER --+       | |
                | +-> DEFAULT NULL STRING --------------+ |
                | +-> NULL STRING -+-> <literal> -------+ |
                |                  +-> <host-variable> -+ |
                +------------------ , <-------------------+
 
 
 

 

2  Arguments
 

3  COMMENT

   Syntax options:

   COMMENT | NOCOMMENT

   Disables or enables the display of of comment text by other SHOW
   commands (for example, SHOW TABLE).
 

3  COUNTER

   Syntax options: ROW COUNTER | NO ROW COUNTER

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

3  DEFAULT_NULL_STRING

   Reverts to using the text 'NULL'.
 

3  EDIT_STRING

   Syntax options:

   EDIT STRING | NO EDIT STRING

   Enables the usage of column edit strings to format values for the
   SELECT statement. Use NO EDIT STRING to disable the use of the
   column edit strings.
 

3  NULL_STRING

   Changes the way NULL values are displayed by interactive SQL.
 

3  QUERY_HEADER

   Syntax options:

   QUERY HEADER | NO QUERY HEADER

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

2  Example

   Example 1: Using the SET DISPLAY Statement

   The following example shows the effect of the SET DISPLAY
   statement. It uses the SHOW DISPLAY command to report the current
   settings.

   SQL> ATTACH 'FILENAME mf_personnel';
   SQL>
   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> SHOW DISPLAY
   Output of the query header is enabled
   Output of the row counter is disabled
   Output using edit strings is enabled
   Page length is set to 24 lines
   Line length is set to 132 bytes
   Display NULL values using "NULL"
   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> INSERT INTO temp_emp (id, sal)
   cont> SELECT employee_id, MAX(salary_amount)
   cont>   FROM salary_history GROUP BY employee_id;
   SQL> UPDATE temp_emp SET id = NULL WHERE id <= 0;
   SQL> DELETE FROM temp_emp WHERE id IS NULL;
   SQL>
   SQL> SET DISPLAY ROW COUNTER;
   SQL> SHOW DISPLAY
   Output of the query header is enabled
   Output of the row counter is enabled
   Output using edit strings is enabled
   Page length is set to 24 lines
   Line length is set to 132 bytes
   Display NULL values using "NULL"
   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 QUERY HEADER;
   SQL> SHOW DISPLAY
   Output of the query header is disabled
   Output of the row counter is enabled
   Output using edit strings is enabled
   Page length is set to 24 lines
   Line length is set to 132 bytes
   Display NULL values using "NULL"
   SQL>
   SQL> DECLARE :res INTEGER;
   SQL>
   SQL> -- This omits the query header for the SELECT statement
   SQL> SELECT * FROM work_status;
    0             INACTIVE      RECORD EXPIRED
    1             ACTIVE        FULL TIME
    2             ACTIVE        PART TIME
   3 rows selected
   SQL>
   SQL> -- This omits the query header for the PRINT statement
   SQL> PRINT :res;
              0
   SQL> PRINT 'This is a print line';
    This is a print line
   SQL>
   SQL> CREATE MODULE call_sample
   cont>     LANGUAGE SQL
   cont>     PROCEDURE add_one (IN :a INTEGER, OUT :b INTEGER);
   cont>     SET :b = :a + 1;
   cont> END MODULE;
   SQL> -- This omits the query header for the OUT/INOUT parameters for CALL
   SQL> CALL add_one (100, :res);
            101
   SQL>
   SQL> DECLARE c CURSOR FOR SELECT * FROM work_status;
   SQL> OPEN c;
   SQL> -- This omits the query headers for the variables fetched
   SQL> FETCH c;
    0             INACTIVE      RECORD EXPIRED
   SQL> SET DISPLAY QUERY HEADER;
   SQL> SHOW DISPLAY
   Output of the query header is enabled
   Output of the row counter is enabled
   Output using edit strings is enabled
   Page length is set to 24 lines
   Line length is set to 132 bytes
   Display NULL values using "NULL"
   SQL> -- This outputs the query headers for the variables fetched
   SQL> FETCH c;
    STATUS_CODE   STATUS_NAME   STATUS_TYPE
    1             ACTIVE        FULL TIME
   SQL> CLOSE c;
   SQL>
   SQL> TRUNCATE TABLE temp_emp;
   SQL> INSERT INTO temp_emp (id, sal)
   cont>     SELECT employee_id, AVG(salary_amount)
   cont> FROM salary_history
   cont> WHERE salary_end IS NULL
   cont> GROUP BY employee_id;
   100 rows inserted
   SQL>
   SQL> SELECT * FROM temp_emp ORDER BY id LIMIT TO 3 ROWS;
             ID          SAL
            164   $51,712.00
            165   $11,676.00
            166   $18,497.00
   3 rows selected
   SQL>
   SQL> SET DISPLAY NO EDIT STRING;
   SQL> SHOW DISPLAY
   Output of the query header is enabled
   Output of the row counter is enabled
   Output using edit strings is disabled
   Page length is set to 24 lines
   Line length is set to 132 bytes
   Display NULL values using "NULL"
   SQL>
   SQL> SELECT * FROM temp_emp ORDER BY id LIMIT TO 3 ROWS;
            164     51712.00
            165     11676.00
            166     18497.00
   3 rows selected
   SQL>
   SQL> SET DISPLAY EDIT STRING;
   SQL> SHOW DISPLAY
   Output of the query header is enabled
   Output of the row counter is enabled
   Output using edit strings is enabled
   Page length is set to 24 lines
   Line length is set to 132 bytes
   Display NULL values using "NULL"
   SQL>
   SQL> SELECT * FROM temp_emp ORDER BY id LIMIT TO 3 ROWS;
             ID          SAL
            164   $51,712.00
            165   $11,676.00
            166   $18,497.00
   3 rows selected

   Example 2: Replacing the NULL values with text to make the output
   easier to read

   SQL> select job_start, job_end,
   cont>             (select department_name
   cont>              from departments d
   cont>              where d.department_code = jh.department_code)
   cont> from job_history jh
   cont> where employee_id = '00164';
    JOB_START     JOB_END
    21-Sep-1981   NULL          Board Manufacturing North
     5-Jul-1980   20-Sep-1981   Cabinet & Frame Manufacturing
   2 rows selected
   SQL> set display null string '(still employeed)'
   SQL> select job_start, job_end,
   cont>             (select department_name
   cont>              from departments d
   cont>              where d.department_code = jh.department_code)
   cont> from job_history jh
   cont> where employee_id = '00164';
    JOB_START           JOB_END
    21-Sep-1981         (still employeed)   Board Manufacturing North
     5-Jul-1980         20-Sep-1981         Cabinet & Frame Manufacturing
   2 rows selected

   Example 3: Disabling the comment display to make the output of
   SHOW easier to read

   SQL> show domain id_dom
   ID_DOM                          CHAR(5)
    Comment:       standard definition of employee id
   SQL> set display no comment;
   SQL> show domain id_dom
   ID_DOM                          CHAR(5)
   SQL>

   Example 4: Save the current NULL string using GET ENVIRONMENT and
   restore after executing a query.

   SQL> declare :ns varchar(100);
   SQL> get environment (session) :ns = NULL_STRING;
   SQL> set null;
   SQL> select job_start, job_end,
   cont>             (select department_name
   cont>              from departments d
   cont>              where d.department_code = jh.department_code)
   cont> from job_history jh
   cont> where employee_id = '00164';
    JOB_START     JOB_END
    21-Sep-1981                 Board Manufacturing North
     5-Jul-1980   20-Sep-1981   Cabinet & Frame Manufacturing
   2 rows selected
   SQL> set display null string :ns;
   SQL> select job_start, job_end,
   cont>             (select department_name
   cont>              from departments d
   cont>              where d.department_code = jh.department_code)
   cont> from job_history jh
   cont> where employee_id = '00164';
    JOB_START     JOB_END
    21-Sep-1981   NULL          Board Manufacturing North
     5-Jul-1980   20-Sep-1981   Cabinet & Frame Manufacturing
   2 rows selected