SQL*Plus Quick Reference
Contents
1. EH w E 4 ERS parameter_name EPF OOTER DW Wd 16 SQL Plus Quick Reference SQL Command List SPOO L SQLCODE TTI TLE USI ER Enter any system variable set by the SET command in place of system_ variable SQL Command List SQL commands were formerly documented in SQL Plus documentation You should now refer to the Oracle8i SQL Reference for full documentation of these commands and clauses Below is a list of major SQL commands ALTER ANALYZE AUDIT COMMENT COMMIT CREATE DELETE DROP EXPLAIN GRANT INSERT LOCK TABLE NOAUDIT RENAME REVOKE ROLLBACK SAVEPOINT SELECT SET ROLE SET TRANSACTION TRUNCATE UPDATE SQL Plus Quick Reference 17 Notes Notes 18 SQL Plus Quick Reference Notes SQL Plus Quick Reference 19 Notes 20 SQL Plus Quick Reference2. n LAST LAST Deletes one or more lines of the buffer indicates the current line You can omit the space between DEL and n or but not between DEL and LAST Enter DEL with no clauses to delete the current line of the buffer I NPUT text Adds one or more new lines of text after the current line in the buffer SQL Plus Quick Reference 5 Manipulating SQL SQL Plus and PL SQL Commands L IST n n min n LAST n LAST LAST Lists one or more lines of the buffer indicates the current line You can omit the space between LIST and n or but not between LIST and LAST Enter LIST with no clauses to list all lines Use the following commands to create and modify command files file_name ext arg Runs the specified command file Specified arguments are substituted for amp 1 amp 2 and so on QR file_name ext Runs the specified nested command file ED IT file name ext Invokes a host operating system text editor on the contents of the specified file or on the contents of the SQL buffer To edit the buffer contents omit the file name GET file name ext LIS T NOL IST Loads a host operating system file into the SQL buffer REM ARK Begins a comment in a command file The REMARK command must appear at the beginning of a line and the comment ends at the end of the line a line cannot contain both a comment and a command SQL Plus does not interpret the comment as a command SAV E
3. file_name ext CRE ATE REP LACE APP END Saves the contents of the buffer into a host operating system file a command file STORE SET file name ext CRE ATE REP LACE APP END Saves the attributes of the current SOL Plus environment in a host operating system file a command file STA RT file_name ext arg Executes the contents of the specified command file Specified arguments are substituted for amp 1 amp 2 and so on 6 SQL Plus Quick Reference Manipulating SQL SQL P lus and PL SQL Commands WHENEVER OSERROR EXIT SUCCESS FAILURE n variable BindVariablel COMMIT ROLLBACK CONTINUE COMMIT ROLLBACK NONE Exits SQL Plus if an operating system error occurs such as a file I O error WHENEVER SQLERROR EXIT SUCCESS FAILURE WARNING n variable BindVariable COMMIT ROLLBACK CONTINUE COMMIT ROLLBACK NONE Exits SQL Plus if a SQL command or PL SQL block generates an error Use the following commands to write interactive commands ACC EPT variable NUM BER CHAR DATE FOR MAT format DEF AULT default PROMPT text NOPR OMPT HIDE Reads a line of input and stores it in a given user variable DEF INE variable variable text Specifies a user variable and assigns it a CHAR value Alternatively lists the value and variable type of a single variable or all variables PAU SE text Displays an empty line followed by a line containing text then
4. SQL Plus Quick Reference Release 8 1 5 Part No A66735 01 Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this publication Your input is an important part of the information used for revision Did you find any errors Is the information clearly presented Do you need more information If so where Are the examples correct Do you need more examples What features did you like most about this manual If you find any errors or have any other suggestions for improvement please indicate the chapter section and page number if available You can send comments to us in the following ways Electronic mail sqlplus us oracle com FAX 61 3 9690 0043 Attention SQL Plus Documentation Manager Postal service SQL Plus Documentation Manager Australian Product Development Center Oracle Corporation Australia Pty Ltd 324 St Kilda Road Melbourne VIC 3004 Australia If you would like a reply please give your name address and telephone number below If you have problems with the software please contact your local Oracle Support Services center SQL Plus Quick Reference This Quick Reference shows the syntax for SQL Plus commands For detailed information on each command refer to the SQL Plus User s Guide and Reference SQL Plus Quick Reference 1 Conventions for Command Syntax Conventions for Command Syntax The following two tables describe the notation and
5. conventions for command syntax used in this Quick Reference Commands Terms and Clauses Feature Example Explanation uppercase BTITLE Enter text exactly as spelled it need not be in uppercase lowercase italics column A clause value substitute an appropriate value words with specific c A single character meanings char A CHAR value a literal in single quotes or an expression with a CHAR value dore A date or an expression with a DATE value expr An unspecified expression morn A number of an expression with a NUMBER value text A CHAR constant with or without single quotes variable A user variable unless the text specifies another variable type Other words are explained where used if their meaning is not explained by context 2 SQL Plus Quick Reference Starting and Leaving SQL P lus Punctuation Feature Example Explanation vertical bar brackets OFF ON braces OFF ON underlining OFF ON ellipsis me Separates alternative syntax elements that may be optional or mandatory One or more optional items If two items appear separated by enter one of the items Do not enter the brackets or A choice of mandatory items enter one of the items Do not enter the braces or I A default value if you enter nothing SQL Plus assumes the underlined value Preceding item s may be repeated any number of times Enter other punctuation marks such as parentheses wh
6. option option represents one of the following clauses BRE AKS BUFF ER COL UMNS COMP UTES SCR EEN SQL TIMI NG column expr option Specifies the display attributes for a given column such as text for the column heading or formats for CHAR NCHAR VARCHAR2 VARCHAR NVARCHAR2 NCHAR VARYING LONG CLOB NCLOB and NUMBER data 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 EWL INE EW_V ALUE variable OPRI NT PRI NT UL L text OLD_V ALUE variable ON OFF SQL Plus Quick Reference 9 Formatting Query Results WRA PPED WOR D_WRAPPI ED TRU NCATED 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 Enter FORMAT followed by the appropriate format element to specify the display format for the column To change the display format of a NUMBER column use FORMAT followed by one of the elements in the following table Element Example s Description 9 9999 Number of 9 s specifies number of significant digits retur
7. waits for the user to press Return Alternatively displays two empty lines and waits for the user s response PROMPT text Sends the specified message or a blank line to the user s screen UNDEF INE variable Deletes given user variables that you defined either explicitly with the DEFINE command or implicitly with an argument to the START command Use the following commands to create and display bind variables PRI NT variable Displays the current values of bind variables VAR IABLE variable NUMBER CHAR CHAR n NCHAR NCHAR n VARCHAR2 n NVARCHAR2 n CLOB NCLOB REFCURSOR Declares a bind variable which can then be referenced in PL SQL If no arguments are supplied VARIABLE lists all declared bind variables SQL Plus Quick Reference 7 Formatting Query Results Use the following symbols to create substitution variables and parameters for use in command files amp n Specifies a parameter in a command file you run using the START command START substitutes values you list after the command file name as follows the first for amp 1 the second for amp 2 and so on amp user variable amp amp user variable Indicates a substitution variable in a SQL or SQL Plus command SQL Plus substitutes the value of the specified user variable for each substitution variable it encounters If the user variable is undefined SQL Plus prompts you for a value each time an amp variable is found a
8. ENTRY INTER ED IAT E FULL AGGER OFF ON H OFF ING OFF ON EP c OFF ON CE instance path LOCAL ESIZE 80 n FSET n 1 pathname 80 n NONE ORMAT WIDTH ES IZE 24 PAU SE OFF ON text ECSEP WR APPED EA CH OFF ECSEPCHAR _ c F F SQL Plus Quick Reference 15 Miscellaneous SERVEROUT PUT OFF ON SIZE n FOR MAT WRA PPED WOR D_WRAPPED TRU NCATED SHIFT INOUT VIS IBLE INV ISIBLE SHOW MODE OFF ON SQLBL ANKLINES ON OFF SQLC ASE MIX ED LO WER UP PER SQLCO NTINUE gt _ text SQLN UMBER OFF ON SQLPRE FIX c SQLP ROMPT SQL gt text SQLT ERMINATOR c OFF ON SUF FIX SQL text TAB OFF ON ERM OUT OFF ON I ME OFF ON TIMI NG OFE ON RIM OUT OFF ON TRIMS POOL ON OFF UND ERLINE c ON OFF VER IFY OFF ON WRA P OFF ON SHO W option Lists the value of a SQL Plus system variable Use one of the following terms or clauses in place of option system_variable LL TI TLE ESCRIBE RR ORS FUNCTION PROCEDURE PACKAGE PACKAGE BODY TRIGGER VIEW TYPE TYPE BODY schema name gt
9. SQL Plus Quick Reference Release 8 1 5 February 1999 Part No A66735 01 ORACLE SQL Plus Quick Reference Release 8 1 5 Part No A66735 01 Copyright 1996 1999 Oracle Corporation All rights reserved Contributing Authors Frank Rovitto Contributors Larry Baer Lisa Colston Roland Kovacs Karen Denchfield Masterson Alison Holloway Sanjeev Jhala Christopher Jones Anita Lam Nimish Mehta Luan Nim Bud Osterberg Irene Paradisis Richard Rendell Farokh Shapoorjee Larry Stevens Andre Touma The Programs are not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications It shall be the licensee s responsibility to take all appropriate fail safe backup redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes and Oracle disclaims liability for any damages caused by such use of the Programs The Programs which include both the software and documentation contain proprietary information of Oracle Corporation they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright patent and other intellectual and industrial property laws Reverse engineering disassembly or decompilation of the Programs is prohibited The information contained in this document is subject to change without notice If you find any problems in the documentation please report
10. TIL CONTROLFILE LOGFILE filename CONTINUE DEFAULT CANCEL PARALLEL clause where options requires the following syntax CANCEL CHANGE integer TIME date and where clause requires the following syntax PARALLEL DEGREE integer DEFAULT INSTANCES integer DEFAULT NOPARALLEL Performs media recovery on one or more tablespaces one or more datafiles or the entire database 14 SQL Plus Quick Reference Miscellaneous SET system variable value Sets a system variable to alter the SQL Plus environment for your current session such as setting the display width for NUMBER data or the number of lines per page Enter a system variable followed by a value as shown below APPI NFO ON OFF ARRAY SIZE 15 n AUTO COMMIT OFF ON AUTOP RINT OFF ON AUTORECOVERY ON OFF AUTOT E OFE ON TRACE ONLY EDIATE n P LAIN STAT ISTICS BLO ERMINATOR c DS c OFE ON COLS _ text PATIBILITY V7 V8 NATIVE CAT c OFF ON PYC OMMIT O n PECHECK OFF ON PYTYE EF INE 7 amp c OFF ON DEP Ha Q x es ESCRIBE HO OFF O EDITF ILE file namel ext B EDDED OFF ON ESC APE c OFF ON 6 n OFF ON H 1 n ALL LINENUM ON OFF INDENT ON OFF ED BACK
11. ch report or lists the current REPHEADER definition Use one of the following clauses in place of printspec COL n S KIP n TAB n LETE CE NTER R IGHT BOLD FORMAT text SPO OL filename ext OFF OUT Stores query results in an operating system file and optionally sends the file to a printer OFF stops spooling OUT stops spooling and sends the file to your host computer s standard default printer Enter SPOOL with no clauses to list the current spooling status 12 SQL Plus Quick Reference Accessing Databases TTI TLE printspec text variable OFF ON Places and formats a specified title at the top of each report page or lists the current TTITLE definition Use one of the following clauses in place of printspec COL n S KIP n TAB n LE F CE NTER R IGHT BOLD FORMAT text Accessing Databases Use the following commands to access and copy data between tables on different databases CONN ECT logon AS SYSOPER SYSDBA where logon requires the following syntax username password net_service_name Connects a given username to Oracle If you omit net_service_name connects you to the default database If you omit username and or password SQL Plus prompts you for them CONNECT followed by a slash connects you using a default OPS logon DISC ONNECT Commits pending changes to the database and logs the current username off O
12. ere shown in the command syntax Starting and Leaving SQL Plus Use the following commands to log in to and out of SQL Plus SQLPLUS S ILENT logon start where logon requires the following syntax username password net_service_name NOLOG and where start requires the following syntax file_name ext SQL Plus Quick Reference 3 Entering and Executing Commands STARTUP FORCE RESTRICT PFILE filename MOUNT OPEN RECOVER database mount_options NOMOUNT where mount_options requires the following syntax EXCLUSIVE PARALLEL SHARED RETRY Starts an Oracle instance with several options including mounting and opening a database SHUTDOWN ABORT IMMEDIATE NORMAL TRANSACTIONAL Shuts down a currently running Oracle instance optionally closing and dismounting a database EXIT QUIT SUCCESS FAILURE WARNING n variable BindVariable COMMIT ROLLBACK Commits all pending changes terminates SQL Plus and returns control to the operating system Entering and Executing Commands Use the following commands to execute and collect timing statistics on SQL commands and PL SQL blocks slash Executes the SQL command or PL SQL block currently stored in the SOL buffer Does not list the command EXEC UTE statement Executes a single PL SQL statement R UN Lists and executes the SQL command or PL SQL block currently stored in
13. ger between 1 and 3999 DATE DATE Displays value as a date in MM DD YY format used to format NUMBER columns that represent Julian dates COMP UTE function LAB EL text OF expr column alias ON expr column alias REPORT ROW Calculates and prints summary lines using various standard computations on subsets of selected rows or lists all COMPUTE definitions The following table lists valid functions All functions except NUMBER apply to non null values only 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 SQL Plus Quick Reference 11 Formatting Query Results Function Computes Applies to Datatypes 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 NUMBER values SUM Sum of non null values NUMBER VAR IANCE Variance of non null values NUMBER REPF OOTER PAGE printspec text variable OFF ON Places and formats a specified report footer at the bottom of each report or lists the current REPFOOTER definition See REPHEADER for additional information on valid printspec clauses REPH EADER PAGE printspec text variable OFF ON Places and formats a specified report header at the top of ea
14. nd the first time an amp amp variable is found period Terminates a substitution variable followed by a character that would otherwise be part of the variable name Formatting Query Results Use the following commands to format store and print your query results ATTRIBUTE type name attribute name option BRE AK 8 SQL Plus Quick Reference Specifies display attributes for a given column or lists the current display attributes for a single column or for all columns option represents one of the following clauses ALI AS alias CLE AR FOR MAT format LIKE type_name attribute_name alias ON OFF ON report_element action action Specifies where and how formatting will change in a report for example skipping a line each time a given column value changes Enter BREAK with no clauses to list the current BREAK definition Formatting Query Results BTI TLE Where report_element requires the following syntax colum expr ROW REPORT and where action requires the following syntax SKI P n SKI P PAGE NODUP LICATES DUP LICATES printspec text variable OFF ON Places and formats the specified title at the bottom of each report page or lists the current BTITLE definition See TITLE for additional information on valid printspec clauses CL EAR option COL UMN Resets or erases the current value or setting for the specified
15. ned Blanks are displayed for leading zeroes A zero 0 is displayed for a value of zero 0 0999 Displays a leading zero or a value of 9990 zero in this position as a 0 9999 Prefixes value with dollar sign B B9999 Displays a zero value as blank regardless of 0 s in the format model MI 9999MI Displays after a negative value For a positive value a trailing space is displayed S 9999 Returns for positive values and for negative values in this position PR 9999PR Displays a negative value in lt angle brackets gt For a positive value a leading and trailing space is displayed D 99D99 Displays the decimal character in this position separating the integral and fractional parts of a number G 96999 Displays the group separator in this position C C999 Displays the ISO currency symbol in 10 SQL Plus Quick Reference this position Formatting Query Results Element Example s Description L L999 Displays the local currency symbol in this position comma 9 999 Displays a comma in this position period 99 99 Displays a period decimal point in this position separating the integral and fractional parts of a number V 999V99 Multiplies value by 10 where n is the number of 9 s after the V EEEE 9 999EEFE Displays value in scientific notation format must contain exactly four E s RN or rn RN Displays upper or lowercase Roman numerals Value can be an inte
16. racle but does not exit SQL Plus COPY FROM username password net_service_name TO username password net_service_name FROM username password net_service_name TO username password net service name APPEND CREATE INSERT REPLACE destination table column column USING query Copies data from one Oracle database to a table in another APPEND CREATE INSERT or REPLACE specifies how COPY treats the existing copy of the destination table if it exists USING query identifies the SQL Plus Quick Reference 13 Miscellaneous source table and determines which rows and columns COPY copies from it PASSW ORD username Allows you to change a password without echoing the password on an input device Miscellaneous ARCHIVE LOG LIST STOP START NEXT ALL integer TO destination Starts or stops automatic archiving on online redo log files manually explicitly archives specified redo log files or displays information about redo log files DESC RIBE schema object net_service_name Lists the column definitions for the specified table view or synonym or the specifications for the specified function or procedure RECOVER AUTOMATIC FROM location STANDBY DATABASE UNTIL options USING BACKUP CONTROLFILE TABLESPACE tablespace tablespace DATAFILE datafilename datafilename STANDBY TABLESPACE tablespace tablespace DATAFILE datafilename datafilename UN
17. the SQL buffer TIMI NG START text SHOW STOP Records timing data for an elapsed period of time lists the current timer s name and timing data or lists the number of active timers 4 SQL Plus Quick Reference Manipulating SQL SQL P lus and PL SQL Commands Use the following command to access the help system HELP topic Accesses help on SQL Plus commands Use the following command to execute host operating system commands HO ST command Executes a host operating system command without leaving SQL Plus Note With some operating systems you can use a VMS UNIX or another character instead of HOST See the Oracle installation and user s manual s provided for your operating system for details Manipulating SQL SQL Plus and PL SQL Commands Use the following commands to edit SQL commands and PL SQL blocks A PPEND text Adds specified text to the end of the current line in the SQL buffer To separate text from the preceding characters with a space enter two spaces between APPEND and text To append text that ends with a semicolon end the command with two semicolons SQL Plus interprets a single semicolon as a command terminator C HANGE sepchar old sepchar new sepchar Changes text on the current line in the SQL buffer You can use any non alphanumeric character such as or as a sepchar You can omit the space between CHANGE and the first sepchar DEL n n min n LAST
18. them to us in writing Oracle Corporation does not warrant that this document is error free Except as may be expressly permitted in your license agreement for these Programs no part of these Programs may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose without the express written permission of Oracle Corporation If the Programs are delivered to the U S Government or anyone licensing or using the Programs on behalf of the U S Government the following notice is applicable Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are commercial computer software and use duplication and disclosure of the Programs including documentation shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement Otherwise Programs delivered subject to the Federal Acquisition Regulations are restricted computer software and use duplication and disclosure of the Programs shall be subject to the restrictions in FAR 52 227 19 Commercial Computer Software Restricted Rights June 1987 Oracle Corporation 500 Oracle Parkway Redwood City CA 94065 Oracle is a registered trademark and Oracle Forms SQL Plus and PL SQL are trademarks or registered trademarks of Oracle Corporation All other company or product names mentioned are used for identification purposes only and may be trademarks of their respective owners Send Us Your Comments
Download Pdf Manuals
Related Search