Library |
Product |
Contents |
Index |
Read this chapter while sitting at your computer and try out the examples shown. Before beginning, make sure you have access to the sample tables described.
COLUMN column_name HEADING column_headingSee the COLUMN command for more details.
Example 4-1 Changing a Column Heading
To produce a report from EMP with new headings specified for DEPTNO, ENAME, and SAL, enter the following commands:
SQL> COLUMN DEPTNO HEADING Department SQL> COLUMN ENAME HEADING Employee SQL> COLUMN SAL HEADING Salary SQL> COLUMN COMM HEADING Commission SQL> SELECT DEPTNO, ENAME, SAL, COMM 2 FROM EMP 3 WHERE JOB = 'SALESMAN';SQL*Plus displays the following output:
Department Employee Salary Commission ---------- ---------- ---------- ---------- 30 ALLEN 1600 300 30 WARD 1250 500 30 MARTIN 1250 1400 30 TURNER 1500 0Note: The new headings will remain in effect until you enter different headings, reset each column's format, or exit from SQL*Plus.
To change a column heading to two or more words, enclose the new heading in single or double quotation marks when you enter the COLUMN command. To display a column heading on more than one line, use a vertical bar (|) where you want to begin a new line. (You can use a character other than a vertical bar by changing the setting of the HEADSEP variable of the SET command. See the SET command for more information.)
Example 4-2 Splitting a Column Heading
To give the column ENAME the heading EMPLOYEE NAME and to split the new heading onto two lines, enter
SQL> COLUMN ENAME HEADING 'Employee|Name'Now re-run the query with the slash (/) command:
SQL> /SQL*Plus displays the following output:
Employee Department Name Salary Commission ---------- ---------- ---------- ---------- 30 ALLEN 1600 300 30 WARD 1250 500 30 MARTIN 1250 1400 30 TURNER 1500 0To change the character used to underline each column heading, set the UNDERLINE variable of the SET command to the desired character.
Example 4-3 Setting the Underline Character
To change the character used to underline headings to an equal sign and re-run the query, enter the following commands:
SQL> SET UNDERLINE = SQL> /SQL*Plus displays the following results:
Employee Department Name Salary Commission ========== ========== ========== ========== 30 ALLEN 1600 300 30 WARD 1250 500 30 MARTIN 1250 1400 30 TURNER 1500 0Now change the underline character back to a dash:
SQL> SET UNDERLINE '-'Note: You must enclose the dash in quotation marks; otherwise, SQL*Plus interprets the dash as a hyphen indicating you wish to continue the command on another line.
SQL*Plus normally displays numbers with as many digits as are required for accuracy, up to a standard display width determined by the value of the NUMWIDTH variable of the SET command (normally 10). If a number is larger than the value of SET NUMWIDTH, SQL*Plus rounds the number up or down to the maximum number of characters allowed.
You can choose a different format for any NUMBER column by using a format model in a COLUMN command. A format model is a representation of the way you want the numbers in the column to appear, using 9's to represent digits.
COLUMN column_name FORMAT modelUse format models to add commas, dollar signs, angle brackets (around negative values), and/or leading zeros to numbers in a given column. You can also round the values to a given number of decimal places, display minus signs to the right of negative values (instead of to the left), and display values in exponential notation.
To use more than one format model for a single column, combine the desired models in one COLUMN command (see Example 4-4). For a complete list of format models and further details, see the COLUMN command.
Example 4-4 Formatting a NUMBER Column
To display SAL with a dollar sign, a comma, and the numeral zero instead of a blank for any zero values, enter the following command:
SQL> COLUMN SAL FORMAT $99,990Now re-run the current query:
SQL> /SQL*Plus displays the following output:
Employee Department Name Salary Commission ---------- ---------- ---------- ---------- 30 ALLEN $1,600 300 30 WARD $1,250 500 30 MARTIN $1,250 1400 30 TURNER $1,500 0Use a zero in your format model, as shown above, when you use other formats such as a dollar sign and wish to display a zero in place of a blank for zero values.
Note: The format model will stay in effect until you enter a new one, reset the column's format, or exit from SQL*Plus.
The sections below describe the defaults and how you can alter the defaults with the COLUMN command.
The display width of LONG, CLOB and NCLOB columns defaults to the value of the LONG or LONGCHUNKSIZE, whichever is smaller. See the SET command in chapter 7 for information about the LONG and LONGCHUNKSIZE variables.
The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default format width is A9. For more information on formatting DATE columns, see the FORMAT clause of the COLUMN command.
The default display width for the Trusted Oracle datatype MLSLABEL is the width defined for the column in the database or the width of the column heading, whichever is longer. (Note that the default display width for a Trusted Oracle column named ROWLABEL is 15.)
Note: The default justification for datatypes and Trusted Oracle columns is left justification.
Within the COLUMN command, identify the column you want to format and the model you want to use:
COLUMN column_name FORMAT modelIf you specify a width shorter than the column heading, SQL*Plus truncates the heading. If you specify a width for a LONG, CLOB, or NCLOB column, SQL*Plus uses the LONGCHUNKSIZE or the specified width, whichever is smaller, as the column width. See the COLUMN command for more details.
Example 4-5 Formatting a Character Column
To set the width of the column ENAME to four characters and re-run the current query, enter
SQL> COLUMN ENAME FORMAT A4 SQL> /SQL*Plus displays the results:
Empl Department Name Salary Commission ---------- ---- ---------- ---------- 30 ALLE $1,600 300 N
30 WARD $1,250 500 30 MART $1,250 1400 IN 30 TURN $1,500 0 ERNote: The format model will stay in effect until you enter a new one, reset the column's format, or exit from SQL*Plus. ENAME could be a CHAR, NCHAR, VARCHAR2 (VARCHAR), or NVARCHAR2 (NCHAR VARYING) column.
If the WRAP variable of the SET command is set to ON (its default value), the employee names wrap to the next line after the fourth character, as shown in Example 4-5. If WRAP is set to OFF, the names are truncated (cut off) after the fourth character.
The system variable WRAP controls all columns; you can override the setting of WRAP for a given column through the WRAPPED, WORD_WRAPPED, and TRUNCATED clauses of the COLUMN command. See COLUMN for more information on these clauses. You will use the WORD_WRAPPED clause of COLUMN later in this chapter.
Note: The column heading is truncated regardless of the setting of WRAP or any COLUMN command clauses.
Now return the column to its previous format:
SQL> COLUMN ENAME FORMAT A10
Example 4-6 Copying a Column's Display Attributes
To give the column COMM the same display attributes you gave to SAL, but to specify a different heading, enter the following command:
SQL> COLUMN COMM LIKE SAL HEADING BonusRe-run the query:
SQL> /SQL*Plus displays the following output:
Employee Department Name Salary Bonus ---------- ---------- ---------- ---------- 30 ALLEN $1,600 $300 30 WARD $1,250 $500 30 MARTIN $1,250 $1,400 30 TURNER $1,500 $0
COLUMN column_nameTo list the current display attributes for all columns, enter the COLUMN command with no column names or clauses after it:
COLUMNTo reset the display attributes for a column to their default values, use the CLEAR clause of the COLUMN command as shown below:
COLUMN column_name CLEARTo reset the attributes for all columns, use the COLUMNS clause of the CLEAR command.
Example 4-7 Resetting Column Display Attributes to their Defaults
To reset all columns' display attributes to their default values, enter the following command:
SQL> CLEAR COLUMNS columns clearedYou may wish to place the command CLEAR COLUMNS at the beginning of every command file to ensure that previously entered COLUMN commands will not affect queries you run in a given file.
COLUMN column_name OFFThe OFF clause tells SQL*Plus to use the default display attributes for the column, but does not remove the attributes you have defined through the COLUMN command. To restore the attributes you defined through COLUMN, use the ON clause:
COLUMN column_name ON
RECSEP determines when the line of characters is printed; you set RECSEP to EACH to print after every line, to WRAPPED to print after wrapped lines, and to OFF to suppress printing. The default setting of RECSEP is WRAPPED.
RECSEPCHAR sets the character printed in each line. You can set RECSEPCHAR to any character.
You may wish to wrap whole words to additional lines when a column value wraps to additional lines. To do so, use the WORD_WRAPPED clause of the COLUMN command as shown below:
COLUMN column_name WORD_WRAPPEDExample 4-8 Printing a Line of Characters after Wrapped Column Values
To print a line of dashes after each wrapped column value, enter the following commands:
SQL> SET RECSEP WRAPPED SQL> SET RECSEPCHAR '-'Now restrict the width of the column LOC and tell SQL*Plus to wrap whole words to additional lines when necessary:
SQL> COLUMN LOC FORMAT A7 WORD_WRAPPEDFinally, enter and run the following query:
SQL> SELECT * FROM DEPT;SQL*Plus displays the results:
DEPTNO DNAME LOC ---------- --------------- ---------- 10 ACCOUNTING NEW YORK ------------------------------------------------- 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONIf you set RECSEP to EACH, SQL*Plus prints a line of characters after every row (after every department, for the above example).
Before continuing, set RECSEP to OFF to suppress the printing of record separators:
SQL> SET RECSEP OFF
The column you specify in a BREAK command is called a break column. By including the break column in your ORDER BY clause, you create meaningful subsets of records in your output. You can then add formatting to the subsets within the same BREAK command, and add a summary line (containing totals, averages, and so on) by specifying the break column in a COMPUTE command.
For example, the following query, without BREAK or COMPUTE commands,
SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;produces the following unformatted results:
DEPTNO ENAME SAL -------- ---------- --------- 10 CLARK 2450 10 MILLER 1300 20 SMITH 800 20 ADAMS 1100 30 ALLEN 1600 30 JAMES 950 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250To make this report more useful, you would use BREAK to establish DEPTNO as the break column. Through BREAK you could suppress duplicate values in DEPTNO and place blank lines or begin a new page between departments. You could use BREAK in conjunction with COMPUTE to calculate and print summary lines containing the total (and/or average, maximum, minimum, standard deviation, variance, or count of rows of) salary for each department and for all departments.
BREAK ON break_columnNote: Whenever you specify a column or expression in a BREAK command, use an ORDER BY clause specifying the same column or expression. If you do not do this, the breaks may appear to occur randomly.
Example 4-9 Suppressing Duplicate Values in a Break Column
To suppress the display of duplicate department numbers in the query results shown above, enter the following commands:
SQL> BREAK ON DEPTNO SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;SQL*Pus displays the following output:
DEPTNO ENAME SAL ---------- ----------- --------- 10 CLARK 2450 MILLER 1300 20 SMITH 800 ADAMS 1100 30 ALLEN 1600 JAMES 950 TURNER 1500 WARD 1250 MARTIN 1250
BREAK ON break_column SKIP nTo skip a page, use the command in this form:
BREAK ON break_column SKIP PAGEExample 4-10 Inserting Space when a Break Column's Value Changes
To place one blank line between departments, enter the following command:
SQL> BREAK ON DEPTNO SKIP 1Now re-run the query:
SQL> /SQL*Plus displays the results:
DEPTNO ENAME SAL ---------- ----------- --------- 10 CLARK 2450 MILLER 1300
20 SMITH 800 ADAMS 1100 30 ALLEN 1600 JAMES 950 TURNER 1500 WARD 1250 MARTIN 1250
BREAK ON ROW SKIP nTo skip a page after every row, use
BREAK ON ROW SKIP PAGENote: SKIP PAGE does not cause a physical page break unless you have also specified NEWPAGE 0.
Example 4-11 Combining Spacing Techniques
First, add another column to the current query:
SQL> L 1 SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4* ORDER BY DEPTNO SQL> 1 SELECT DEPTNO, JOB, ENAME, SAL SQL> 4 ORDER BY DEPTNO, JOBNow, to skip a page when the value of DEPTNO changes and one line when the value of JOB changes, enter the following command:
SQL> BREAK ON DEPTNO SKIP PAGE ON JOB SKIP 1To show that SKIP PAGE has taken effect, create a TTITLE with a page number, enter
SQL> TTITLE COL 35 FORMAT 9 'Page:' SQL.PNORun the new query to see the results:
SQL> / Page: 1 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 10 CLERK MILLER 300 MANAGER CLARK 2450 Page: 2 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 20 CLERK SMITH 800 ADAMS 1100 Page: 3 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 30 CLERK JAMES 950 SALESMAN ALLEN 1600 TURNER 1500 WARD 1250 MARTIN 1250
BREAKYou can remove the current break definition by entering the CLEAR command with the BREAKS clause:
CLEAR BREAKSYou may wish to place the command CLEAR BREAKS at the beginning of every command file to ensure that previously entered BREAK commands will not affect queries you run in a given file.
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_columnYou can include multiple break columns and actions, such as skipping lines in the BREAK command, as long as the column you name after ON in the COMPUTE command also appears after ON in the BREAK command. To include multiple break columns and actions in BREAK when using it in conjunction with COMPUTE, use these commands in the following forms:
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 COMPUTE function LABEL label_name OF column column column ... ON break_column_2The COMPUTE command has no effect without a corresponding BREAK command.
You can COMPUTE on NUMBER columns and, in certain cases, on all types of columns. See COMPUTE for details.
The following table lists compute functions and their effects:
Labels for ON REPORT and ON ROW computations appear in the first column; otherwise, they appear in the column specified in the ON clause.
You can change the compute label by using COMPUTE LABEL. If you do not define a label for the computed value, SQL*Plus prints the unabbreviated function keyword.
The compute label can be suppressed by using the NOPRINT option of the COLUMN command on the break column. See the COMPUTE command for more details.
Example 4-12 Computing and Printing Subtotals
To compute the total of SAL by department, first list the current BREAK definition:
SQL> BREAK break on DEPTNO skip 0 page nodup on JOB skip 1 nodupNow enter the following COMPUTE command and run the current query:
SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> /SQL*Plus displays the following output:
DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 10 CLERK MILLER 1300 MANAGER CLARK 2450 ********** ********* ---------- sum 3750 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 20 CLERK SMITH 800 ADAMS 1100 ********** ********* ---------- sum 1900 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 30 CLERK JAMES 950 SALESMAN ALLEN 1600 TURNER 1500 WARD 1250 MARTIN 1250 ********** ********* ---------- sum 6550To compute the sum of salaries for departments 10 and 20 without printing the compute label:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SKIP 1 SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 ---------- 8750 20 JONES 2975 20 FORD 3000 20 SMITH 800 20 SCOTT 3000 20 ADAMS 1100 ---------- 10875To compute the salaries at the end of the report:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 JONES 2975 20 FORD 3000 20 SMITH 800 20 SCOTT 3000 20 ADAMS 1100 ---------- 19625Note: The format of the column SAL controls the appearance of the sum of SAL, as well as the individual values of SAL. When you establish the format of a NUMBER column, you must allow for the size of sums you will include in your report.
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORTExample 4-13 Computing and Printing a Grand Total
To calculate and print the grand total of salaries for all salesmen and change the compute label, first enter the following BREAK and COMPUTE commands:
SQL> BREAK ON REPORT SQL> COMPUTE SUM LABEL TOTAL OF SAL ON REPORTNext, enter and run a new query:
SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE JOB = 'SALESMAN';SQL*Plus displays the results:
ENAME SAL ---------- -------- ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ********** -------- TOTAL 5600To print a grand total (or grand average, grand maximum, and so on) in addition to subtotals (or sub-averages, and so on), include a break column and an ON REPORT clause in your BREAK command. Then, enter one COMPUTE command for the break column and another to compute ON REPORT:
BREAK ON break_column ON REPORT COMPUTE function LABEL label_name OF column ON break_column COMPUTE function LABEL label_name OF column ON REPORT
Example 4-14 Computing the Same Type of Summary Value on Different Columns
To print the total of salaries and commissions for all salesmen, first enter the following COMPUTE command:
SQL> COMPUTE SUM OF SAL COMM ON REPORTYou do not have to enter a BREAK command; the BREAK you entered in Example 4-13 is still in effect. Now, add COMM to the current query:
SQL> 1 SELECT ENAME, SAL, COMMFinally, run the revised query to see the results:
SQL> / ENAME SAL COMM ---------- -------- ---------- ALLEN 1600 300 WARD 1250 500 MARTIN 1250 1400 TURNER 1500 0 ********** -------- ---------- sum 5600 2200You can also print multiple summary lines on the same break column. To do so, include the function for each summary line in the COMPUTE command as follows:
COMPUTE function LABEL label_name function LABEL label_name function LABEL label_name ... OF column ON break_columnIf you include multiple columns after OFF and before ON, COMPUTE calculates and prints values for each column you specify.
Example 4-15 Computing Multiple Summary Lines on the Same Break Column
To compute the average and sum of salaries for the sales department, first enter the following BREAK and COMPUTE commands:
SQL> BREAK ON DEPTNO SQL> COMPUTE AVG SUM OF SAL ON DEPTNONow, enter and run the following query:
SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO = 30 4 ORDER BY DEPTNO, SAL;SQL*Plus displays the results:
DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 WARD 1250 MARTIN 1250 TURNER 1500 ALLEN 1600 BLAKE 2850 ********** ---------- avg 1566.66667 sum 9400
COMPUTEYou can remove all the COMPUTE definitions by entering the CLEAR command with the COMPUTES clause.
Example 4-16 Removing COMPUTE Definitions
To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:
SQL> CLEAR BREAKS breaks cleared SQL> CLEAR COMPUTES computes clearedYou may wish to place the commands CLEAR BREAKS and CLEAR COMPUTES at the beginning of every command file to ensure that previously entered BREAK and COMPUTE commands will not affect queries you run in a given file.
The word report refers to the complete results of a query. You can also place headers and footers on each report and format them in the same way as top and bottom titles on pages.
You can also set a header and footer for each report. The REPHEADER command defines the report header; the REPFOOTER command defines the report footer.
A TTITLE, BTITLE, REPHEADER or REPFOOTER command consists of the command name followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:
TTITLE position_clause(s) char_value position_clause(s) char_value ...
BTITLE position_clause(s) char_value position_clause(s) char_value ...
REPHEADER position_clause(s) char_value position_clause(s) char_value ...
REPFOOTER position_clause(s) char_value position_clause(s) char_value ...The most often used clauses of TTITLE, BTITLE, REPHEADER and REPFOOTER are summarized in the following table. For descriptions of all TTITLE, BTITLE, REPHEADER and REPFOOTER clauses, see the discussions of TTITLE and REPHEADER.
To put titles at the top and bottom of each page of a report, enter
SQL> TTITLE CENTER - > 'ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT' SQL> BTITLE CENTER 'COMPANY CONFIDENTIAL'Now run the current query:
SQL> /SQL*Plus displays the following output:
ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 COMPANY CONFIDENTIALExample 4-18 Placing a Header on a Report
To put a report header on a separate page, and to center it, enter
SQL> REPHEADER PAGE CENTER 'ACME WIDGET'Now run the current query:
SQL> /SQL*Plus displays the following output on page one
ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT ACME WIDGET COMPANY CONFIDENTIALand the following output on page two
ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 COMPANY CONFIDENTIALTo suppress the report header without changing its definition, enter
SQL> REPHEADER OFF
You can accomplish these changes by adding some clauses to the TTITLE command and by resetting the system variable LINESIZE, as the following example shows.
You can format report headers and footers in the same way as BTITLE and TTITLE using the REPHEADER and REPFOOTER commands.
Example 4-19 Positioning Title Elements
To redisplay the personnel report with a repositioned top title, enter the following commands:
SQL> TTITLE CENTER 'A C M E W I D G E T' SKIP 1 - > CENTER ================ SKIP 1 LEFT 'PERSONNEL REPORT' - > RIGHT 'SALES DEPARTMENT' SKIP 2 SQL> SET LINESIZE 60 SQL> /SQL*Plus displays the results:
A C M E W I D G E T ==================== PERSONNEL REPORT SALES DEPARTMENT DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 COMPANY CONFIDENTIALThe LEFT, RIGHT, and CENTER clauses place the following values at the beginning, end, and center of the line. The SKIP clause tells SQL*Plus to move down one or more lines.
Note that there is no longer any space between the last row of the results and the bottom title. The last line of the bottom title prints on the last line of the page. The amount of space between the last row of the report and the bottom title depends on the overall page size, the number of lines occupied by the top title, and the number of rows in a given page. In the above example, the top title occupies three more lines than the top title in the previous example. You will learn to set the number of lines per page later in this chapter.
To always print n blank lines before the bottom title, use the SKIP n clause at the beginning of the BTITLE command. For example, to skip one line before the bottom title in the example above, you could enter the following command:
BTITLE SKIP 1 CENTER 'COMPANY CONFIDENTIAL'
Exercise 4-20 Indenting a Title Element
To print the company name left-aligned with the report name indented five spaces on the next line, enter
SQL> TTITLE LEFT 'ACME WIDGET' SKIP 1 - > COL 6 'SALES DEPARTMENT PERSONNEL REPORT' SKIP 2Now re-run the current query to see the results:
SQL> / ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 COMPANY CONFIDENTIAL
SQL> DEFINE LINE1 = 'This is the first line...' SQL> DEFINE LINE2 = 'This is the second line...' SQL> DEFINE LINE3 = 'This is the third line...'Then, reference the variables in your TTITLE or BTITLE command as follows:
SQL> TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 - > CENTER LINE3
TTITLE LEFT system-maintained_value_nameThere are five system-maintained values you can display in titles, the most commonly used of which is SQL.PNO (the current page number). Refer to the TTITLE command for a list of system-maintained values you can display in titles.
Example 4-21 Displaying the Current Page Number in a Title
To display the current page number at the top of each page, along with the company name, enter the following command:
SQL> TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' SQL.PNO SKIP 2Now re-run the current query:
SQL> /SQL*Plus displays the following results:
ACME WIDGET PAGE: 1 DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 COMPANY CONFIDENTIALNote that SQL.PNO has a format ten spaces wide. You can change this format with the FORMAT clause of TTITLE (or BTITLE).
Example 4-22 Formatting a System-Maintained Value in a Title
To close up the space between the word PAGE: and the page number, re-enter the TTITLE command as shown:
SQL> TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT 999 - > SQL.PNO SKIP 2Now re-run the query:
SQL> /SQL*Plus displays the following results:
ACME WIDGET PAGE: 1 DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 COMPANY CONFIDENTIAL
TTITLE BTITLETo suppress a title definition, enter:
TTITLE OFF BTITLE OFFThese commands cause SQL*Plus to cease displaying titles on reports, but do not clear the current definitions of the titles. You may restore the current definitions by entering
TTITLE ON BTITLE ON
COLUMN column_name NEW_VALUE variable_nameYou must include the master column in an ORDER BY clause and in a BREAK command using the SKIP PAGE clause.
Example 4-23 Creating a Master/Detail Report
Suppose you want to create a report that displays two different managers' employee numbers, each at the top of a separate page, and the people reporting to the manager on the same page as the manager's employee number. First create a variable, MGRVAR, to hold the value of the current manager's employee number:
SQL> COLUMN MGR NEW_VALUE MGRVAR NOPRINTBecause you will display the managers' employee numbers in the title, you do not want them to print as part of the detail. The NOPRINT clause you entered above tells SQL*Plus not to print the column MGR.
Next, include a label and the value in your page title, enter the proper BREAK command, and suppress the bottom title from the last example:
SQL> TTITLE LEFT 'Manager: ' MGRVAR SKIP 2 SQL> BREAK ON MGR SKIP PAGE SQL> BTITLE OFFFinally, enter and run the following query:
SQL> SELECT MGR, ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE MGR IN (7698, 7839) 3 ORDER BY MGR;SQL*Plus displays the following output:
Manager: 7698 ENAME SAL DEPTNO ---------- -------- ---------- ALLEN 1600 30 WARD 1250 30 TURNER 1500 30 MARTIN 1250 30 JAMES 950 30 Manager: 7839 ENAME SAL DEPTNO ---------- -------- ---------- JONES 2975 20 BLAKE 2850 30 CLARK 2450 10If you want to print the value of a column at the bottom of the page, you can use the COLUMN command in the following form:
COLUMN column_name OLD_VALUE variable_nameSQL*Plus prints the bottom title as part of the process of breaking to a new page--after finding the new value for the master column. Therefore, if you simply referenced the NEW_VALUE of the master column, you would get the value for the next set of details. OLD_VALUE remembers the value of the master column that was in effect before the page break began.
To create the variable (in this example named _DATE), you can add the following commands to your SQL*Plus LOGIN file:
SET TERMOUT OFF BREAK ON TODAY COLUMN TODAY NEW_VALUE _DATE SELECT TO_CHAR(SYSDATE, 'fmMonth DD, YYYY') TODAY FROM DUAL; CLEAR BREAKS SET TERMOUT ONWhen you start SQL*Plus, these commands place the value of SYSDATE (the current date) into a variable named _DATE. To display the current date, you can reference _DATE in a title as you would any other variable.
The date format model you include in the SELECT command in your LOGIN file determines the format in which SQL*Plus displays the date. See your Oracle8 Server SQL Reference Manual for more information on date format models. For more information about the LOGIN file, see "Modifying Your LOGIN File".
You can also enter these commands interactively at the command prompt. For more information, see the COLUMN command.
The default page dimensions used by SQL*Plus are shown below:
You can change the page length with the system variable PAGESIZE. For example, you may wish to do so when you print a report, since printed pages are customarily 66 lines long.
To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:
SET NEWPAGE number_of_linesIf you set NEWPAGE to zero, SQL*Plus skips zero lines and displays and prints a formfeed character to begin a new page. On most types of computer screens, the formfeed character clears the screen and moves the cursor to the beginning of the first line. When you print a report, the formfeed character makes the printer move to the top of a new sheet of paper, even if the overall page length is less than that of the paper. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between report pages.
To set the number of lines on a page, use the PAGESIZE variable of the SET command:
SET PAGESIZE number_of_linesYou may wish to reduce the linesize to center a title properly over your output, or you may want to increase linesize for printing on wide paper. You can change the line width using the LINESIZE variable of the SET command:
SET LINESIZE number_of_charactersExample 4-24 Setting Page Dimensions
To set the page size to 66 lines, clear the screen (or advance the printer to a new sheet of paper) at the start of each page, and set the linesize to 32, enter the following commands:
SQL> SET PAGESIZE 66 SQL> SET NEWPAGE 0 SQL> SET LINESIZE 32Now enter and run the following commands to see the results:
SQL> TTITLE CENTER 'ACME WIDGET PERSONNEL REPORT' SKIP 1 - > CENTER '10-JAN-89' SKIP 2 SQL> COLUMN DEPTNO HEADING DEPARTMENT SQL> COLUMN ENAME HEADING EMPLOYEE SQL> COLUMN SAL FORMAT $99,999 HEADING SALARY SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 ORDER BY DEPTNO;SQL*Plus displays a formfeed followed by the query results:
ACME WIDGET PERSONNEL REPORT 10-JAN-89 DEPARTMENT EMPLOYEE SALARY ---------- ---------- ---------- 10 CLARK $2,450 10 KING $5,000 10 MILLER $1,300 20 SMITH $800 20 ADAMS $1,100 20 FORD $3,000 20 SCOTT $3,000 20 JONES $2,975 30 ALLEN $1,600 30 BLAKE $2,850 30 MARTIN $1,250 30 JAMES $950 30 TURNER $1,500 30 WARD $1,250Now reset PAGESIZE, NEWPAGE, and LINESIZE to their default values:
SQL> SET PAGESIZE 24 SQL> SET NEWPAGE 1 SQL> SET LINESIZE 80To list the current values of these variables, use the SHOW command:
SQL> SHOW PAGESIZE pagesize 24 SQL> SHOW NEWPAGE newpage 1 SQL> SHOW LINESIZE linesize 80Through the SQL*Plus command SPOOL, you can store your query results in a file or print them on your computer's default printer.
SPOOL file_nameSQL*Plus stores all information displayed on the screen after you enter the SPOOL command in the file you specify.
To store the results of a query in a file--and still display them on the screen--enter the SPOOL command in the following form:
SPOOL file_nameIf you do not follow the filename with a period and an extension, SPOOL adds a default file extension to the filename to identify it as an output file. The default varies with the host operating system; on most hosts it is LST or LIS. See the Oracle installation and user's manual(s) provided for your operating system for more information.
SQL*Plus continues to spool information to the file until you turn spooling off, using the following form of SPOOL:
SPOOL OFF
To create a flat file with SQL*Plus, you first must enter the following SET commands:
SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFFAfter entering these commands, you use the SPOOL command as shown in the previous section to create the flat file.
The SET COLSEP command may be useful to delineate the columns. For more information, see the SET command.
SPOOL OUTSQL*Plus stops spooling and copies the contents of the spooled file to your host computer's standard (default) printer. SPOOL OUT does not delete the spool file after printing.
Example 4-25 Sending Query Results to a Printer
To generate a final report and spool and print the results, create a command file named EMPRPT containing the following commands.
First, use EDIT to create the command file with your host operating system text editor. (Do not use INPUT and SAVE, or SQL*Plus will add a slash to the end of the file and will run the command file twice--once as a result of the semicolon and once due to the slash.)
SQL> EDIT EMPRPTNext, enter the following commands into the file, using your text editor:
SPOOL TEMP CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN DEPTNO HEADING DEPARTMENT COLUMN ENAME HEADING EMPLOYEE COLUMN SAL HEADING SALARY FORMAT $99,999 BREAK ON DEPTNO SKIP 1 ON REPORT COMPUTE SUM OF SAL ON DEPTNO COMPUTE SUM OF SAL ON REPORT SET PAGESIZE 21 SET NEWPAGE 0 SET LINESIZE 30 TTITLE CENTER 'A C M E W I D G E T' SKIP 2 - LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' - FORMAT 999 SQL.PNO SKIP 2 BTITLE CENTER 'COMPANY CONFIDENTIAL' SELECT DEPTNO, ENAME, SAL FROM EMP ORDER BY DEPTNO; SPOOL OUTIf you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file. Save the file (you automatically return to SQL*Plus). Now, run the command file EMPRPT:
SQL> @EMPRPTSQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), spools it to the file TEMP, and sends the contents of TEMP to your default printer:
A C M E W I D G E T EMPLOYEE REPORT PAGE: 1 DEPARTMENT EMPLOYEE SALARY ---------- ---------- -------- 10 CLARK $2,450 KING $5,000 MILLER $1,300 ********** -------- sum $8,750 20 SMITH $800 ADAMS $1,100 FORD $3,000 SCOTT $3,000 JONES $2,975 ********** -------- sum $10,875 COMPANY CONFIDENTIAL
A C M E W I D G E T EMPLOYEE REPORT PAGE: 2 DEPARTMENT EMPLOYEE SALARY ---------- ---------- -------- 30 ALLEN $1,600 BLAKE $2,850 MARTIN $1,250 JAMES $900 TURNER $1,500 WARD $1,250 ********** -------- sum $9,400 ********** -------- sum $29,025 COMPANY CONFIDENTIAL
Prev Next |
Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |