• Overview
• Logging In to Oracle
• Changing Your Password
• Creating a Table
• Inserting Tuples
• Getting the Value of a Relation
• Getting Rid of Your Tables
• Getting Information About Your Database
• Quitting sqlplus
• Executing SQL From a File
• Editing Commands in the Buffer
• Recording Your Session

Overview

We shall be using an Oracle database system to implement our PDA this quarter. Everyone should log in ASAP to verify your account works and change your password.

Logging In to Oracle

You MUST be logged onto yale.

Log in to Oracle by typing:

     sqlplus <yourname>

Here, sqlplus is the name of the generic SQL interface. <yourname> refers to your unix login.

You will be prompted for your password. This password is provided on your "Computer Science Oracle Account" sheet. If you are logging into Oracle for the first time you will be prompted to change your password. We suggest that you NOT use your regular unix password, because as we shall see, there are opportunities for the password to become visible under certain circumstances. When you enter your password, you should receive the prompt

     SQL>

Changing Your Password

If you want to change your password again, In response to the SQL> prompt, type

     alter user <yourname> identified by <newPassword>;

where <yourname&> is again your unix login, and <newPassword> is the password you would like to use in the future. This command, like all other SQL command, should be terminated with a semicolon.

Creating a Table

In sqlplus we can execute any SQL command. One simple type of command creates a table (relation). The form is

     CREATE TABLE <tableName> (
         <list of attributes and their types>
     );

You may enter text on one line or on several lines. If your command runs over several lines, you will be prompted with line numbers until you type the semicolon that ends any command. Warning: an empty line terminates the command but does not execute it. An example table-creation command is:

     create table test (
         i int,
         s char(10)
     );

Note that SQL is case insensitive, so CREATE TABLE and create table are the same. This command creates a table named test with two attributes. The first, named i, is an integer, and the second, named s, is a character string of length (up to) 10.

Inserting Tuples

Having created a table, we can insert tuples into it. The simplest way to insert is with the insert command:

     INSERT INTO <tableName>
         VALUES( <list of values for attributes, in order> );

For instance, we can insert the tuple (10, ``foobar'') into relation test by

     INSERT INTO test VALUES(10, 'foobar');

Getting the Value of a Relation

We can see the tuples in a relation with the command:

     SELECT *
     FROM <tableName> ;

For instance, after the above create and insert statements, the command

     SELECT * FROM test;

produces the result

              I S
     ---------- ----------
             10 foobar

Getting Rid of Your Tables

To remove a table from your database, execute

     DROP TABLE <tableName> ;

We suggest you execute

     DROP TABLE test;

after trying out this sequence of commands to avoid leaving a lot of garbage around that will be there the next time you use the Oracle system.

Getting Information About Your Database

     SELECT TABLE_NAME
     FROM USER_TABLES;

     SELECT *
     FROM USER_TABLES;

     DESCRIBE MyTable;

Quitting sqlplus

To leave sqlplus, type

     quit;

in response to the SQL> prompt.

Executing SQL From a File

Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed. There are two ways to do so.

The first is to provide your password and the name of the file in the command line with which you open sqlplus. The form of the command is:

     sqlplus <yourname>/<yourpassword> @<filename>

For instance, if user sally, whose password is etaoinshrdlu, wishes to execute the file foo, then she would say:

     sqlplus sally/etaoinshrdlu @foo

Notice that this mode presents a risk that Sally's password will be discovered, so it should be used carefully.

A second, safer way, is for Sally to log in to sqlplus as usual. She then says, in response to the SQL> prompt:

     @foo

and the file foo's contents will be executed.

Editing Commands in the Buffer

I highly recommend you just edit a file in one window and then execute your sql from the file as above. The editing interface to plsql is horrid, but if you want:

If you end a command without a semicolon, but by hitting ``return'' twice, the command goes into a buffer. You may execute the command in the buffer by either the command RUN or with a single slash (/).

You may also edit the command in the buffer before you execute it. Here are some useful editing commands. They are shown in upper case but may be either upper or lower.

1. L (list the command).
2. L n (print line n of the command; also makes this line the ``current'' line).
3. L n , (print lines n through m).
4. I (input text following current line). You must terminate the sequence of new lines with a pair of ``returns.''
5. C /old/new (replace the text ``old'' by ``new'' in the current line).
6. A text (append ``text'' to the end of the current line).
7. DEL (delete the current line).

Recording Your Session

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is

script [ -a ] [ filename ]

exit

Alternatively, you can use the spool command within sqlplus. You say:

     spool foo;

     spool off;