My Fusion, SOA, OSB, ADF Learnings

Saturday, 25 July 2020

XSLT TransformationTIP

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.

Sunday, 19 July 2020

Using Global Temporary Tables - GTT

Purpose:

Global temporary tables are used when we have to prepare bulk data in a transaction/session.

The life cycle of the data will be cleared from temporary tables as soon the transaction committed or session closed.

We don't need a separate process to clear the data.

Generally we create purge scripts on permanent tables and schedule them for all practical purposes.

1. Keeping life cycle per transaction commit:


CREATE GLOBAL TEMPORARY TABLE my_tmp_tab (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO my_tmp_tab VALUES (1, 'ONE');

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         0

SQL>

1. Keeping life cycle per session:

CREATE GLOBAL TEMPORARY TABLE my_tmp_tab (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO my_tmp_tab VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN soademo/soademo

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         0

SQL>


Features:
- Data in temporary tables automatically deleted at the end of the database session, even if it ends abnormally.
- Indexes can be created on the temporary tables. The life cycle of the index is same as database session.
- Views can be created on temporary tables and with combination of temporary and permanent tables.
- Truncate statement issued on temporary table will effect only that session.

Thanks to the blog for references.

Wednesday, 1 July 2020

Consuming plsql database TYPE Objects from Oracle SOA

- 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.