 Library |
 Product |
 Contents |
 Index |
Library |
Product |
Contents |
Index |
Places and formats a specified title at the bottom of each report page or lists the current BTITLE definition.
For a description of the old form of BTITLE, see Appendix F.
Syntax
BTI[TLE] [printspec [text|variable] ...]|[OFF|ON]
Terms and Clauses
Refer to the TTITLE command for additional information on terms and clauses in the BTITLE command syntax.
Enter BTITLE with no clauses to list the current BTITLE definition.
Usage Notes
If you do not enter a printspec clause before the first occurrence of text, BTITLE left justifies the text. SQL*Plus interprets BTITLE in the new form if a valid printspec clause (LEFT, SKIP, COL, and so on) immediately follows the command name.
Examples
To set a bottom title with CORPORATE PLANNING DEPARTMENT on the left and a date on the right, enter
SQL> BTITLE LEFT 'CORPORATE PLANNING DEPARTMENT' - > RIGHT '27 Jun 1997'
To set a bottom title with CONFIDENTIAL in column 50, followed by six spaces and a date, enter
SQL> BTITLE COL 50 'CONFIDENTIAL' TAB 6 '27 Jun 1997'
Changes the first occurrence of text on the current line in the buffer.
Syntax
C[HANGE] sepchar old [sepchar [new [sepchar]]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
sepchar
Represents any non-alphanumeric character such as "/" or "!". Use a sepchar that does not appear in old or new. You can omit the space between CHANGE and the first sepchar.
old
Represents the text you wish to change. CHANGE ignores case in searching for old. For example,
CHANGE /aq/awwill find the first occurrence of "aq", "AQ", "aQ", or "Aq" and change it to "aw". SQL*Plus inserts the new text exactly as you specify it.
If old is prefixed with "...", it matches everything up to and including the first occurrence of old. If it is suffixed with "...", it matches the first occurrence of old and everything that follows on that line. If it contains an embedded "...", it matches everything from the preceding part of old through the following part of old.
new
Represents the text with which you wish to replace old. If you omit new and, optionally, the second and third sepchars, CHANGE deletes old from the current line of the buffer.
Usage Notes
You can also use CHANGE to modify a line in the buffer that has generated an Oracle error. SQL*Plus sets the buffer's current line to the line containing the error so that you can make modifications.
To re-enter an entire line, you can type the line number followed by the new contents of the line. If you specify a line number larger than the number of lines in the buffer and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero ("0") for the line number and follow the zero with text, then SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).
Examples
Assume the current line of the buffer contains the following text:
4* WHERE JOB IS IN ('CLERK','SECRETARY','RECEPTIONIST')Enter the following command:
SQL> C /RECEPTIONIST/GUARD/
The text in the buffer changes as follows:
4* WHERE JOB IS IN ('CLERK','SECRETARY','GUARD')Or enter the following command:
SQL> C /'CLERK',.../'CLERK')/
The original line changes to
4* WHERE JOB IS IN ('CLERK')Or enter the following command:
SQL> C /(...)/('COOK','BUTLER')/The original line changes to
4* WHERE JOB IS IN ('COOK','BUTLER')You can replace the contents of an entire line using the line number. This entry
SQL> 2 FROM EMP e1
causes the second line of the buffer to be replaced with
FROM EMP e1
Note that entering a line number followed by a string will replace the line regardless of what text follows the line number. Thus,
SQL> 2 c/old/new/
will change the second line of the buffer to be
2* c/old/new/
Resets or erases the current value or setting for the specified option.
Syntax
CL[EAR] option ...
where option represents one of the following clauses:
BRE[AKS] BUFF[ER] COL[UMNS] COMP[UTES] SCR[EEN] SQL TIMI[NG]
Terms and Clauses
Refer to the following list for a description of each term or clause:
BRE[AKS]
Removes the break definition set by the BREAK command.
BUFF[ER]
Clears text from the buffer. CLEAR BUFFER has the same effect as CLEAR SQL, unless you are using multiple buffers (see the SET BUFFER command in Appendix F).
COL[UMNS]
Resets column display attributes set by the COLUMN command to default settings for all columns. To reset display attributes for a single column, use the CLEAR clause of the COLUMN command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.
COMP[UTES]
Removes all COMPUTE definitions set by the COMPUTE command.
SCR[EEN]
SQL
Clears the text from SQL buffer. CLEAR SQL has the same effect as CLEAR BUFFER, unless you are using multiple buffers (see the SET BUFFER command in Appendix F).
TIMI[NG]
Deletes all timers created by the TIMING command.
Example
To clear breaks, enter
SQL> CLEAR BREAKS
To clear column definitions, enter
SQL> CLEAR COLUMNS
Specifies display attributes for a given column, such as
Syntax
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.
CLE[AR]
Resets the display attributes for the column to default values.
To reset the attributes for all columns, use the CLEAR COLUMNS command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.
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.
Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999--not a variable.
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.
DATE Columns The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default width is A9. In Oracle8, the NLS parameters may be set in your database parameter file or may be environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. (See the documentation for the Oracle8 Server for a complete description of the NLS parameters).
When you use SQL functions like TO_CHAR, Oracle automatically allows for a very wide column.
To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated.
NUMBER Columns To change a NUMBER column's width, use FORMAT followed by an element as specified in Table 7 - 1.
If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.
A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.
SQL*Plus formats NUMBER data right-justified. 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.
If a value does not fit within the column width, SQL*Plus indicates overflow by displaying a pound sign (#) in place of each digit the width allows.
If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~).
With all number formats, SQL*Plus rounds each value to the specified number of significant digits as set with the SET NUMWIDTH command.
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.
NEWL[INE]
Starts a new line before displaying the column's value. NEWLINE has the same effect as FOLD_BEFORE.
Specifies a variable to hold a column value. You can reference the variable in TTITLE commands. Use NEW_VALUE to display column values or the date in the top title. You must include the column in a BREAK command with the SKIP PAGE action. The variable name cannot contain a pound sign (#).
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.
Specifies a variable to hold a column value. You can reference the variable in BTITLE commands. Use OLD_VALUE to display column values in the bottom title. You must include the column in a BREAK command with the SKIP PAGE action.
OLD_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.
For information on displaying a column value in the top title, see COLUMN NEW_VALUE. Refer to TTITLE for more information on referencing variables in titles.
ON|OFF
Controls the status of display attributes for a column. OFF disables the attributes for a column without affecting the attributes' definition. ON reinstates the attributes.
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 ORDIf 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 ORDIf you specify TRUNCATE, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS ---------- --------- -------- -------------------- 123 25-AUG-86 144 This order must be s
In order to print the current date and the name of each job in the top title, enter the following. (For details on creating a date variable, see "Displaying the Current Date in Titles" under "Defining Page Titles and Dimensions".)
SQL> COLUMN JOB NOPRINT NEW_VALUE JOBVAR
SQL> COLUMN TODAY NOPRINT NEW_VALUE DATEVAR
SQL> BREAK ON JOB SKIP PAGE ON TODAY
SQL> TTITLE CENTER 'Job Report' RIGHT DATEVAR SKIP 2 -
> LEFT 'Job: ' JOBVAR SKIP 2
SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YY') TODAY,
2 ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO
3 FROM EMP WHERE JOB IN ('CLERK', 'SALESMAN')
4 ORDER BY JOB, ENAME;Your two page report would look similar to the following report, with "Job Report" centered within your current linesize:
Job Report 10/01/96 Job: CLERK ENAME MGR HIREDATE SAL DEPTNO ---------- ------- --------- ----------- ---------- ADAMS 7788 14-JAN-87 1100 20 JAMES 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20
Job Report 10/01/96 Job: CLERK ENAME MGR HIREDATE SAL DEPTNO ---------- ------- --------- ----------- ---------- ALLEN 7698 20-JAN-81 1600 30 MARTIN 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20
To change the default format of DATE columns to 'YYYY-MM-DD', you can enter
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
The following output results:
Session altered
To display the change, enter a SELECT statement, such as:
SQL> SELECT HIREDATE 2 FROM EMP 3 WHERE EMPNO = 7839;
The following output results:
HIREDATE ---------- 1981-11-17
See the Oracle8 Server SQL Reference Manual for information on the ALTER SESSION command.
Note that in a SELECT statement, some SQL calculations or functions, such as TO_CHAR, may cause a column to be very wide. In such cases, use the FORMAT option to alter the column width.
Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. (For details on how to create summaries, see "Clarifying Your Report with Spacing and Summary Lines".)
Syntax
COMP[UTE] [function [LAB[EL] text] ... OF {expr|column|alias} ... ON {expr|column|alias|REPORT|ROW} ...]
Terms and Clauses
Refer to the following list for a description of each term or clause:
function ...
Represents one of the functions listed in Table 6-2. If you specify more than one function, use spaces to separate the functions.
Function
Computes
Applies to Datatypes
AVG
Average of non-null values
NUMBER
COU[NT]
Count of non-null values
all types
MAX[IMUM]
Maximum value
NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
MIN[IMUM]
Minimum value
NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
NUM[BER]
Count of rows
all types
STD
Standard deviation of non-null values
NUMBER
SUM
Sum of non-null values
NUMBER
VAR[IANCE]
Variance of non-null values
NUMBER
Table 7 - 2. COMPUTE Functions
LAB[EL] text
Defines the label to be printed for the computed value. If no LABEL clause is used, text defaults to the unabbreviated function keyword. If text contains spaces or punctuation, you must enclose it with single quotes. The label prints left justified and truncates to the column width or linesize, whichever is smaller. The maximum length of a label is 500 characters.
The label for the computed value appears in the break column specified. To suppress the label, use the NOPRINT option of the COLUMN command on the break column.
If you repeat a function in a COMPUTE command, SQL*Plus issues a warning and uses the first occurrence of the function.
With ON REPORT and ON ROW computations, the label appears in the first column listed in the SELECT statement. The label can be suppressed by using a NOPRINT column first in the SELECT statement. When you compute a function of the first column in the SELECT statement ON REPORT or ON ROW, then the computed value appears in the first column and the label is not displayed. To see the label, select a dummy column first in the SELECT list.
OF {expr|column|alias}...
Specifies the column(s) or expression(s) you wish to use in the computation. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) You must also specify these columns in the SQL SELECT command, or SQL*Plus will ignore the COMPUTE command.
If you use a SQL SELECT list alias, you must use the SQL alias in the COMPUTE command, not the column name. If you use the column name in this case, SQL*Plus will ignore the COMPUTE command.
If you do not want the computed values of a column to appear in the output of a SELECT command, specify that column in a COLUMN command with a NOPRINT clause. Use spaces to separate multiple expressions, columns, or aliases within the OF clause.
ON {expr|column|alias|REPORT|ROW} ...
Specifies the event SQL*Plus will use as a break. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) COMPUTE prints the computed value and restarts the computation when the event occurs (that is, when the value of the expression changes, a new ROW is fetched, or the end of the report is reached).
If multiple COMPUTE commands reference the same column in the ON clause, only the last COMPUTE command applies.
Enter COMPUTE without clauses to list all COMPUTE definitions.
Usage Notes
In order for the computations to occur, the following conditions must all be true:
Examples
To subtotal the salary for the "clerk", "analyst", and "salesman" job classifications with a compute label of "TOTAL", enter
SQL> BREAK ON JOB SKIP 1
SQL> COMPUTE SUM LABEL 'TOTAL' OF SAL ON JOB
SQL> SELECT JOB, ENAME, SAL
2 FROM EMP
3 WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN')
4 ORDER BY JOB, SAL;The following output results:
JOB ENAME SAL
--------- ---------- ----------
ANALYST SCOTT 3000
FORD 3000
********* ----------
TOTAL 6000
CLERK SMITH 800
JAMES 950
ADAMS 1100
MILLER 1300
********* ----------
TOTAL 4150
SALESMAN WARD 1250
MARTIN 1250
TURNER 1500
ALLEN 1600
********* ----------
TOTAL 5600To calculate the total of salaries less than 1,000 on a report, enter
SQL> COMPUTE SUM OF SAL ON REPORT SQL> BREAK ON REPORT SQL> COLUMN DUMMY HEADING '' SQL> SELECT ' ' DUMMY, SAL, EMPNO 2 FROM EMP 3 WHERE SAL < 1000 4 ORDER BY SAL;
The following output results:
SAL EMPNO
--- ---------- -----------
800 7369
950 7900
----------
sum 5350To compute the average and maximum salary for the accounting and sales departments, enter
SQL> BREAK ON DNAME SKIP 1
SQL> COMPUTE AVG LABEL 'Dept Average' -
> MAX LABEL 'Dept Maximum' -
> OF SAL ON DNAME
SQL> SELECT DNAME, ENAME, SAL
2 FROM DEPT, EMP
3 WHERE DEPT.DEPTNO = EMP.DEPTNO
4 AND DNAME IN ('ACCOUNTING', 'SALES')
5 ORDER BY DNAME;The following output results:
DNAME ENAME SAL
-------------- ---------- ----------
ACCOUNTING CLARK 2450
KING 5000
MILLER 1300
************** ----------
Dept Average 2916.66667
Dept Maximum 5000
SALES ALLEN 1600
WARD 1250
JAMES 950
TURNER 1500
MARTIN 1250
BLAKE 2850
************** ----------
Dept Average 1566.66667
Dept Maximum 2850To 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
----------
10875If, instead, you do not want to print the label, only the salary total 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
----------
19625Connects a given username to Oracle.
Syntax
CONN[ECT] [logon]
where:
logon
Requires the following syntax: username[/password][@database_specification]|/
Terms and Clauses
Refer to the following list for a description of each term or clause:
username [/password]
Represent the username and password with which you wish to connect to Oracle. If you omit username and password, SQL*Plus prompts you for them. If you enter a slash (/) or simply enter [Return] to the prompt for username, SQL*Plus logs you in using a default logon (see "/" below).
If you omit only password, SQL*Plus prompts you for password. When prompting, SQL*Plus does not display password on your terminal screen. See the PASSWORD command in this chapter for information about changing your password.
database specification
Consists of a SQL*Net connection string. The exact syntax depends upon the SQL*Net communications protocol your Oracle installation uses. For more information, refer to the SQL*Net manual appropriate for your protocol or contact your DBA. SQL*Plus does not prompt for a database specification, but uses your default database if you do not include a specification.
Represents a default logon using operating system authentication. You cannot enter a database_specification if you use a default logon. In a default logon, SQL*Plus typically attempts to log you in using the username OPS$name, where name is your operating system username. See the Oracle8 Server Administrator's Guide for information about operating system authentication.
Usage Notes
CONNECT commits the current transaction to the database, disconnects the current username from Oracle, and reconnects with the specified username.
If you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect.
If an account is locked, a message is displayed and connection into that account (as that user) is not permitted until the account is unlocked by your DBA.
For further information on account management, refer to the documentation on the CREATE and ALTER USER commands. Also see the CREATE PROFILE command in the Oracle8 Server SQL Reference Manual.
Examples
To connect across SQL*Net using username SCOTT and password TIGER to the database known by the SQL*Net alias as FLEETDB, enter
SQL> CONNECT SCOTT/TIGER@FLEETDB
To connect using username SCOTT, letting SQL*Plus prompt you for the password, enter
SQL> CONNECT SCOTT
Copies the data from a query to a table in a local or remote database.
Syntax
COPY {FROM username[/password]@database_specification| TO username[/password]@database_specification| FROM username[/password]@database_specification TO username[/password]@database_specification} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column ...)] USING query
Terms and Clauses
Refer to the following list for a description of each term or clause:
username[/password]
Represent the Oracle username/password you wish to COPY FROM and TO. In the FROM clause, username/password identifies the source of the data; in the TO clause, username/password identifies the destination. If you do not specify password in either the FROM clause or the TO clause, SQL*Plus will prompt you for it. SQL*Plus suppresses the display of your response to these prompts.
database_specification
Consists of a SQL*Net connection string. You must include a database_specification clause in the COPY command. In the FROM clause, database_specification represents the database at the source; in the TO clause, database_specification represents the database at the destination. The exact syntax depends upon the SQL*Net communications protocol your Oracle installation uses. For more information, refer to the SQL*Net manual appropriate for your protocol or contact your DBA.
destination_table
Represents the table you wish to create or to which you wish to add data.
(column, column, column, ...)
Specifies the names of the columns in destination_table. You must enclose a name in double quotes if it contains lowercase letters or blanks.
If you specify columns, the number of columns must equal the number of columns selected by the query. If you do not specify any columns, the copied columns will have the same names in the destination table as they had in the source if COPY creates destination_table.
USING query
Specifies a SQL query (SELECT command) determining which rows and columns COPY copies.
FROM username[/password]@database_specification
Specifies the username, password, and database that contains the data to be copied. If you omit the FROM clause, the source defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must include a FROM clause to specify a source database other than the default.
TO username[/password]@database_specification
Specifies the database containing the destination table. If you omit the TO clause, the destination defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must include a TO clause to specify a destination database other than the default.
APPEND
Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.
CREATE
Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.
INSERT
Inserts the rows from query into destination_table. If destination_table does not exist, COPY returns an error. When using INSERT, the USING query must select one column for each column in the destination_table.
REPLACE
Replaces destination_table and its contents with the rows from query. If destination_table does not exist, COPY creates it. Otherwise, COPY drops the existing table and replaces it with a table containing the copied data.
Usage Notes
To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.
The SQL*Plus SET variable LONG limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.
SQL*Plus performs a commit at the end of each successful COPY. If you set the SQL*Plus SET variable COPYCOMMIT to a positive value n, SQL*Plus performs a commit after copying every n batches of records. The SQL*Plus SET variable ARRAYSIZE determines the size of a batch.
Some operating environments require that database specifications be placed in double quotes.
Examples
The following command copies the entire EMP table to a table named WESTEMP. Note that the tables are located in two different databases. If WESTEMP already exists, SQL*Plus replaces the table and its contents. The columns in WESTEMP have the same names as the columns in the source table, EMP.
SQL> COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST - > REPLACE WESTEMP - > USING SELECT * FROM EMP
The following command copies selected records from EMP to the database to which SQL*Plus is connected. SQL*Plus creates SALESMEN through the copy. SQL*Plus copies only the columns EMPNO and ENAME, and at the destination names them EMPNO and SALESMAN.
SQL> COPY FROM SCOTT/TIGER@HQ - > CREATE SALESMEN (EMPNO,SALESMAN) - > USING SELECT EMPNO, ENAME FROM EMP - > WHERE JOB='SALESMAN'
Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.
Syntax
DEF[INE] [variable]|[variable = text]
Terms and Clauses
Refer to the following list for a description of each term or clause:
variable
Represents the user variable whose value you wish to assign or list.
text
Represents the CHAR value you wish to assign to variable. Enclose text in single quotes if it contains punctuation or blanks.
variable = text
Defines (names) a user variable and assigns it a CHAR value.
Enter DEFINE followed by variable to list the value and type of variable. Enter DEFINE with no clauses to list the values and types of all user variables.
Usage Notes
DEFINEd variables retain their values until one of the following events occurs:
Note that you can use DEFINE to define the variable, _EDITOR, which establishes the host system editor invoked by the SQL*Plus EDIT command.
If you continue the value of a DEFINEd variable on multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return you enter with a space in the resulting variable. For example, SQL*Plus interprets
SQL> DEFINE TEXT = 'ONE- > TWO- > THREE'
as
SQL> DEFINE TEXT = 'ONE TWO THREE'
Examples
To assign the value MANAGER to the variable POS, type:
SQL> DEFINE POS = MANAGER
If you execute a command that contains a reference to &POS, SQL*Plus will substitute the value MANAGER for &POS and will not prompt you for a POS value.
To assign the CHAR value 20 to the variable DEPTNO, type:
SQL> DEFINE DEPTNO = 20
Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPTNO consisting of two characters, 2 and 0.
To list the definition of DEPTNO, enter
SQL> DEFINE DEPTNO DEFINE DEPTNO = "20" (CHAR)
This result shows that the value of DEPTNO is 20.
Deletes one or more lines of the buffer.
Syntax
DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Terms and Clauses
Refer to the following list for a description of each term or clause:
n
n m
n *
Deletes line n through the current line.
n LAST
Deletes line n through the last line.
*
* n
Deletes the current line through line n.
* LAST
Deletes the current line through the last line.
LAST
Deletes the last line.
Enter DEL with no clauses to delete the current line of the buffer.
Usage Notes
DEL makes the following line of the buffer (if any) the current line. You can enter DEL several times to delete several consecutive lines.
Note: DEL is a SQL*Plus command and DELETE is a SQL command. For more information about the SQL DELETE command, see the Oracle8 Server SQL Reference Manual.
Examples
Assume the SQL buffer contains the following query:
1 SELECT ENAME, DEPTNO 2 FROM EMP 3 WHERE JOB = 'SALESMAN' 4* ORDER BY DEPTNO
To make the line containing the WHERE clause the current line, you could enter
SQL> LIST 3 3* WHERE JOB = 'SALESMAN'
followed by
SQL> DEL
The SQL buffer now contains the following lines:
1 SELECT ENAME, DEPTNO 2 FROM EMP 3* ORDER BY DEPTNO
To delete the second line of the buffer, enter
SQL> DEL 2
The SQL buffer now contains the following lines:
1 SELECT ENAME, DEPTNO 2* ORDER BY DEPTNO
Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure.
Syntax
DESC[RIBE] {[schema.]object[@database_link_name]}
Terms and Clauses
Refer to the following list for a description of each term or clause:
schema
Represents the schema where the object resides. If you omit schema, SQL*Plus assumes you own object.
object
Represents the table, view, type, procedure, function, package or synonym you wish to describe.
database_link_name
Consists of the database link name corresponding to the database where object exists. For more information on which privileges allow access to another table in a different schema, refer to the Oracle8 Server SQL Reference Manual.
Usage Notes
The description for tables, views, types and synonyms contains the following information:
The description for functions and procedures contains the following information:
To describe the table EMP, enter
SQL> DESCRIBE EMP
DESCRIBE lists the following information:
Name Null? Type ------------------------------ -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB JOB(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
To describe a procedure called CUSTOMER_LOOKUP, enter
SQL> DESCRIBE customer_lookup
DESCRIBE lists the following information:
PROCEDURE customer_lookup Argument Name Type In/Out Default? ---------------------- -------- -------- --------- CUST_ID NUMBER IN CUST_NAME VARCHAR2 OUT
To create and describe the package APACK that contains the procedures aproc and bproc, enter
SQL> CREATE PACKAGE apack AS 2 PROCEDURE aproc(P1 CHAR, P2 NUMBER); 3 PROCEDURE bproc(P1 CHAR, P2 NUMBER); 4 END apack; 5 /
SQL> DESCRIBE apack
DESCRIBE lists the following information:
PROCEDURE aproc Argument Name Type In/Out Default? ---------------------- -------- -------- --------- P1 CHAR IN P2 NUMBER IN PROCEDURE bproc Argument Name Type In/Out Default? ---------------------- -------- -------- --------- P1 CHAR IN P2 NUMBER IN
To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter
SQL> CREATE TYPE ADDRESS AS OBJECT 2 ( STREET VARCHAR2(20), 3 CITY VARCHAR2(20) 4 ); 5 /
SQL> DESCRIBE address
DESCRIBE lists the following information:
Name Null? Type ------------------------------ -------- ------------ STREET VARCHAR2(20) CITY VARCHAR2(20)
To create and describe the object type EMPLOYEE that contains the attributes ENAME, EMPADDR, JOB and SAL, enter
SQL> CREATE TYPE EMPLOYEE AS OBJECT 2 ( ENAME VARCHAR2(30), 3 EMPADDR ADDRESS, 4 JOB VARCHAR2(20), 5 SAL NUMBER(7,2) 6 ); 7 /
SQL> DESCRIBE employee
DESCRIBE lists the following information:
Name Null? Type ------------------------------ -------- ------------ ENAME VARCHAR2(30) EMPADDR ADDRESS JOB VARCHAR2(20) SAL NUMBER(7,2)
To create and describe the object type addr_type as a table of the object type ADDRESS, enter
SQL> CREATE TYPE addr_type IS TABLE OF ADDRESS; 2 /
SQL> DESCRIBE addr_type
DESCRIBE lists the following information:
addr_type TABLE OF ADDRESS
Name Null? Type ------------------------------ -------- ------------ STREET VARCHAR2(20) CITY VARCHAR2(20)
To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter
SQL> CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS; 2 /
SQL> DESCRIBE addr_varray
DESCRIBE lists the following information:
addr_varray VARRAY(10) OF ADDRESS
Name Null? Type ------------------------------ -------- ------------ STREET VARCHAR2(20) CITY VARCHAR2(20)
To create and describe the table dept_emp that contains the columns DEPTNO, PERSON and LOC, enter
SQL> CREATE TABLE dept_emp
2 ( DEPTNO NUMBER, 3 PERSON EMPLOYEE, 4 LOC NUMBER 5 ); 6 /
SQL> DESCRIBE dept_emp
DESCRIBE lists the following information:
Name Null? Type ------------------------------ -------- ------------ DEPTNO NUMBER PERSON EMPLOYEE LOC NUMBER
To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter
SQL> CREATE OR REPLACE TYPE rational AS OBJECT 2 ( NUMERATOR NUMBER, 3 DENOMINATOR NUMBER, 4 MAP MEMBER FUNCTION rational_order - > RETURN DOUBLE PRECISION, 5 PRAGMA RESTRICT_REFERENCES 6 (rational_order, RNDS, WNDS, RNPS, WNPS) ); 7 /
SQL> CREATE OR REPLACE TYPE BODY rational AS OBJECT 2 MAP MEMBER FUNCTION rational_order - > RETURN DOUBLE PRECISION IS 3 BEGIN 4 RETURN NUMERATOR/DENOMINATOR; 5 END; 6 END; 7 /
SQL> DESCRIBE rational
DESCRIBE lists the following information:
Name Null? Type ------------------------------ -------- ------------ NUMERATOR NUMBER DENOMINATOR NUMBER
METHOD ------ MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER
For more information on using the CREATE TYPE command, see your Oracle8 Server SQL Reference Manual.
Commits pending changes to the database and logs the current username out of Oracle, but does not exit SQL*Plus.
Syntax
DISC[ONNECT]
Usage Notes
Use DISCONNECT within a command file to prevent user access to the database when you want to log the user out of Oracle but have the user remain in SQL*Plus. Use EXIT or QUIT to log out of Oracle and return control to your host computer's operating system.
Example
Your command file might begin with a CONNECT command and end with a DISCONNECT, as shown below.
SQL> GET MYFILE
1 CONNECT ...
.
.
.
.
15* DISCONNECT
|
Prev Next |
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |