Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

B2B XML Application: Step by Step , 11 of 32

## Create and Build the Retailer and Supplier Schemas

These schema scripts set up the Retailer and Supplier environment, users, tablespaces, quota, and so on. They also create and then populate the schemas.

- SQL Example 1: Set up the Retailer and Supplier Environment -- BuildAll.sql. This calls:

- SQL Example 2: Create and Populate the Retailer-Supplier Schema -- BuildSchema.sql

### SQL Example 1: Set up the Retailer and Supplier Environment -- BuildAll.sql

`BuildAll.sql` sets up the environment for the Retailer and Supplier schema. It calls `BuildSchema.sql` which creates the Retailer and Supplier schemas and then populates them with data.

    --
    -- buildall.sql builds all the schemas
    --
    accept sysPswd prompt 'Enter the system password
    > ' hide
    accept cStr    prompt 'Enter the connect string if any, including ''@'' sign (ie
    @atp-1) > '
    connect system/&sysPswd&cStr
    drop user retailer cascade
    /
    drop user supplier cascade
    /
    col tablespace_name head "Available Tablespaces"
    select tablespace_name from dba_tablespaces
    /
    prompt
    accept userTbsp prompt 'What is the DEFAULT Tablespace name ?   > '
    accept tempTbsp prompt 'What is the TEMPORARY Tablespace name ? > '
    prompt

    create user retailer identified by retailer
    default tablespace &userTbsp
    temporary tablespace &tempTbsp
    quota unlimited on &userTbsp
    /
    grant connect, resource, create any directory to retailer
    /
    create user supplier identified by supplier
    default tablespace &userTbsp
    temporary tablespace &tempTbsp
    quota unlimited on &userTbsp
    /
    grant connect, resource, create any directory to supplier
    /
    prompt Now populating Supplier, hit [Return]
    pause
    connect supplier/supplier&cStr
    @buildSchema
    prompt Now populating Retailer, hit [Return]
    pause
    connect retailer/retailer&cStr
    @buildSchema
    prompt done !

### SQL Example 2: Create and Populate the Retailer-Supplier Schema -- BuildSchema.sql

`BuildSchema.sql` is called from `BuildAll.sql`. It creates, populates, and builds the Retailer and Supplier schema.

This script creates and populates the following five tables:

- Customers

- Suppliers

- Inventory_item

- Ord

- Line_item

See Figure 13-3 for an illustration of this schema.

    --
    -- buildSchema.sql drops then creates all the tables for the B2B XML Application
    --
    drop trigger line_item_insert_trigger;
    drop table line_item;
    drop table ord;
    drop table customer;
    drop table inventory_item;
    drop table supplier;
    drop sequence ord_seq;
    drop sequence customer_seq;
    drop sequence line_item_seq;
    drop sequence supplier_seq;
    drop sequence inventory_item_seq;

    prompt
    prompt Creating sequences...
    prompt
    prompt
    prompt Creating sequence ORD_SEQ
    create sequence ord_seq start with 101;

    prompt Creating sequence CUSTOMER_SEQ
    create sequence customer_seq start with 201;

    prompt Creating sequence LINE_ITEM_SEQ
    create sequence line_item_seq start with 1001;

    prompt Creating sequence SUPPLIER_SEQ
    create sequence supplier_seq start with 301;

    prompt Creating sequence INVENTORY_ITEM_SEQ
    create sequence inventory_item_seq start with 401;

    prompt
    prompt
    prompt Creating tables...
    prompt
    prompt
    --
    -- ***** Create table CUSTOMERS ******
    --
    prompt Creating table CUSTOMER
    create table customer(
      id         number,
      name       varchar2(30),
      status     varchar2(8),
      web_site   varchar2(40),
      constraint
         customer_pk
         primary key (id)
    );
    --
    -- ***** Create table SUPPLIERS ******
    --
    prompt Creating table SUPPLIER
    create table supplier(
      id         number,
      name       varchar2(30),
      web_site   varchar2(40),
      constraint
         supplier_pk
         primary key (id)
    );
    --
    -- ***** Create table INVENTORY_ITEM ******
    --
    prompt Creating table INVENTORY_ITEM
    create table inventory_item(
      id             number,
      description    varchar2(30),
      price          number(8,2),
      onhand         number,
      supplier_id    number,
      constraint
         inventory_item_pk
         primary key (id),
      constraint
         supplied_by
         foreign key (supplier_id) references supplier
    );
    --
    -- ***** Create table ORD ******
    --
    prompt Creating table ORD
    create table ord (
      id             number,
      orderDate      date,
      contactName    varchar2(30),
      trackingNo     varchar2(20),
      status         varchar2(10),
      customer_id    number,
      constraint
         ord_pk
         primary key (id),
      constraint
         order_placed_by
         foreign key (customer_id) references customer
    );

    prompt Creating table LINE_ITEM
    create table line_item(
      id             number,
      quantity       number,
      item_id        number,
      ord_id         number,
      discount       number,
      constraint
         line_item_pk
         primary key (id),
      constraint
         item_ordered_on
         foreign key (ord_id) references ord,
      constraint
         order_for_item
         foreign key (item_id) references inventory_item
    );

    prompt
    prompt
    prompt Inserting data...
    prompt
    prompt
    prompt Inserting values into SUPPLIER and INVENTORY_ITEM
    prompt

    insert into supplier values( supplier_seq.nextval,'DELL','http://dell.com');
    insert into inventory_item values( inventory_item_seq.nextval,'Optiplex GXPro',
    1500, 27, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 7000',
    2500, 49, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval,'PowerEdge 6300',
    7500, 16, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 3000',
    2500, 0, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 2000',
    2500, 0, supplier_seq.currval );

    insert into supplier values( supplier_seq.nextval, 'HP', 'http://hp.com');
    insert into inventory_item values( inventory_item_seq.nextval, 'LaserJet 6MP',
    899, 123, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval, 'Jornada 2000',
    450, 1198, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval, 'HP 12C', 69,
    801, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval, 'LaserJet 2', 69,
    3, supplier_seq.currval );

    insert into inventory_item values( inventory_item_seq.nextval,'Jaz PCMCIA
    adapter', 125, 54, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval,'8860 Digital
    phone', 499, 12, supplier_seq.currval );
    insert into inventory_item values( inventory_item_seq.nextval,'Jaz carrying
    bag', 20, 66, supplier_seq.currval );

    insert into inventory_item values(supplier_seq.nextval,'Intel',
    'http://www.intel.com');
    prompt Inserting values into CUSTOMER
    prompt

    insert into ord values( ord_seq.nextval, sysdate, 'George', 'AX'||ord_
    seq.currval, 'Pending', 201);
    insert into line_item values (line_item_seq.nextval, 2, 410,ord_seq.currval, 0);
    insert into line_item values (line_item_seq.nextval, 1, 402,ord_seq.currval, 0);
    insert into line_item values (line_item_seq.nextval, 1, 406,ord_seq.currval, 0);

    insert into ord values(ord_seq.nextval,sysdate,'Elaine','AX'||ord_seq.currval,
    create trigger line_item_insert_trigger
      before insert on line_item for each row
      begin
         select line_item_seq.nextval into :new.id from dual ;
      end;
    /

    commit;

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index