Oracle8 SQL*Plus User's Guide

Document Conventions

1. Introduction
2. Getting Started
   • 2.1. Applying for an User Account
   • 2.2. Host Login and Initial Oracle Setup Procedure
   • 2.3. New Database Request
   • 2.4. Starting and Stopping Oracle SQL*Plus
   • 2.5. Changing your Oracle Password
   • 2.6. Entering SQL Commands
3. Oracle SQL*Plus Commands
   • 3.1. The SQL Buffer
   • 3.2. Editing the SQL Command
   • 3.3. Rerunning the SQL Command
   • 3.4. Saving the SQL Command As A Command File
   • 3.5. Running A Command File
   • 3.6. Running A Nested Command File
   • 3.7. Capturing and Printing Query Results
   • 3.8. Running Host Commands
   • 3.9. Getting Help
   • 3.10. Basic SQL*Plus commands
4. Using SQL to Manipulate Data
   • 4.1. Basic SQL DDL Commands
     • 4.1.1. The CREATE TABLE Command
     • 4.1.2. The CREATE TABLE AS Command
     • 4.1.3. Modify Table Definition
     • 4.1.4. Delete A Table From The Database
     • 4.1.5. Data Types
   • 4.2. Basic SQL DML Commands
     • 4.2.1. The SQL SELECT Command
     • 4.2.2. The SQL INSERT Command
     • 4.2.3. The SQL UPDATE Command
     • 4.2.4. The SQL DELETE Command
     • 4.2.5. Search Conditions
     • 4.2.6. The SQL Group Functions
     • 4.2.7. Views
   • 4.3. Granting Permission
5. Object-Relational Extension
   • 5.1. Define Object Types
   • 5.2. Dropping Objects
   • 5.3. Create Column Objects
   • 5.4. Build Object Tables
     • 5.4.1 Object Identifiers
   • 5.5. Implement Object Methods
     • 5.5.1 Constructor Methods
     • 5.5.2 User-defined Methods
     • 5.5.3 Invoking Object Methods
     • 5.5.4 Comparing Object
     • 5.5.5 Data Dictionary Views for Methods
   • 5.6. Define VARRAYS
     • 5.6.1 VARRAYS Limitations
   • 5.7. Build Object Views
6. Trouble Shooting
   • 6.1. The "ksh: sqlplus: not found" Error

Disclaimer
Copyright © 1998-2000 Dalhousie University
Last Updated: Mar, 1999 by Poh Chua  (poh.chua@dal.ca)

Document Conventions

• Words in boldface are key words and must be typed as shown.

• Words in italics are user-supplied elements.

• Clauses enclosed in square brackets ([ ]) are optional.

• Key words or clauses separated by vertical bars (|) indicate a list from which one element is chosen.

• Clauses enclosed in curly braces ({ }) are optional and can be repeated zero or more times.

1. Introduction

This document is intended for users who are new to Oracle and SQL language. Readers are expected to have a basic understanding of relational database model and some familiarity in unix environment.

2. Getting Started

Oracle is available on GS.Dal.CA. Please follow the General Services Host - GS.Dal.CA procedure to obtain a username.

Users on GS are NOT automatically registered to use Oracle. You must make the request by indicating it on your username application form. If you already have your GS username and you are currently not an Oracle user, please email the Oracle Database Administrator now!

2.2. Host Login and Initial Oracle Setup Procedure

Most computer labs on campus provide telnet software for access to host "gs.dal.ca". You may also use your home computer with dial up capability for GS connection.

To setup your GS username to use Oracle, login to GS and at the $ prompt, enter:

. setup oracle8

(note there is a space before and after the word setup)
This is a one time procedure. Oracle will be automatically setup whenever you login.

2.3. New Database Request

Under normal circumstances, users who requested Oracle access will be registered to use the database named GS. Course instructors or individuals who have special needs may ask for separate databases during or after username applications.

For those who shared the same database, their information are strictly confidential. To allow others to view and/or modify the data, the owners must explicitly grant access permissions to one or more users.

