ORACLE LAB GUIDELINES

ORACLE LAB GUIDELINES

0. Notation

In the following text, you will be expected to type everything between the brackets <> exactly as written. The exception is your username and password, which you need to provide. For example if you are asked to type < sqlplus username/password > , and your username is scott and password is tiger, then you would type: sqlplus scott/tiger

1. Login

Before you can use Oracle you need to have an account on the Oracle system. If you have provided your login name your oracle account should already have been set up. Otherwise, the default oracle account name will be s followed by your SID, e.g. s1234567.

Notice that to use Oracle, you need to log in the Oracle system with a separate username and password. Initially they have been set to your account’s username. That is, for account s2134567, both the username and password for Oracle are set to s2134567.

The Oracle system is installed at a server machine called dingo. So you need to telnet to dingo from the workstation you are currently using before you can run Oracle. Also, there are environment variables like PATH and ORACLE_HOME which need to be set. There are scripts to handle this, which you need to run every time you telnet to dingo. (You can include these environment variables in your default login script, then you don’t need to run the Oracle setup script each time). In Summary, we need to do things as follows to run ORACLE

Telnet to dingo by typing < telnet dingo>, and log in the server with your account's username and password.

(Only for the first time.) Create a directory called "tmp" at your home directory. The $HOME/tmp directory is for storing Oracle Book's temporary files when you make notes to the online document: < mkdir $HOME/tmp >.

Run the set up script to set up Oracle's environment variables
- If you are using Bourne, Korn or Bash shell, type < . /home/oracle/local/bin/b_oracle > (the "." is important).
- If you are using c or TC shell, type < source /home/oracle/local/bin/c_oracle > (again the "source" is necessary).
This step is ONLY required if you want to change the default Oracle login & password

Invoke SQL*Plus by typing < sqlplus username/password >
ALTER USER username IDENTIFIED BY new_password >

Depending on the tool you are going to use, type the appropriate command to invoke that tool. Refer to the following sections about SQL*Plus, PL/SQL, Discover 2000 and Developer 2000

2. On-line manuals

All the oracle manuals are available on line, and you can use the tool oracle book to read each of these manuals. For your information, Oracle book is a hypertext product which provides hypertext links between documents and bookmarks etc. Documents under Oracle Book can be grouped into a set called a bookshelf . For instance, there are 3 separate manuals about Oracle Browser, each of these manuals corresponds to a separate document in oracle book. You can open an individual manual or open the bookshelf that contains references to these three manuals.

An index page has already been set up which contains links to each of the available manuals. To use the index page, you only need to type < oradocm > at the UNIX prompt. You may see some warning messages complaining unable to access audio device, ignore that.

To look at the individual manual, double click at the manual item.

Then click at the < Contents > option at the tool bar to look at the content page.

Oracle Book is relatively easy to use, if you do need to know more about it, refer to the on line documentation ( Oracle Book v.2.2 --- User's Guide from the index page ).

3. A Suggested Lab Schedule

Weeks 9-10 (SQL Server): Read Chapters 1, 2, 3, and 4 in SQL*Plus v 3.1 --- User Guide & Reference , as well as run the exmaples in these chapters. To run these examples, you should load databases in - please read the next two sections.
Weeks 11 (SQL): Read Chapters 1, 2, 3, and 4 in Oracle Server v. 7.0 --- SQL Language Reference Manual , as well as run the examples in these chapters. You are encouraged to try more SQL commends that you learnt.
Weeks 12 (PL/SQL): Read Chapter 1, 2, 3, and 4 in Oracle Server v. 7.0 --- PL/SQL User's Guide and Reference, and try the examples in these chapters.
Weeks 13 (Browser): Read and follow Section 6 in the guildlines and Chapter 4 in Oracle Browser v 2.0 --- User's Guide .
Weeks 14 (Developer 2000): Follow Section 7 in the guildlines. This is an optional lab practice.

4. SQL*Plus

It is important that you learn about SQL. Though there are front-end tools like Oracle Browser which makes writing SQL a breeze, there still occasions that you need to study the SQL statements generated by these tools to have an understanding of what are actually being executed.

Oracle SQL conforms to ANSI's SQL-92 Entry Level. What this actually means is that Oracle SQL provides the functionality as specified by SQL-92 Entry Level plus some other features that satisfy the Intermediate and Full Level Compliance. The full set of SQL commands can be found at the "Oracle7 Server SQL Reference Manual"

SQL is a database language, and it needs an interface to work in practice. Oracle provides an environment called SQL*Plus that can be used to write SQL and PL/SQL commands (PL/SQL is a separate database language specific to Oracle, it extends the SQL language to include procedural features).

With SQL*Plus, you can

· enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks

· format, perform calculations on, store, and print query results in the form of reports

· list column definitions for any table

· access and copy data between SQL databases

· send messages to and accept responses from an end user

There is only one manual about SQL*Plus (SQL*Plus v.3.1 --- User Guide & Reference). This manual contains many examples on the syntax of SQL and PL/SQL.

The useful chapters are Learning SQL*Plus Basics, Manipulating Commands, Formatting Query Results and Accessing SQL Databases. Because of the time limit in this tutorial, spend about 1/2 hour on the chapter "Learning SQL*Plus Basics" and come back to the lab at other time to finish the other chapters

To invoke SQL*Plus, type

· < sqlplus username/password > -- Use Oracle username and password There are tables you need to create and populate for the exercises described in this manual. Before you do the exercise · If you are in SQL*Plus already, exit to the shell by typing <exit>. Then type < demobld username password > at the unix shell. -- Use Oracle username and password

· When you have finished the exercise, these tables can be dropped by typing < demodrop usename password > at the unix shell. -- Use Oracle username and password

To find out what tables are accessible, type < select * from tab > when you are in SQL*Plus

5. PL/SQL

PL/SQL extends the SQL with procedural features. A quick way to learn PL/SQL is to follow some useful examples at the APPENDIX B SAMPLE-PROGRAMS of the manual Oracle Server v.7.0 --- PL/SQL User's Guide and Reference. /home/oracle/app/oracle/product/oracle/7.3.2/plsql/demo/ has text files of these examples.

(You should spend about 1/2 hour on this part, and finish the exercise at some other time)

a) Create tables needed for the examples. Invoke SQL*Plus by typing < sqlplus username/password >

