Step by Step to use Oracle through SQLPlus in UMBC

Prepared by Fei Xu

This tutorial will give basic information about how to use Oracle through SQLPlus in UMBC. Absolutely, you need a GL account, in CMSC461 class to get access priority of Oracle. If you are connecting the GL server from your own pc, you also need a ssh client. I assume the readers know how to connect to the GL machine, know basic ideas of Database and SQL statements.

Step 0: Preparation

Now we begin to our tutorial. I will use these naïve tables as my example.

Student (Studentid, Studentname)

Course (Courseid,Coursename Teachername)

Report (Studentid, Courseid,Grade)

The database has three tables. Student table contains the student information. Course table contains the course information. Report table contains what courses the student chooses and their grades. In the following step, I will show you how to build tables, add, delete and update records, execute queries through SQLPlus.

Now try to draw the E-R diagram for this naïve example.

Step 1: Connecting to Oracle and Exit SQLPlus

Use your GL account to connect to the GL server. (An ssh client can be downloaded from myUMBC) I assume you are already connecting to the GL server in the following steps.

At the prompt, type:

source /usr/local/oracle/oracle.cshrc (if you are using tcsh or csh)

source /usr/local/oracle/oracle.profile (if you are using bash)

Then type:

sqlplus $USER

You can use your gl account’s name to replace $USER or just keep it. If you see the symble below:

SQL>

You’ve successfully connect to the Oracle server.

If you want to exit SQLPlus, simply type in

Exit

You can then leave SQLPlus and exit to the shell you login.

Step 2: Getting Help from SQLPlus

SQLPlus is an interactive program, which means if you forget something, you can ask SQLPlus to tell you. The “help” command is very useful.

HELP displays information on the commands and conventions of SQL*Plus, SQL, and PL/SQL. Type "help", a space, all or part of any topic, and then press Enter. Since it is an easy command, here I do not show any display information for it.

Assume you forget how to create a table, try “help create table”. See what the SQLPlus displays.

If you even forget the name of command that you want to use, type “help topics”. SQLplus will show you all topics that it can provide help.

Step 3: Check what tables your have built in Oracle

Oracle maintains some system tables, when user creates an object, it automatically update these system tables. For detailed information, please check the Oracle’s manual. These tables give us a way to check what we have built in the system. It is always important to know what exists in the Database before creating new objects.

System.tab is the table that contains all objects’ name under the current user built before. So type in:

Select * from system.tab;

will list all objects (tables, views…) name.

SQL> select * from system.tab;

no rows selected

Remember: The semicolon at the end of the command is required. The difference between a sqlplus command and a sql statement is that a sql statement should finish with a semicolon.

Since I have not created anything, the returns result gives me nothing.

After getting the table’s name, “describe” command can show the definition of the table. The format is “describe objectname”.

Try “describe system.tab”

Step 4: Creating tables

There are two ways to input SQL statements in SQLPlus. One is interactively input directly. The other is using a plain text file. Now I introduce the first way. The second way will be discussed later.

In SQLplus, input:

Create table student

(studentid char(4), studentname char(30), primary key (studentid));

The SQLPlus will show like this:

SQL> Create table student

(studentid char(4), studentname char(30), primary key (studentid));

Table created.

Now, try to build the course table and the report table. Using the sql statements below:

Create table course

(courseid char(4), coursename char(30), teachername char(30), primary key (courseid));

Create table report

(studentid char(4), courseid char(4), grade char(1), primary key (studentid,courseid) );

It is a good chance to practice Step 3 now.

Check what you have built in Oracle and show the course table’s definition.

To drop a table, simply type “ drop tablename ;”. I do not get examples here. Readers shall try themselves.

Step 5: dealing with data

The big deal is to deal with data. Now we have three tables, we want to add some data into these tables. Remember three statements”insert, delete and update”.In this step, I will show examples for each sql statements. And readers need to finish exercises I provide.

I’d like to add four students and four courses

Student :

0001 Micro

0002 Soft

0003 IMD

0004 Antel

Course

0001 Database Microsoft

0002 OS Redhat

0003 Architecture Sun

0004 AI IBM

grade

0001 0001 A

0001 0002 F

0002 0003 D

0002 0004 B

0003 0002 A

0003 0003 C

0004 0002 A

0004 0003 B

Now, type

Insert into student values ( ‘0001’, ‘Micro’);

SQLPlus will show

SQL> Insert into student values ( '0001', 'Micro');

1 row created.

Add other rows to the tables

Another way to insert records is to insert records with a query sub statement, please see the textbook for more information.

The format of delete is “ delete from tablename (where sub-statement)”. The “where sub-statement” is not necessary but powerful.

To delete all records in the student table, just input “delete from student”. However we seldom use this statement except if users want to alter table and thus delete all records first.

Example: to delete the student with student number 0001

Input:

Delete from student where studentid = ‘0001’;

SQLPlus will show

SQL> Delete from student where studentid = '0001';

1 row deleted.

Now do the following exercise:

1. delete all records in table grade

2. delete all courses that taught by Redhat

3. delete all records which contains course id “0003”

4. Restore the database after doing the exercise.

The format of update is “ update tablename set columnname = expression (where sub-statement)”. The “where sub-statement” is not necessary. If no “where sub-statement” exists, the update command will update all records and set new value to all column. Suppose teacher “Microsoft” want to teach all courses and get rid of all other teachers, type

Update course set teachername = ‘Microsoft’;

SQLPlus will show

SQL> Update course set teachername = 'Microsoft';

