Guides for

• New Students
• Students
• Faculty
• Staff
• IT Staff

Search the UBIT site

Questions and comments about this should be sent to: cit-webmaster@buffalo.edu
Privacy Accessibility Statement
Last updated:
Copyright © 2006
All Rights Reserved.

Documentation

Oracle

Introduction

Oracle Relational Database Management System (RDBMS) is available on UBUnix, the central UNIX system at the University at Buffalo. The RDBMS stores data in columns and rows, with each data item of information in its own slot, called a field, at the intersection of a row and a column.

Setup

You need to obtain a special account to use Oracle by sending a message to ascit-dba@buffalo.edu. Once you have obtained your Oracle account and have logged into UNIX, you need to set your system search path by initializing the setup file by entering:
source /util6/bin/coraenv

SQL Language

The relational database language that is used to create, store, modify, retrieve, and manage information in Oracle is called SQL (Structural Query Language), pronounced "sequel."

Starting Oracle

To start Oracle, enter sqlplus.

You will see a product heading with a version and release notification; then you will be queried for your Oracle account name and password. If you have entered your account name and password successfully, you will see a completion message, and then see the prompt:
SQL>

All commands in Oracle are issued at this prompt.

Changing Your Oracle Password

Your Oracle password may contain any characters from the database character set and must be from eight to 30 characters in length. It must start with an alphabetic character and is not case-sensitive. To change your Oracle password, use the alter command. For example, if your username is myname and you want to change your password to secret#21, you would enter the following at the SQL> prompt:
alter user myname identified by secret#21;

Input

SQL commands are not case-sensitive. A semicolon followed by Return or Enter terminates a command line. When a command extends over several lines, you can press Return or Enter at the end of each line. SQL responds with a new line number rather than the SQL prompt until you terminate the command.

Saving Your Session

To save the commands and responses during your Oracle session, enter spool fname at the SQL prompt, where fname is the name of the file into which your session is saved. To end the session log, enter spool out. The file will be in the directory from which you have started Oracle.

Table Description

Oracle files are considered flat and are called tables. A table is a series of columns and rows, each field of which contains a single value. Information can be retrieved selectively from one table. When a set of data is repeated in multiple tables you can choose any data by matching comparable fields; thus, giving meaning to the word relational in the expression RDBMS.

Creating Tables

To create a table named dept, for example, use the create table command:
create table dept (Deptnum number(2),
Deptname char(14), Locate char(13));

In the parentheses following the name of the table, dept is a list of the column names and their characteristics, seperated by commas. In this example, the first column is Deptnum and it has a numerical value with two digits at most. Remember to terminate the command by entering a semicolon (;) then pressing Return or Enter.

Refer to Oracle Help to review restrictions on data names and data types.

Inserting a Row of Data into a Table

To insert a new row of data into the example table dept, use the insert command:
insert into dept
values (32, 'Budget','Crosby Hall');

On the screen you will see just one line:
1 row created.

Selecting Data from a Table

To check your record entry you can use the select command:
select * from dept where deptname='Budget';

You will see:

The query asks the database to return all rows that fulfill the request of the conditional where clause by using the wildcard character, *. Notice that the search for data is case-sensitive.

Generally, you can retrieve data from tables by selecting specific fields in one or more tables that can contain constricting conditional or Boolean phrases to satisfy a particular request. In the following example, fldn is a field and tbln is a table:
select fld1, fld3, fld6, fldn
from tbl5, tbl8, tbln
where fld6 = 'value' or fldn > num;

All character data fields are referenced in single quotes, whereas numeric values can be compared directly.

Changing Data in a Row

Information can be changed in an existing table with the update command, and the search for unique row identifiers to insure proper application of the required corrections. Using previous symbols in this document, you could have a table update command that looks like:
update tbl5
set fld6 = 'new value', fldn = new_num
where fld3 = 'unique value';

Deleting a Row from a Table

A data record (row) can be dropped from a table by using the delete command:
delete from tbl5
where fld6 = 'value' and fldn = num;

Depending on the impact of the conditional statements in the where clause, Oracle answers with a message indicating how many rows were deleted from the table.

Using a Relational Database to Join Two or More Tables

It is often necessary to get information from two or more tables at the same time. This can be done by using a join, in which the information is retrieved by specifying the proper conditional statement.

Example:
Employees table fields: EmpLast, EmpFirst, EmpPhone, EmpDeptnum, EmpDivision.
Dept table fields: Deptnum, Deptname, Division, Location
select EmpLast, EmpFirst, EmpPhone, Deptname
from Employees, Dept
where EmpDivision = Division and EmpDeptnum = Deptnum;

If the tables being joined do not have unique field names then the field name must be prefixed by the table name to remove any ambiguity. In the following example, both the Employees and Dept tables have a Deptnum and a Division field. When performing the join, the table names must be added as prefixes to the fields having the same name.

Example:
Employees table fields: Last, First, Phone, Deptnum, Division
Dept table fields: Deptnum, Deptname, Division, Location
select Last, First, Phone, Dept.Deptnum, Deptname
from Employees, Dept
where Employees.Division = Dept.Division and Employees.Deptnum = Dept.Deptnum;

Exiting Your Oracle Session

When you are through with your SQLPLUS session, you can leave the program by typing quit.

After you issue this command:

• Any changes you have made to your Oracle RDBMS tables become permanent.
• The SQLPLUS program closes down and leaves you at the operating system prompt.

Additional Information

Extensive online help is available by typing help at the SQL prompt.

Document Information