Using Oracle 8 on the CPS Suns

    Accessing your Oracle 8 account
    Basic operations
    Using the bulk loader
    Getting help online
    Books

Accessing your Oracle 8 account

  1. Connect to one of the Sun workstations in AN 131 using ssh. See here for instructions on how to do this.

  2. To start Oracle, enter the following command line at the prompt for input: 
       sqlplus <oracleid>@udorcl
    where <oracleid> is the string oracle followed by the number of your account, e.g., if your <logname> is cps430-n1.05 then your <oracleid> is oracle5.

  3. Enter your Oracle password (initially set to <oracleid> and case-sensitive).

  4. Change your password, enter the following command: 
    
   SQL> passw
    After entering the command, the system will prompt you for your old password and then you must enter your new password twice.

Basic operations

    Listing all tables: SQL> SELECT * FROM tab;

    To execute several SQL commands in batch:

    1. prepare a text file containing the SQL commands you desire to run in batch
    2. connect into SQL*Plus and run the batch of commands as follows (assuming that you name the text file script.sql): 
      SQL> START script.sql;
    To output results to a text file:

    You can log to a text file the output of SQL commands you execute. 
    SQL> SPOOL <filename>;
    To end logging, enter the following command: 
    SQL> SPOOL OFF;
    Comments: everything after -- on a line is ignored 
    -- query for non-null student names
SELECT *
FROM Students
WHERE NAME IS NOT NULL -- those with a name
    Help within the system: SQL> HELP [topic]

    For example, 
    SQL> help passw

 PASSWORD
 --------

 Allows you to change a password without echoing the password
 on an input device.

 PASSW[ORD] [username]

 For detailed information on this command, see the SQL*Plus User's
 Guide and Reference.


SQL>

Using the bulk loader

Adapted from notes developed by the Stanford University Database Group.

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) a control file that tells Oracle how to `map' fields of that flat file to columns of an RDBMS table and (ii) your data file(s). Data can also be appended at the end of the control file, making the process simpler.
  1. Preparing the control file:

    Create a control file using a text editor such as vi and then save it with a .ctl extension. A control file has the following form 
      LOAD DATA
INFILE <dataFile>
APPEND INTO TABLE <tableName>
FIELDS TERMINATED BY <separator>
      (list of all attribute names to load)

    where <datafile> is the name of data file, <tableName> is the name of the table, and <separator> specifies the field separator used in the data file.

    Example: LOAD DATA INFILE sample.dat APPEND INTO TABLE Movies FIELDS TERMINATED BY '|' (title, year, length, studio)

  2. Creating the data file

    Each line in data file specifies one tuple to be loaded into the table <tableName>. It lists, in order, values for the attributes in the list specified in the control file, separated by <separator>. For instance, the following is a data file sample.dat: 
      Forest Gump|1994|142|Paramount
Terminator 2|1991|137|Universal Studios
Species II|1998|93|MGM Distribution Company
Golden Eye|1995|130|United Artists Films
Jurassic Park|1993|127|Universal Pictures
  3. Invoking the loader:

    The Oracle bulk loader is invoked through a UNIX command called sqlldr, i.e., it should be entered from the UNIX shell and not from the Oracle prompt SQL>.

    Enter the following commands in the specified order at the UNIX command prompt to invoke the Oracle bulk loader: 
    
   sqlldr <oracleid>@udorcl
    After entering both command lines above, you will prompted for the control filename. Enter the name of your control file ending in .ctl.

    Next, you will prompted for your Oracle account password (not the password for your UNIX account). Enter your Oracle account password.

    To verify that the data has indeed been loaded successfully, start Oracle and enter SELECT * FROM <tableName> at the SQL> prompt.

Getting help online

Books

Pick up any programming reference on Oracle 8 at e.g., the Roesch Library. A good place to start is: R. Sunderraman. ORACLE 8 Programming: A Primer. Reading, MA: Addison-Wesley, 2000.

Return Home