Part 13 Oracle SQL*PlusCopyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page 2 Oracle SQL*Plus Oracle’s SQL*Plus is a set of extensions to ANSI SQL. In addition to including standard SQL statements, there are commands to give you the ability to: - Format reports - Edit and save SQL statements - Store and print query results - Get help For example: COLUMN ename HEADING Employee|Name COLUMN hired HEADING Date|Hired TTITLE ‘Employees Hired|Last Year’ SELECT ename, hired FROM emp WHERE hired > ‘01-DEC-92’; Yields: Employees Hired Last Year Employee Date Name Hired barger 23-Jan-93 radl 03-Dec-92 thomas 03-Dec-92Copyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page 3 SQL Buffer The SQL buffer holds the CURRENT SQL command. This command is held in the buffer until you enter another SQL command or exit. The command in the buffer may contain many lines, and, for ease of editing, it is best to place a complex SQL command on many lines, generally one clause per line. SQL*PLUS editing commands: LIST or L Displays all lines of the buffer, puts you at last line LIST 4 or 4 Displays line number 4 only, puts you at line 4 LIST * Displays the current line LIST LAST Display the last line and puts you there LIST 2 4 Displays lines 2 through 4 CHANGE or C Edits the line: C /old text/new text INPUT or I Adds a new blank line after current line APPEND or A Appends text to current line: A added text DEL Deletes current line CLEAR BUFF Deletes all lines in the current buffer RUN or / Executes the buffer as an SQL command SAVE fname Save buffer to file fname.sql ED fname Edits fname.sql using current editor GET fname Reads fname.sql into buffer START fname Executes commands in file fname.sqlCopyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page 4 Controlling the Environment The SHOW ALL command will display the values of about 250 environment variables which can be changed with appropriate commands. Some of the more relevant: SHOW LINESIZE (will be 80 columns in width by default) SET LINESIZE 79 SHOW PAGESIZE (will be 14 lines in height by default) SET PAGESIZE 22 SHOW SPOOL (will be OFF by default) SPOOL myfile (will start sending output to a file named myfile.lis) SPOOL OFF (will stop sending output to file) SPOOL OUT (will stop sending output to file, will send file to printer) SHOW TTITLE (will be OFF by default) TTITLE ‘First Line|Second Line’ SHOW BTITLE (will be OFF by default) BTITLE ‘Message’ LEFTCopyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page 5 Controlling the Appearance The DESCRIBE tablename command will show the field names and formats within a table. The COLUMN columnname command will show the formatting and heading(s) to be applied to a column display The BREAK ON fieldname command will cause a break to occur whenever the value in fieldname changes (normally, you would sort on fieldname as part of the query) The COMPUTE SUM OF fieldone ON breakfield will cause a sum to be printed when the break occurs For example: SQL> COLUMN ename FORMAT a8 WRAP HEADING ‘Last|Name’ SQL> BREAK ON mgr SQL> COMPUTE SUM OF rate ON mgr SQL> SELECT ename, mgr, rate FROM emp ORDER by mgr; would generate a report containing all employees, with a break after each manager, containing a total of the hourly rates of all the employees working for that manager.Copyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page 6 Getting Information The HELP command is supposed to show you all the legal SQL commands, and HELP command show you the syntax of a specific command (in Oracle). For some reason it doesn’t do that on the DEC Alpha. The HOST command will execute any VMS command typed after it, for example: HOST DIR *.SQL would give you a list of all files with the .SQL file extension.Copyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page 7 Formatting The following formatting commands can be used: A10 10 character alphabetic 9999 4 digit number 099 3 digit number with preceding zero $99.99 preceding $, decimal point alignment, 2 decimal places B99 preceding zero printed as blank 999MI negative numbers have a trailing - 99PR negative numbers are in angle brackets 9,999 insert comma if >= 1000 999V99 multiply value by 100 9.99EEEE display in scientific notationCopyright © 1971-2002 Thomas P. Sturm Oracle SQL*Plus Part 13, Page