This article helps you to fully understand the Sqlplus command.
Remark: Tell Sqlplus the next word is a comment, not an order.
Set Haedsep: The title separator identifies a character that tells Sqlplus to break a heading into two or more rows.
Ttitle: Sets the header header for each page of the report.
Btitle: Sets the tail caption for each page of the report.
Column: Gives sqlplus various instructions about the title, format, and processing of the column.
Break on: Tells Sqlplus to insert spaces between parts of the report, or to disconnect subtotals and grand totals.
Compute sum: Sqlplus calculates subtotals.
Set Linesize: Sets the maximum number of characters for a report's rows.
Set pagesize: Sets the maximum number of rows for the page.
Set NewPage: Sets the number of blank lines between pages.
Spool: Moves a report that is usually displayed on the screen into a file for printing.
/**/marks the beginning and end of a comment within a SQL item. Similar to remark.
--marks the beginning of an online comment within a SQL item. All content that begins at the end of the line is treated as a comment. Similar to remark.
Set pause: Causes the screen to appear paused between pages.
Save: Saves the SQL query you are building to a selected file.
Host: Sends commands to the main operating system.
Start or @: tells Sqlplus to execute instructions that have been stored in the file.
Edit: Allows you to quickly leave Sqlplus and enter the editor you choose.
Define_editor: Tell Sqlplus the name of the editor you chose.
Exit or quit: terminates Sqlplus.
A simple example of a report:
The following are the referenced contents: Activity.lst Sql> Sql> Select Name, Title, Checkoutdate, Returneddate, 2 returneddate-checkoutdate as Daysout/*count days*/ 3 from Bookshelf_checkout 4 ORDER by Name,checkoutdate; Mon Aug Page 1 Checkout Log for 1/1/02-3/31/02 Days NAME TITLE checkoutdate returneddate out ------------ -------- ------------ ------------ ---- Dorah TALBOT either/or 02-jan-02 10-jan-02 8.00 POLAR EXPRESS 01-feb-02 15-feb-02 14.00 Good DOG, CARL 01-feb-02 15-feb-02 14.00. My Ledger 15-feb-02 03-mar-02 16.00 ******************** ------- Avg 13.00 EMILY TALBOT ANNE of GREEN Gables 02-jan-02 20-jan-02 18.00 Midnight MAGIC 20-jan-02 03-feb-02 14.00 HARRY POTTER and 03-feb-02 14-feb-02 11.00 The Goblet of FIRE ******************** ------- Avg 14.33 FRED FULLER JOHN ADAMS 01-feb-02 01-mar-02 28.00 TRUMAN 01-mar-02 20-mar-02 19.00 ******************** ------- Avg 23.50 Gerhardt Kentgen Wonderful Life 02-jan-02 02-feb-02 31.00 Midnight MAGIC 05-feb-02 10-feb-02 5.00 The Mismeasure of 13-feb-02 05-mar-02 20.00 Mans ******************** ------- Avg 18.67 JED HOPKINS innumeracy 01-jan-02 22-jan-02 21.00 To KILL A 15-feb-02 01-mar-02 14.00 Mockingbird ******************** ------- Avg 17.50 PAT Lavay the SHIPPING NEWS 02-jan-02 12-jan-02 10.00 The Mismeasure of 12-jan-02 12-feb-02 31.00 Mans ******************** ------- Avg 20.50 ROLAND BRANDT the SHIPPING NEWS 12-jan-02 12-mar-02 59.00 The discoverers 12-jan-02 01-mar-02 48.00 West with the NIGHT 12-jan-02 01-mar-02 48.00 ******************** ------- Avg 51.67 ------- Avg 22.58 From the Bookshelf Sql> Start Activity.sql REM Bookshelf Activity Set HEADSEP! Ttitle ' Checkout Log for 1/1/02-3/31/02 ' Btitle ' from the Bookshelf ' Column Name format A20 Column Title format A20 word_wrapped Column Daysout format 999.99 Column Daysout heading ' days! Out ' Break on Name Skip 1 on Compute avg of Daysout on Name Compute avg of Daysout on Set Linesize 100 Set pagesize 60 Set NewPage 0 Set Feedback off Spool Activity.lst Select Name, Title, Checkoutdate, Returneddate, Returneddate-checkoutdate as Daysout/**//*count days*/ From Bookshelf_checkout Order BY Name,checkoutdate; Spool off
|