My Fusion, SOA, OSB, ADF Learnings: soa

Purpose:

While reviewing one of implementation issue, I have observed a different behavior wrt XSLT transformation.

Thought of sharing.

Issue:

The previous data of the XSLT target variable will be reset/cleared. The target variable will be updated with the result of the Transformation.

Fix/workaround:

1. Use XSLT target variable also as one of its input variable.

2. Copy previous data from its own variable inside XSLT to target.

3. Assign the new data from other XSLT input variables as needed.

Implementation:

The below bpel has two transformations conditionally.

Else Block

DemoXsltTransformComposite/SOA/Transformations/UOM_Amount_status_date.xsl

If Block

DemoXsltTransformComposite/SOA/Transformations/UOM_Amount_Status_Date_2.xsl

Input:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
        <soap:Body>
    <ns1:PurchaseOrder xmlns:ns1="http://xmlns.oracle.com/apps/scm/orderManagement/purchaseOrder/service/types">
        <ns1:orderId>10</ns1:orderId>
        <ns1:orderedQty>10</ns1:orderedQty>
        <ns1:orderedUOM>EA</ns1:orderedUOM>
        <ns1:amount>100</ns1:amount>
        <ns1:status>XSLT</ns1:status>
        <ns1:orderedDate>2019-08-25</ns1:orderedDate>
        </ns1:PurchaseOrder>
    </soap:Body>
</soap:Envelope>

Else Block result:

OrderId, OrderedQty were missing as data cleared by default xslt transformation.

If Block result:

OrderId, OrderedQty were retained.

Please find the project here.

- The idea of this article is to consume single/multiple purchase orders from SOA to database table using Record/type objects.

if the Purchase order gets updated in database table if already having same purchase order id, otherwise a new record will be created.

Please find the below steps.

1. create database object types

1.1:

create or replace type PURCHASEORDER_TYPE as OBJECT (ORDER_ID NUMBER,
ORDERED_QTY          NUMBER,
ORDERED_UOM          VARCHAR2(6),
AMOUNT               NUMBER,
STATUS               VARCHAR2(10),
ORDER_DATE             DATE);

1.2:

create or replace TYPE PURCHASEORDER_TBL IS TABLE of PURCHASEORDER_TYPE;

2. Create a package header with procedures for creating single and multiple Purchase Orders

create or replace package soademo_pk

as
procedure createPO(p_purchaseOrder IN PurchaseOrder_type);
procedure createPOs(p_pos IN purchaseorder_tbl);

END soademo_pk;

procedure:

- createPO -> for creating single purchase order

- createPOs -> for creating multiple purchase orders.

3. Create a package body and implement the procedures in package header.

create or replace PACKAGE BODY SOADEMO_PK AS

procedure createPO(p_purchaseOrder IN PurchaseOrder_type)
AS
l_count integer;
BEGIN
    DBMS_OUTPUT.PUT_LINE('inside createPO !!');
    DBMS_OUTPUT.PUT_LINE('order_id: ' || p_purchaseOrder.order_id);
    DBMS_OUTPUT.PUT_LINE('ORDERED_QTY: ' || p_purchaseOrder.ORDERED_QTY);
    DBMS_OUTPUT.PUT_LINE('ORDERED_UOM: ' || p_purchaseOrder.ORDERED_UOM);
    DBMS_OUTPUT.PUT_LINE('AMOUNT: ' || p_purchaseOrder.AMOUNT);
    DBMS_OUTPUT.PUT_LINE('STATUS: ' || p_purchaseOrder.STATUS);
    DBMS_OUTPUT.PUT_LINE('ORDER_DATE: ' || p_purchaseOrder.ORDER_DATE);
    /*
    -- insert the purchase order directly.
    insert into purchase_order
    select p_purchaseOrder.order_id,
            p_purchaseOrder.ORDERED_QTY,
            p_purchaseOrder.ORDERED_UOM,
            p_purchaseOrder.AMOUNT,
            p_purchaseOrder.STATUS,
            p_purchaseOrder.ORDER_DATE from dual;
    */
-- merge the purchase order.
    MERGE INTO PURCHASE_ORDER po
    USING (select p_purchaseOrder.order_id as order_id,
            p_purchaseOrder.ORDERED_QTY as ORDERED_QTY,
            p_purchaseOrder.ORDERED_UOM as ORDERED_UOM,
            p_purchaseOrder.AMOUNT as AMOUNT,
            p_purchaseOrder.STATUS as status,
            p_purchaseOrder.ORDER_DATE as order_date from dual) pt
    ON (po.order_id=pt.order_id)
    when matched then
        update set
                ordered_qty = pt.ordered_qty,
                ordered_uom = pt.ordered_uom,
                amount      = pt.amount,
                status      = pt.status,
                order_date = pt.order_date

    when not matched then
        insert (order_id,ORDERED_QTY,ORDERED_UOM,AMOUNT,STATUS,ORDER_DATE)
        values( p_purchaseOrder.order_id,
            p_purchaseOrder.ORDERED_QTY,
            p_purchaseOrder.ORDERED_UOM,
            p_purchaseOrder.AMOUNT,
            p_purchaseOrder.STATUS,
            p_purchaseOrder.ORDER_DATE);
    l_count := sql%rowcount;
    DBMS_OUTPUT.PUT_LINE('l_count: ' || l_count);

    EXCEPTION
    when OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
        DBMS_OUTPUT.PUT_LINE('SQLCODE' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('sqlerrm' || SQLERRM);
END createPO;

procedure createPOs(p_pos IN purchaseorder_tbl)
AS
BEGIN
    -- TODO: Implementation required for procedure SOADEMO_PK.createPOs
    MERGE INTO PURCHASE_ORDER po
    USING(select * from table(p_pos)) pt
    ON (po.order_id=pt.order_id)
    when matched then
        update set
                ordered_qty = pt.ordered_qty,
                ordered_uom = pt.ordered_uom,
                amount      = pt.amount,
                status      = pt.status,
                order_date = pt.order_date

    when not matched then
        insert (order_id,ORDERED_QTY,ORDERED_UOM,AMOUNT,STATUS,ORDER_DATE)
        values( pt.order_id,
                pt.ORDERED_QTY,
                pt.ORDERED_UOM,
                pt.AMOUNT,
                pt.STATUS,
                pt.ORDER_DATE);
    NULL;
END createPOs;

END SOADEMO_PK;

4. create a datasource in WebLogic console.

5. create DB Adapter connection.

JNDI:

DB Adapter, Datasource association

6. Consume database procedures from SOA using DB adapter.

please find the project here.

My Fusion, SOA, OSB, ADF Learnings

Saturday, 25 July 2020

XSLT TransformationTIP

Wednesday, 1 July 2020

Consuming plsql database TYPE Objects from Oracle SOA