UIS Home   Search the UIS Website   A-Z index
UIS Homepage

Information Technology Services University of Illinois Springfield

Getting Started with Oracle

[ return to previous page]


To request an Oracle Account, one must be enrolled at Computer Science or Management Information System courses or a faculty, staff or researcher doing official business in the University. Supply the necessary information such as name, netid, date of birth, department, and status.


To log into the Oracle Server you need the Oracle Database client and Oracle account. This client is installed on uisacad.uis.edu and all PC’s in the computer labs on campus. If you are on a system with the oracle client installed on it you can connect by typing:

sqlplus <yourAccount>@oracle.uis.edu

then enter password when prompted

Here, sqlplus is Oracle’s generic SQL interface. <yourAccount> refers to your Oracle account. 

After you enter the account and password, the SQL prompt should appear as follows:

SQL>

You can also connect using this sqlplus<yourAccount>/<yourPassword>@oracle.uis.edu but this will expose your password.


Changing Your Password
In response to the SQL> prompt, type

ALTER USER <yourName> IDENTIFIED BY <newPassword>;

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

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.

In addition to the alter user command, you can use the password command in SQLPLUS to change your password.  When you enter the password command, you will prompted for the old and new passwords.


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; see Editing Commands in the Buffer.] An example table-creation command is:

CREATE TABLE test (

         id int,

         name char(10)

     );

This command creates a table named test with two attributes. The first, named id, is an integer, and the second, named name, is a character string of length (up to) 10.


Creating a Table With a Primary Key
To create a table that declares attribute a to be a primary key:

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


To create a table that declares the set of attributes (a,b,c) to be a primary key:

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

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 still there the next time you use the Oracle system.


Getting Information About Your Database
The system keeps information about your own database in certain system tables. The most important for now is USER_TABLES. You can recall the names of your tables by issuing the query:

SELECT TABLE_NAME

     FROM USER_TABLES;

More information about your tables is available from USER_TABLES. To see all the attributes of USER_TABLES, try:

SELECT *

     FROM USER_TABLES;

It is also possible to recall the attributes of a table once you know its name. Issue the command:

DESCRIBE <tableName>;

to learn about the attributes of relation <tableName>.


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. 

The safest way to do it, is for a user to log in to sqlplus as usual. Then type, in response to the SQL> prompt:

@foo.sql

and the file foo.sql‘s contents will be executed.

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
If you end a command without a semicolon, but with an empty new line, the command goes into a buffer. You may execute the command in the buffer by either the command RUN or 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.

L    lists the command buffer, and makes the
last line in the buffer the “current” line   
L
n    prints line n of the command buffer,
and makes line n the current line   
L
m n    prints lines m through n,
and makes line n the current line   
I    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”   
C /old/new    replaces the text “old
by “new
in the current line   
A text    appends “text
to the end of the current line   
DEL    deletes the current line   

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.  
  Alternatively you can put the the following command: 
setenv EDITOR emacs

in your .login file in  your home directory.


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 ]

The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type

exit

For more information about script, check out its man page.

Alternatively, you can use the spool command within sqlplus. At the SQL> prompt, you say:

spool foo;

and a file called foo.lst will appear in your current directory and will hold everything typed, until you exit sqlplus or type:

spool off;

Finally, if you use Emacs, you can simply run sqlplus in a shell buffer and save the buffer to a file. 


Help Facilities
In response to the SQL> prompt, type help followed by a keyword or sequence of keywords that are used together in SQL commands (e.g., help select and help create table both work). If you are lucky, the keyword or phrase will be among those for which help exists, and you will get a (somewhat) helpful message, usually ending in an example or two.

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;

to activate the paging feature.  When this feature is activated, output will pause at the end of each screen until you hit the “return” key. To turn this feature off, use

set pause off;

This document was written originally by Jeff Ullman.  Modified by Tulio Llosa 03/02/2001


Creating a Table With a Primary Key
To create a table that declares attribute a to be a primary key:

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

To create a table that declares the set of attributes (a,b,c) to be a primary key: 
     CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));

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 ('John Doe','uistud1s '1985-01-01') into relation to the student table by:

INSERT INTO student VALUES('John Doe','uistud1s '1985-01-01');

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 student;

produces the result

+----------+---------+------------+
| name      | netid      | birth          |
+----------+---------+------------+
| John Doe | uistud1   | 1985-01-01 |
+----------+---------+------------+
1 row in set (0.00 sec)              

To delete all the contents of your table, type:

delete from <TableName>;

Getting Rid of Your Tables To remove a table from your database, execute

DROP TABLE <tableName>;

We suggest you execute

DROP TABLE student;

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


Getting Information About Your Database
The system keeps information about your own database in certain system tables. The most important for now is USER_TABLES. You can recall the names of your tables by issuing the query:

SELECT <TableColumn> FROM <TableName>;

More information about your tables is available from the UserTable. To see all the attributes of the user’s table, try:

SELECT * FROM <TableName>;

It is also possible to recall the attributes of a table once you know its name. Issue the command:

DESCRIBE <TableName>;

to learn about the attributes of relation <TableName>.  


Quitting mysql. 
To leave MySQL, type

quit;  or
     \q

in response to the mysql> prompt.

This will bring you back to the shell prompt.


This document was written originally for Prof. Jeff Ullman’s CS145 class in Autumn, 1997; revised by Tulio Llosa.

[ return to top]

Text Only Options

Top of page


Text Only Options

Open the original version of this page.

Usablenet Assistive is a UsableNet product. Usablenet Assistive Main Page.