This document details the SQL needed to establish the NORDIC schema in an Oracle XE database for the demonstration of the use of TopLink which is included in Oracle JDeveloper 11g (Build 5188). This SQL is based on the NORDIC series of articles on this blog.
Object views are used to replace object tables because TopLink cannot import Object Tables into the Offline database.
An entire new schema is to be created to replace the original ORDBMS schema used in the original series. This is the minimum required to get the SQL to work.
Use the Run SQL Command Line program from the Oracle Database 10g Express Edition program group to run the following commands:
/* Become the super user. */ CONNECT / AS SYSDBA /* Create the owner of the database objects */ CREATE USER nordic IDENTIFIED BY some_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; /* Grant minimal privileges to create the database objects */ GRANT CREATE SESSION, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER TO nordic; /* Connect as the database object owner to continue */ CONNECT nordic/some_password
Connection Between JDeveloper and XE Database
- Create a new application called
Oklahomavia File then New
- Create a new project called
Intro. The default package is
- Create a new database connection. The name of the connection is
XE_Nordic. Other details are:
- Within that project, created a new Offline Database. The name was
Nordic_database, and the database to emulate is
Oracle10g Express Edition Release 2
|Connection Type||Oracle (JDBC)|
The building attributes are simple. There is only one (1): the name of the building. This name also uniquely identifies the building. There is no ambiguity in this data model.
The external predicate for this object view is
CREATE TABLE building_base ( name VARCHAR2( 30 ) CONSTRAINT building_base_pk PRIMARY KEY ); CREATE TYPE building AS OBJECT ( name VARCHAR2( 30 ) ) FINAL; / CREATE VIEW buildings OF building WITH OBJECT IDENTIFIER( name ) AS SELECT name FROM building_base;
The surrey attributes is more complex, as state is now introduced. There are only two (2) attributes: the name of the surrey, and the state of the surrey. This name also uniquely identifies the surrey. There is no ambiguity in this data model.
The external predicate for this object view is:
CREATE TABLE surrey_base ( name VARCHAR2( 30 ) CONSTRAINT surrey_base_pk PRIMARY KEY, current_state VARCHAR2(30) CONSTRAINT surrey_base_current_state_nn NOT NULL CONSTRAINT surrey_base_current_state_ck CHECK ( current_state IN ( 'Broken', 'Working' ) ) ); CREATE OR REPLACE TYPE surrey_stateflow AS OBJECT ( current_state VARCHAR2(30) ) FINAL; / CREATE TYPE surrey AS OBJECT ( name VARCHAR2( 30 ), state surrey_stateflow ) FINAL; / CREATE VIEW surreys OF surrey WITH OBJECT IDENTIFIER( name ) AS SELECT name, surrey_stateflow( current_state ) AS state FROM surrey_base;
Copy the Objects into JDeveloper
Oklahoma application in JDeveloper.
- Right click on
Nordic_databaseto bring up the context menu. Choose
Copy to project. Source is from database and the connection is called XE_Nordic. Click
- The project is
Intro.jprand the offline database is
- Schema should be
- Select the following objects by highlighting them and clicking the blue right-pointing single arrow (>) and then click
- Choose the defaults. Click
Create the Database Diagrams
Intro project of the
Oklahoma application in JDeveloper,
- Create a new Database Diagram called
- Drag the following objects from the Application Navigator:
The diagram should look like the following:
Doing the same for the surrey objects,