Oracle Information Sheet

1. After you login to your class account on grid1.  The first step that you should do is add the following line to your .cshrc file in your class account using any editor.

        alias   startsql        'source ~oracle/Client/bin/coraenv'

        Note do not forget to have a return at the end of the line or
        the alias will not work! Also failing to put in the ending 
        `` ' `` will prevent you from logging in!!!

2. The alias will be automatically available to you the next time you log in. To use the modified c-shell during the current session you must type following:

        source ~/.cshrc

3. Logging on to sqlplus
1. Type "startsql" and you should get the following:

grid1.cs: startsql
ORACLE_SID = [ORCL] ? 
ORACLE_HOME = [/usr/local/oracle/Client] ? 
grid1.cs:

After the two prompts just press the 'Enter key'
2. Starting Sqlplus. (For this example the class account is 'paultest' and the password is 'mypassword'. 
   
   grid1.cs: sqlplus paultest@ORCL_sandbox.cs.uwm.edu
   
   SQL*Plus: Release 8.1.7.0.0 - Production on Sat Oct 20 16:46:26 2001
   
   (c) Copyright 2000 Oracle Corporation.  All rights reserved.
   
   Enter password:
   Connected to:
   Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
   With the Partitioning option
   JServer Release 8.1.7.2.0 - Production
   
   SQL> 
   
   
   
3. Setting sqlplus display variables. There are a number of variables which effect the display in sqlplus. Here is how you get the information you need and how you set them. (Sample shown below)
1. You first want to determine the number of lines and columns currently in your display window. You do this by using the host command and the Unix ``stty'' function with the option ``size'' Type "!stty size" this will give you the number of row and columns in the current window.
2. To use this information to configure sqlplus you use the ``set pagesize'' and ``set linesize'' commands. ie If you got ``24 80'' as the result of the ``stty size'' command you would type: "set pagesize 24" to set the pagesize and "set linesize 80" to change the linesize.
3. If you wish sqlplus to pause scrolling at the end of each page you would use the command ``set pause on''. Similarily, the command ``set pause off'' turns it off.
4. If you wish to echo information that is being read from a file you need to use the command ``set echo on''. To turn it off use ``set echo off''.
5. If you wish to clear the screen type ``clear screen''
6. If you wish to examine the Oracle Sqlplus envirnomental variables you need to use the command ``show all''.

 Sample
SQL> !stty size
30 80

SQL> set pagesize 30
SQL> set linesize 80
SQL> set pause on
SQL> set echo on
SQL> set pause off
SQL> set echo off
SQL> clear screen

 
4. On line help. To get help first set the pause on, then type ``help [<command name>]''. Note help is limited to sql*plus commands.

SQL> help index

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET
 CONNECT       PASSWORD     SHOW


SQL>

5. SQLPLUS Line Editing Commands.

(a)ppend text i.e. a adding to the end of current line
(c)hange/old/new/ i.e. c/pual/paul/
(c)change/text/ c/drop/
del deletes the current line.
(i)nput text add a line after the current line
(l)ist Show all the lines inthe buffer (l)ist n Show line number n in the buffer.
 
6. To determine what tables you have access to type the following in sqlplus: ``select table_name,owner from all_tables;''
7. To determine what tables you own type the following in sqlplus: ``select table_name from user_tables;''
8. To determine the names and datatypes of the columns in a table you will use the following command in sqlplus: ``describe tablename''

 

 

4. Common Sql data types

                        PARTIAL LIST OF DATA TYPES
        DATA TYPES      DESCRIPTION

char(size)      Stores fixed-length character data, with a maximum size of
                255. Default is 1 byte. Padded on right with blanks to full 
                length of size.

varchar2(size)  Stores variable length character data up to size, 
                with a maximum size of 2000

number(l,d)     Stores numberic data, where "l" stands for length and 
                "d" for the number of decimal digits

number(l)       Same as Number.

number(*)       Same as Number.

date            Stores dates from Jan 1, 4712 B.C. to DEC 31, 4712AD

long            Stores variable-length character data up to 2GB.  Only one 
                Long column may be defined per table.  Long columns man NOT 
                be used in subqueries, functions, expressions, where clauses, 
                or indexes. A table with a long may not be clustered.

**************************************************************************

Additional supported data types

raw(size)       Raw binary data, size byteslong. Max size is 255 bytes

long raw        Raw binary data; otherwise the same as long.

rowid           A value that uniquely identifies a row in an oracle database. 
                It is returned by the pseudo-column ROWIN. TABLE COLUMNS 
                MAY NOT BE ASSIGNED THIS TYPE.

varchar(size)   use varchar2 may change in future

smallint        Same as number.

long varchar    Same as LONG.
        
integer(size)   Integer of specified size digits wide.

varchar         Currently the same as char.

decimal         Same as Number. Does not accept size or decimal digits

float           Same as number.

                    AGENT TABLE

NAME                 HANDLER                   VALUE
-------------------- -------------------- ----------
Alice                Alice                        20
Bob                  Alice                        10
Carmen               Alice                         5
David                David                        30
Emma                 David                        10
Frank                David                        35

*********************************************************

        SPY TABLE

NAME                 BDATE
-------------------- ---------
Alice                14-SEP-85
Frank                20-FEB-59
Greta                30-SEP-55