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