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.