2.4. Starting and Stopping Oracle SQL*Plus

To start SQL*Plus, enter the following command at the operating system prompt (i.e. "$") and press [RETURN]:

$ sqlplus username

Next Oracle will prompt you for your password. The initial password is the same as your Oracle username. Enter your password and press [Return]. If everything went well, SQL*Plus displays the SQL*Plus command prompt:

SQL>

The command prompt indicates that SQL*Plus is ready to accept your commands.

To leave SQL*Plus, enter the EXIT command at the SQL*Plus command prompt:

SQL> exit

2.5. Changing your Oracle Password

Each new Oracle user is assigned a default password. It is the same as the Oracle username. After login to SQL*Plus, probably the first task the user should do is to change the password for security reason.

To change your Oracle password, enter the following command at SQL*Plus prompt

SQL> alter user username identified by newpassword;

Here is a sample SQL command:

SQL> select empno, ename, job, sal from emp;

If the data is shared by many users, Oracle may lock the data being used depending on circumstances. You should include the SQL COMMIT in your SQL statements at the end of logically complete units of work to release locks on the data. For example:

SQL> select empno, ename, job, sal from emp;

...query results here...

SQL> update emp set sal=sal*1.05;

37 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> select empno, ename, job, sal
  2  from emp
  3  where sal < 1000;

3.2. Editing the SQL Command

Command	Abbreviation	Purpose
APPEND text	A text	adds text at the end of a line
0 text	0 text	adds text before the first line
CHANGE /old/new	C /old/new	changes old to new in a line
CHANGE /text	C /text	deletes text from a line
CLEAR BUFFER	CL BUFF	deletes all lines
DEL	(none)	deletes the current line
DEL n	(none)	deletes line n
DEL *	(none)	deletes the current line
DEL LAST	(none)	deletes the last line
DEL m n	(none)	deletes a range of lines (m to n)
INPUT	I	adds one or more lines
INPUT text	I text	adds a line consisting of text
LIST	L	lists all lines in the SQL buffer
LIST n	L n or n	lists line n
LIST *	L *	lists the current line
LIST LAST	L LAST	lists the last line
LIST m n	L m n	lists a range of lines (m to n)

3.3. Rerunning the SQL Command

SQL> run
or
SQL> /

SQL> list
1   select empno, ename, job, sal2   from emp 
3*  where sal < 1000
SQL> save filename

SQL> start filename
or 
SQL> @filename

$ sqlplus [username[/password]] @filename

SQL> spool assignment1
SQL> (your SQL command here)
SQL> (your SQL results)
SQL> spool off

Be aware that the SPOOL command automatically overwrite any existing file with the same filename.

You can tell whether the spooling is on or off simply by entering the SPOOL command without any argument.

To print the spooled file, use the following command at the operating system prompt:

$ lpr -J Abanner filename

You can pick up the printout at the machine room, basement of Killam Library.

If you do not wish to send the file to the central line printer, you can FTP it to another machine or download it to your pc and print it from there.

3.8. Running Host Commands

SQL> host ls *.sql

The Oracle8 Server Version 8.0.3 On-line Generic Documentation contains a complete set of manuals on Oracle8 Server and related product components.

3.10. Basic SQL*Plus commands

Command	Description
ACC[EPT] variable [PROMPT text]	Reads input from the user and stores it in a variable.
DEF[INE] [variable]|[variable = text]	Defines memory variable.
DESC[RIBE] objectname	Displays table or object attributes.
ED[IT] [filename[.ext]]	Invokes the text editor for you to edit the contents of the SQL buffer or the specified file.
[EXIT|QUIT] [COMMIT|ROLLBACK]	Terminates SQL*Plus and COMMITs or ROLLBACKs the changes if specified.
GET filename[.ext]	Places the contents of the specified SQL file into the SQL buffer.
HO[ST] [command]	Temporary exits to OS or executes an OS command.
L[IST] [n]	Displays the nth line of the SQL statement in the SQL buffer. Current line will be displayed if n is ignored.
PROMPT [text]	Displays text on screen.
R[UN]	Executes the SQL statement in the buffer.
SAV[E] filename[.ext]	Saves the contents of SQL buffer to a file.
SET	Sets the SQL*Plus environment eg. SET AUTOCOMMIT ON.
SHO[W]	Shows SQL*Plus environment settings. eg. SHOW ALL.
SPO[OL] filename[.ext]	Saves query results to a text file.
STA[RT] filename[.ext]	Executes a SQL command file.

