Getting Started With Oracle

---

• Overview
• Logging In to Oracle
• Changing Your Password
• Creating a Table
• Creating a Table With a Primary Key
• 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
• Help Facilities

---

Overview

---

Logging In to Oracle

Before using Oracle, execute the following line in your shell to set up the correct environment variables:

     source /afs/ir/class/cs145/all.env

You may wish to put this line in your shell initialization file instead (for example, .cshrc).

Now, you can log in to Oracle by typing:

     sqlplus <yourName>

You will be prompted for your password. This password is initially changemesoon and must be changed as soon as possible. For security reasons, we suggest that you not use your regular leland password, because as we shall see there are opportunities for this password to become visible under certain circumstances. After you enter the correct password, you should receive the prompt

     SQL>

---

Changing Your Password

     ALTER USER <yourName> IDENTIFIED BY <newPassword>;

Note that SQL is completely case-insensitive. Once you are in sqlplus, you can use capitals or not in keywords like ALTER; Even your password is case insensitive. We tend to capitalize keywords and not other things.

---

Creating a Table

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

     CREATE TABLE test (
         i int,
         s char(10)
     );

---

Creating a Table With a Primary Key

     CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);

     CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));

---

Inserting Tuples

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

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

---

Getting the Value of a Relation

     SELECT *
     FROM <tableName>;

     SELECT * FROM test;

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

---

Getting Rid of Your Tables

     DROP TABLE <tableName>;

     DROP TABLE test;

---

Getting Information About Your Database

     SELECT TABLE_NAME
     FROM USER_TABLES;

     SELECT *
     FROM USER_TABLES;

     DESCRIBE <tableName>;

---

Quitting sqlplus

     quit;

---

Executing SQL From a File

To run the file foo.sql, type:

     @foo

sqlplus assumes by default the file extension ".sql" if there is no extension. So you could have entered @foo.sql at the SQL> prompt, but if you wanted to execute the file bar.txt, you would have to enter @bar.txt.

You can also run a file at connection by using a special form on the Unix command line. The form of the command is:

     sqlplus <yourName>/<yourPassword> @<fileName>

     sqlplus sally/etaoinshrdlu @foo

NOTE: If you are getting an error of the form "Input truncated to 2 characters" when you try to run your file, try putting an empty line at the bottom of your .sql file. This seems to make the error go away.

---

Editing Commands in the Buffer

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.

LIST	lists the command buffer, and makes the last line in the buffer the "current" line
LIST n	prints line n of the command buffer, and makes line n the current line
LIST m n	prints lines m through n, and makes line n the current line
INPUT	enters a mode that allows you to input text following the current line; you must terminate the sequence of new lines with a pair of "returns"
CHANGE /old/new	replaces the text "old" by "new" in the current line
APPEND text	appends "text" to the end of the current line
DEL	deletes the current line

All of these commands may be executed by entering the first letter or any other prefix of the command except for the DEL command.

An alternative is to edit the file where your SQL is kept directly from sqlplus. If you say

     edit foo.sql

the file foo.sql will be passed to an editor of your choice. The default is vi. However, you may say

     DEFINE _EDITOR = "emacs"

if you prefer to use the emacs editor; other editor choices may be called for in the analogous way. In fact, if you would like to make emacs your default editor, there is a login file that you may create in the directory from which you call sqlplus. Put in the file called login.sql the above editor-defining command, or any other commands you would like executed every time you call sqlplus.

---

Recording Your Session

   script [ -a ] [ filename ] 

     exit

sqlplus provides the command spool to save query results to a file. At the SQL> prompt, you say:

     spool foo;

     spool off;

Finally, if you use Emacs, you can simply run sqlplus in a shell buffer and save the buffer to a file. To prevent your Oracle password from being echoed in the Emacs buffer, add the following lines to your .emacs file:

(setq-default
 comint-output-filter-functions
 '(comint-watch-for-password-prompt))
(setq
 comint-password-prompt-regexp
 "\\(\\([Oo]ld \\|[Nn]ew \\|^\\)[Pp]assword\\|Enter password\\):\\s *\\'")

---

Help Facilities

The output from help, and in general, the results of many SQL commands, can be too long to display on a screen. You can use

     set pause on;

     set pause off;