لطفا منتظر باشید ...
Creating the Framework
Because your widget company already has an existing application that must be maintained, you first need to set up the database schema, after which you can begin configuring AQ and the SOAP service for your application. This will provide the framework for the application.
Creating the Database Schema
Instead of using XMLType functionality that didn’t exist when your original application was written, you will continue to use relational storage. This type of storage is not complicated, because your company has very straightforward requirements. But before you create the database schema, you need to create the administrator after logging in as SYSDBA, as follows:
CREATE USER poadmin IDENTIFIED BY poadmin
DEFAULT tablespace USERS;
GRANT CONNECT, RESOURCE TO poadmin;
-- Grant AQ Privileges
GRANT aq_administrator_role TO poadmin;
GRANT execute ON dbms_aqadm TO poadmin;
/
Don’t worry about the AQ privileges, as these will be discussed later. Recalling the schema description, the following SQL script will create your database schema:
CREATE TABLE PO(
po_id raw(16) PRIMARY KEY,
sendby_csname VARCHAR2(50),
sendby_street VARCHAR2(100),
sendby_city VARCHAR2(200),
sendby_state VARCHAR2(20),
sendby_zip VARCHAR2(20),
sendby_country VARCHAR2(30),
shipping_csname VARCHAR2(50),
shipping_street VARCHAR2(100),
shipping_city VARCHAR2(200),
shipping_state VARCHAR2(20),
shipping_zip VARCHAR2(20),
shipping_country VARCHAR2(30),
billing_csname VARCHAR2(50),
billing_street VARCHAR2(100),
billing_city VARCHAR2(200),
billing_state VARCHAR2(20),
billing_zip VARCHAR2(20),
billing_country VARCHAR2(30)
);
CREATE TABLE LineItems(
po_id raw(16),
product_id VARCHAR2(10)
);
CREATE TABLE LineItem(
product_id VARCHAR2(10) primary key,
product_name VARCHAR2(10),
product_quantity NUMBER(30),
product_price VARCHAR2(20)
);
CREATE SEQUENCE poobj_seq START WITH 1;
/
This script defines three different addresses: SENDBY for the shipper, SHIPPING for the destination, and BILLING for the invoice. A LINEITEMS table is created to associate the individual PO with its lineitems stored in the LINEITEM table.
Since there are multiple tables, you have to manage a single PO insertion as a multitable insert. This requires additional code and the creation of several objects and an object view. These objects link the columns together into types, and the object view defines the relationship between the tables in a way that they can be queried as one table. The following SQL script creates the first objects you need:
CREATE TYPE address_typ AS OBJECT(
customer_name VARCHAR2(50),
street VARCHAR2(100),
city VARCHAR2(200),
state VARCHAR2(20),
zip VARCHAR2(20),
country VARCHAR2(30)
);
/
CREATE TYPE lineitem_typ AS OBJECT(
product_id VARCHAR2(10),
product_name VARCHAR2(10),
product_quantity NUMBER(30),
product_price VARCHAR2(20)
);
/
CREATE TYPE lineitem_list AS TABLE OF lineitem_typ;
/
CREATE TYPE po_typ AS OBJECT(
po_id raw(16),
sendby_addr address_typ,
shipping_addr address_typ,
billing_addr address_typ,
lineitems lineitem_list
);
/
Note that the final po_typ object is made up of the four object types. This provides the linkage you need to treat the entire PO as a single document insert. Now you can create a PO view to represent the XML structure of the PO, as follows:
CREATE OR REPLACE VIEW purchaseorder OF po_typ
WITH OBJECT OID (po_id) AS
select po_id, address_typ(sendby_csname,
sendby_street,
sendby_city,
sendby_state,
sendby_zip,
sendby_country),
address_typ(shipping_csname,
shipping_street,
shipping_city,
shipping_state,
shipping_zip,
shipping_country),
address_typ(billing_csname,
billing_street,
billing_city,
billing_state,
billing_zip,
billing_country),
cast(multiset(
select lineitem.product_id, product_name,
product_quantity, product_price
from lineitem, lineitems
where lineitems.po_id=po.po_id and
lineitem.product_id=lineitems.product_id
) as lineitem_list
) lineitems
from po;
/
With the database schema created, you need to add one more piece of code to perform inserts. This function is called an INSTEAD OF trigger and is executed by the database in place of the normal DML operation. In this case, because you need to perform inserts, you need to create an INSTEAD OF INSERT trigger, as follows:
CREATE TRIGGER purchaseorder_insert_trigger
INSTEAD OF INSERT ON purchaseorder
FOR EACH ROW
DECLARE
v_lineitems lineitem_list;
v_lineitem lineitem_typ;
v_id RAW(16);
v_num number;
BEGIN
-------------------------------------------------------------
-- Insert into PO table
------------------------------------------------------------
SELECT current_id into v_id from poid where rownum=1;
INSERT INTO po
values (v_id,
:new.sendby_addr.customer_name,
:new.sendby_addr.street,
:new.sendby_addr.city,
:new.sendby_addr.state,
:new.sendby_addr.zip,
:new.sendby_addr.country,
:new.shipping_addr.customer_name,
:new.shipping_addr.street,
:new.shipping_addr.city,
:new.shipping_addr.state,
:new.shipping_addr.zip,
:new.shipping_addr.country,
:new.billing_addr.customer_name,
:new.billing_addr.street,
:new.billing_addr.city,
:new.billing_addr.state,
:new.billing_addr.zip,
:new.billing_addr.country);
----------------------------------------------------------
-- Insert into LineItem and LineItems table
--------------------------------------------------------
v_lineitems := :new.lineitems;
FOR i in 1..v_lineitems.count loop
v_lineitem := v_lineitems(i);
SELECT count(1) INTO v_num FROM lineitem
WHERE product_id = v_lineitem.product_id;
IF v_num = 0 THEN
INSERT INTO lineitem
VALUES(v_lineitem.product_id,
v_lineitem.product_name,
v_lineitem.product_quantity,
v_lineitem.product_price);
END IF;
INSERT INTO lineitems VALUES(v_id, v_lineitem.product_id);
END LOOP;
END;
/
SHOW ERRORS;
The first thing to note is that you attach the trigger to the view, not to the object. The reason you do this is that you are inserting the data in an XML document using XSU, and you must insert into a compliant structure. Next, since there is an ID relationship that is not conveyed within the document, you need to declare variables to store the calculated result. The same holds true for the master-detail relationship in the LINEITEMS and LINEITEM relationship. The actual SQL code is identical to what you would use to insert into these objects manually.
Creating the XML Purchase Orders
Now that you have the database schema set up to accept XML POs in addition to keyed-in ones, you need to create a valid XML PO. You can generate an XML schema for the PO by using XSU’s capability to generate a schema from the object view. The following query will generate an XML schema for the po_typ object:
SET LONG 10000;
SELECT DBMS_XMLSCHEMA.generateschema('POUSER', 'PO_TYP').getClobVal()
FROM DUAL;
This query generates the following schema:
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xsi:schemaLocation="http://xmlns.oracle.com/xdb
http://xmlns.oracle.com/xdb/XDBSchema.xsd">
<xsd:element name="PO_TYP" type="PO_TYPType" xdb:SQLType="PO_TYP"
xdb:SQLSchema="POUSER"/>
<xsd:complexType name="PO_TYPType" xdb:SQLType="PO_TYP"
xdb:SQLSchema="POUSER" xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="PO_ID" type="xsd:hexBinary" xdb:SQLName="PO_ID"
xdb:SQLType="RAW"/>
<xsd:element name="SENDBY_ADDR" type="ADDRESS_TYPType"
xdb:SQLName="SENDBY_ADDR" xdb:SQLSchema="POUSER"
xdb:SQLType="ADDRESS_TYP"/>
<xsd:element name="SHIPPING_ADDR" type="ADDRESS_TYPType"
xdb:SQLName="SHIPPING_ADDR" xdb:SQLSchema="POUSER"
xdb:SQLType="ADDRESS_TYP"/>
<xsd:element name="BILLING_ADDR" type="ADDRESS_TYPType"
xdb:SQLName="BILLING_ADDR" xdb:SQLSchema="POUSER"
xdb:SQLType="ADDRESS_TYP"/>
<xsd:element name="LINEITEMS" type="LINEITEM_TYPType"
maxOccurs="unbounded" minOccurs="0" xdb:SQLName="LINEITEMS"
xdb:SQLCollType="LINEITEM_LIST" xdb:SQLType="LINEITEM_TYP"
xdb:SQLSchema="POUSER" xdb:SQLCollSchema="POUSER"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="ADDRESS_TYPType" xdb:SQLType="ADDRESS_TYP"
xdb:SQLSchema="POUSER" xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="CUSTOMER_NAME" xdb:SQLName="CUSTOMER_NAME"
xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="STREET" xdb:SQLName="STREET" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="100"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="CITY" xdb:SQLName="CITY" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="200"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="STATE" xdb:SQLName="STATE" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="ZIP" xdb:SQLName="ZIP" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="COUNTRY" xdb:SQLName="COUNTRY"
xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="30"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="LINEITEM_TYPType" xdb:SQLType="LINEITEM_TYP"
xdb:SQLSchema="POUSER" xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="PRODUCT_ID" xdb:SQLName="PRODUCT_ID"
xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="PRODUCT_NAME" xdb:SQLName="PRODUCT_NAME"
xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="PRODUCT_QUANTITY" type="xsd:double"
xdb:SQLName="PRODUCT_QUANTITY" xdb:SQLType="NUMBER"/>
<xsd:element name="PRODUCT_PRICE" xdb:SQLName="PRODUCT_PRICE"
xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
In the first part of your application, you will use this schema to validate incoming POs that are submitted as SOAP messages. Once a PO is validated, a confirmation e-mail can be sent to the customer via AQ’s e-mail functionality and the data will be added to your application’s schema. In the second part, you will extend the design to accept POs that do not conform to your application’s schema but do map to it.