Run the exambld script to create tables; the start command loads and executes SQL scripts.

< start $ORACLE_HOME/plsql/demo/exampbld >. After the tables have been created, populate the tables with < start $ORACLE_HOME/plsql/demo/examplod > b) You can input PL/SQL blocks in the sample programs via SQL*Plus. · Type the PL/SQL block like the following. Notice that line number is provided automatically by SQL*Plus, and remember to have the < / > as the last command of the block. The < / > signals the end of the PL/SQL block and starts the execution. SQL> DECLARE

2 x NUMBER := 100;

3 BEGIN

4 FOR i IN 1..10 LOOP

5 IF MOD(i,2) = 0 THEN -- i is even

6 INSERT INTO temp VALUES (i, x, 'i is even');

7 ELSE

8 INSERT INTO temp VALUES (i, x, 'i is odd');

9 END IF;

10 x := x + 100;

11 END LOOP;

12 COMMIT;

13 END;

14 /

· To see the result, you need to use SQL code to retrieve the temp table. Notice that for SQL codes, the terminator is < ; > SQL> SELECT * FROM temp ORDER BY num_col1; 6. Browser

Oracle Browser facilitates the writing of ad-hoc queries. Also it can be used to create and re-structure tables. It can also be used for quick data entry that do not need much sophisticated checking. Though it is not absolutely necessary for your project which SQL and PL/SQL can be used to create tables, enter data and query results. The use of Browser certainly can make the work of data entry easier.

There are 3 manuals for the Browser, Oracle Browser v.2.0 --- User's Guide, System Administrator's Guide and Reference Guide. It is advisable to go through the User's Guide only. Pay attention in particular to the use of Data Editor for data entry, and Schema Editor for defining table structure.

In this tutorial, spend about 1/2 hour on the chapter 4 "Taking a Test Drive" of the User's Guide. After you have finished the exercise, you should be able to use the Browser to create tables and input data into the tables. (For sophisticated data entry form, oracle has a separate product called FORMS in the Developer 2000)

a) Invoke SQL*Plus by typing < sqlplus username / password >

b) Create sample tables with the SQL script brwbld20

< start $ORACLE_HOME/browser20/demo/brwbld20 > c) Exit the SQL*Plus by typing < exit >

d) Now you should be at the unix shell, invoke the Browser with the command < browser20em >

e) A login window should appear, log in the Browser with your Oracle username and password.

f) Follow the chapter "Taking a Test Drive" of the User's Guide.

When you finish the exercises, the sample tables can be dropped by running the SQL script brwdrp20 < start $ORACLE_HOME/browser20/demo/brwdrp20 > 7. Developer 2000 (Optional)

Oracle has 2 major development environments, Power Object for the "lite" development and Developer 2000 for the more complex applications.

Developer 2000 includes many features that help you build sophisticated applications. It has a canvas tool for building the user interface, an object navigator for repository management and a PL/SQL editor to write procedures, functions and packages. Besides its main use for building a sophisticated form that can be used to input data, check integrity constraint and display output, the Developer 2000 environment also supports the building of application that integrates forms, graphic and report.

There are 3 commands to invoke different executables of Oracle Forms; you probably will use the f45desm most of the time.

- f45desm (for designing form which also includes code generation and runtime)

- f45genm (for generating executable)

- f45runm (for running a form executable)

The development environment is very rich in features, and it is advisable to have an overview by following some of the examples at the manual "Getting Started with Forms"; the other useful manual to study is "Forms Developer Guide".

a) Copy sample forms into your directory.
% mkdir oracle; cd oracle

% cp /home/oracle/TUTORIAL/forms/sample.fmx .

% cp /home/oracle/TUTORIAL/forms/sample.fmb .

b) Build sample data tables
· Invoke SQL*Plus by typing < sqlplus username/password >

· <start /home/oracle/TUTORIAL/forms/summit2>

c) Quit SQL*Plus and at the unix shell, type < f45desm > to invoke Oracle Form.
d) Follow the exercise in chapter 2 of the manual "Getting Started with Forms". To use the copied sample forms, you need to open the sample.fmb at the directory $HOME/oracle, and you should see a new module called SAMPLE at the Object Navigator.

e) When you finish the exercise, the sample tables can be dropped by running the script

<start /home/oracle/TUTORIAL/forms/summdrop> To understand how to implement Forms application efficiently, refer to manual Forms Developer's Guide. In particular, on the chapter Writing Event Triggers which describe how Oracle actually processes an interface event like WHEN-BUTTON-PRESSED. Note that the association of trigger with item field can be used to implement database constraints at the forms level.

There is also a rather involved demonstration about the integrated ability of Oracle Forms, Graphics, Reports at the directory /home/oracle/app/oracle/product/7.3.2/forms45/demos/start. Your environment should already be set up to run this demo, if you have time and would like to have a look, type <f45runm> and at the file prompt, type <start>; you need to log in as scott/tiger who owns the demonstration tables. Do not update, insert or delete data as other students may want to have a look as well.