Appendix F: Example Oracle Database

---

This appendix contains the definitions of tables, views, triggers, and procedures of the Oracle database ORA_EMP used in this redbook. The complete source code can be downloaded from the IBM Redbooks Web site. Please refer to Appendix G, "Additional material" on page 415 for the details.

Table definition

There are nine tables in ORA_EMP database. The table definitions are:

 CREATE TABLE accounts (    acct_id           NUMBER(3) NOT NULL,    dept_code         CHAR(3) NOT NULL,    acct_desc         VARCHAR2(2000),    max_employees     NUMBER(3),    current_employees NUMBER(3),    num_projects      NUMBER(1)) TABLESPACE user_data_tbs; ALTER TABLE ACCOUNTS ADD (CONSTRAINT ACCOUNTS_DEPT_CODE_ACCT_ID PRIMARY KEY ( DEPT_CODE, ACCT_ID)); CREATE TABLE connect_audit (    user_name VARCHAR2(30),    operation VARCHAR2(30), TABLESPACE user_data_tbs; CREATE TABLE debug_table (    linecount NUMBER NOT NULL,    debug_str VARCHAR2(200)) TABLESPACE user_data_tbs; ALTER TABLE DEBUG_TABLE ADD ( CONSTRAINT pk_debug_lineout PRIMARY KEY ( LINECOUNT )); CREATE TABLE departments (    dept_code       CHAR(3) NOT NULL,    dept_name       VARCHAR2(30),    total_projects  NUMBER,    total_employees NUMBER) TABLESPACE user_data_tbs; ALTER TABLE departments ADD ( CONSTRAINT pk_dept_code PRIMARY KEY ( dept_code )); CREATE TABLE destination (    key   NUMBER(5),    value NUMBER) TABLESPACE user_data_tbs; CREATE TABLE employees (    emp_id             NUMBER(5) NOT NULL,    first_name         VARCHAR2(20),    last_name          VARCHAR2(20),    department         VARCHAR2(30),    current_projects   NUMBER(3),    emp_mgr_id         NUMBER(5),    dept_code          CHAR(3) NOT NULL,    acct_id            NUMBER(3) NOT NULL,    office_id          NUMBER(5),    band               CHAR(1)) TABLESPACE user_data_tbs; ALTER TABLE employees ADD ( CONSTRAINT pk_employees_emp_id PRIMARY KEY ( emp_id )); CREATE TABLE emp_photo (    emp_id          NUMBER(5) NOT NULL,    photo_format    VARCHAR2(10) NOT NULL,    picture         BLOB) TABLESPACE user_data_tbs; ALTER TABLE EMP_PHOTO ADD ( CONSTRAINT EMP_PHOTO_PK11058611148823 PRIMARY KEY ( emp_id )); CREATE TABLE emp_resume (    emp_id          NUMBER(5) NOT NULL,    resume_format   VARCHAR2(10),    resume          CLOB) TABLESPACE user_data_tbs; ALTER TABLE EMP_RESUME ADD ( CONSTRAINT EMP_RESUME_UK11058551798461 UNIQUE ( emp_id )); CREATE TABLE log_table (    code     NUMBER,    message  VARCHAR2(200),    info     VARCHAR2(100)) TABLESPACE user_data_tbs; CREATE TABLE manager_audit (    change_type        CHAR(1) NOT NULL,    changed_by         VARCHAR2(8) NOT NULL,    timestamp          DATE NOT NULL,    old_employee_id    NUMBER(5),    old_dept_code      CHAR(3),    old_acct_id        NUMBER(3),    old_band           CHAR(1),    new_employee_id    NUMBER(5),    new_dept_code      CHAR(3),    new_acct_id        NUMBER(3),    new_band           CHAR(1)) TABLESPACE user_data_tbs; CREATE TABLE offices (    office_id         NUMBER(5) NOT NULL,    building          VARCHAR2(15),    office_number     NUMBER(4),    number_seats      NUMBER(4),    description       VARCHAR2(50)) TABLESPACE user_data_tbs; ALTER TABLE OFFICES ADD ( CONSTRAINT pk_offices_office_id PRIMARY KEY ( OFFICE_ID )); CREATE TABLE source (    key     NUMBER(5) NOT NULL PRIMARY KEY,    value   VARCHAR2(50)) ORGANIZATION INDEX TABLESPACE user_data_tbs; CREATE TABLE temp_table (    num_col   NUMBER,    char_col  VARCHAR2(60)) TABLESPACE user_data_tbs; 

---