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
/