Re: SQL Plus report problem
Date: Thu, 06 May 1999 20:24:43 GMT
Message-ID: <fUmY2.4481$u34.1478_at_news.rdc1.md.home.com>
You can use some combination of setting the 'global' linesize and wrapping characteristics with
SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|
WOR[D_WRAPPED]|TRU[NCATED]}]
Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of
stored procedures or PL/SQL blocks in SQL*Plus. OFF suppresses the output of
DBMS_OUTPUT.PUT_LINE; ON displays the output.
SIZE sets the number of bytes of the output that can be buffered within the
Oracle8 Server. The default for n is 2000. n cannot be less than 2000 or
greater than 1,000,000.
When WRAPPED is enabled SQL*Plus wraps the server output within the line
size specified by SET LINESIZE, beginning new lines when required.
When WORD_WRAPPED is enabled, each line of server output is wrapped within
the line size specified by SET LINESIZE. Lines are broken on word
boundaries. SQL*Plus left justifies each line, skipping all leading
whitespace.
When TRUNCATED is enabled, each line of server output is truncated to the
line size specified by SET LINESIZE.
For each FORMAT, every server output line begins on a new output line.
globally set the just the Wrap characteristics of SQL*Plus OR
WRA[P] {OFF|ON} Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width. OFF truncates the SELECTed row; ON allows the SELECTed row to wrap to the next line. Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.
or tailor the output characteristics of individual columns (what you are trying to do) with
COL[UMN] [{column|expr} [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
Terms and Clauses
Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.
Refer to the following list for a description of each term or clause:
{column|expr}
Identifies the data item (typically, the name of a column) in a SQL SELECT command to which the column command refers. If you use an expression in a COLUMN command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a COLUMN command to refer to the expression in the SELECT command.
If you select columns with the same name from different tables, a COLUMN command for that column name will apply to both columns. That is, a COLUMN command for the column ENAME applies to all columns named ENAME that you reference in this session. COLUMN ignores table name prefixes in SELECT commands. Also, spaces are ignored unless the name is placed in double quotes.
To format the columns differently, assign a unique alias to each column within the SELECT command itself (do not use the ALIAS clause of the COLUMN command) and enter a COLUMN command for each column's alias.
ALI[AS] alias
Assigns a specified alias to a column, which can be used to refer to the column in BREAK, COMPUTE, and other COLUMN commands.
Note: A SQL*Plus alias is different from a SQL alias. See the Oracle8 Server SQL Reference Manual for further information on the SQL alias.
FOLD_A[FTER] Inserts a carriage return after the column heading and after each row in the column. SQL*Plus does not insert an extra carriage return after the last column in the SELECT list.
FOLD_B[EFORE] Inserts a carriage return before the column heading and before each row of the column. SQL*Plus does not insert an extra carriage return before the first column in the SELECT list.
FOR[MAT] format
Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999--not a variable.
Character Columns The default width of CHAR, NCHAR, VARCHAR2 (VARCHAR) and NVARCHAR2 (NCHAR VARYING) columns is the width of the column in the database. SQL*Plus formats these datatypes left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP.
A LONG, CLOB or NCLOB column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller.
A Trusted Oracle column of datatype MLSLABEL defaults to the width defined for the column in the database or the length of the column's heading, whichever is longer. The default display width for a Trusted Oracle ROWLABEL column is 15.
To change the width of a datatype or Trusted Oracle column to n, use FORMAT An. (A stands for alphanumeric.) 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. HEA[DING] text
Defines a column heading. If you do not use a HEADING clause, the column's heading defaults to column or expr. If text contains blanks or punctuation characters, you must enclose it with single or double quotes. Each occurrence of the HEADSEP character (by default, '|') begins a new line. For example,
COLUMN ENAME HEADING 'Employee |Name'
would produce a two-line column heading. See the HEADSEP variable of the SET
command in this chapter for information on changing the HEADSEP character.
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} Aligns the heading. If you do not use a JUSTIFY clause, headings for NUMBER columns default to RIGHT and headings for other column types default to LEFT. LIKE {expr|alias}
Copies the display attributes of another column or expression (whose attributes you have already defined with another COLUMN command). LIKE copies only attributes not defined by another clause in the current COLUMN command.
NEW_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause. See the example at the end of this command description.
For information on displaying a column value in the bottom title, see COLUMN OLD_VALUE. Refer to TTITLE for more information on referencing variables in titles. See COLUMN FORMAT for details on formatting and valid format models.
NOPRI[NT]|PRI[NT] Controls the printing of the column (the column heading and all the selected values). NOPRINT turns the printing of the column off. PRINT turns the printing of the column on.
NUL[L] text
Controls the text SQL*Plus displays for null values in the given column. The default is a white space. SET NULL controls the text displayed for all null values for all columns, unless overridden for a specific column by the NULL clause of the COLUMN command. When a NULL value is SELECTed, a variable's type will always become CHAR so the SET NULL text can be stored in it.
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] Specifies how SQL*Plus will treat a datatype or DATE string that is too wide for a column. WRAPPED wraps the string within the column bounds, beginning new lines when required. When WORD_WRAP is enabled, SQL*Plus left justifies each new line, skipping all leading whitespace (for example, returns, newline characters, tabs and spaces), including embedded newline characters. Embedded whitespace not on a line boundary is not skipped. TRUNCATED truncates the string at the end of the first line of display.
Usage Notes
You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command. Thus, the COLUMN commands you enter can control a column's display attributes for multiple SQL SELECT commands.
When you enter multiple COLUMN commands for the same column, SQL*Plus applies their clauses collectively. If several COLUMN commands apply the same clause to the same column, the last one entered will control the output.
Examples
To make the ENAME column 20 characters wide and display EMPLOYEE NAME on two lines at the top, enter
SQL> COLUMN ENAME FORMAT A20 HEADING 'EMPLOYEE |NAME' To format the SAL column so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, you would enter
SQL> COLUMN SAL FORMAT $9,999,990.99
To assign the alias NET to a column containing a long expression, to display
the result in a dollar format, and to display <NULL> for null values, you
might enter
SQL> COLUMN SAL+COMM+BONUS-EXPENSES-INS-TAX ALIAS NET SQL> COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>' Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.
Also note that in the first command you must enter the expression exactly as you entered it (or will enter it) in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.
To wrap long values in a column named REMARKS, you can enter
SQL> COLUMN REMARKS FORMAT A20 WRAP
For example:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- --------------------
123 25-AUG-86 144 This order must be s
hipped by air freigh
t to ORD
If you replace WRAP with WORD_WRAP, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- ---------------------
123 25-AUG-86 144 This order must be
shipped by air freight
to ORD
If you specify TRUNCATE, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- -------------------- 123 25-AUG-86 144 This order must be s
<analyst_user_at_my-dejanews.com> wrote in message
news:7gsjfp$88k$1_at_nnrp1.deja.com...
> I have a report that has 3 comment columns that are specified as length
10,10,
> and 10. In the table these fields have a length of 30,30,30.
> Is there a way to have just one column comments of length 30, and have the
2
> comments print under each other?
I've supplied much of the help for the Column command, but there's more (research?).
Mike Rose
mmrose_at_home.com Received on Thu May 06 1999 - 22:24:43 CEST