Guide to Oracle SQL (Buffalo State University Version)

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SID                                       NOT NULL VARCHAR2(9)
S_LNAME                                   NOT NULL VARCHAR2(10)
S_FNAME                                   NOT NULL VARCHAR2(10)
GENDER                                             VARCHAR2(1)
MAJOR                                              VARCHAR2(3)
GPA                                                NUMBER(3,2)
SAT                                                NUMBER(5)
DOB                                                DATE

CHAR

Used to store fixed length character data. For example, a state field would be CHAR(2).

VARCHAR2

Used to store variable length character data. For example, a firstName field would be VARCHAR2(25). This allows the field to be any number of characters between 0 and 25.

NUMBER

Used to store integer data. For example, the age field would be NUMBER(2).

NUMBER can also be used to store floating point (decimal) data. For example, a currency field would be NUMBER(9,2) which allows 7 digits to the left of the decimal point and 2 digits to the right of the decdimal point.

DATE

Used to store the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

LOB (Large OBject)

BLOB - Used to store unstructured binary data.

CLOB - Used to store database character set data.

NCLOB - Used to store Unicode national character set data.

BFILE - used to store a pointer to a binary file in the operating system outside the database.

1. Login to your bsuacad.buffalostate.edu account using PuTTy using your BSC Username and Password.
   Your oracle-password is a capital B plus your Student ID and if your Student ID has 7 digits then add a zero in front of your Student ID and if it has 6 digits then add two (2) zeros.
   
   For example:
   
   Student ID: 1234567  Oracle Password: B01234567Student ID:  987654  Oracle Password: B00987654
2. Edit your .bashrc file using the vi editor (see the Washington University site for more information about Using VI) to setup the your Oracle environment and your sqlplus alias:
1. At the Unix $ prompt type: vi .bashrc
2. Use the arrow keys to move to this line:
   fi
3. Use SHIFT-A to enter Append mode.
4. Then add these lines:
   ORACLE_BASE=/home/oracle/db; export ORACLE_BASE
ORACLE_HOME=/home/oracle/db/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=acad12c; export ORACLE_SID
5. Press CONTROL-] to exit Append mode.
6. Use the arrow keys to move to this line:
   PATH=PATH/bin:
7. Use SHIFT-A to enter Append mode.
8. Add this to the end of that line: PATH=PATH=$PATH:$ORACLE_HOME/bin:
9. Press CONTROL-] to exit Append mode.
10. Scroll to the end of the file
11. Press SHIFT-A to enter Append mode.
12. Press the ENTER key to go to the next line
13. Type this line: alias sqlplus=cd public_html; sqlplus your-userid/your-Oracle-password'
14. Press CONTROL-] to exit the append mode.
15. Press the colon (:) key to move to the vi command prompt.
16. To save your .bashrc file, at the (: prompt, type: wq
3. At the Unix $ prompt type: source .bashrc
4. At the Unix $ prompt type: sqlplus
5. At the SQL> prompt type: DESC testTable;
6. There should not be any listed yet, so you should see:
   SQL> desc testTable;
ERROR: ORA-04043: object testTable does not exist SQL>
7. To create a table, at the SQL> prompt type: 
   CREATE TABLE testTable
(testId NUMBER(6) NOT NULL,
firstName varchar2(25) NOT NULL,
lastName varchar2(25) NOT NULL,
CONSTRAINT id_pk PRIMARY KEY (testId)
);
8. At the SQL> prompt type: DESC testTable;
9. You should see:
   

    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    TESTID                                    NOT NULL NUMBER(6)
    FIRSTNAME                                 NOT NULL VARCHAR2(25)
    LASTNAME                                  NOT NULL VARCHAR2(25)
   

10. To add data to your table type:
    INSERT INTO testTable (testID, firstName, lastName)
VALUES (1, 'your first name', 'your last name');
11. You should see:
    

    1 row created.

12. To view the data in your table type:
    SELECT * FROM testTable ORDER BY lastName, firstName;
13. You should see:
    

        TESTID FIRSTNAME                 LASTNAME
    ---------- ------------------------- -------------------------
             1 Jim                       Gerland
    

14. To exit sqlplus, at the SQL> prompt type: exit
15. You should now be back at the Unix $ prompt.
16. To logout type: exit

1. In the Name field, type: localhost
2. In the Username field, type: system
3. In the Password field, type: oracle
4. Check the Save Password box.
5. In the Hostname field, type: localhost
6. In the SID field, type: acad12c
7. Click: Test
8. If the test is successful then click: Connect

You should now see your connection in the right side Connections navigation window.