Notes on using Oracle on Omega at UTA
Oracle accounts on Omega are created at the beginning of each semester to students enrolled in a database classes offererd by CSE and INSY departments.
The Oracle accounts will be created at the instructors' request only, because of license restriction.

The following documentation is divided into 3 sections:
Section 1 - For beginners who take database class for the first time.
Section 2 - For advanced users and instructors.
Section 3 - Using JDBC to Connect to Oracle.


--------------------------------------------------------------------------------

1.1 Getting Started
a. Log on to your omega account.

b. Log into SQL*Plus by typing:

omega>sqlplus

You will then be prompted for your Oracle username, which is the same as your Omega

username. When you are prompted for a password, enter your default password

Your default password is mmddyynn. Substitute the letters for the month, day, and

last 2 digits of your date of birth followed by the first 2 numbers of your social security/student ID.

After logging in successfully, you will see the prompt: SQL>.

--------------------------------------------------------------------------------


1.2 Entering a SQL command in SQL*Plus
You can enter single-line or multiple-line SQL statement. SQL statement is not case-sensitive. To end a SQL statement and execute, you can do it in one of the two ways:

end with a semicolon ;
end with a slash / on a line by itself


--------------------------------------------------------------------------------

1.3 Changing your passwords
To change your Oracle password. In sqlplus, type

SQL> ALTER USER username IDENTIFIED BY newpassword;


--------------------------------------------------------------------------------

1.4 Running SQL Command Files
The START command retrieves a SQL command file and runs the command(s) it contains. Use START to run a command file containing SQL commands and/or SQL*Plus commands.
Follow the word START with the name of the file:

SQL> START file_name

If the file has the extension .sql, you need not add the period and the extension sql to the filename.


--------------------------------------------------------------------------------

1.5 Getting out of SQL*Plus
Type

SQL> EXIT


--------------------------------------------------------------------------------

1.6 How to create the table in Oracle?

Type like following in SQL prompt.

SQL> create table example (

name varchar(10),

address varchar(30),

number carchar(10));

another way is folloing

  1. you make test.sql.
  2. type the folloing SQL statement into test.sql file.

    3. create table example (

    name varchar(10),

    address varchar(30),

    number carchar(10));

    commit;

  3. Upload test.sql file to omega.
  4. In the Oracle prompt, you type like following:

SQL> @ test.sql

---------------------------------------------------------------------------------------------

1.7. How to insert data to table?

SQL> insert into example values('Tom','Arlington','10');

-------------------------------------------------------------------------------------------

1.7 List tables owned by you
Enter the following SQL statement to list schema objects (including tables, views and sequences) owned by you

SQL> SELECT * FROM example;

---------------------------------------------------------------------------------------------------


1.6 Capture output from SQL*Plus to a file
In SQLPLUS, enter the command

SPOOL output.lst

to begin capture output
and the command

SPOOL OFF

to end the spooling. Any commands issued in between the SPOOL commands are saved in 'output.lst'.

SQL> SPOOL output.lst
SQL> SELECT * FROM mytable;
SQL> SPOOL OFF
SQL> EXIT
omega> cat output.lst

----------------------------------------------------------------------------------

    1. How to delete table?

SQL> drop table example;
--------------------------------------------------------------------------------

1.8 List all columns of a table
Enter the following SQL statement to list all columns of table 'staff'.

SQL> DESCRIBE staff;


--------------------------------------------------------------------------------

1.9 List Database Objects owned by you
Enter the following SQL statement to list database objects owned by you:

SQL> SELECT * FROM user_objects;

To list only useful information, enter the following SQL statement:
SQL> SELECT object_name, timestamp, object_type FROM user_objects;


--------------------------------------------------------------------------------

1.10 Compilation of a Pro*C program
There is an alias named "make.proc" created to compile a Pro*C program.
This alias is good to compile ProC program with filename in the format of lab#.pc where # is a number from the range 1 to 10.


The usage is "make.proc lab1" where lab1 is the Pro*C program - lab1.pc
The output executable file is "lab1" in the above example.


--------------------------------------------------------------------------------

1.11 Oracle9i Documentation Library
Oracle9i Documentation Library is available to UTA staff and students taking database classes only, it is at http://omega.uta.edu.


--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

2.1 Check your system privileges
To check for system privileges granted on your current session, enter the following SQL command:


SQL> SELECT * from SESSION_PRIVS;


--------------------------------------------------------------------------------

2.2 Create public synonyms
You must have CREATE PUBLIC SYNONYM privilege to create a public synonym. This privilege will only be granted to an instructor, please contact Sunny Siu.
To create a public sysnonym in Scott's schema:

SQL> CREATE PUBLIC SYNONYM staff FOR scott.staff;


--------------------------------------------------------------------------------

2.3 Using SQL*Loader
The Unix command to invoke SQL*Loader is 'sqlldr'.
To understand the data loading concept, the structure of control file and the usage of 'sqlldr' command, click the system on-line documentation hyperlink on Omega home page and select Oracle8 Server library and Oracle8 Utilities section for information.

At Unix system prompt, enter the command 'sqlldr' to see a list of keywords accepted by the sqlldr command.



--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

3.1 JDBC Examples
JDBCconnect.java - Retrieving data with JDBC.
JdbcApplet.java - Using JDBC in an Applet.



--------------------------------------------------------------------------------

3.2 Internet Resources for JDBC
Oracle Technology Network - Includes downloads and sample code.
Oracle JDBC Frequenty Asked Questions - contains answers to the most frequently asked questions about Oracle's JDBC drivers
Sun Microsystems' JDBC Data Access API - JDBC API from Sun Microsystems. Includes links for JDBC drivers.
Search Google.com - Search for JDBC on Google.com.



--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
¡¡