Help on Oracle

Oracle 8 documentation

A Simple Guide to ORACLE 7 SQLPLUS

Adapted from A Simple Guide to ORACLE SQLPLUS, Shuguang Hong, Georgia State University, Atlanta.

Topics

Access ORACLE DBMS on GL

Currently the client and server are only installed on oracle.gl.umbc.edu so you will have to log into that machine to use the client. We hope to get UCS to install the oracle client on the cs machines soon.

ORACLE Login Procedures

ORACLE can only be accessed in your gl account. When you log into the account the first time, you need to set up the ORACLE working environment and change its password following the steps.

To run the interactive sql client, sqlplus, you will first have to execute the commands in /opt/bin/oracle_cshrc. So, you could do:

   source /opt/bin/oracle_cshrc
   sqlplus
or you could add the contents of this file to your .cshrc: setenv ORACLE_HOME /u01/app/oracle/product/7.3.2 setenv ORACLE_BASE /u01/app/oracle setenv ORACLE_SID test setenv ORACLE_TERM vt100 setenv SGI_ABI -32 set path=($ORACLE_HOME/bin /opt/bin $path) set ORAENV_ASK = NO source /opt/bin/coraenv unset ORAENV_ASK

Access ORACLE DBMS on CS

Currently the client and server are only installed on everest.cs.umbc.edu so you will have to log into that machine to use the client.

ORACLE Login Procedures

ORACLE can only be accessed in your cs account. When you log into the account the first time, you need to set up the ORACLE working environment and change its password following the steps.

To run the interactive sql client, sqlplus, you will first have to execute the commands in /opt/bin/oracle_cshrc. So, you could do:

   source /opt/bin/oracle_cshrc
   sqlplus
or you could add the contents of this file to your .cshrc: setenv ORACLE_HOME /u01/app/oracle/product/7.3.2 setenv ORACLE_BASE /u01/app/oracle setenv ORACLE_SID csee setenv ORACLE_TERM vt100 setenv SGI_ABI -32 set path=($ORACLE_HOME/bin /opt/bin $path) set ORAENV_ASK = NO source /opt/bin/coraenv unset ORAENV_ASK

Invoking SQLPLUS

To enter the ad hoc query system, invoke the sqlplus command.
  dbs1[2]%   sqlplus

  SQL*Plus: Release 3.3.2.0.0 - Production on Thu Nov  7 15:23:29 1996
  Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
  
  Enter user-name: finin
  Enter password: 
  
  Connected to:  Oracle7 Server Release 7.3.2.1.0 - Production Release
  With IRIX Enhancements, PL/SQL Release 2.3.2.0.0 - Production
  
  SQL> help;
                                      HELP
  
   HELP [topic] | help
  
   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.
  
   Typing only part of a topic (for example, HELP SE) will cause all help
   topics that match the string (SE) to be displayed on your screen in
   alphabetical order (for example, HELP SE will retrieve help on the
   SELECT statement, the SET command, and the Set Operators--in that order).
  
       ENTER THIS...     TO DISPLAY THIS...
       --------------------------------------------------------------
       help commands     a list of SQL*Plus, SQL, and PL/SQL commands
       help comm         information on commands, comments, and commit
       help help         this screen
  
  SQL> exit;
  Disconnected from Oracle7 Server Release 7.3.2.1.0 - Production Release
  With IRIX Enhancements
  PL/SQL Release 2.3.2.0.0 - Production
  dbs1[3]% 

You can change your ORACLE password using the SQL grant command:

Exit From SQLPLUS

Type Exit while inside SQLPLUS followed by the Enter key.

Documentation and help

To get help from the SQL prompt, you can simply type `help something', where `something' is a command (e.g. `help select;'), a function (e.g. `help to_char;'), or a datatype (e.g. `help char;'). This is the fastest and easiest way to get help with commands. There is another, more comprehensive documentation program (oradocm) which has not yet been installed.

Protect Your Work from Mistakes

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.

Other Useful SQL Commands

Prepare SQL Statements as Text Files

You could prepare all your SQL commands, such as creation of relations and queries, into a text file and execute the SQL commands in this file when you are in SQLPLUS. As the file, working.sql, is kept in the UNIX account, you should down load it to your PC from the UNIX account and then print it from your printer or incorporate it into your document.

Query Results Output

Simple Formatting Query Results