Basic Stuff

ORACLE 8i is installed in the undergraduate lab in McBryde Hall (Room 118). To use it, you first need an account on the lab machines. Please contact the lab administrator for details. In addition to the regular account, you need a "database" account that you will use to connect to the ORACLE database server. We have already created these accounts for you. These accounts have names like "user1", "user2" etc. The passwords will be handed out in class. You cannot change the account name, but the passwords *should be* changed. So, as soon as you receive the account information, login to change the initial password (instructions follow below).

Open ORACLE8i on a Win2000 Machine

Oracle 8i is installed on each Win2000 machine in the CS lab in McB 118. To open Oracle, log on to your account. Choose "Start" -> "Programs" -> "Course Software" -> "Oracle 8i" -> "SQL Plus". When you enter "SQL Plus", you will see a dialog box. Enter your groupname as "User Name" (user1, user2 etc.), password as "Password", and "cs4604" as "Host String".  Then, the system will force you to change the password.

If you still need to change the password later, you can use the following command.

alter user "user1" identified by "hello";

The command as above changes the password for user "user1" to "hello".

Oracle Documentation

You can access the Oracle Documentation online. Just click http://www.csee.umbc.edu/help/oracle8/server.815/index.htm, and then select Oracle8i SQL Reference. Notice that this manual is for a slightly older version of ORACLE, but it shouldn't matter.

Using SQLPlus

CREATE TABLE STUDENT (
SSN NUMBER(9) NOT NULL PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL,
GENDER CHAR(1),  
DEPARTMENT VARCHAR2(25)  
);
INSERT INTO STUDENT VALUES(
123456789, 'CHRISTINA', 'F', 'MARKETING'
);
SELECT * FROM STUDENT;
SELECT * FROM STUDENT WHERE GENDER = 'F';
DROP TABLE STUDENT;

Exiting SQLPlus

Type "quit;" at the prompt.

Recording your Session

Use the "spool" command within sqlplus. At the SQL> prompt, type: 
    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;

Using the Bulk Loader in ORACLE

The ORACLE SQL* Loader is a facility that allows you to populate database tables from flat files. To use this facility, you need to prepare (i) your flat file(s) (containing data), and (ii) a control file that tells ORACLE how to "map" fields of the flat file to columns of an RDBMS table. Furthermore, the data can also be appended at the end of the control file, making the process much simpler. Most probably, you will prepare one control file for each table that you want to bulk load to. Notice that preparing the data is part of your assignment. There are no "tools" for this. You either create them from some C program (example) that generates the data and outputs it into a file, or if you are lucky, you might find a web page that already has the data that you need with minimal text processing.

Here are some hints and steps to help in bulk loading.
  1. Preparing the Control File: You can create one using a text editor such as notepad and then save it with a ".ctl" extension. You can click http://www.csee.umbc.edu/help/oracle8/server.815/a67792/toc.htm, and then select SQL*Loader Case Studies. There are a lot of control file samples for your reference.

    Important: Make sure that you have a carriage return after the last tuple in your data file. Otherwise the loader doesn't like it and fails to insert the last tuple. After you have your control file and data file ready, login to ORACLE, create the table with normal SQL DDL commands, and then invoke the SQL bulk loader.

  2. Invoking the Loader: Goto the DOS prompt and cd to "C:\oracle\ora81\bin". From there, invoke the SQL LOADER by typing the following:

    sqlldr userid=pid/password@hoststring control=drive:\path\controlfilename.ctl log=drive:\path\logfilename.log
    The hoststring in our case is "cs4604". Yes, the password has to be typed on the command line! :-) (We haven't found a way around this). If the command executed successfully, go back to ORACLE and do a "SELECT *" on that table and make sure that the data is indeed there! You can also get a list of various options by just typing "sqlldr" on the DOS prompt.

  3. Committing Transactions: We are getting ahead of the lectures here, but this is needed to understand how ORACLE does things.

    • Notice that changes made to your database via the insert, delete, update commands can be reversed if necessary. Towards this end, ORACLE SQL provides two commands: commit, and rollback. The changes you make on the command line prompt (using insert, delete/update) can be made permanent using the commit statement. Thus, for example if you delete everything from the SQL prompt and then do a SELECT, you might find the table "empty". However, if you try to bulk load, the loader will not allow this. The reason is because what you are seeing on the screen is not "permanent". To make it permanent, type commit and try bulk loading again.

      Similarly, the changes made to the database since the last commit can be reversed by using the rollback statement.

    • Also notice that some commands force a commit even without the user explicitly issuing a commit statement. This is typical of ORACLE DDL commands (and also the bulk loader).

    • When the user exits an SQL session, the system automatically commits all the changes.