UMBC CMSC 461 Spring '99 CSEE | 461 | 461 S'99 | lectures | news | Oracle Help | help

Oracle SQL*Plus

Oracle's SQL*Plus is an interactive environment with Oracle's RDBMS server.

Logging into Oracle

There are three forms of the command to log in to the Oracle server from the UNIX prompt: Obviously, the first version is not as secure as the other two, because anyone that sees the monitor as you type will discover your password and could use it without your permission.

Exiting Oracle

Exit Oracle with the exit command.

Executing Commands in the SQL*Plus

There are three forms of commands: SQL statements can be entered at the SQL> prompt (or read in from a text file). The statement continues until is terminated by a semicolon or line that has only a slash (/ on it, in which case the statement is executed. Another termination is with a blank line, and the statement is stored in a buffer for later execution.

SQL*Plus commands

NAME DESCRIPTION
describe Lists the column definitions for a database table
execute Executes a single PL/SQL statement
help Get online help for SQL*Plus commands
host Executes a host operating system command
remark Used for commands (also -- )
run Executes the SQL statement present in the buffer
set Sets SQL*Plus system variables
spool Stores the query results in an operating system file
start Executes commands in an operating system file

SQL*Plus Buffer Editing Commands

COMMAND DESCRIPTION
append text Add text to the end of the line
change /old/new Change old to new in a line
change /text Delete text from a line
clear buffer Deletes all lines
del Delete a line
get file Reads a file into the buffer
input Add one or more lines
input text Add a line of text
list List all lines in a buffer
list n List one line and make it current
list * List the current line
list last List the last line
list m n List lines m through n
save file Save contents of buffer to a file named file

Formatting Query Results

column command

Used to change the column headings and reformat the data in the column: column heading format Some samples: 
column student-no heading "Student ID" format 99999
column lastnaem heading "Last Name" format A15
column price format $9,99.99
Format Element Models and Description
ELEMENT EXAMPLE DESCRIPTION
9 9999 Number of "9"s specifies digits returned. Blanks are returned for leading zeroes and a zero is displayed for a value of zero.
0 0999
9990		 Displays a zero value as blank, or a value of zero in this position as a 0
$ $9999 Prefixes value with dollar sign
B B9999 Displays a value as a blank, regardless of the "0"s in the format model
MI 9999MI Displays "-" after a negative values. For a positive value, a trailing space is displayed.
S S9999 Returns "+" or positive values and "-" for negative values in this position.
PR 99D99 Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed.
D 9G999 Displays the decimal character in this position, separating the integral and fractional parts of a number.
G 9G999 Displays the group separator in this position.
C C999 Displays the ISO currency symbol in this position.
L L999 Displays the local currency symbol in this position.
,(comma) 9,999 displays a comma in this position.
.(period) 99.99 Displays a period (decimal point) in this position, separating the integral and fractional parts of a number.
V 999V99 Multiiplies value by 10n, where n is the number of "9"s after the "V".
EEEE 9.999EEEE Displays value in scientific notation (format must contain exactly four "E"s.
RN or rn RN Displays upper- or lowercaseRoman numberals. Value can be an integer between 1 and 3999
DATE DATE Displays value as a date in MM/DD/YY format; used to format NUMBER columns that represent Julian Dates.
When the order by clause is used in the query, the break command is used to create subsets of rows in the result, every time the value of a sort column changes. Space can be added after each subset of rows using skip.
compute command is used to display summary informat for each of the subsets.
SQL*Plus functions
All functions except NUMBER apply to non-null values only
FUNCTION COMPUTES APPLIES TO DATATYPES
AVG Average of non-null values NUMBER
COUNT
Count of non-null values 		All types
MAX Maximum value NUMBER, CHAR, NCHAR, VARCHAR2, NVARCHAR2 (NCHAR VARYING)
MIN Minimum NUMBER, CHAR, NCHAR, VARCHAR2, NVARCHAR2 (NCHAR VARYING)
NUMBER Count of rows All types
STD Standard deviation of non-null values NUMBER
SUM Sum of all non-null values NUMBER
D Variance of non-null values NUMBER
ttitle and btitle print titles on the top and bottom of each page.
Sample
spool report.dat
clear columns
clear breaks
clear computes
set headsep !

ttitle 'Student Report!Fall 1998!CMSC-461'
btitle 'Report by Gary Burt'

column studentno heading 'Student ID' format a10 word-wrapped
column lastname heading 'Last Name' format a12 word-wrapped
column firstname heading 'First Name' format a12 word-wrapped
column componentname heading 'Component' format a10 word-wrapped
column points heading 'Points' format 9990

break on studentno
compute sum of points on studentno

set linesize 79
set pagesize 50
set newpage 0

select E.studentno, S.lastname, S.firstname, T.points
from enrolls E, students S, components C, scores T
where  S.studentno = T.studentno and
       S.studentno = E.studentno and
       E.term = C.term and
       E.stars = C.stars and
       C.componentname = T.componentname and
       E.term = 'Fall 98' and
       E.stars = 7231;

spool off

Screen Capture of an SQL*Plus Session

Capture allows the capture of all session activity with: 
Spool capture.dat
Set echo on
and ends with: 
set echo off
spool off

CSEE | 461 | 461 S'99 | lectures | news | help

Tuesday, 08-Jun-1999 11:59:53 EDT