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 , 13 of 32

## Create the Broker Schema Including XSL Stylesheet Table

Run the following scripts to create and populate the stylesheets, tasks, and applications tables:

- SQL Example 9: Create Broker Schema -- mkSSTables.sql.

  This calls:

- SQL (PL/SQL) Example 10: Input XSL data into CLOB. Populate the Broker Schema -- setup.sql

### SQL Example 9: Create Broker Schema -- mkSSTables.sql

Run `mkSSTables.sql` to create the Broker schema. It creates and populates the following three tables:

- Stylesheets

- Tasks

- Applications

This schema is illustrated in Figure 13-4. This script then calls `setup.sql`.

    prompt Building Stylesheets management tables.
    prompt Must be connected as aqMessBrok (like the borker)
    accept cStr prompt 'ConnectString (WITH @ sign, like @Ora8i) > '
    connect aqMessBrok/aqMessBrok&cStr

    drop table styleSheets
    /
    drop table tasks

/

    drop table applications
    /
    create table applications
    (
      code  varchar2(16) not null,
      descr varchar2(256)
    )
    /
    alter table applications
      add constraint PK_APP
      primary key (code)
    /
    create table tasks
    (
      code_app varchar2(16) not null,
      code     varchar2(16) not null,
      descr    varchar2(256)
    )
    /
    alter table tasks
      add constraint PK_TASKS
      primary key (code_app,code)
    /
    alter table tasks
      add constraint TASK_FK_APP
      foreign key (code_app)
      references applications(code) on delete cascade
    /
    create table styleSheets
    (
      appFrom varchar2(16) not null,
      appTo   varchar2(16) not null,
      op      varchar2(16) not null,
      xsl     clob
    )
    /
    alter table styleSheets
      add constraint PK_SS
      primary key (appFrom,appTo,op)
    /
    alter table styleSheets
      add constraint SS_FK_FROM
      foreign key (appFrom)
      references applications(code)
    /
    alter table styleSheets
      add constraints SS_FK_TASK
      foreign key (appTo,op)
      references tasks(code_app,code)
    /
    @setup

### SQL (PL/SQL) Example 10: Input XSL data into CLOB. Populate the Broker Schema -- setup.sql

`setup.sql` installs stylesheet data into the XSL column (CLOB) of the stylesheets table. This script creates a procedure, `loadlob`. The script also uses PL/SQL packages `dbms_lob` and `dbms_output`.

    prompt Installing the stylesheets
    -- accept cStr prompt 'ConnectString (WITH @ sign, like @Ora8i) > '
    -- connect aqMessBrok/aqMessBrok&cStr
    prompt Creating LoadLob procedure
    create or replace procedure loadLob (imgDir in varchar2,
                                         fname in varchar2,
                                         app_From in varchar2,
                                         app_To in varchar2,
                                         oper in varchar2) as
      tempClob  CLOB;
      fileOnOS  BFILE := bfilename(imgDir, fname);
      ignore    INTEGER;
    begin
      dbms_lob.fileopen(fileOnOS, dbms_lob.file_readonly);
      select xsl
      into tempClob
      from StyleSheets S
      where s.APPFROM = app_From and
            s.APPTO = app_To and
            s.OP = oper
      for UPDATE;
    dbms_output.put_line('External file size is: ' || dbms_lob.getlength(fileOnOS));
    dbms_lob.loadfromfile(tempClob, fileOnOS, dbms_lob.getlength(fileOnOS));
    dbms_lob.fileclose(fileOnOS);
    dbms_output.put_line('Internal CLOB size is: '|| dbms_lob.getlength(tempClob));
    exception
      When Others then
        dbms_output.put_line('Oooops : ' || SQLERRM);
    end LoadLob;
    /
    show errors
    set scan off

    create or replace directory "LOB_DIR" as 'D:\xml817\references\olivier_new'
    /
    insert into applications values ('RETAIL', 'Origin')
    /
    insert into applications values ('SUPPLY', 'Destination')
    /
    insert into tasks values ('SUPPLY', 'NEW ORDER', 'Insert a new Order')
    /
    insert into tasks values ('RETAIL', 'UPDATE ORDER', 'Update an Order Status')
    /

    set serveroutput on
    begin
     insert into StyleSheets values ('RETAIL','SUPPLY','NEW ORDER',EMPTY_CLOB());
     loadLob('LOB_DIR', 'one.xsl', 'RETAIL','SUPPLY','NEW ORDER');
     insert into StyleSheets values ('SUPPLY','RETAIL','UPDATE ORDER',EMPTY_CLOB());
     loadLob('LOB_DIR', 'two.xsl', 'SUPPLY','RETAIL','UPDATE ORDER');
    exception
      when others then
      dbms_output.put_line('Error Occurred : ' || chr(10) || SQLERRM);
    end;
    /
    commit
    /

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