4 rows updated.

Try “select * from course;”

SQL> select * from course;

COUR COURSENAME TEACHERNAME

---- ------------------------------ ------------------------------

0001 database Microsoft

0002 OS Microsoft

0003 Architecture Microsoft

0004 AI Microsoft

Microsoft now is so happy that he teaches all courses.

Suppose you are teacher Sun. Now you want to get back what you teach.

Try “update course set teachername=’Sun’ where courseid=’0003’; “

Now, it is your turn to correct other teachers’ courses. Do the exercises to help IBM and Redhat correct their courses.

Step 6: Queries

Queries are done by the “select” statement. This is an important and complicated statement. I cannot totally show the statement here. Please refer to the textbook for more information. The aim of this step is to show how to input queries interactively.

Example1: Get all records from course table

Type:

select * from course;

SQLPlus will show

SQL> select * from course;

COUR COURSENAME TEACHERNAME

---- ------------------------------ ------------------------------

0001 database Microsoft

0002 OS Redhat

0003 Architecture Sun

0004 AI IBM

Try “select student.studentname, report.grade from student, report where student.studentid=report.studentid;”. Explain what this query searches.

Now finish these queries:

1. find the student’s grade whose name is Micro

2. find the teacher’s name whose course is AI

3. Find the students’ names who choose the course OS.

4. Find the teacher’s name that at least one of the students get an A in his course.

Step 7: Using Text File.

You could prepare all your SQL statements such as creation of relations and queries, into a text file and execute the SQL commands in this file when you are in SQLPLUS. Use your favorite editor to edit sql statements. If you are using a word processor such as Microsoft Word, you need to save the file as plain text. Now transfer the file to your GL home directory. The “start,@ and @@” commands can load a sql file and execute. Generally, we can use command “start” only.

Before starting using the text file ,type in “help start”.

The SQLPlus shows:

SQL> help start

START

-----

START executes the contents of a command file. The @ ("at" sign)

and @@ (double "at" sign) commands work similarly to the START

command, but do not enable the passing of values to parameters.

STA[RT] file_name[.ext] [arg ...]

For detailed information on this command, see the SQL*Plus User's

Guide and Reference.

Edit the following file and save it as “example.sql”

Drop table course;

Drop table student;

Drop table report;

Create table student

(studentid char(4), studentname char(30), primary key (studentid));

Create table course

(courseid char(4), coursename char(30), teachername char(30), primary key (courseid));

Create table report

(studentid char(4), courseid char(4), grade char(1), primary key (studentid,courseid) );

Insert into student values ( ‘0001’, ‘Micro’);

Insert into student values ( ‘0002’, ‘Soft’);

Insert into student values ( ‘0003’, ‘IMD’);

Insert into student values ( ‘0004’, ‘ANTEL’);

select * from student;

Suppose now the file is in the user’s directory. In SQLPlus: type in “start example.sql”

The SQLPlus will execute each command in that file.

SQL> start example.sql

Table dropped.

Table created.

1 row created.

STUD STUDENTNAME

---- ------------------------------

0001 Micro

0002 Soft

0003 IMD

0004 ANTEL

Now type your own file and try.

Step 8: Save outputs

The command “spool” can save all that displays in the screen, including what you input and the system returns back.

Type “spool filename” to start output

Type “spool off” to stop output.

Now do the exercise to save the output to a file called my.log

However sometimes we only want to save the sql statement we input. We do not want to save the return information.

The command “save filename” will save the last sql statement you input into the new file. If the file already exists, the old one will be deleted.

The command “save filename app” will save the last sql statement to the end of the existing file.

Now redo the exercise of step 6. First try to save four queries into four files. Then try to save four queries into one file

Step 9: Commit and Rollback

You should execute the commit command before you change your database. When you are satisfied with your work, you could save it in the database permanently by executing the commit command, or you could discard all changes you made since the last commit command by typing rollback command.

Save all changes in the database:

SQL>commit

Discard all changes made since the last commit:

SQL>rollback

Appendix A: Connect to Oracle 9 using SQLPlus (From ken clingenpeel’s email, thanks Ken)

Simply copy the oracle.profile from /usr/local/oracle to the home directory and replace 8.1.5 with 9.2.0. Then sourced this edited file, and you can use oracle 9.2.0.1.0 when loggin in remotely.

Appendix B: Code to Connect to UMBC’s Oracle and Execute SQL Statements using JDBC

import java.sql.*;

public class JdbcSample {

public static void main(String[] args) {

String userLogin = null, userPasswd = null;

if (args.length < 2) {

System.err.println("usage :: java Test <username> <passwd>");

System.exit(1);

}

else {

userLogin = args[0];

userPasswd = args[1];

}

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

}

catch(ClassNotFoundException ex) {

ex.printStackTrace(System.err);

System.exit(1);

}

String url = "jdbc:oracle:thin:@oracle.gl.umbc.edu:1521:GL";

Connection con = null;

try {

con = DriverManager.getConnection(url, userLogin, userPasswd);

System.out.println("Connected to Oracle.");

}

catch (SQLException se ){

System.out.println("Unable to connect to Oracle.");

se.printStackTrace();

System.exit(1);

}

System.out.println("Tables for user " + userLogin);

try {

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("select table_name from user_tables");

while ( rs.next() ) {

String name = rs.getString(1);

System.out.println(name);

}

stmt.close();

}

catch (SQLException ex ){

System.out.println("Error while executing query.");

ex.printStackTrace(System.err);

System.exit(1);

}