{"id":6133,"date":"2017-02-22T14:20:19","date_gmt":"2017-02-22T19:20:19","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=6133"},"modified":"2021-06-29T19:30:30","modified_gmt":"2021-06-30T00:30:30","slug":"camel-route-to-consume-web-service-and-call-oracle-plsql-to-insert-object-in-database","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2017\/02\/22\/camel-route-to-consume-web-service-and-call-oracle-plsql-to-insert-object-in-database\/","title":{"rendered":""},"content":{"rendered":"<p>Broken out into two routes\u2026<\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li>Consume web service and enqueuer in ActiveMQ<\/li>\n<li>Dequeue from ActiveMQ and persist in PO object in Oracle database<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"1049\">&nbsp;<\/p>\n<p>&lt;route id=&#8221;get-po&#8221;&gt;<\/p>\n<p>&lt;from uri=&#8221;timer:\/\/simpleTimer?period=30s&#8221;\/&gt;<\/p>\n<p>&lt;to uri=&#8221;<a href=\"http:\/\/cmhlcarchapp01:8080\/tradestone\/send_po.jsp\">http:\/\/cmhlcarchapp01:8080\/tradestone\/send_po.jsp<\/a>&#8220;\/&gt;<\/p>\n<p>&lt;to uri=&#8221;jms:queue:testMQ&#8221; \/&gt;<\/p>\n<p>&lt;\/route&gt;<\/p>\n<p>&nbsp;<\/p>\n<p>&lt;route id=&#8221;insert-db&#8221;&gt;<\/p>\n<p>&lt;from uri=&#8221;jms:queue:testMQ&#8221; \/&gt;<\/p>\n<p>&lt;convertBodyTo type=&#8221;java.lang.String&#8221;\/&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;po_id&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/po_id\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;vendor_id&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/vendor_id\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;quantity&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/quantity\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;price_per&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/price_per\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;to uri=&#8221;log:insertLog?showHeaders=true&#8221; \/&gt;<\/p>\n<p>&lt;to uri=&#8221;ts:declare l_po po := po(null,1,1,1); l number; begin l := l_po.create_po(:#vendor_id,:#quantity,:#price_per); end;&#8221;\/&gt;<\/p>\n<p>&lt;\/route&gt;<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>From:<\/strong> Howard, Steve<br \/>\n<strong>Sent:<\/strong> Wednesday, December 21, 2016 12:18 PM<br \/>\n<strong>To:<\/strong> Mehta, Kushal &lt;<a href=\"mailto:kmehta@express.com\">kmehta@express.com<\/a>&gt;<br \/>\n<strong>Subject:<\/strong> RE: Full code<\/p>\n<p>&nbsp;<\/p>\n<p>Thinking of other things before I forget.\u00a0 Tradestone mentioned their web service GET is destructive (they didn\u2019t use that term, but that is what it is).\u00a0 I think they actually flag a message as being picked up, which breaks most standards for idempotence.<\/p>\n<p>&nbsp;<\/p>\n<p>Regardless, to handle conditions where a system fails after we pick it up from TS but before we persist it (RMS, ESB, etc.),we\u00a0 need to either:<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Handle with XA\/two phase commit so all or none work<\/li>\n<li>Have our code be able to handle duplicates on PO\u2019s<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>From:<\/strong> Howard, Steve<br \/>\n<strong>Sent:<\/strong> Wednesday, December 21, 2016 11:53 AM<br \/>\n<strong>To:<\/strong> Mehta, Kushal &lt;<a href=\"mailto:kmehta@express.com\">kmehta@express.com<\/a>&gt;<br \/>\n<strong>Subject:<\/strong> RE: Full code<\/p>\n<p>&nbsp;<\/p>\n<p>This route works, although it simply consumes hardcoded XML in a JSP.\u00a0 It doesn\u2019t enqueue it in the ESB, Camel just routes it.\u00a0 However, it would be trivial to do that.<\/p>\n<p>&nbsp;<\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"1037\">&nbsp;<\/p>\n<p>&lt;route id=&#8221;insert-db&#8221;&gt;<\/p>\n<p>&lt;from uri=&#8221;timer:\/\/simpleTimer?period=30000&#8243;\/&gt;<\/p>\n<p>&lt;to uri=&#8221;<a href=\"http:\/\/cmhlcarchapp01:8080\/tradestone\/send_po.jsp\">http:\/\/cmhlcarchapp01:8080\/tradestone\/send_po.jsp<\/a>&#8220;\/&gt;<\/p>\n<p>&lt;convertBodyTo type=&#8221;java.lang.String&#8221;\/&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;po_id&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/po_id\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;vendor_id&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/vendor_id\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;quantity&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/quantity\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;setHeader headerName=&#8221;price_per&#8221;&gt;<\/p>\n<p>&lt;xpath resultType=&#8221;java.lang.String&#8221;&gt;\/\/price_per\/text()&lt;\/xpath&gt;<\/p>\n<p>&lt;\/setHeader&gt;<\/p>\n<p>&lt;to uri=&#8221;log:insertLog?showHeaders=true&#8221; \/&gt; &lt;!\u2014debug headers\u00e0<\/p>\n<p>&lt;to uri=&#8221;ts:declare l_po po := po(null,1,1,1); l number; begin l := l_po.create_po(:#vendor_id,:#quantity,:#price_per); end;&#8221;\/&gt;<\/p>\n<p>&lt;\/route&gt;<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>From:<\/strong> Mehta, Kushal<br \/>\n<strong>Sent:<\/strong> Tuesday, December 20, 2016 4:49 PM<br \/>\n<strong>To:<\/strong> Howard, Steve &lt;<a href=\"mailto:showard@express.com\">showard@express.com<\/a>&gt;<br \/>\n<strong>Subject:<\/strong> Re: Full code<\/p>\n<p>&nbsp;<\/p>\n<p>We are going to anyway have a package to build RIB object and to incorporate any additional business logic (which will be called by Camel route) on RMS side. With that package in between ESB and RMA will insulate our integration from any Oracle changes in core package.<\/p>\n<p>&nbsp;<\/p>\n<p>Just my thought.<\/p>\n<p>&nbsp;<\/p>\n<p>Thanks,<\/p>\n<p>Kushal Mehta<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Get <a href=\"https:\/\/aka.ms\/o0ukef\">Outlook for iOS<\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>From:<\/strong> Howard, Steve<br \/>\n<strong>Sent:<\/strong> Tuesday, December 20, 2016 4:33:20 PM<br \/>\n<strong>To:<\/strong> Mehta, Kushal<br \/>\n<strong>Subject:<\/strong> Full code<\/p>\n<p>&nbsp;<\/p>\n<p>It occurred to me that we may want to go through the ODR(?) API your Gap compadre mentioned.\u00a0 By doing this, we insulate ourselves from changes in the PLSQL object interface that Oracle may make going forward.<\/p>\n<p>&nbsp;<\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"1223\">&nbsp;<\/p>\n<p>SQL&gt; desc purchase_order<\/p>\n<p>Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Null?\u00a0\u00a0\u00a0 Type<\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n<p>PO_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/p>\n<p>VENDOR_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/p>\n<p>QUANTITY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/p>\n<p>PRICE_PER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/p>\n<p>&nbsp;<\/p>\n<p>SQL&gt; create or replace type po as object (po_id number,<\/p>\n<p>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 vendor_id number,<\/p>\n<p>3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0quantity number,<\/p>\n<p>4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 price_per number,<\/p>\n<p>5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 member function create_po(p_vendor_id in number,<\/p>\n<p>6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0p_quantity in number,<\/p>\n<p>7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_price_per in number)<\/p>\n<p>8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return number)<\/p>\n<p>9\u00a0 \/<\/p>\n<p>&nbsp;<\/p>\n<p>Type created.<\/p>\n<p>&nbsp;<\/p>\n<p>SQL&gt; create or replace type body po as<\/p>\n<p>2\u00a0\u00a0\u00a0 member function create_po(p_vendor_id in number, p_quantity in number, p_price_per in number) return number is<\/p>\n<p>3\u00a0\u00a0\u00a0\u00a0\u00a0 l_po_id number;<\/p>\n<p>4\u00a0\u00a0\u00a0 begin<\/p>\n<p>5\u00a0\u00a0\u00a0\u00a0\u00a0 select po_seq.nextval into l_po_id from dual;<\/p>\n<p>6\u00a0\u00a0\u00a0\u00a0\u00a0 insert into purchase_order values(l_po_id,p_vendor_id,p_quantity,p_price_per);<\/p>\n<p>7\u00a0\u00a0\u00a0\u00a0\u00a0 commit;<\/p>\n<p>8\u00a0\u00a0\u00a0\u00a0\u00a0 return l_po_id;<\/p>\n<p>9\u00a0\u00a0\u00a0 end create_po;<\/p>\n<p>10\u00a0 end;<\/p>\n<p>11\u00a0 \/<\/p>\n<p>&nbsp;<\/p>\n<p>Type body created.<\/p>\n<p>&nbsp;<\/p>\n<p>SQL&gt; declare<\/p>\n<p>2\u00a0\u00a0\u00a0 l_po po := po(null,1,1,1);<\/p>\n<p>3\u00a0 begin<\/p>\n<p>4\u00a0\u00a0\u00a0 dbms_output.put_line(l_po.create_po(100,100,100));<\/p>\n<p>5\u00a0 end;<\/p>\n<p>6\u00a0 \/<\/p>\n<p>&nbsp;<\/p>\n<p>PL\/SQL procedure successfully completed.<\/p>\n<p>&nbsp;<\/p>\n<p>SQL&gt; select * from purchase_order;<\/p>\n<p>&nbsp;<\/p>\n<p>PO_ID\u00a0 VENDOR_ID\u00a0\u00a0 QUANTITY\u00a0 PRICE_PER<\/p>\n<p>&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;-<\/p>\n<p>1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100<\/p>\n<p>&nbsp;<\/p>\n<p>SQL&gt;<\/p>\n<p>&nbsp;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Broken out into two routes\u2026 &nbsp; Consume web service and enqueuer in ActiveMQ Dequeue from ActiveMQ and persist in PO object in Oracle database &nbsp; &nbsp; &lt;route id=&#8221;get-po&#8221;&gt; &lt;from uri=&#8221;timer:\/\/simpleTimer?period=30s&#8221;\/&gt; &lt;to uri=&#8221;http:\/\/cmhlcarchapp01:8080\/tradestone\/send_po.jsp&#8220;\/&gt; &lt;to uri=&#8221;jms:queue:testMQ&#8221; \/&gt; &lt;\/route&gt; &nbsp; &lt;route id=&#8221;insert-db&#8221;&gt; &lt;from&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2017\/02\/22\/camel-route-to-consume-web-service-and-call-oracle-plsql-to-insert-object-in-database\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[70,69,22,29],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6133"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=6133"}],"version-history":[{"count":3,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6133\/revisions"}],"predecessor-version":[{"id":6944,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6133\/revisions\/6944"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=6133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=6133"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=6133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}