Release 8.1.7
Part Number A82950-01
Library |
Product |
Contents |
Index |
| SQL*Plus
User's Guide and Reference
Release 8.1.7 Part Number A82950-01 |
|
This chapter explains how to format your query results to produce a finished report. This chapter covers the following topics:
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 in Chapter 1.
Through the SQL*Plus COLUMN command, you can change the column
headings and reformat the column data in your query results.
When displaying column headings, you can either use the default
heading or you can change it using the COLUMN command. The sections below
describe how the default headings are derived and how you can alter them
with the COLUMN command.
SQL*Plus uses column or expression names as default column
headings when displaying query results. Column names are often short and
cryptic, however, and expressions can be hard to understand.
You can define a more useful column heading with the HEADING clause of the COLUMN command, in the format shown below:
COLUMN column_name HEADING column_heading
See the COLUMN command in Chapter
8 for more details.
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 0
|
Note: 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 in
Chapter
8 for more information.)
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 rerun 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 0
To change the character used to underline each column heading,
set the UNDERLINE variable of the SET command to the desired character.
To change the character used to underline headings to an equal sign and rerun 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 0
Now 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 that you wish to continue
the command on another line.
|
When displaying NUMBER columns, you can either accept the
SQL*Plus default display width or you can change it using the COLUMN command.
The sections below describe the default display and how you can alter the
default with the COLUMN command.
A NUMBER column's width equals the width of the heading or
the width of the FORMAT plus one space for the sign, whichever is greater.
If you do not explicitly use FORMAT, then the column's width will always
be at least the value of SET NUMWIDTH.
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.
The COLUMN command identifies the column you want to format and the model you want to use, as shown below:
COLUMN column_name FORMAT model
Use 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 in Chapter
8.
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,990
Now rerun 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 0
Use 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: or exit from SQL*Plus. |
When displaying datatypes, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. Datatypes, in this manual, include the following variables:
|
Note: The NCHAR, NVARCHAR2 (NCHAR VARYING), CLOB and NCLOB datatypes
require Oracle8 or higher.
|
The default width of datatype columns is the width of the
column in the database.
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
in Chapter 8.
|
Note: The default justification for datatypes is left justification.
|
You can change the displayed width of a datatype or DATE,
by using the COLUMN command with a format model consisting of the letter
A (for alphanumeric) followed by a number representing the width of the
column in characters.
Within the COLUMN command, identify the column you want to format and the model you want to use:
COLUMN column_name FORMAT model
If 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 in Chapter 8 for more details.
To set the width of the column ENAME to four characters and rerun 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 ER
|
Note: or exit from SQL*Plus. |
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 the COLUMN
command in Chapter 8 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
When you want to give more than one column the same display
attributes, you can reduce the length of the commands you must enter by
using the LIKE clause of the COLUMN command. The LIKE clause tells SQL*Plus
to copy the display attributes of a previously defined column to the new
column, except for changes made by other clauses in the same command.
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 Bonus
Rerun 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
To list the current display attributes for a given column, use the COLUMN command followed by the column name only, as shown below:
COLUMN column_name
To list the current display attributes for all columns, enter the COLUMN command with no column names or clauses after it:
COLUMN
To 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 CLEAR
To reset the attributes for all columns, use the COLUMNS
clause of the CLEAR command.
To reset all columns' display attributes to their default values, enter the following command:
SQL> CLEAR COLUMNS columns cleared
You can suppress and restore the display attributes you have given a specific column. To suppress a column's display attributes, enter a COLUMN command in the following form:
COLUMN column_name OFF
The 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
As you have seen, by default SQL*Plus wraps column values
to additional lines when the value does not fit within the column width.
If you want to insert a record separator (a line of characters or
a blank line) after each wrapped line of output (or after every row), use
the RECSEP and RECSEPCHAR variables of the SET command.
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_WRAPPED
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_WRAPPED
Finally, 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 BOSTON
If 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
When you use an ORDER BY clause in your SQL SELECT command,
rows with the same value in the ordered column (or expression) are displayed
together in your output. You can make this output more useful to the user
by using the SQL*Plus BREAK and COMPUTE commands to create subsets of records
and add space and/or summary lines after each subset.
COMPUTE command functions are always executed in the sequence
AVG, COUNT, MINIMUM, MAXIMUM, NUMBER, SUM, STD, VARIANCE, regardless of
their order in the COMPUTE command.
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 1250 9 rows selected.
To 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.
The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:
BREAK ON 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*Plus 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 9 rows selected.
You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:
BREAK ON break_column SKIP n
To skip a page, use the command in this form:
BREAK ON break_column SKIP PAGE
To place one blank line between departments, enter the following command:
SQL> BREAK ON DEPTNO SKIP 1
Now rerun 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 9 rows selected.
You may wish to insert blank lines or a blank page after every row. To skip n lines after every row, use BREAK in the following form:
BREAK ON ROW SKIP n
To skip a page after every row, use
BREAK ON ROW SKIP PAGE
|
Note: SKIP PAGE does not cause a physical page break unless you
have also specified NEWPAGE 0.
|
Suppose you have more than one column in your ORDER BY clause
and wish to insert space when each column's value changes. Each BREAK command
you enter replaces the previous one. Thus, if you want to use different
spacing techniques in one report or insert space after the value changes
in more than one ordered column, you must specify multiple columns and
actions in a single BREAK command.
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, JOB
Now, 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 1
To show that SKIP PAGE has taken effect, create a TTITLE with a page number, enter
SQL> TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
Run 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 9 rows selected.
Before continuing, turn off the top title display without changing its definition, using:
SQL> TTITLE OFF
You can list your current break definition by entering the BREAK command with no clauses:
BREAK
You can remove the current break definition by entering the CLEAR command with the BREAKS clause:
CLEAR BREAKS
You 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.
If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column
You 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_2
The 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. For more information about the COMPUTE
command, see the "Command Reference" in Chapter
8
The following table lists compute functions and their effects
The function you specify in the COMPUTE command applies to
all columns you enter after OF and before ON. The computed values print
on a separate line when the value of the ordered column changes.
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 in Chapter 8 for more details.
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 nodup
Now 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 6550 9 rows selected.
To 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 ---------- 10875 8 rows selected.
To 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 ---------- 19625 8 rows selected.
You can calculate and print summary lines based on all values in a column by using BREAK and COMPUTE in the following forms:
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORT
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 REPORT
Next, 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 5600
To 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
You can compute and print the same type of summary value
on different columns. To do so, enter a separate COMPUTE command for each
column.
To print the total of salaries and commissions for all salesmen, first enter the following COMPUTE command:
SQL> COMPUTE SUM OF SAL COMM ON REPORT
You 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, COMM
Finally, 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 2200
You 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_column
If you include multiple columns after OFF and before ON,
COMPUTE calculates and prints values for each column you specify.
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 DEPTNO
Now, 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.6667 sum 9400 6 rows selected.
You can list your current COMPUTE definitions by entering the COMPUTE command with no clauses:
COMPUTE
To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:
SQL> CLEAR BREAKS breaks cleared SQL> CLEAR COMPUTES computes cleared
You 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 page refers to a screenful of information
on your display or a page of a spooled (printed) report. You can place
top and bottom titles on each page, set the number of lines per page, and
determine the width of each line.
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.
As you have already seen, you can set a title to display
at the top of each page of a report. You can also set a title to display
at the bottom of each page. The TTITLE command defines the top title; the
BTITLE command defines the bottom title.
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 ...
For descriptions of all TTITLE, BTITLE, REPHEADER and REPFOOTER
clauses, see the TTITLE and REPHEADER
commands in Chapter 8.
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 CONFIDENTIAL 6 rows selected.
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 CONFIDENTIAL
and 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 CONFIDENTIAL 6 rows selected.
To suppress the report header without changing its definition, enter
SQL> REPHEADER OFF
The report in the preceding exercises might look more attractive
if you give the company name more emphasis and place the type of report
and the department name on either end of a separate line. It may also help
to reduce the linesize and thus center the titles more closely around the
data.
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.
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 CONFIDENTIAL 6 rows selected.
The 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'
You can use the COL clause in TTITLE or BTITLE to indent
the title element a specific number of spaces. For example, COL 1 places
the following values in the first character position, and so is equivalent
to LEFT, or an indent of zero. COL 15 places the title element in the 15th
character position, indenting it 14 spaces.
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 2
Now rerun 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 6 rows selected.
If you need to enter a title greater than 500 characters in length, you can use the SQL*Plus command DEFINE to place the text of each line of the title in a separate user variable:
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
You can display the current page number and other system-maintained values in your title by entering a system value name as a title element, for example:
TTITLE LEFT system-maintained_value_name
There are five system-maintained values you can display in
titles, the most commonly used of which is SQL.PNO (the current page number).
For a list of system-maintained values you can display in titles, see the
TTITLE
command in the "Command Reference" in Chapter
8.
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 2
Now rerun 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 CONFIDENTIAL 6 rows selected.
Note that SQL.PNO has a format ten spaces wide. You can change
this format with the FORMAT clause of TTITLE (or BTITLE).
To close up the space between the word PAGE: and the page number, reenter the TTITLE command as shown:
SQL> TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT 999 - > SQL.PNO SKIP 2
Now rerun 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 6 rows selected.
To list a page title definition, enter the appropriate title command with no clauses:
TTITLE BTITLE
To suppress a title definition, enter:
TTITLE OFF BTITLE OFF
These 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
You may wish to create a master/detail report that displays a changing master column value at the top of each page with the detail query results for that value below. You can reference a column value in a top title by storing the desired value in a variable and referencing the variable in a TTITLE command. Use the following form of the COLUMN command to define the variable:
COLUMN column_name NEW_VALUE variable_name
You must include the master column in an ORDER BY clause
and in a BREAK command using the SKIP PAGE clause.
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 NOPRINT
Because 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 OFF
Finally, enter and run the following query:
SQL> SELECT MGR, ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE MGR IN (7698, 7839) 4 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 10 8 rows selected.
If 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_name
SQL*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.
You can, of course, date your reports by simply typing a
value in the title. This is satisfactory for ad hoc reports, but if you
want to run the same report repeatedly, you would probably prefer to have
the date automatically appear when the report is run. You can do this by
creating a variable to hold the current date.
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 ON
When 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 Oracle8i SQL Reference for more information on date format
models. For more information about the LOGIN file, see the section "Modifying
Your LOGIN File" in Chapter 3.
You can also enter these commands interactively at the command
prompt. For more information, see the COLUMN
command in Chapter 8.
Typically, a page of a report contains the number of blank
line(s) set in the NEWPAGE variable of the SET command, a top title, column
headings, your query results, and a bottom title. SQL*Plus displays a report
that is too long to fit on one page on several consecutive pages, each
with its own titles and column headings. The amount of data SQL*Plus displays
on each page depends on the current page dimensions.
The default page dimensions used by SQL*Plus are shown below:
You can change these settings to match the size of your computer
screen or, for printing, the size of a sheet of paper.
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_lines
If 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_lines
You 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_characters
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 32
Now enter and run the following commands to see the results:
SQL> TTITLE CENTER 'ACME WIDGET PERSONNEL REPORT' SKIP 1 - > CENTER '10-JAN-99' 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-99 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,250 14 rows selected.
Now reset PAGESIZE, NEWPAGE, and LINESIZE to their default values:
SQL> SET PAGESIZE 24 SQL> SET NEWPAGE 1 SQL> SET LINESIZE 80
To 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 80
Through the SQL*Plus command SPOOL, you can store your query
results in a file or print them on your computer's default printer.
Send your query results to a file when you want to edit them
with a word processor before printing or include them in a letter, memo,
or other document.
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_name
If 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
When moving data between different software products, it
is sometimes necessary to use a "flat" file (an operating system file with
no escape characters, headings, or extra characters embedded). For example,
if you do not have Net8, you need to create a flat file for use with SQL*Loader
when moving data from Oracle7 to Oracle8.
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 OFF SET MARKUP HTML OFF SPOOL OFF
After 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
in Chapter 8.
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_name
SQL*Plus stores all information displayed on the screen after
you enter the SPOOL command in the file you specify.
To print query results, spool them to a file as described in the previous section. Then, instead of using SPOOL OFF, enter the command in the following form:
SPOOL OUT
SQL*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.
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 EMPRPT
Next, 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 OUT
If 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> @EMPRPT
SQL*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
SQL*Plus allows you to generate either a complete standalone
web page, or HTML output which can be embedded in a web page. You can use
SQLPLUS -MARKUP HTML ON or SET MARKUP HTML ON SPOOL ON to produce standalone
web pages. SQL*Plus generates complete HTML pages automatically encapsulated
with <HTML> and <BODY> tags.
The content is written to a HTML table by default, though
you can optionally direct output to the HTML <PRE> tag so that it displays
in a web browser exactly as it appears in SQL*Plus. See the SQLPLUS -MARKUP
command in the "Starting SQL*Plus Using the SQLPLUS
Command" section of Chapter 7 and the
SET MARKUP command in the "SET" section of
Chapter
8 for more information about these commands.
SQLPLUS -MARKUP HTML ON is useful when embedding SQL*Plus
in program scripts. On starting, it outputs the HTML and BODY tags before
executing any commands. All subsequent output is in HTML until SQL*Plus
terminates. The -SILENT and -RESTRICT command line options may be effectively
used in conjunction with -MARKUP to suppress the display of SQL*Plus prompt
and banner information and to restrict the use of some commands.
SET MARKUP HTML ON SPOOL ON generates complete HTML pages
for each subsequently spooled file. The HTML tags in a spool file are closed
when SPOOL OFF is executed or SQL*Plus exits.
You can use SET MARKUP HTML ON SPOOL OFF to generate HTML
output suitable for embedding in an existing web page. HTML output generated
this way has no <HTML> or <BODY> tags.
You use the SET MARKUP command interactively during a SQL*Plus
session to write HTML to a spool file. The output can be viewed in a web
browser.
SET MARKUP only specifies that SQL*Plus output will be HTML
encoded, it does not create or begin writing to an output file. You must
use SET MARKUP HTML ON SPOOL ON to enable the spooling of HTML output.
You then use the SQL*Plus SPOOL command to create and name a spool file,
and to begin writing HMTL output to it.
When creating a HTML file, it is important and convenient
to specify a .html file extension which is the standard file extension
for HTML files. This allows you to easily identify the type of your
output files, and also allows web browsers to identify and correctly display
your HTML files. If no extension is specified, the default SQL*Plus file
extension, .lst, is used.
You use SPOOL OFF or EXIT to append final HTML tags to the
spool file and then close it. If you enter another SPOOL filename
command, the current spool file is closed as for SPOOL OFF or EXIT, and
a new HTML spool file with the specified name is created.
You can use the SET MARKUP command to enable or disable HTML
output as required.
You can create HTML output in an interactive SQL*Plus session using the SET MARKUP command. You can include an embedded style sheet, or any other valid text in the HTML <HEAD> tag. Open a SQL*Plus session and enter the following:
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON - HEAD '<TITLE>Department Report</TITLE> - <STYLE type="text/css"> - <!-- BODY {background: yellow} --> - </STYLE>' - BODY 'TEXT="#FF00FF"' - TABLE 'WIDTH="50%" BORDER="5"'
You use the COLUMN command to control column output. The following COLUMN commands create new heading names for the SQL query output. The first command also turns off entity mapping for the DNAME column to allow HTML hyperlinks to be correctly created in this column of the output data:
COLUMN DNAME HEADING "Department" ENTMAP OFF COLUMN LOC HEADING "Location"
SET MARKUP HTML ON SPOOL ON enables SQL*Plus to write HTML to a spool file. The following SPOOL command triggers the writing of the <HTML> and <BODY> tags to the named file:
SPOOL report.html
After the SPOOL command, anything entered or displayed on
standard output is written to the spool file, report.html.
Enter a SQL query:
SELECT '<A HREF="http://oracle.com/'||DNAME||'.html">'||DNAME||'</A>' DNAME, LOC FROM DEPT ORDER BY DEPTNO;
Enter the SPOOL OFF command:
SPOOL OFF
The </BODY> and </HTML> tags are appended to the spool
file, report.html, before it is closed.
The output from report.sql is a file, report.html. This is is a standalone web page that can be loaded into a web browser. Open report.html in your web browser. It should appear something like the following:
Text description of the illustration
4-report.gif
The SQL*Plus command in this example contains several items of usage worth noting:
View the report.html source in your web browser, or in a text editor to see that the table cells for the Department column contain fully formed hyperlinks as shown:
<html><head> <title>Department Report</title> <STYLE type="text/css"> <!-- BODY {background: yellow} --> </STYLE> <META Name="generator" content="SQL*Plus 8.1.7"> </head> <body text="#ff00ff"> <p> <table width="50%" border="5"> <tr><th>Department</th><th>Location</th></tr> <tr><td><a href="http://oracle.com/ACCOUNTING.html">ACCOUNTING</a></td> <td>NEW YORK</td></tr> <tr><td><a href="http://oracle.com/RESEARCH.html">RESEARCH</a></td> <td>DALLAS</td></tr> <tr><td><a href="http://oracle.com/SALES.html">SALES</a></td> <td>CHICAGO</td></tr> <tr><td><a href="http://oracle.com/OPERATIONS.html">OPERATIONS</a></td> <td>BOSTON</td></tr> </table> <p></body></html>
Enter the following command at the operating system prompt:
SQLPLUS -S -M "HTML ON TABLE 'BORDER="2"'" scott/tiger @dept.sql > dept.html
where dept.sql contains:
SELECT DEPTNO, DNAME, LOC FROM DEPT ORDER BY DEPTNO; EXIT
This example starts SQL*Plus with user "scott", sets HTML
ON, sets a BORDER attribute for TABLE, and runs the script dept.sql.
The output from dept.sql is a complete web page which is this case
has been redirected to the file dept.html using the > operating
system command. It could be sent to a web browser if SQL*Plus was called
in a web server CGI script. See Creating a Standalone
Web Report from a CGI Script for information about calling SQL*Plus
from a CGI script.
Start your web browser and enter the appropriate URL to open dept.html:
Text description of the illustration
4-dept.gif
The SQLPLUS command in this example contains three layers of nested quotes. From the inside out, these are:
The nesting of quotes may be different in some operating
systems or program scripting languages.
The SQLPLUS -MARKUP command allows you to start a SQL*Plus
session in Internet enabled mode, rather than using the SET MARKUP command
interactively. This allows you to run a SQL*Plus session embedded inside
a Common Gateway Interface (CGI) script or an operating system command
file. A file created in this way can be displayed directly in a web browser.
You can use any script language which is supported by your web server such
as a shell script, a Windows command file (batch file), Java, JavaScript
or a Perl file.
You can use this embedded approach to produce HTML web outputs
that use existing SQL*Plus scripts unchanged. It provides an easy way to
provide dynamically-created, web-based reports.
You can use a CGI script to run SQL*Plus, and so produce a web report from a SQL script. There are three main elements required:
In this example, the web page is a form which prompts for
your username and password, a database connection string and the name of
the SQL script to run.
<html> <head><title>SQL*Plus Report Demonstration</title></head> <body bgcolor="#ffffff"> <h1>SQL*Plus Report Demonstration</h1> <!-- Change the URL here. On Windows NT you may need to use http://whatever/cgi-bin/perl?plus.pl if your web server is not configured to identify the script as a Perl program --> <form method=post action="http://whatever/cgi-bin/plus.pl"> <table border=0 summary=""> <tr> <td>Username:</td> <td><input type="text" name="username" size="10" align="left"></td> </tr> <tr> <td>Password:</td> <td><input type="password" name="password" size="10" align="left"></td> </tr> <tr> <td>Connect string: </td> <td><input type="text" name="db" size="10" align="left"></td> </tr> <tr> <td>Report to run: </td> <td><input type="text" name="script" value="emp.sql" size=40></td> </tr> <tr> <td><input type="submit" value="Run it"> <input type="reset" value="Reset Form"></td> <td> </td> </tr> </table> </form> </body> </html>
In this example, the CGI script is a Perl script, but it could be a shell script, a Java class or any other language supported by your web server. Create the following Perl CGI script and save it as sqlscript.pl in the cgi-bin directory of your web server:
# Set up the SQL*Plus environment $ENV{'ORACLE_SID'} = "P817"; # Your SID goes here $ENV{'ORACLE_HOME'} = "/oracle/817"; # Your Oracle Home directory goes here $ENV{'TNS_ADMIN'} = "/var/opt/oracle"; $plusexe = "$ENV{'ORACLE_HOME'}/bin/sqlplus"; # Extract parameters and values from data entered through the web browser $i=<>; @in = split(/[&;]/,$i); foreach $i (0 .. $#in) { ($key,$val) = split(/=/,$in[$i],2); # Change encoding to machine character set $key =~ s/%([A-Fa-f0-9]{2})/pack("c",hex($1))/ge; $val =~ s/%([A-Fa-f0-9]{2})/pack("c",hex($1))/ge; # Store the value $in{"$key"} = $val; } # Construct the connection string from values passed in $connstr = $in{'username'}."/".$in{'password'}; $connstr = $connstr."@".$in{'db'} if ($in{'db'}); # Construct the SQL script to be run $script = "@".$in{'script'}; # Force output to be flushed $| = 1; # Print mime type print "Content-Type: text/html\n\n"; if ($debug) { print "<html><body>\n"; print "$plusexe:$connstr:$script:\n"; print "</body></html>\n"; exit; } # Call SQL*Plus with the parameters entered from the web browser system ("$plusexe -r 3 -s -m \"html on\" $connstr $script"); exit;
Create the following SQL*Plus script in a text editor and save it as employee.sql in the cgi-bin directory of your web server:
select empno, ename from emp; exit;
Start your web browser and enter the appropriate URL to open sqlplus.html:
Text description of the illustration
4-plus.gif
Click Run It to execute the shell script sqlscript.pl, which in turn starts SQL*Plus and runs the employee.sql script. The query results are displayed directly in your web browser:
Text description of the illustration
4-employ.gif
It is recommended that you use SILENT mode to start your
SQL*Plus session. This ensures that only the results of your SQL query
appear in the web browser.
The SQLPLUS -SILENT option is particularly useful when used
in combination with -MARKUP to generate embedded SQL*Plus reports using
CGI scripts or operating system command files. It suppresses the display
of SQL*Plus commands and the SQL*Plus banner. Your HTML output shows only
the data resulting from your SQL query.
Certain characters, <, >, " and & have predefined meaning in HTML. In the previous example, you may have noticed that the > character was replaced by > as soon as you entered the SET MARKUP HTML ON command. To enable these characters to be displayed in your web browser, HTML provides character entities to use instead.
| Character | HTML Entity | Meaning |
|---|---|---|
|
< |
< |
Start HTML tag label |
|
> |
> |
End HTML tag label |
|
" |
" |
Double quote |
|
& |
& |
Ampersand |
The web browser displays the > character, but the actual
text in the HTML encoded file is the HTML entity, >. The SET MARKUP
option, ENTMAP, controls the substitution of HTML entities. ENTMAP is set
ON by default. It ensures that the characters <, >, " and & are
always replaced by the HTML entities representing these characters. This
prevents web browsers from misinterpreting these characters when they occur
in your SQL*Plus commands, or in data resulting from your query.
|
Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|