Camel route to consume web service and call Oracle PLSQL to insert object in database

PLSQL object type in Oracle database…

SQL> desc purchase_order
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PO_ID                                              NUMBER
VENDOR_ID                                          NUMBER
QUANTITY                                           NUMBER
PRICE_PER                                          NUMBER
 
SQL> create or replace type po as object (po_id number, 
  2                                       vendor_id number, 
  3                                       quantity number, 
  4                                       price_per number,
  5                                       member function create_po(p_vendor_id in number, 
  6                                                                 p_quantity in number, 
  7                                                                 p_price_per in number) 
  8                                         return number)
  9  /
 
Type created.
 
SQL> create or replace type body po as
  2    member function create_po(p_vendor_id in number, p_quantity in number, p_price_per in number) return number is
  3      l_po_id number;
  4    begin
  5      select po_seq.nextval into l_po_id from dual;
  6      insert into purchase_order values(l_po_id,p_vendor_id,p_quantity,p_price_per);
  7      commit;
  8      return l_po_id;
  9    end create_po;
10  end;
11  /
 
Type body created.
 
SQL> declare
  2    l_po po := po(null,1,1,1);
  3  begin
  4    dbms_output.put_line(l_po.create_po(100,100,100));
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from purchase_order;
 
     PO_ID  VENDOR_ID   QUANTITY  PRICE_PER
---------- ---------- ---------- ----------
         1        100        100        100
 
SQL>

…and camel route…

     <route id="get-po">
       <from uri="timer://simpleTimer?period=30s"/>
       <to uri="http://cmhlcarchapp01:8080/tradestone/send_po.jsp"/>
       <to uri="jms:queue:testMQ" />
     </route>

     <route id="insert-db">
       <from uri="jms:queue:testMQ" />
       <convertBodyTo type="java.lang.String"/>
       <setHeader headerName="po_id">
         <xpath resultType="java.lang.String">//po_id/text()</xpath>
       </setHeader>
       <setHeader headerName="vendor_id">
         <xpath resultType="java.lang.String">//vendor_id/text()</xpath>
       </setHeader>
       <setHeader headerName="quantity">
         <xpath resultType="java.lang.String">//quantity/text()</xpath>
       </setHeader>
       <setHeader headerName="price_per">
         <xpath resultType="java.lang.String">//price_per/text()</xpath>
       </setHeader>
       <to uri="log:insertLog?showHeaders=true" />
       <to uri="ts:declare l_po po := po(null,1,1,1); l number; begin l := l_po.create_po(:#vendor_id,:#quantity,:#price_per); end;"/>
     </route>