Oracle SQL Developer. Alphabetic List of SQLcl Commands. Command-Line Quick Reference Release PDF

Please download to get full document.

View again

of 22
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

Recipes/Menus

Published:

Views: 0 | Pages: 22

Extension: PDF | Download: 0

Share
Related documents
Description
Oracle SQL Developer Command-Line Quick Reference Release 4.2 E May 2017 This Quick Reference discusses the following features of SQL Developer Command- Line (SQLcl): 1. Alphabetic List of SQLcl
Transcript
Oracle SQL Developer Command-Line Quick Reference Release 4.2 E May 2017 This Quick Reference discusses the following features of SQL Developer Command- Line (SQLcl): 1. Alphabetic List of SQLcl Commands (page 1) 2. SQL*Plus List of Unsupported Commands and Features (page 4) 3. Starting and Leaving SQLcl (page 5) 4. Starting Up and Shutting Down a Database (page 5) 5. Entering and Executing Commands (page 6) 6. Manipulating SQL, SQLcl and PL/SQL Commands (page 7) 7. Formatting Query Results (page 10) 8. Accessing Databases (page 14) 9. Miscellaneous Commands (page 15) Alphabetic List of SQLcl file_name[.ext]} [arg...] { url file_name[.ext] } [arg...] / (slash) ACC[EPT] variable [NUM[BER] CHAR DATE BINARY_FLOAT BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text NOPR[OMPT]] [HIDE] ALIAS [ name = sql statement ; LOAD [ filename ] SAVE [ filename ] LIST [ NAME ] DROP name DESC name Description String ] APEX [export application_id ] A[PPEND] text ARCHIVE LOG LIST BRE[AK] [ON report_element [action [action]]]... 1 BRIDGE BTI[TLE] [printspec [text variable]...] [ON OFF] CD [ directory ] C[HANGE] sepchar old [sepchar [new [sepchar]]] CL[EAR] option... COL[UMN] [{column expr} [option...]] COMP[UTE] [function [LAB[EL] text]... OF {expr column alias}...on {expr column alias REPORT ROW}...] CONN[ECT] [{ logon / proxy} [AS {SYSOPER SYSDBA SYSASM}] [edition=value]] CTAS table new_table COPY {FROM database TO database FROM database TO database} {APPEND CREATE INSERT REPLACE APPEND_BYTE CREATE_BYTE REPLACE_BYTE} destination_table[(column, column, column,...)] USING query DDL [object_name [type] [SAVE filename]] DEF[INE] [variable] [variable = text] DEL [n n m n * n LAST * * n * LAST LAST] DESC[RIBE] DISC[ONNECT] ED[IT] [file_name[.ext]] EXEC[UTE] statement {EXIT QUIT} [SUCCESS FAILURE WARNING n variable :BindVariable] [COMMIT ROLLBACK] FIND [ filename ] FORMAT [BUFFER RULES filename FILE input_file output_file ] GET [FILE] file_name[.ext] [LIST NOLIST] HELP? [topic] HISTORY [index FULL USAGE SCRIPT TIME CLEAR (SESSION)?] HO[ST] [command] INFO[RMATION] I[NPUT] [text] L[IST] [n n m n * n LAST * * n * LAST LAST] LOAD file_name 2 OERR facility error PASSW[ORD] [username] PAU[SE] [text] PRINT [variable...] PRO[MPT] [text] {QUIT EXIT} [SUCCESS FAILURE WARNING n variable :BindVariable] [COMMIT ROLLBACK] REM[ARK] REPEAT iterations sleep REST [export [ module_name module_prefix ] modules privileges schemas] R[UN] SAV[E] [FILE] file_name[.ext] [CRE[ATE] REP[LACE] APP[END]] SCRIPT script file SET system_variable value SHO[W] [option] SHUTDOWN [ABORT IMMEDIATE NORMAL TRANSACTIONAL [LOCAL]] SODA SPO[OL] [filename[.ext] [CRE[ATE] REP[LACE] APP[END]] OFF OUT] SSHTUNNEL -i identity_file [-L localport:remotehost:remoteport] STA[RT] { url file_name[.ext] } [arg...] STARTUP db_options cdb_options upgrade_options STORE {SET} file_name[.ext] [CRE[ATE] REP[LACE] APP[END]] TNSPING address TTI[TLE] [printspec [text variable]...] [ON OFF] UNDEF[INE] variable... WHENEVER OSERROR {EXIT [SUCCESS FAILURE n variable :BindVariable] [COMMIT ROLLBACK] CONTINUE[COMMIT ROLLBACK NONE]} WHENEVER SQLERROR {EXIT [SUCCESS FAILURE WARNING n variable :BindVariable] [COMMIT ROLLBACK] CONTINUE [COMMIT ROLLBACK NONE]} WHICH filename XQUERY xquery_statement 3 Note: You can use the up and down arrow keys to cycle through the previous 100 statements or scripts. List of Unsupported Commands and Features in SQL*Plus Commands REPHEADER REPFOOTER TIMING The TIMING command is replaced by the SET TIMING command. System Variables and Environment Settings through the SET Command autoprint autorecovery cmdsep copytypecheck describe eschar flagger flush fullcolname logsource loboffset markup recsep recsepchar shiftinout sqlcase sqlprefix sqlterminator tab underline 4 xmloptimizationcheck Starting and Leaving SQLcl Use the following commands to log in to and out of SQLcl. SQLCL [[option] [logon / NOLOG] [start]] where option has the following syntax: -H[ELP] -V[ERSION] [ [-C[OMPATIBILITY] x.y[.z]]] [-L[OGON]] [- NOLOGINTIME] [-R[ESTRICT] {1 2 3}] [-S[ILENT]] [-AC]] where logon has the following syntax: {username[/password] /} [AS {SYSASM SYSBACKUP SYSDBA SYSDG SYSOPER SYSRAC SYSKM}] [edition=value] and where start has the following file_name[.ext]} [arg...] {EXIT QUIT} [SUCCESS FAILURE WARNING n variable :BindVariable] [COMMIT ROLLBACK] Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system. {QUIT EXIT} [SUCCESS FAILURE WARNING n variable :BindVariable] [COMMIT ROLLBACK] Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system. Starting Up and Shutting Down a Database Starting up and shutting down a database requires DBA privileges. STARTUP db_options cdb_options upgrade_options where db options has the following syntax: [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] [ OPEN [open_db_options] [dbname] ] NOMOUNT ] where open_db_options has the following syntax: READ {ONLY WRITE [RECOVER]} RECOVER where cdb_options has the following syntax: root_connection_options pdb_connection_options where root_connection_options has the following syntax: PLUGGABLE DATABASE pdbname [FORCE] [RESTRICT] [ OPEN {open_pdb_options}] where pdb_connection_options has the following syntax: 5 [FORCE] [RESTRICT] [ OPEN {open_pdb_options}] where open_pdb_options has the following syntax: READ WRITE READ ONLY and where upgrade_options has the following syntax: [PFILE=filename] {UPGRADE DOWNGRADE} [QUIET] Starts an Oracle Database instance with several options, including mounting and opening a database. SHUTDOWN [ABORT IMMEDIATE NORMAL TRANSACTIONAL [LOCAL]] Shuts down a currently running Oracle instance, optionally closing and dismounting a database. Entering and Executing Commands Use the following commands to execute and collect timing statistics on SQL commands and PL/SQL blocks: / (slash) Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer. Does not list the command. Use slash (/) at the command prompt or line number prompt in SQLcl command line. EXEC[UTE] statement Executes a single PL/SQL statement or runs a stored procedure. R[UN] Lists and executes the most recently executed SQLcl command or PL/SQL block which is stored in the SQL buffer. The buffer has no command history list and does not record SQLcl commands. TIMI[NG] Timing is only available as a switch. Use the following command to access the help system: HELP? [topic] Accesses the command-line help system. Enter HELP INDEX or? INDEX for a list of topics. You can view the Oracle Database Library at technology/documentation. Use the following command to execute operating system commands: HO[ST] [command] Executes an operating system command without leaving SQLcl. Enter HOST without command to display an operating system prompt. You can then enter multiple operating system commands. 6 With some operating systems, you can use another character instead of HOST such as ! (UNIX) and $ (Windows). See the Oracle installation and user's manuals provided for your operating system for details. Use the following command to recall the history of SQLcl commands: HISTORY [index FULL USAGE SCRIPT TIME CLEAR (SESSION)?] FAILS Use the Up and Down arrow keys to navigate through history items at the prompt. Use the HISTORY command to print the history contents. History is limited to the last 100 statements. SET HISTORY LIMIT N allows you to change the default limit, where N is the maximum number. History is retained between SQLcl sessions. By default, the SHOW, HISTORY, CONNECT, and SET commands are not saved in history. SET HISTORY BLACKLIST allows you to set the commands that should not be recorded in history. Manipulating SQL, SQLcl, 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. To append text that ends with a semicolon, end the command with two semicolons (a single semicolon is interpreted as a command terminator). C[HANGE] sepchar old [sepchar [new [sepchar]]] Changes first occurrence of old on the current line of the SQL buffer. The buffer has no command history list and does not record SQLcl commands. You can use any nonalphanumeric character such as / or ! as a sepchar. You can omit the space between CHANGE and the first sepchar. DEL [n n m n * n LAST * * n * LAST LAST] Deletes one or more lines of the SQL 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. The buffer has no command history list and does not record SQLcl commands. I[NPUT] [text] Adds one or more new lines of text after the current line in the SQL buffer. The buffer has no command history list and does not record SQLcl commands. L[IST] [n n m n * n LAST * * n * LAST LAST] 7 Lists one or more lines of the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer. Asterisk (*) 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. In SQLcl, you can also use ; to list all the lines in the SQL buffer. The buffer has no command history list and does not record SQLcl commands. Use the following commands to run { url file_name[.ext] } [arg...] Runs the SQLcl statements in the specified script. The script can be called from the local file system or a web server. You can pass values to script variables in the usual way. { url file_name[.ext] } [arg...] Runs the SQLcl statements in the specified script. This command is almost identical to command. It is useful for running nested scripts because it has the additional functionality of looking for the specified script in the same path or url as the calling script. REPEAT iterations sleep Repeats the current SQL in the buffer at the specified times with sleep intervals. The maximum sleep interval is 120 seconds. SCRIPT script file Runs the SQLcl statements in the specified script. STA[RT] { url file_name[.ext] } [arg...] Runs the SQLcl statements in the specified script. The script can be called from the local file system or a web server. You can pass values to script variables in the usual way. Use the following commands to create and modify scripts: ED[IT] [file_name[.ext]] Invokes an 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. The DEFINE variable _EDITOR can be used to set the editor to use. In SQLcl, _EDITOR can be set to any editor that you prefer. Inline will set the editor to be the SQLcl editor. This supports the following shortcuts: ^R - Run the current buffer ^W - Go to top of buffer ^S - Go to bottom of buffer ^A - Go to start of line ^E - Go to end of line FORMAT 8 FORMAT BUFFER - formats the script in the SQLcl Buffer FORMAT RULES filename - Loads SQLDeveloper Formatter rules file to formatter FORMAT FILE input_file output_file GET file_name[.ext] [LIST NOLIST] Loads a SQL statement or PL/SQL block from a file into the SQL buffer. The buffer has no command history list and does not record SQLcl commands. REM[ARK] Begins a comment in a script. 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). SQLcl does not interpret the comment as a command. SAV[E] [FILE] file_name[.ext] [CRE[ATE] REP[LACE] APP[END]] Saves the contents of the SQL buffer in a script. The buffer has no command history list and does not record SQLcl commands. STORE {SET} file_name[.ext] [CRE[ATE] REP[LACE] APP[END]] Saves attributes of the current SQLcl environment in a file. WHENEVER OSERROR {EXIT [SUCCESS FAILURE n variable :BindVariable] [COMMIT ROLLBACK] CONTINUE [COMMIT ROLLBACK NONE]} Performs the specified action (exits SQLcl by default) if an operating system error occurs (such as a file writing error). WHENEVER SQLERROR {EXIT [SUCCESS FAILURE WARNING n variable :BindVariable] [COMMIT ROLLBACK] CONTINUE [COMMIT ROLLBACK NONE]} Performs the specified action (exits SQLcl by default) 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 BINARY_FLOAT BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text NOPR[OMPT]] [HIDE] Reads a line of input and stores it in a given substitution variable. DEF[INE] [variable] [variable = text] Specifies a substitution variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables. PAU[SE] [text] Displays the specified text then waits for the user to press RETURN. PRO[MPT] [text] Sends the specified message or a blank line to the user's screen. 9 UNDEF[INE] variable... Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with a START command argument). Use the following commands to create and display bind variables: PRINT [variable...] Displays the current values of bind variables, or lists all bind variables. Use the following symbols to create substitution variables and parameters for use in scripts: &n Specifies a parameter in a script you run using the START command. START substitutes values you list after the script name as follows: the first for &1, the second for &2, and so on. &user_variable, &&user_variable Indicates a substitution variable in a SQL or SQLcl command. SQLcl substitutes the value of the specified substitution variable for each substitution variable it encounters. If the substitution variable is undefined, SQLcl prompts you for a value each time an & variable is found, and the first time an && 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. BRE[AK] [ON report_element [action [action]]]... Specifies where changes occur in a report and the formatting action to perform, such as: suppressing the display of duplicate values for a given column skipping a line each time a given column value changes printing computed figures each time a given column value changes or at the end of the report Enter BREAK with no clauses to list the current BREAK definition. Where report_element has the following syntax: {column expression ROW REPORT} and where action has the following syntax: [SKI[P] n [SKI[P]] PAGE] [NODUP[LICATES] DUP[LICATES]] BTI[TLE] [printspec [text variable]...] [ON OFF] 10 Places and formats a title at the bottom of each report page, or lists the current BTITLE definition. Use one of the following clauses in place of printspec: BOLD CE[NTER] COL n FORMAT text LE[FT] R[IGHT] S[KIP] [n] TAB n CL[EAR] option... Resets or erases the current value or setting for the specified option. Where option represents one of the following clauses: BRE[AKS] BUFF[ER] COL[UMNS] COMP[UTES] CONTEXT SCR[EEN] SQL TIMI[NG] COL[UMN] [{column expr} [option...]] Specifies display attributes for a given column, such as: text for the column heading alignment for the column heading format for NUMBER data wrapping of column data Also lists the current display attributes for a single column or for all columns. Where option represents one of the following clauses: ALI[AS] alias CLE[AR] ENTMAP {ON OFF} FOR[MAT] format HEA[DING] text JUS[TIFY] {L[EFT] C[ENTER] R[IGHT]} LIKE {expr alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT] PRI[NT] NUL[L] text 11 OLD_V[ALUE] variable ON OFF WRA[PPED] WOR[D_WRAPPED] TRU[NCATED] Note: Currently only NEW_V[ALUE] variable syntax is supported. Enter COLUMN [{column expr} FORMAT format] where the format element specifies 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 Examples Description, (comma) 9,999 Displays a comma in the specified position.. (period) $ $9999 Displays a period (decimal point) to separate the integral and fractional parts of a number. Displays a leading dollar sign Displays leading or trailing zeros (0). 9 B C 9999 B9999 C999 Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeros. A zero (0) is displayed for a value of zero. Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model. Displays the ISO currency symbol in the specified position. D EEEE G L 99D EEEE 9G999 L999 Displays the decimal character to separate the integral and fractional parts of a number. Displays a value in scientific notation (format must contain exactly four E s). Displays the group separator in the specified positions in the integral part of a number. Displays the local currency symbol in the specified position. 12 Element Examples Description MI PR RN rn 9999MI 9999PR RN rn Displays a trailing minus sign after a negative value. Displays a trailing space after a positive value. Displays a negative value in angle brackets . Displays a positive value with a leading and trailing space. Displays uppercase Roman numerals. Displays lowercase Roman numerals. Value can be an integer between 1 and S S S Displays a leading minus or plus sign. Displays a trailing minus or plus sign. TM U TM U9999 Displays the smallest number of decimal characters possible. The default is TM9. Fixed notation is used for output up to 64 characters, scientific notation for more than 64 characters. Cannot precede TM with any other element. TM can only be followed by a single 9 or E. Displays the dual currency symbol in the specified position. COMP[UTE] [function [LAB[EL] text]... OF {expr column alias}...on {expr column alias REPORT ROW}...] In combination with the BREAK command, calculates and prints summary lines using various standard computations. It also lists all COMPUTE definitions. The following table lists valid functions. All functions except NUMBER apply to non-null values only. COMPUTE functions are always executed in the following sequence AVG, COUNT, MINIMUM, MAXIMUM, NUMBER, SUM, STD, VARIANCE. Function Computes Applies to Datatypes AVG Average of non-null values NUMBER COU[NT] Count of non-null values All types MIN[IMUM] Minimum value NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING) MAX[IMUM] Maximum value NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING) NUM[BER] Count of rows All types SUM Sum of non-null values NUMBER 13 Function Computes Applies to Datatypes STD VAR[IANCE ] Standard deviation of non-null values Variance of non-null values NUMBER NUMBER SET SQLFORMAT SET SQLFORMAT {csv html xml json ansiconsole insert loader fixed default} Outputs reports in various formats. The ansiconsole option formats and resizes data according to the column widths, for easier readability. SET SQLFORMAT DELIMITED delimiter left enclosure right enclosure allows you to set a custom delimited format. SPO[OL] [filename[.ext] [CRE[ATE] REP[LACE] APP[END]] OFF OUT] Stores query results in a file, or optionally sends the file to a printer. OFF stops spooling. OUT stops spooling and sends the file to your computer's default printer. Enter SPOOL with no clauses to list the current spooling status. If no file extension is given, the default extension,.lst or.lis, is used. TTI[TLE] [printspec [text variable]...] [ON OFF] Places and formats a specified title at the top of each report
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks