Oracle at GWU

Introduction

This tutorial assumes you already have an Oracle account. Currently, Oracle runs on the machine delphi and does not run in remote-client mode, which means:

You need to log into delphi to do any Oracle work.

Occasionally, you need to be able to display a window from delphi onto your own workstation.

Note: your Oracle password is different from your Unix password. The rules that apply to your system password apply to your Oracle password: You are NOT to release this password to anyone else and should be careful to safeguard your password.

As an Oracle user you will be able to create and delete relations. You are welcome to try different commands. However, please don't create gobs of data. The disk space allocated to all students is limited. If you are using excessive space beyond the course requirements, your account may be dropped.

Step 1: Learning how to bring up a window from `delphi`

Use this set of instructions to learn how to set displays.

NOTE: for most Oracle work, you will not need to display windows remotely. However, you will need to if you are running Forms or Java.

Step 2: Setting up PATH variables

Login into delphi with the UNIX account assigned to you from anywhere (you don't need X-windows at this time). Use your system password for the login.

You will need to make changes to your PATH variable.

tcsh

.cshrc

Add the following pathnames to your $path variable in the following order:


       /usr/ccs/bin
       /opt/SUNWspro/bin
       /home/dba/app/oracle/product/7.3.2/bin

You need to MAKE SURE that the /opt/SUNWspro/bin path appears BEFORE /usr/ucb in the overall sequence of paths. If you don't have /usr/ucb in your paths then ignore the previous sentence.

If none of the above makes sense to you, look at your .cshrc. It should have a statement like:


      set $path (/usr/bin /usr/lang /usr/etc /usr/ucb)

What you need to do is simply add the paths above (there's no problem in making the statement really long):


      set $path (/usr/bin /usr/lang /usr/etc /usr/ccs/bin /opt/SUNWspro/bin /home/dba/app/oracle/product/7.3.2/bin)

Add the following statements to your .cshrc file (anywhere in the file):


      setenv ORACLE_BASE /usr/delphi1/app/oracle
      setenv ORACLE_HOME $ORACLE_BASE/product/7.3.2      
      setenv ORACLE_TERM sun5
      setenv ORACLE_SID orc2
      setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/openwin/lib:/usr/dt/lib:.

NOTE: make sure you don't have an extra '/' by accident. For example, the following simple mistake causes major problems:


      setenv ORACLE_BASE /usr/delphi1/app/oracle/

To enforce these changes, you can log out and log back in or type:
```
     % source .cshrc
       
```
at the Unix command line.
Verify the changes by examining your path variable by typing:
```
     % setenv
       
```
You should see the changes reflected in the PATH variable, and every time you login, you should have all such changes ready.
If you like, you can copy over my .cshrc file and copy over all my path statements. However, be warned that you might have to add back all the paths you originally had (e.g., the simple path "." that indicates the current directory). ALSO: if you copy over my file, make sure that you replace my username with your username! Otherwise a lot of things may not work correctly, including receiving mail.

If you are using K-shell, you need to modify your .profile file accordingly so that it contains:


ulimit -n 128
export ORACLE_BASE=/usr/delphi1/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/7.3.2
export ORACLE_TERM=sun5
export ORACLE_SID=orc2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib:/usr/dt/lib
export PATH=.:$ORACLE_HOME/bin:/opt/bin:/bin:/usr/bin:/usr/ccs/bin:/opt/gnu/bin:/usr/local/bin:/opt/SUNWspro/bin:/usr/ucb:/usr/delphi1/oracle:/gwu:$PATH
ORAENV_ASK=NO
. /opt/bin/oraenv

NOTE: This path stuff needs to be done only ONCE.

Copy the file prefs.ora from the oracle user to your home directory:


     % cd
     % cp ~oracle/prefs.ora .

Now you should be all set to begin working with Oracle.

Step 3: Log in

You will be logging into delphi EVERYTIME you use Oracle. Most likely you will use telnet or rlogin from another machine.

There are two modes in which you will use Oracle:

Command-line mode. Here, you don't need X-windows. Simply telnet to delphi from wherever you are. For example, if you're on parrot:
```
	  parrot% telnet delphi
	 
```
X-mode. In this case, you are interested in popping up Oracle windows (such as when working with Oracle Forms or reading the on-line documentation). Then, you need to follow the instructions for bringing up a window (shown earlier).

Step 4: Read Online Oracle Documentation

(Forthcoming)

Step 5: SQL interpreter (SQLPLUS)

To try out SQL queries and statements, you will use a tool called sqlplus. Using this tool does not require X-windows. It is a simple command-line interpreter.

Type sqlplus at the command line:


    delphi% sqlplus

You will be prompted for a password (your Oracle Password, which is different from your UNIX password).

Once in sqlplus you will get the prompt


    SQL>

To exit, type quit.

To execute an SQL statement, simply type it in and end it with a semi-colon and a carriage return. For example:


    SQL> select * from simha.accounts;

This selects all tuples from the table accounts owned by user simha. Alternatively, you can place SQL statements (as many as you like) in a file and indicate the file name. For example, suppose the Unix file test.sql contains the line select * from accounts; Then


    SQL> @test.sql

causes the statements to be executed. Type help to get help within SQLPlus.

To execute a shell command from sqlplus, use ! before the Unix command. For example:


    SQL>!ls

lists the current directory contents. You can do without this feature, but it helps you avoid going in and out of SQLPlus.

Problems?

The very first thing to do if you have a problem is to make sure Oracle is actually running. Sometimes, the machine is taken down for maintenance, in which case somebody has to start up Oracle again. To check, list the processes running:


    delphi% ps -uax | more

Then look for Oracle processes that end in _osid. If you see at least two, then Oracle is running.

Second, make sure your paths and environment variables have been set up.

If nothing happens when you type sqlplus, then it means you are unable to access the binary (i.e., the /bin directory in the $ORACLE_HOME directory). Check your paths.

If you get sqlplus to run, but are are unable to get your password to work, something may be wrong with your account or your password is incorrect.