4. Using SQL to Manipulate Data

• Simplified syntax:

  create table tablename
     (columnname datatype [not null][default expr][column_constraint]
     {, columnname datatype [not null][default expr][column_constraint]}
     [table_constraint{, table_constraint}]);

• Examples:

  drop table dept;
  create table dept (
    deptno number(2) not null, 
    dname varchar2(20) not null, 
    primary key (deptno));
  
  drop table emp;
  create table emp (
    empno number(4) not null, 
    ename varchar2(25),
    job varchar2(9), 
    hiredate date,
    sal float check(sal >= 0.00),
    deptno number(2),
    primary key (empno),
    foreign key (deptno) references dept);
  

4.1.2. The CREATE TABLE AS Command

• Simplified syntax:

  create table tablename
     [(columnname format {, columnname format})] as
     select...;

• Task:

  Create a new table with all employees who now earn more that $50,000.

  create table overpaid as
     select * from emp where sal > 50000;
  commit;

  • overpaid created as a permanent table
  • * is shorthand for all fields

• To view the data definition of existing tables, you use the DESCRIBE command. For example:

  describe emp;

• You can use the ALTER TABLE command to change the table definition.

  alter table emp
  add (bonus number(5,2));

  alter table emp
  modify (deptno number(2) not null);

• Want to get a list of tables you have access to? Try the following command:

  select *
  from user_tables;

• Syntax:

  drop table tablename;

• Example:

  drop table overpaid;

ANSI SQL Datatype	Oracle Datatype	Description
CHARACTER(n), CHAR(n)	CHAR(n)	Fixed length character data of length n bytes. Maximum n is 2000. Default and minimum n is 1 byte. Values are padded with blanks to the specified length. Examples Storing "Joe" in a CHAR(10) datatype would result in Joe and seven blanks
CHARACTER VARYING(n), CHAR VARYING(n)	VARCHAR2(n)	Variable length character string having maximum length n bytes. Maximum n is 4000, and minimum is 1. You must specify n for a VARCHAR2
NUMERIC(p,s), DECIMAL(p,s)	NUMBER(p,s)	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Examples NUMBER(2) - Two digit before the decimal place and zero digits after NUMBER(4,2) - Two digit before the decimal place and two digits after NUMBER(6,-2) -Eight digits, with the last two digits always zero.
INTEGER, INT, SMALLINT	NUMBER(38)	Number with precision 38. This is the maximum digits allowed by the system with no digits after the decimal.
FLOAT(b), DOUBLE PRECISION, REAL	NUMBER	The FLOAT datatype is a floating point number with a binary precision b. This default precision for this datatype is 126 binary, or 38 decimal. The DOUBLE PRECISION datatype is a floating point number with binary precision 126. The REAL datatype is a floating point number with a binary precision of 63, or 18 decimal.
	DATE	Valid date range from January 1, 4712 BC to December 31, 4712 AD.
	LOB(size)	There are three LOB or Large OBject data types. LOB data types can contain up to 4 GB of data. CLOB - is used to store long variable-length character strings BLOB - is used to store binary large objects, such as bitmaps of audio or video images BFILE - is a database reference to an operating system file that is stored outside of Oracle Note Oracle7 used LONG or LONG RAW data types to store very long strings or large binary objects of up to 2 GB of data.
	ROWID	Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

4.2. Basic SQL DML Commands

• Syntax:

  select [all | distinct] expression {, expression}

  from tablename [corr_name]

  [where search_condition]

  [order by columnname [asc | desc] {,columnname [asc | desc]}]

