SQL*Plus is a component of Personal Oracle7 and is used for executing SQL statements. SQL*Plus can also function as a rudimentary report writer with the capability to subtotal, total, and suppress repeating values.
SQL*Plus plays an important role in developing applications for Personal Oracle7. You'll use SQL*Plus to develop scripts for constructing tables, indexes, and other database objects. It's also valuable for prototyping the SQL statements that your application will use. After you've refined the appropriate SQL statements, you can incorporate them into whatever application tool you're using--;PowerBuilder, Oracle Forms, or Visual Basic. Another advantage of using SQL*Plus is that it offers a consistent environment for the execution of scripts, regardless of the operating system in which it resides.
Both this chapter and the online Oracle SQL*Plus User's Guide use the term SQL*Plus session to refer to commands and responses that occur from the time you connect to SQL*Plus until the time that you disconnect from SQL*Plus.
To master the use of SQL*Plus, you need to understand the difference between Oracle SQL statements and SQL*Plus commands.
SQL statements fall into three categories:
No formal categories exist for SQL*Plus commands, but here is a convenient way to group them:
NoteYou can use SQL*DBA to issue SQL statements to Personal Oracle7. However, SQL*DBA doesn't recognize any SQL*Plus commands and lacks a command buffer that can be edited or saved.
SQL statements differ from SQL*Plus commands in several ways. SQL statements are terminated with a semicolon and don't require a continuation character at the end of each line; if a SQL*Plus command requires more than a single line of input, you must use the hyphen character (-)to tell SQL*Plus that another line of input follows.
SQL statements can be used in fourth-generation languages (4GLs), programs built with an Oracle precompiler, and ad hoc query tools and report writers; SQL*Plus commands cannot be used outside of SQL*Plus.
Many SQL*Plus commands remain in effect until new settings override them.
DESCRIBE is an indispensable command that displays a table definition. The information returned by DESCRIBE includes each column's name, its datatype, its length and scale, and whether or not it is mandatory.
SQL> desc Depot Name Null? Type ------------------------------- -------- ---- DEPOT_ID NOT NULL NUMBER(4) COMPANY_NAME VARCHAR2(30) STREET_ADDRESS VARCHAR2(60) CITY VARCHAR2(30) STATE VARCHAR2(2) ZIPCODE VARCHAR2(9) TELEPHONE VARCHAR2(10) FAX VARCHAR2(10) PRIMARY_CONTACT_NAME VARCHAR2(30) CREATED_DATE DATE CREATED_BY VARCHAR2(30) MODIFIED_DATE DATE MODIFIED_BY VARCHAR2(30)
However, the DESCRIBE command does not indicate which columns are part of the primary key, which columns are foreign keys, or which (if any) table or column constraints have been defined.
You can also use the DESCRIBE command to display the calling sequence for a function, procedure, or package.
SQL*Plus maintains the current SQL statement in a command buffer. Each line in this command buffer is numbered. You can edit, invoke, and save the contents of the command buffer; you can also display the buffer's contents by typing L (for list) at the SQL*Plus prompt.
SQL> list 1 select Customer_ID, Last_Name, First_Name 2 from Customer 3* order by Customer_ID SQL> l 1 select Customer_ID, Last_Name, First_Name 2 from Customer 3* order by Customer_ID
You can edit the contents of the command buffer in three ways:
TipIf you're trying to build a large SQL statement, use a text editor (such as Windows Notepad or Write) or a word processor. The editing limitations of SQL*Plus can be quite frustrating. You should build your SQL statement in another tool and either copy the lines or execute them from SQL*Plus.
For new SQL*Plus users, the most intuitive method for manipulating the command buffer is through copying and pasting. For example, you could highlight valid SQL statements from an open file in the Windows Notepad and paste the text into SQL*Plus.
To copy and paste text in SQL*Plus, do the following:
As an alternative to the copy-and-paste method, SQL*Plus furnishes an item in
the Edit menu that invokes an Editor selection. Under the Editor menu item, you may
either invoke the editor that's been defined or define a different editor. By default,
Windows Notepad is the defined editor. (See Figure 7.1.) *
Figure 7.1. Using Notepad to edit the
SQL*Plus command buffer.
A set of commands is available to you for editing the command buffer. People who used SQL*Plus before the advent of graphical user interfaces (GUIs) were forced to rely on these commands. Although these editing commands are not intuitive or user-friendly, I have included them here for the sake of completeness: using them is a matter of preference.
As with all editor commands, these commands are based on the concept of the current line in the command buffer. For example, you can type L to see the contents of the command buffer.
Command Description
L(LIST) To list the contents of the command buffer. N (where N is a number) Make N the current line to be edited. I(INSERT) Insert a new line after the current line. The new line becomes the current line. D(DELETE) Delete the current line. A(APPEND) text Append text to the current line. C(CHANGE)/string1/string2/ Change the first occurrence of string1 to string2.
The following paragraphs explain how these commands are actually used. If you want to edit the second line, you type a 2 at the SQL prompt.
SQL> l 1 select Customer_ID, Last_Name, First_Name 2 from Customer 3* order by Customer_ID SQL> 2 2* from Customer
Now that line 2 is the current line, insert a new line.
SQL> 2 2* from Customer SQL> i 3i where 4i Last_Name like 'J%' 5i SQL> l 1 select Customer_ID, Last_Name, First_Name 2 from Customer 3 where 4 Last_Name like 'J%' 5* order by Customer_ID
You've added a WHERE clause to the query, but now you realize that you want to add an additional criterion on line 4.
SQL> 4 4* Last_Name like 'J%' SQL> a or First_Name like '%e%' 4* Last_Name like 'J%' or First_Name like '%e%'
You should notice two things about the APPEND command. First, if you need a space between the end of a line and what you plan to append, you'll need two spaces between the A or APPEND and whatever it is you are adding. Second, the APPEND command echoes the line.
Finally, you realize that you want to change the last line so that the output is ordered by Last_Name instead of by Customer_ID.
SQL> 5 5* order by Customer_ID SQL> c/Customer_ID/Last_Name/ 5* order by Last_Name
In SQL*Plus, you can execute a SQL statement in one of three ways:
SQL> select Customer_ID, Last_Name, First_Name
2 from Customer
3 where
4 Last_Name like 'J%' or First_Name like '%e%'
5 order by Last_Name;
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ----------
1005 Horace Michelle
6104 Jensen Rachel
1001 Johnson Samuel
1002 Martinez Steve
1007 McClintock Harvey
1003 Smyth Julie
6 rows selected.
SQL> r
1 select Customer_ID, Last_Name, First_Name
2 from Customer
3 where
4 Last_Name like 'J%' or First_Name like '%e%'
5* order by Last_Name
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ----------
1005 Horace Michelle
6104 Jensen Rachel
1001 Johnson Samuel
1002 Martinez Steve
1007 McClintock Harvey
1003 Smyth Julie
6 rows selected.
SQL> /
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ----------
1005 Horace Michelle
6104 Jensen Rachel
1001 Johnson Samuel
1002 Martinez Steve
1007 McClintock Harvey
1003 Smyth Julie
6 rows selected.
You can use either of two methods to save SQL statements to an MS-DOS file:
The SAVE command stores the contents of the SQL*Plus command buffer into the specified MS-DOS file. If the file already exists, the SAVE command will not automatically override it.
SQL> l 1 select Customer_ID, Last_Name, First_Name 2 from Customer 3 where 4 Last_Name like 'J%' or First_Name like '%e%' 5* order by Last_Name SQL> save c:\fraywire\custqry Created file c:\fraywire\custqry
By default, the SAVE command uses a file extension of .SQL. You can override the default by specifying a different file extension. If you try to save the command buffer to an existing file, SQL*Plus returns this message:
SQL> save c:\fraywire\custqry File "c:\fraywire\custqry.SQL" already exists. Use another name or "SAVE filename REPLACE". SQL> save c:\fraywire\custqry replace Wrote file c:\fraywire\custqry
Presumably, you've saved a SQL statement using the SAVE command so that you can use it again. With the SQL*Plus GET command, you can retrieve the contents of the command buffer that were stored to a file with the SAVE command. This example starts with an empty command buffer, uses the GET command to retrieve a file containing a valid SQL statement, and executes the statement when the user types an R.
SQL> l
No lines in SQL buffer.
SQL> get c:\fraywire\custqry
1 select Customer_ID, Last_Name, First_Name
2 from Customer
3 where
4 Last_Name like 'J%' or First_Name like '%e%'
5* order by Last_Name
SQL> r
1 select Customer_ID, Last_Name, First_Name
2 from Customer
3 where
4 Last_Name like 'J%' or First_Name like '%e%'
5* order by Last_Name
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ------------------------------
1005 Horace Michelle
6104 Jensen Rachel
1001 Johnson Samuel
1002 Martinez Steve
1007 McClintock Harvey
1003 Smyth Julie
6 rows selected.
You can also use the / command to execute the contents of the command buffer. The difference between the / command and the R or RUN command is that the / doesn't echo the contents of the command buffer before executing the SQL statement. Remember that if you don't specify an extension for the filename, GET assumes that it is .SQL.
The SPOOL command saves query results to a file. The following code shows you how to invoke the SPOOL command at the SQL*Plus prompt:
SQL> spool c:\fraywire\qry_oput SQL> select Product_ID, Manufacturer_ID, Description 2 from Product 3 where 4 Manufacturer_ID = 'SEN101' 5 order by Product_ID; PRODUCT_ID MANUFA ------------ ------ DESCRIPTION -------------------------------------------------------------------- A504 SEN101 AMP 300W PER CHAN, RMS A509 SEN101 AMP 600 WATTS PER CHANNEL, RMS B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNEL B9310 SEN101 Pre amp, 250 W/channel, RMS C3002 SEN101 JUKEBOX, CD - 100 DISK CAPACITY D301 SEN101 6 rows selected. SQL> spool off
As shown in the previous example, the SQL*Plus command to stop spooling is
SQL> spool off
The version of SQL*Plus supplied with Personal Oracle7 also enables you to invoke
the SPOOL command as a menu item. (See Figure 7.2.)
Figure 7.2. Specifying a spool file
in SQL*Plus.
A SQL*Plus script consists of SQL*Plus commands and SQL statements. You can use several methods to invoke a SQL*Plus script:
start script
@script
Figure 7.3. Properties of a shortcut
that invokes a SQL*Plus script.
A SQL*Plus script can contain a START command to invoke another SQL*Plus script. For example, you could write a SQL*Plus script that calls four other SQL*Plus scripts in this way:
start c:\fraywire\cust_ca start c:\fraywire\cust_mi start c:\fraywire\cust_wa start c:\fraywire\prod_sen
Here's the output from invoking the MASTER.SQL script:
SQL> start c:\fraywire\master
SQL> start c:\fraywire\cust_ca
SQL> select Customer_ID
2 from Customer
3 where
4 Input truncated to 13 characters
State = 'CA';
CUSTOMER_ID
-----------
1001
SQL> start c:\fraywire\cust_mi
SQL> select Customer_ID
2 from Customer
3 where
4 Input truncated to 13 characters
State = 'MI';
CUSTOMER_ID
-----------
1008
1009
SQL> start c:\fraywire\cust_wa
SQL> select Customer_ID
2 from Customer
3 where
4 Input truncated to 13 characters
State = 'WA';
CUSTOMER_ID
-----------
1007
SQL> start c:\fraywire\prod_sen
SQL> select Product_ID
2 from Product
3 where
4 Input truncated to 27 characters
Manufacturer_ID = 'SEN101';
PRODUCT_ID
------------
A504
A509
B801
C3002
B9310
D301
6 rows selected.
The formatting that this section covers is achieved via SQL*Plus commands. These commands enable you to specify formats, totals, and subtotals and to suppress repeating values. The formatting commands are temporal; they remain in effect only for SQL statements processed during the same SQL*Plus session.
The COLUMN command offers many options for formatting a column's display. The COLUMN command can override the default output formatting that SQL*Plus provides.
Each column, depending on its datatype, has a format that can be specified for displaying output. For example, a numeric column used for storing price information can be formatted in many ways, including: With a dollar sign
With negative values enclosed in parentheses
With leading zeros
In your database application, you might need to display the estimate returned by
a depot in this way:
SQL> column Total_Cost format '$9,999'
SQL> r
1 select Repair_ID, Item_Number, Total_Cost
2 from Depot_Estimate
3* order by Repair_ID
REPAIR_ID ITEM_NUMBER TOTAL_COST
--------- ----------- ----------
2003 1 $523
2004 1 $316
2005 1 $142
2006 1 $217
2006 2 $66
As you can see, the format used for the Total_Cost column didn't include a decimal point; as a result, cents aren't displayed. You can use the COLUMN command to change the format.
SQL> column Total_Cost format '$9,999.99'
SQL> r
1 select Repair_ID, Item_Number, Total_Cost
2 from Depot_Estimate
3* order by Repair_ID
REPAIR_ID ITEM_NUMBER TOTAL_COST
--------- ----------- ----------
2003 1 $522.75
2004 1 $315.68
2005 1 $141.94
2006 1 $216.76
2006 2 $65.59
For character columns, you can use the FORMAT option to specify the displayed width of a column. For the Product table, you might want a report in which the product description wraps after displaying up to 30 characters.
SQL> column Description format a30
SQL> r
1 select Product_ID, Manufacturer_ID, Description
2 from Product
3* order by Product_ID
PRODUCT_ID MANUFA DESCRIPTION
------------ ------ ------------------------------
A2001 TES801 AMPLIFIER, 100W PER CHANNEL, R
MS
A504 SEN101 AMP 300W PER CHAN, RMS
A509 SEN101 AMP 600 WATTS PER CHANNEL, RMS
A903 TES801 AMP, PROFESSIONAL 800W RMS PER
CHANNEL
B311 TES801 Pre-amp, 120 W per channel
B384 TES801 PREAMP, 460 W/RMS
B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNE
L
B901 TES801 Preamplifier, 200 W per channe
l, RMS
B9310 SEN101 Pre amp, 250 W/channel, RMS
C2002 MIT501 CD PLAYER, SINGLE-DISK
C2005 MIT501 5-DISK CD PLAYER
C3002 SEN101 JUKEBOX, CD - 100 DISK CAPACIT
Y
D301 SEN101
TR901 TES801 Tuner
14 rows selected.
Of course, you really don't want the description to wrap to the next line if you're in the middle of a word. To prevent midword wrapping, you add the phrase WORD_WRAP to the COLUMN command in this way:
SQL> column Description format a30 word_wrap
SQL> r
1 select Product_ID, Manufacturer_ID, Description
2 from Product
3* order by Product_ID
PRODUCT_ID MANUFA DESCRIPTION
------------ ------ ------------------------------
A2001 TES801 AMPLIFIER, 100W PER CHANNEL,
RMS
A504 SEN101 AMP 300W PER CHAN, RMS
A509 SEN101 AMP 600 WATTS PER CHANNEL, RMS
A903 TES801 AMP, PROFESSIONAL 800W RMS PER
CHANNEL
B311 TES801 Pre-amp, 120 W per channel
B384 TES801 PREAMP, 460 W/RMS
B801 SEN101 PRE AMPLIFIER 150
WATTS/CHANNEL
B901 TES801 Preamplifier, 200 W per
channel, RMS
B9310 SEN101 Pre amp, 250 W/channel, RMS
C2002 MIT501 CD PLAYER, SINGLE-DISK
C2005 MIT501 5-DISK CD PLAYER
C3002 SEN101 JUKEBOX, CD - 100 DISK
CAPACITY
D301 SEN101
TR901 TES801 Tuner
14 rows selected.
As an alternative to word wrapping, you can use the TRUNC option to truncate the display of a column's contents.
SQL> column Description format a30 trunc SQL> select Prodct_ID, Manufacturer_ID, Description 2 from Product 3 order by Product_ID; PRODUCT_ID MANUFA DESCRIPTION ------------ ------ ------------------------------ A2001 TES801 AMPLIFIER, 100W PER CHANNEL, R A504 SEN101 AMP 300W PER CHAN, RMS A509 SEN101 AMP 600 WATTS PER CHANNEL, RMS A903 TES801 AMP, PROFESSIONAL 800W RMS PER B311 TES801 Pre-amp, 120 W per channel B384 TES801 PREAMP, 460 W/RMS B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNE B901 TES801 Preamplifier, 200 W per channe B9310 SEN101 Pre amp, 250 W/channel, RMS C2002 MIT501 CD PLAYER, SINGLE-DISK C2005 MIT501 5-DISK CD PLAYER C3002 SEN101 JUKEBOX, CD - 100 DISK CAPACIT D301 SEN101 TR901 TES801 Tuner 14 rows selected.
NoteRemember that if you set a column's format with the COLUMN command, SQL*Plus uses that format for all columns with the same name--;even if they occur in different tables. You can either reissue the COLUMN command with a different format before each SELECT statement or specify an alias so that each column name is unique.
When you specify a list of columns to be displayed in a query, SQL*Plus uses the column name as the column heading. If you want to use a different column heading, you have two choices: either use a column alias or use the COLUMN command to specify a different column heading.
A column alias is specified after the column expression. This example specifies the alias Depreciated_Amount for the column expression Initial_Retail_Value --; Current_Used_Value.
SQL> select Product_ID, Initial_Retail_Value - Current_Used_Value 2 Depreciated_Amount 3 from Product 4 order by Product_ID; PRODUCT_ID DEPRECIATED_AMOUNT ------------ ------------------ A2001 100 A504 195 A509 175 A903 225
If you want to use more than one word as a column heading, enclose the column heading in double quotes. In this case you aren't using an underscore (_) character between the words Depreciated and Amount.
SQL> r 1 select Product_ID, Initial_Retail_Value - Current_Used_Value 2 "Depreciated Amount" 3 from Product 4* order by Product_ID PRODUCT_ID Depreciated Amount ------------ ------------------ A2001 100 A504 195 A509 175 A903 225
Here's an example of how to change a column heading with the FORMAT command.
SQL> column Manufacturer_ID format A20 heading "Manufacturer" SQL> r 1 select Product_ID, Manufacturer_ID 2 from Product 3 where Description like '%CD%' 4* order by Product_ID PRODUCT_ID Manufacturer ------------ -------------------- C2002 MIT501 C2005 MIT501 C3002 SEN101
Personal Oracle7 provides a built-in function for computing a total--;the SUM function--;but SQL*Plus also provides the BREAK and COMPUTE commands for printing summary lines, including subtotals, averages, and totals. Suppose you want to display the average value of repair depot estimates along with the details of each estimate.
SQL> break on Depot_ID skip 1
SQL> compute avg of Total_Cost on Depot_ID
SQL> select Depot_ID, Labor_Cost, Parts_Cost, Total_Cost
2 from Depot_Estimate
3 order by Depot_ID;
DEPOT_ID LABOR_COST PARTS_COST TOTAL_COST
--------- ---------- ---------- ----------
1001 240 260 $522.75
83 123 $216.76
21 41 $65.59
********* ----------
avg $268.37
1002 183 122 $315.68
93 45 $141.94
89 139 $240.16
********* ----------
avg $232.59
1003 320 310 $657.13
91 93 $192.14
********* ----------
avg $424.64
1004 119 212 $349.55
21 46 $71.03
********* ----------
avg $210.29
DEPOT_ID LABOR_COST PARTS_COST TOTAL_COST
--------- ---------- ---------- ----------
1005 91 251 $363.96
55 81 $143.09
********* ----------
avg $253.53
12 rows selected.
The preceding example uses the BREAK command to suppress repeating values of Depot_ID and to skip a line whenever the value of Depot_ID changes. The COMPUTE command computes the average of Total_Cost for each Depot_ID.
The BREAK command instructs SQL*Plus to suppress the display of repeating values for a column. The following code shows you how to specify multiple columns for suppressing repeating values.
SQL> break on Repair_ID on Depot_ID
SQL> select Repair_ID, Depot_ID, Item_Number, Total_Cost
2 from Depot_Estimate
3 order by Repair_ID, Depot_ID, Item_Number;
REPAIR_ID DEPOT_ID ITEM_NUMBER TOTAL_COST
--------- --------- ----------- ----------
2003 1001 1 522.75
2004 1002 1 315.68
2005 1002 1 141.94
2006 1001 1 216.76
2 65.59
4001 1003 1 657.13
4002 1004 1 349.55
4003 1005 1 363.96
4004 1004 1 71.03
4005 1005 1 143.09
4006 1002 1 240.16
4007 1003 1 192.14
12 rows selected.
Remember that the BREAK command does not affect the ordering in which the SELECT statement returns the rows. Use the ORDER BY clause to specify the columns to be used in ordering the query results.
You can compute more than one function using the COMPUTE command. The following example computes the average and total of Total_Cost for each Depot_ID:
SQL> break on Depot_ID skip 1
SQL> compute avg sum of Total_Cost on Depot_ID
SQL> select Depot_ID, Labor_Cost, Parts_Cost, Total_Cost
2 from Depot_Estimate
3 order by Depot_ID;
DEPOT_ID LABOR_COST PARTS_COST TOTAL_COST
--------- ---------- ---------- ----------
1001 240 260 $522.75
83 123 $216.76
21 41 $65.59
********* ----------
avg $268.37
sum $805.10
1002 183 122 $315.68
93 45 $141.94
89 139 $240.16
********* ----------
avg $232.59
sum $697.78
1003 320 310 $657.13
91 93 $192.14
********* ----------
avg $424.64
sum $849.27
1004 119 212 $349.55
DEPOT_ID LABOR_COST PARTS_COST TOTAL_COST
--------- ---------- ---------- ----------
1004 21 46 $71.03
********* ----------
avg $210.29
sum $420.58
1005 91 251 $363.96
55 81 $143.09
********* ----------
avg $253.53
sum $507.05
12 rows selected.
In conjunction with its other formatting commands, SQL*Plus provides two commands for producing titles: TTITLE for specifying the title at the top of each page and BTITLE for specifying the title at the bottom of each page.
In the next example TTITLE and BTITLE display a title, a date, and the current page number.
SQL> ttitle left 'Frayed Wires Product Report' center 'May 9, 1997' -
> right 'Page' format 99 sql.pno
SQL> btitle center 'FOR INTERNAL USE ONLY'
SQL> select Product_ID, Manufacturer_ID, Description
2 from Product
3 order by Product_ID, Manufacturer_ID;
Frayed Wires Product Report May 9, 1997 Page 1
PRODUCT_ID MANUFACTURER_ID DESCRIPTION
-------------------- --------------- ------------------------------
A2001 TES801 AMPLIFIER, 100W PER CHANNEL, R
A504 SEN101 AMP 300W PER CHAN, RMS
A509 SEN101 AMP 600 WATTS PER CHANNEL, RMS
A903 TES801 AMP, PROFESSIONAL 800W RMS PER
B311 TES801 Pre-amp, 120 W per channel
B384 TES801 PREAMP, 460 W/RMS
B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNE
B901 TES801 Preamplifier, 200 W per channe
B9310 SEN101 Pre amp, 250 W/channel, RMS
C2002 MIT501 CD PLAYER, SINGLE-DISK
C2005 MIT501 5-DISK CD PLAYER
C3002 SEN101 JUKEBOX, CD - 100 DISK CAPACIT
D301 SEN101
TR901 TES801 Tuner
FOR INTERNAL USE ONLY
The TTITLE command specifies three separate strings:
The page number refers to a SQL*Plus variable named SQL.PNO. Another SQL*Plus variable that you might want to reference in a report is SQL.LNO, the current line number.
As you can see, BTITLE uses the same syntax as TTITLE.
SQL*Plus employs a number of system variables that control the characteristics of each SQL*Plus session. The SET command, for example, sets a system variable to a particular value; the SHOW command views the current setting of a system variable. You can also use the SHOW command to view all system variable settings.
SQL> show all arraysize 15 autocommit OFF autoprint OFF blockterminator "." (hex 2e) btitle OFF and is the 1st few characters of the next SELECT statement closecursor OFF colsep " " cmdsep OFF compatibility version NATIVE concat "." (hex 2e) copycommit 0 copytypecheck is ON crt "" define "&" (hex 26) echo OFF editfile "afiedt.buf" embedded OFF escape OFF feedback ON for 6 or more rows flagger OFF flush ON heading ON headsep "|" (hex 7c) linesize 100 lno 24 long 80 longchunksize 80 maxdata 60000 newpage 1 null "" numformat "" numwidth 9 pagesize 24 pause is OFF pno 0 recsep WRAP recsepchar " " (hex 20) release 702020301 serveroutput OFF showmode OFF spool OFF sqlcase MIXED sqlcode 0 sqlcontinue "> " sqlnumber ON sqlprefix "#" (hex 23) sqlprompt "SQL> " sqlterminator ";" (hex 3b) suffix "SQL" tab ON termout ON time OFF timing OFF trimout ON trimspool OFF ttitle OFF and is the 1st few characters of the next SELECT statement underline "-" (hex 2d) user is "SYSTEM" verify ON wrap : lines will be wrapped
As you can see, SQL*Plus has several system variables. A discussion of the more important ones follows.
AUTOCOMMIT controls how SQL*Plus commits transactions. If AUTOCOMMIT is set to ON, SQL*Plus performs a commit after each SQL statement is processed. The default setting for AUTOCOMMIT is OFF. With AUTOCOMMIT set to ON, if you accidentally delete some rows from a table, you can't roll back from the transaction. You generally want to leave AUTOCOMMIT set to OFF.
If you don't want to echo the SQL statements that SQL*Plus processes, set ECHO to OFF. OFF is commonly used to prepare reports in which you want to see only the desired results without the SQL statements that produced the results.
The system variable FEEDBACK controls when and whether SQL*Plus indicates the number of records returned by a query. The default setting of FEEDBACK is 6. In other words, if a query retrieves six or more rows, SQL*Plus displays the number of rows. If the query returns less than six rows, SQL*Plus will not provide this feedback. You can set the number of rows that will trigger FEEDBACK.
By default, SQL*Plus displays column headings. To disable the display of headings, simply set HEADING to OFF.
LINESIZE controls the maximum number of characters that appear on a line
of output. However, if you want to increase the LINESIZE, you'll also need
to increase the width of the screen buffer. (See Figure 7.4.)
Figure 7.4. Changing the SQL*Plus screen
buffer width.
If you absolutely need to store more than 2,000 characters in a column, you'll need to define the column using Oracle's LONG datatype. Please be aware that SQL*Plus will not display more than 80 characters of a LONG column unless you increase the LONG system variable. If you need to display a large number of characters--;say, up to 5,000--;you'll need to set LONG to 5000. In addition, you'll probably need to reduce ARRAYSIZE to 1.
SQL*Plus uses the value of NUMWIDTH to determine the width to use when displaying numbers. The default value of NUMWIDTH is 10. You can increase this value if your application requires it.
PAGESIZE defines the number of lines on a page, which determines when column headings and page titles should be displayed. If you want to suppress all titles and column headings, set PAGESIZE to 0.
If you submit a query that retrieves many records, the default behavior of SQL*Plus is to send the results zipping by you on the screen. If you set PAUSE to ON or to a string, SQL*Plus waits for you to press the Return key before continuing with the next screen's worth of output.
SQL> set pause More?
SQL> select *
2 from Customer;
More?
CUSTOMER_ID LAST_NAME FIRST_NAME SALUT
----------- ------------------------------ ------------------------------ -----
STREET_ADDRESS CITY
------------------------------------------------------------ ------------------
ZIPCODE WORK_TELEP HOME_TELEP FAX_NUMBER EARLIEST_ LATEST_TI LOC CREATED_D
--------- ---------- ---------- ---------- --------- --------- --- ---------
CREATED_BY MODIFIED_ MODIFIED_BY
------------------------------ --------- ------------------------------
1001 Johnson Samuel Mr.
1922 Bedford Blvd. Santa Margherina
91010 7145559876 7145550123
1002 Martinez Steve Mr.
9303 Channel Drive. Williamshire
12912 6025559133 6025553811 6025553833
1003 Smyth Julie Ms.
39121 Pillar Ave. Portsmith
03991 5035553843 5035551283
More?
SQL*Plus prompts you to press the Return key after displaying the number of lines defined in the system variable PAGESIZE.
You can include the current time in the SQL*Plus prompt by setting TIME to ON. You may find this setting useful when spooling output to a file.
SQL> set time on 18:39:48 SQL>
TIMING sets the display of timing statistics for each SQL command to ON or OFF. If you're trying to collect performance information about Personal Oracle7, you can set TIMING to ON and spool the statistics to a file.
SQL> set timing on
SQL> select count(*) from Product;
COUNT(*)
---------
14
real: 1210
In the ORAWIN95\PLS32 directory, you will find a file named GLOGIN.SQL. You can incorporate additional SQL*Plus commands by adding lines to this file. These system variable settings take effect for each SQL*Plus session.
Within SQL*Plus, you can reference substitution variables in your SQL statements. Substitution variables enable you to rerun the same SQL statement with a different set of values. You denote a substitution variable by preceding a user variable name with a single or double ampersand (&). For instance, a query is more flexible when it references a substitution variable.
SQL> r 1 select Product_ID, Manufacturer_ID, Initial_Retail_Value 2 from Product 3 where 4 Initial_Retail_Value > &init_value 5* order by Product_ID Enter value for init_value: 500 old 4: Initial_Retail_Value > &init_value new 4: Initial_Retail_Value > 500 PRODUCT_ID MANUFA INITIAL_RETAIL_VALUE ------------ ------ -------------------- A504 SEN101 585 A509 SEN101 850 A903 TES801 1050 B384 TES801 740 C3002 SEN101 2150
By embedding a substitution variable inside a text string, you will avoid having to include quotes around the value. The following example illustrates the use of two different substitution variables--;the first substitution variable is used for specifying a numeric value and the second substitution variable is placed inside a text string:
SQL> r 1 select Product_ID, Manufacturer_ID, Initial_Retail_Value 2 from Product 3 where 4 Initial_Retail_Value > &init_value and 5 Manufacturer_ID like '%&Manuf_ID%' 6* order by Product_ID Enter value for init_value: 400 old 4: Initial_Retail_Value > &init_value and new 4: Initial_Retail_Value > 400 and Enter value for manuf_id: SEN old 5: Manufacturer_ID like '%&Manuf_ID%' new 5: Manufacturer_ID like '%SEN%' PRODUCT_ID MANUFA INITIAL_RETAIL_VALUE ------------ ------ -------------------- A504 SEN101 585 A509 SEN101 850 B9310 SEN101 430 C3002 SEN101 2150
When SQL*Plus prompts you to supply a value for a substitution value, you must supply a value that corresponds to the appropriate datatype. For example, if you want to retrieve products whose Manufacturer_ID is like '%SEN%', you must remember surround the string with single quotes.
SQL> r
1 select Product_ID, Manufacturer_ID, Initial_Retail_Value
2 from Product
3 where
4 Manufacturer_ID like &Manuf_ID
5* order by Product_ID
Enter value for manuf_id: %SEN%
old 4: Manufacturer_ID like &Manuf_ID
new 4: Manufacturer_ID like %SEN%
Manufacturer_ID like %SEN%
*
ERROR at line 4:
ORA-00911: invalid character
The convenience of a substitution variable is that it enables you to rerun the same SQL statement with different values. You should use substitution variables with INSERT statements to reduce your keystrokes. Remember to give your substitution variables meaningful names.
The START command invokes a specified SQL*Plus script that contains SQL statements and SQL*Plus commands. You can also use the START command to pass values to the script that you want to run.
Suppose that you want to create a report containing customer information for customers living in a specific state. However, you want to be able to specify the state when you run the report. The following SQL*Plus script, named custstat.sql, shows you how to implement these specifications:
set echo off spool c:\fraywire\custstat select Customer_ID, Last_Name, First_Name from Customer where State = '&1' order by &2;
spool off
Notice that instead of having meaningful names, the substitution variables are named &1 and &2. From SQL*Plus, you invoke the custstat.sql script with the START command in this way:
SQL> start c:\fraywire\custstat 'MI' Last_Name
old 4: State = '&1'
new 4: State = 'MI'
old 5: order by &2
new 5: order by Last_Name
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ------------------------------
1009 Chen Laura
1008 Moran Sarah
The name of each substitution variable corresponds to its order in the START command: &1 corresponded to 'MI', and &2 corresponded to Last_Name. In general, the syntax for the START command is
START script [sub1] [sub2] ... [subN]
where script is the filename of the SQL*Plus script to be executed and sub1 through subN are arguments to be used as values in substitution variables.
SQL*Plus provides two commands for setting user variables: DEFINE and ACCEPT.
With the DEFINE command, you can create a user variable and assign a character value to it.
SQL> define Selected_Mfgr = "SEN101"
SQL> r
1 select Product_ID, Manufacturer_ID, Description
2 from Product
3 where
4 Manufacturer_ID = '&Selected_Mfgr'
5* order by Product_ID
old 4: Manufacturer_ID = '&Selected_Mfgr'
new 4: Manufacturer_ID = 'SEN101'
Frayed Wires Product Report May 9, 1997 Page 1
PRODUCT_ID MANUFACTURER_ID DESCRIPTION
-------------------- --------------- ------------------------------
A504 SEN101 AMP 300W PER CHAN, RMS
A509 SEN101 AMP 600 WATTS PER CHANNEL, RMS
B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNE
B9310 SEN101 Pre amp, 250 W/channel, RMS
C3002 SEN101 JUKEBOX, CD - 100 DISK CAPACIT
The ACCEPT command specifies a user variable, its datatype, and the text to be used in a user prompt.
SQL> accept Descrip_Pattern char -
> prompt 'Enter Description text to search for: '
Enter Description text to search for: CD
SQL> select Product_ID, Manufacturer_ID, Description
2 from Product
3 where
4 Description like '%&Descrip_Pattern%';
old 4: Description like '%&Descrip_Pattern%'
new 4: Description like '%CD%'
Frayed Wires Product Report May 9, 1997 Page 1
PRODUCT_ID MANUFACTURER_ID DESCRIPTION
-------------------- --------------- ------------------------------
C2002 MIT501 CD PLAYER, SINGLE-DISK
C2005 MIT501 5-DISK CD PLAYER
C3002 SEN101 JUKEBOX, CD - 100 DISK CAPACIT
If you want to provide multiple lines of information when prompting the user, use the PROMPT command in conjunction with ACCEPT. Here's a SQL*Plus script that contains the PROMPT and ACCEPT commands:
column Description format a20 trunc set echo off prompt Please enter the pattern to search prompt product descriptions. You may use the percent sign prompt as a wildcard and the underscore as a placeholder accept Descrip_Pattern char prompt 'Pattern: ' select Product_ID, Manufacturer_ID, Description from Product where Description like '&Descrip_Pattern' order by Product_ID;
Here is how the SQL*Plus script behaves when invoked:
SQL> @c:\fraywire\accept Please enter the pattern to search product descriptions. You may use the percent sign as a wildcard and the underscore as a placeholder Pattern: %AMP% old 4: Description like '&Descrip_Pattern' new 4: Description like '%AMP%' PRODUCT_ID MANUFA DESCRIPTION ------------ ------ -------------------- A2001 TES801 AMPLIFIER, 100W PER A504 SEN101 AMP 300W PER CHAN, R A509 SEN101 AMP 600 WATTS PER CH A903 TES801 AMP, PROFESSIONAL 80 B384 TES801 PREAMP, 460 W/RMS B801 SEN101 PRE AMPLIFIER 150 WA 6 rows selected.
One SQL*Plus command that you'll find quite useful is the COPY command. At first glance, COPY seems to offer the same capability as the INSERT and CREATE TABLE statements; that is, it copies data from an existing table to another existing table or a new table. The COPY command does have one major advantage over the INSERT statement; see Chapter 9, "Using SQL to Modify Data," for a thorough discussion.
SQL*Plus--;a Personal Oracle7 tool that offers an interactive SQL interface--;is suitable for creating database objects such as tables, indexes, triggers, and stored procedures. In addition, you can use SQL*Plus for prototyping SQL statements for use in an application development tool such as PowerBuilder or Visual Basic.
SQL*Plus can process SQL statements interactively, or it can invoke a script that contains SQL statements and SQL*Plus commands.
Each SQL statement is stored in a command buffer that can be executed repeatedly or modified.