Oracle 9i, Oracle 10g and Oracle 11g Developer Suite Forms and Reports Prerequisites

Before following this tutorial, a student must have a valid user account in an Oracle server or a local installation of an Oracle database. Contact your DBA or systems administrator to learn the details of how Oracle server is set up in your organization.

If you have a local instance of the Oracle database, please do not use the SYSTEM (DBA) account to practice development. There is a good chance you can corrupt
the entire database by mistake. You are much better off making use of the SCOTT/TIGER schema or (even better) creating a separate user (schema) for this tutorial work and for practicing.

One more word of caution. Oracle has designed Oracle9i, 10g and 11g as a three tier architecture. This means that the database (Oracle9i, 10g or 11g server) should run on one server, the application Server (Oracle9i, 10g or 11g Application Server or Oracle9iAS Containers for Java (OC4J) server as used here) runs on a second server and the client application (written in Oracle Forms and Reports for example) runs on a third machine – typically through a web browser. One can get all of these components to run on the same machine, but it requires both a fast processor and a lot of RAM. Here is one example:
Service/Application RAM consumed while running
Oracel9i Database (minimal instance) 300 MB
Oracle9i Forms Builder 60 MB
Oracle9i Reports Builder 120 MB
Oracle9i Forms Runtime web launcher 20 MB
OC4J Service (Java) 50 MB
Firefox 11 running a form 65 MB
Oracle9i SQL*Plus 12 MB
Total approx. 630 MB

Therefore, having at least 2GB of real RAM (not swap space) is a necessity. 2 GB of RAM will yield significantly better performance. If you have less than 2GB of RAM, you should create a swap file (Virtual memory) of at least 2 GB.

This tutorial relies on the existence of a few tables in the student’s schema. Use the Oracle SQL*Plus tool to create three tables named EMPLOYEE, DEPARTMENT and DEPENDENT with the following attributes. Note: This schema originally appears in the book:
Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
Second Edition. Addison-Wesley Pub Co, 1994.

CREATE TABLE employee
(FNAME VARCHAR2(15),
MINIT VARCHAR2(2),
LNAME VARCHAR2(15),
SSN NUMBER(12) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR2(35),
SEX VARCHAR2(1),
SALARY NUMBER(7) NOT NULL,
SUPERSSN NUMBER(12),
DNO NUMBER(2) NOT NULL) ;

ALTER TABLE employee ADD CONSTRAINT pk_employee
PRIMARY KEY (ssn);

CREATE TABLE DEPARTMENT
(DNAME VARCHAR2(15),
DNUMBER NUMBER(2) NOT NULL,
MGRSSN NUMBER(12),
MGRSTARTDATE DATE) ;

ALTER TABLE department ADD CONSTRAINT pk_department
PRIMARY KEY (dnumber);

CREATE TABLE DEPENDENT
(ESSN NUMBER(12),
DEPENDENT_NAME VARCHAR2(15),
SEX VARCHAR2(1),
BDATE DATE,
RELATIONSHIP VARCHAR2(12)) ;

ALTER TABLE dependent ADD CONSTRAINT pk_dependent
PRIMARY KEY (essn, dependent_name);

ALTER TABLE dependent ADD CONSTRAINT fk_employee
FOREIGN KEY (essn) REFERENCES employee (ssn);

ALTER TABLE employee ADD CONSTRAINT fk_department
FOREIGN KEY (dno) REFERENCES department (dnumber);

INSERT INTO DEPARTMENT VALUES (‘RESEARCH’, 5, 333445555, ’22-MAY-1978′) ;
INSERT INTO DEPARTMENT VALUES (‘ADMINISTRATION’, 4, 987654321, ’01-JAN-1985′) ;
INSERT INTO DEPARTMENT VALUES (‘HEADQUARTERS’, 1, 888665555, ’19-JUN-1971′) ;

INSERT INTO EMPLOYEE VALUES
(‘JOHN’,’B’,’SMITH’,123456789,’09-JAN-1955′,’731 FONDREN, HOUSTON, TX’, ‘M’,30000,333445555,5) ;
INSERT INTO EMPLOYEE VALUES
(‘FRANKLIN’,’T’,’WONG’,333445555,’08-DEC-1945′,’638 VOSS,HOUSTON TX’, ‘M’,40000,888665555,5) ;
INSERT INTO EMPLOYEE VALUES
(‘ALICIA’,’J’,’ZELAYA’,999887777,’19-JUL-1958′,’3321 CASTLE, SPRING, TX’, ‘F’,25000,987654321,4) ;
INSERT INTO EMPLOYEE VALUES
(‘JENNIFER’,’S’,’WALLACE’,987654321,’20-JUN-1931′,’291 BERRY, BELLAIRE, TX’, ‘F’,43000,888665555,4) ;
INSERT INTO EMPLOYEE VALUES
(‘RAMESH’,’K’,’NARAYAN’,666884444,’15-SEP-1952′,’975 FIRE OAK, HUMBLE, TX’, ‘M’,38000,333445555,5) ;
INSERT INTO EMPLOYEE VALUES
(‘JOYCE’,’A’,’ENGLISH’,453453453,’31-JUL-1962′,’5631 RICE, HOUSTON, TX’, ‘F’,25000,333445555,5);
INSERT INTO EMPLOYEE VALUES
(‘AHMAD’,’V’,’JABBAR’,987987987,’29-MAR-1959′,’980 DALLAS, HOUSTON, TX’, ‘M’,25000,987654321,4) ;
INSERT INTO EMPLOYEE VALUES
(‘JAMES’,’E’,’BORG’,888665555,’10-NOV-1927′, ‘450 STONE, HOUSTON, TX’, ‘M’,55000,NULL,1) ;

INSERT INTO DEPENDENT VALUES (333445555,’ALICE’,’F’,’05-APR-1976′,’DAUGHTER’) ;
INSERT INTO DEPENDENT VALUES (333445555,’THEODORE’,’M’,’25-OCT-1973′,’SON’) ;
INSERT INTO DEPENDENT VALUES (333445555,’JOY’,’F’,’03-MAY-1948′,’SPOUSE’);
INSERT INTO DEPENDENT VALUES (123456789,’MICHAEL’,’M’,’01-JAN-1978′,’SON’);
INSERT INTO DEPENDENT VALUES (123456789,’ALICE’,’F’,’31-DEC-1978′,’DAUGHTER’);
INSERT INTO DEPENDENT VALUES (123456789,’ELIZABETH’,’F’,’05-MAY-1957′,’SPOUSE’);
INSERT INTO DEPENDENT VALUES (987654321,’ABNER’,’M’,’26-FEB-1932′,’SPOUSE’);