• Examples:

  select empno, ename

  from emp;

  select emp.ename, emp.job, dept.dname

  from emp, dept

  where emp.deptno=dept.deptno;

  select a.ename, a.job, b.dname

  from emp a, dept b

  where a.deptno=b.deptno and a.sal > 10000;

• Syntax:

  insert into tablename [column {,column}]
  values (expression {,expression})

• Inserts new rows into the specified table
  • one-to-one correspondence between column and expression
  • unspecified columns default according to table definitions

• Examples:

  insert into dept (deptno,dname)
  values (1,'Marketing');

  or

  insert into dept
  values (1,'Marketing');

• Syntax:

  update tablename [corr_name]
  from tablename [corr_name] {, tablename [corr_name]}
  set columnname=expression {, columnname=expression}
  [where search_condition]

• Examples:

  update emp
  set sal = 23000
  where empno = 784;
  commit;
  

  Update emp e
  from dept d
  set sal = e.sal * 1.10
  where e.deptno = d.deptno
     and d.dname = 'Marketing';
  commit;
  

• Syntax:

  delete from tablename [corr_name]
  [where search_condition]

• If no search_condition is specified, all rows are removed from the table.

• Example (deleting one row):

  delete from emp
  where empno = 98;
  commit;
  

• Example (deleting all rows):

  delete from temp_emp;
  commit;
  

This section explains the various search conditions supported by Oracle SQL. Search conditions are the search criteria you specified to qualify the selection of data. They are used in where and having clauses in your SQL statements.

More than one search conditions can be used in a SQL statement with the logical operators and, or, and not together with parentheses to indicate clauses [e.g. (deptno=1 and sal>30000) or (deptno=2 and sal>25000)].

A search condition must include at least one of the following predicates:

Predicate	Functions and Examples
Comparison	Syntax: = equal to <> not equal to > greater than >= greater than or equal to < less than <= less than or equal to Example: select ename from emp where sal >= 50000;
Like	Syntax: columnname [not] like pattern where pattern is a string constant and pattern matching characters are: % (percent sign) matches 0 or more characters _ (underscore) matches exactly one character Example: select * from emp where ename like 'S%';
Between	Syntax: y [not] between x and z Example: select * from emp where sal between 25000 and 55000;
In	Syntax: y [not] in (x, ..., z) y [not] in (subquery) Examples: select * from emp where ename in ('Smith', 'Smyth'); select * from dept where deptno not in (select distinct deptno from emp);
Any-or-All	Syntax: = any|all(options|subquery) <> any|all(options|subquery) > any|all(options|subquery) >= any|all(options|subquery) < any|all(options|subquery) <= any|all(options|subquery) Examples: select * from emp where ename = any ('Smith', 'Smyth'); Note: This example is equivalent to the first example for the operator "in". select * from dept where deptno <> any (select distinct deptno from emp); Note: This example is equivalent to the second example for the operator "in".
Exists	Syntax: [not] exists (subquery) Example: select * from dept where not exists (select * from emp where deptno=dept.deptno); Note: This example is equivalent to the second example for the operator "in".
Is Null	Syntax: is [not] null Example: select * from emp where job is null;

4.2.6. The SQL Group Functions

For instant, you can obtain the average salary of all employees in the emp table using the avg function or count the number of employees using the count function. You may use the group by clause in conjunction with the group functions in the SQL select statement to divide data into groups.

Available functions:

Function Name	Description
count	Returns the number of rows in the query
sum	Returns sum of values of n
avg	Returns average value of n
max	Returns maximum value of expr
min	Returns minimum value of expr
stddev	Returns standard deviation of x, a number. Oracle8 calculates the standard deviation as the square root of the variance defined for the VARIANCE group function
variance	Returns variance of x, a number. Oracle8 calculates the variance of x using this formula: where: xi is one of the elements of x. n is the number of elements in the set x. If n is 1, the variance is defined to be 0.

Examples: