心有猛虎，细嗅蔷薇: OEPE_004：与Oracle Database集成

开发运行环境：OEPE 12c（12.1.1.1.0）+ Oracle Database 10g Express Edition 10.2.0.1

1. SQL脚本

（1）创建troubleticketuser用户

sqlplus system/welcome1 @create_troubleticketuser_user.sql
grant connect, resource to troubleticketuser identified by welcome1;
Exit;

（2）创建表

drop table PRODUCT cascade constraints;
drop table TECHNICIAN cascade constraints;
drop table TECHNICIANPRIVATEDATA cascade constraints;
drop table DEPARTMENT cascade constraints;
drop table TICKET cascade constraints;
drop table TICKETHISTORY cascade constraints;

CREATE TABLE PRODUCT (ID INTEGER NOT NULL PRIMARY KEY, CODE VARCHAR(10) NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255), CONSTRAINT UNI_PROD_CODE UNIQUE(CODE) );
CREATE TABLE DEPARTMENT (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(100) NOT NULL );
CREATE TABLE TECHNICIAN (ID VARCHAR(30) NOT NULL PRIMARY KEY, PASSWORD VARCHAR(30) NOT NULL, NAME VARCHAR(255) NOT NULL, EMAIL VARCHAR(100), DEPTID INTEGER NOT NULL, CONSTRAINT DEPT_ID_REF FOREIGN KEY(DEPTID) REFERENCES DEPARTMENT(ID) );
CREATE TABLE TECHNICIANPRIVATEDATA(PK_ID INTEGER NOT NULL PRIMARY KEY, ID VARCHAR(30) NOT NULL, SSN_NO INTEGER NOT NULL, CONSTRAINT UNI_SSN_NO UNIQUE(SSN_NO), CONSTRAINT TECHPRIVATE_ID_REF FOREIGN KEY(ID) REFERENCES TECHNICIAN(ID) );
CREATE TABLE TICKET (ID INTEGER NOT NULL PRIMARY KEY, PRODUCTID INTEGER NOT NULL, CUSTOMERNAME VARCHAR(100) NOT NULL, CUSTOMEREMAIL VARCHAR(100), TITLE VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255), SUBMISSIONDATE VARCHAR(30) NOT NULL, TECHNICIANID VARCHAR(30), STATE VARCHAR(15) NOT NULL, CONSTRAINT PRODID FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT(ID), CONSTRAINT TECH_ASSOCIATE_ID FOREIGN KEY(TECHNICIANID) REFERENCES TECHNICIAN(ID) );
CREATE TABLE TICKETHISTORY (ID INTEGER NOT NULL PRIMARY KEY, TICKETID NOT NULL, TECHNICIANID VARCHAR(30), STATE VARCHAR(15) NOT NULL, COMMENTS VARCHAR(255), UPDATEDATE VARCHAR(30) NOT NULL, CONSTRAINT TICKET_ID_FK FOREIGN KEY(TICKETID) REFERENCES TICKET(ID), CONSTRAINT TECH_ASSOCIATE_HISTORY_ID FOREIGN KEY(TECHNICIANID) REFERENCES TECHNICIAN(ID) );

INSERT INTO PRODUCT VALUES(1001,'11gDB','Oracle Database 11g', 'Oracle gives Oracle customers the agility to respond faster to changing business conditions, gain competitive advantage through technology innovation, and reduce costs.');
INSERT INTO PRODUCT VALUES(2001,'OEPE11g','Oracle Enterprise Pack for Eclispe 11g', 'OEPE is a set of Eclipse plug-ins that is designed to help develop, deploy and debug applications for Oracle WebLogic Server.');
INSERT INTO PRODUCT VALUES(3001,'JDeveloper','Oracle JDeveloper','Oracle JDeveloper is a free integrated development environment that simplifies the development of Java-based SOA applications and user interfaces with support for the full development life cycle.');
INSERT INTO DEPARTMENT VALUES (99, 'SALES');
INSERT INTO DEPARTMENT VALUES (199, 'MARKETING');
INSERT INTO TECHNICIAN VALUES ('david', 'david', 'David Dodd', 'david@oracle.com', 99);
INSERT INTO TECHNICIAN VALUES ('peter', 'peter', 'Peter Smith', 'peter@oracle.com', 199);
INSERT INTO TECHNICIANPRIVATEDATA values (25,'david',123456);
INSERT INTO TECHNICIANPRIVATEDATA values (35,'peter',678910);
INSERT INTO TICKET VALUES (101, 1001, 'john', 'john@hotmail.com', '11g JDBC Driver could not connect', 'Oracle 11g JDBC driver hangs while connecting with the database.', '05/16/2009 11:10:15 AM', 'peter', 'ASSIGNED' );
INSERT INTO TICKET VALUES (102, 2001, 'tom', 'tom@yahoo.com', 'OEPE crashes while starting', 'Oracle Enterprise Pack for Eclipse crashes while launching it.', '06/12/2009 10:40:15 AM', null, 'NEW' );
INSERT INTO TICKET VALUES (103, 3001, 'kelly', 'kelly@ibm.com', 'JDeveloper compiler fails', 'The java compiler fails while compiling the files.', '06/12/2009 09:35:15 AM', null, 'OPEN' );
INSERT INTO TICKETHISTORY VALUES (11, 101, 'david', 'ASSIGNED', 'Assigned to David', '05/10/2009 13:20:15 PM');
INSERT INTO TICKETHISTORY VALUES (12, 101, null, 'OPEN', 'Released from David', '05/11/2009 13:20:15 PM');
INSERT INTO TICKETHISTORY VALUES (13, 101, 'peter', 'ASSIGNED', 'Assigned to Peter', '05/16/2009 11:10:15 AM');
INSERT INTO TICKETHISTORY VALUES (14, 102, 'peter', 'ASSIGNED', 'Assigned to Peter', '06/12/2009 11:20:15 AM');
INSERT INTO TICKETHISTORY VALUES (15, 102, null, 'OPEN', 'Keep it open for the next patch release.', '06/12/2009 13:35:15 PM');

2. 打开Data Source Explorer