ch20.htm

Learn SQL in an easy way...

<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">appinfo is ON and set to &quot;SQL*Plus&quot;
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
blockterminator &quot;.&quot; (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
closecursor OFF
colsep &quot; &quot;
cmdsep OFF
compatibility version NATIVE
concat &quot;.&quot; (hex 2e)
copycommit 0
copytypecheck is ON
crt &quot;&quot;
define &quot;&amp;&quot; (hex 26)
echo OFF
editfile &quot;afiedt.buf&quot;
embedded OFF
escape OFF
feedback ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep &quot;|&quot; (hex 7c)
linesize 100
lno 6
long 80
longchunksize 80
maxdata 60000
newpage 1
null &quot;&quot;
numformat &quot;&quot;
numwidth 9
pagesize 24
pause is OFF
pno 1
recsep WRAP
recsepchar &quot; &quot; (hex 20)
release 703020200
repheader OFF and is NULL
repfooter OFF and is NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 1007
sqlcontinue &quot;&gt; &quot;
sqlnumber ON
sqlprefix &quot;#&quot; (hex 23)
sqlprompt &quot;SQL&gt; &quot;
sqlterminator &quot;;&quot; (hex 3b)
suffix &quot;SQL&quot;
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 &quot;-&quot; (hex 2d)
user is &quot;RYAN&quot;
verify ON
wrap : lines will be wrapped
</FONT></PRE>
<P>The <TT>SHOW </TT>command displays a specific setting entered by the user. Suppose
you have access to multiple database user IDs and you want to see how you are logged
on. You can issue the following command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>show user</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">user is &quot;RYAN&quot;
</FONT></PRE>
<P>To see the current line size of output, you would type:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>show linesize</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">linesize 100
</FONT></PRE>
<H2><FONT COLOR="#000077">File Commands</FONT></H2>
<P>Various commands enable you to manipulate files in SQL*Plus. These commands include
creating a file, editing the file using a full-screen editor as opposed to using
the SQL*Plus buffer, and redirecting output to a file. You also need to know how
to execute an SQL file after it is created.
<H3><FONT COLOR="#000077">The SAVE, GET, and EDIT Commands</FONT></H3>
<P>The <TT>SAVE</TT> command saves the contents of the SQL statement in the buffer
to a file whose name you specify. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from products</B>
  3  <B>where unit_cost &lt; 25</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>save query1.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Created file query1.sql
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>After a file has been saved, you can use the <TT>GET</TT> command to list the
file. <TT>GET</TT> is very similar to the <TT>LIST</TT> command. Just remember that
<TT>GET</TT> deals with statements that have been saved to files, whereas <TT>LIST</TT>
deals with the statement that is stored in the buffer.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> get query1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  1  select *
  2  from products
  3* where unit_cost &lt; 25
</FONT></PRE>
<P>You can use the <TT>EDIT</TT> command either to create a new file or to edit an
existing file. When issuing this command, you are taken into a full-screen editor,
more than likely Notepad in Windows. You will find that it is usually easier to modify
a file with <TT>EDIT</TT> than through the buffer, particularly if you are dealing
with a large or complex statement. Figure 20.1 shows an example of the <TT>EDIT</TT>
command.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>edit query1.sql</B>
</FONT></PRE>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 20.1.<BR>
</B></A><I>Editing a file in SQL*Plus.</I></P>
<H3><FONT COLOR="#000077">Starting a File</FONT></H3>
<P>Now that you know how to create and edit an SQL file, the command to execute it
is simple. It can take one of the following forms:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">START filename
</FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">STA filename
</FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">@filename
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Commands are not case sensitive.
	
<HR>
</P>

</BLOCKQUOTE>

<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>start query1.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                   10.5
P05 NATURE CALENDAR                    12.99
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You do not have to specify the file
	extension <TT>.sql</TT> to start a file from SQL*Plus. The database assumes that
	the file you are executing has this extension. Similarly, when you are creating a
	file from the <TT>SQL&gt;</TT> prompt or use <TT>SAVE</TT>, <TT>GET</TT>, or <TT>EDIT</TT>,
	you do not have to include the extension if it is <TT>.sql</TT>. 
<HR>
</P>

</BLOCKQUOTE>

<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> @query1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                  10.5
P05 NATURE CALENDAR                    12.99</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>run query1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  1  select *
  2  from products
  3* where unit_cost &lt; 25

PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                   10.5
P05 NATURE CALENDAR                    12.99
</FONT></PRE>
<P>Notice that when you use <TT>RUN</TT> to execute a query, the statement is echoed,
or displayed on the screen.
<H3><FONT COLOR="#000077">Spooling Query Output</FONT></H3>
<P>Viewing the output of your query on the screen is very convenient, but what if
you want to save the results for future reference or you want to print the file?
The <TT>SPOOL</TT> command allows you to send your output to a specified file. If
the file does not exist, it will be created. If the file exists, it will be overwritten,
as shown in Figure 20.2.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>spool prod.lst</B>
SQL&gt; <B>select *</B>
  2  <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ --------- 
P01 MICKEY MOUSE LAMP                  29.95
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                   10.5
P05 NATURE CALENDAR                    12.99
P06 SQL COMMAND REFERENCE              29.99
P07 BLACK LEATHER BRIEFCASE            99.99

7 rows selected.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>spool off</B>
SQL&gt; <B>edit prod.lst</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The output in Figure 20.2 is an SQL*Plus file. You must use the <TT>SPOOL OFF</TT>
command to stop spooling to a file. When you exit SQL*Plus, <TT>SPOOL OFF</TT> is
automatic. But if you do not exit and you continue to work in SQL*Plus, everything
you do will be spooled to your file until you issue the command <TT>SPOOL OFF</TT>.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 20.2.<BR>
</B></A><I>Spooling your output to a file.</I></P>
<H2><FONT COLOR="#000077">SET Commands</FONT></H2>
<P><TT>SET</TT> commands in Oracle change SQL*Plus session settings. By using these
commands, you can customize your SQL working environment and invoke options to make
your output results more presentable. You can control many of the <TT>SET</TT> commands
by turning an option on or off.</P>
<P>To see how the <TT>SET</TT> commands work, perform a simple <TT>select</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ --------- 
P01 MICKEY MOUSE LAMP                  29.95
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                   10.5
P05 NATURE CALENDAR                    12.99
P06 SQL COMMAND REFERENCE              29.99
P07 BLACK LEATHER BRIEFCASE            99.99

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The last line of output</P>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<P>is called feedback, which is an SQL setting that can be modified. The settings
have defaults, and in this case the default for <TT>FEEDBACK</TT> is <TT>on</TT>.
If you wanted, you could type</P>
<PRE><FONT COLOR="#0066FF">SET FEEDBACK ON
</FONT></PRE>
<P>before issuing your select statement. Now suppose that you do not want to see
the feedback, as happens to be the case with some reports, particularly summarized
reports with computations.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>set feedback off</B>
SQL&gt; <B>select *</B>
  2  <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP                  29.95
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                   10.5
P05 NATURE CALENDAR                    12.99
P06 SQL COMMAND REFERENCE              29.99
P07 BLACK LEATHER BRIEFCASE            99.99
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>SET FEEDBACK OFF</TT> turns off the feedback display.</P>
<P>In some cases you may want to suppress the column headings from being displayed
on a report. This setting is called <TT>HEADING</TT>, which can also be set <TT>ON</TT>
or <TT>OFF</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>set heading off</B>
SQL&gt;<B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">P01 MICKEY MOUSE LAMP                  29.95
P02 NO 2 PENCILS - 20 PACK              1.99
P03 COFFEE MUG                          6.95
P04 FAR SIDE CALENDAR                   10.5
P05 NATURE CALENDAR                    12.99
P06 SQL COMMAND REFERENCE              29.99
P07 BLACK LEATHER BRIEFCASE            99.99
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The column headings have been eliminated from the output. Only the actual data
is displayed.</P>
<P>You can change a wide array of settings to manipulate how your output is displayed.
One option, <TT>LINESIZE</TT>, allows you to specify the length of each line of your
output. A small line size will more than likely cause your output to wrap; increasing
the line size may be necessary to suppress wrapping of a line that exceeds the default