{"id":472,"date":"2015-11-17T09:00:08","date_gmt":"2015-11-17T08:00:08","guid":{"rendered":"http:\/\/www.psinke.nl\/blog\/?p=472"},"modified":"2020-04-03T19:19:50","modified_gmt":"2020-04-03T18:19:50","slug":"refreshing-plsql-regions-with-ajax-in-oracle-apex","status":"publish","type":"post","link":"https:\/\/www.psinke.nl\/blog\/refreshing-plsql-regions-with-ajax-in-oracle-apex\/","title":{"rendered":"refreshing PL\/SQL regions with AJAX in Oracle APEX"},"content":{"rendered":"<div class=\"afa2b3b851443650502c43e85c746c1f\" data-index=\"1\" style=\"float: none; margin:0px;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-4223201538046844\";\r\n\/* standaardblok *\/\r\ngoogle_ad_slot = \"9522527129\";\r\ngoogle_ad_width = 320;\r\ngoogle_ad_height = 50;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n<p>Not all regions in APEX can be refreshed using the PPR mechanism. Especially PL\/SQL regions are difficult to refresh with AJAX.<\/p>\n<p>Although\u2026 the fact that the HTML in a\u00a0PL\/SQL\u00a0region is\u00a0constructed in PL\/SQL may be in your advantage. With the use of a tiny bit of JQuery and an application process, you will be able to update a region asynchronously. I\u2019ll show you how.<\/p>\n<p>First, create a PL\/SQL region and write some PL\/SQL that generates your report using the htp.p and alike functions. Make sure that your code is placed in a database package, let\u2019s say we call the procedure <em>apx_report.emp_report<\/em>. Add a class or ID to your table so you can refer to it from Javascript. In this example, I\u2019ve named the ID \u201cEmpTable\u201d.<\/p>\n<p>Next, create a new application process, type On Demand and name it <em>refreshEmp<\/em>. Call the procedure\u00a0you\u2019ve just compiled in the process source.<\/p>\n<p>Now create a dynamic action that fires on the event you want your region te reload, for instance an on-change.<\/p>\n<p><a class=\"shutterset_4741\" href=\"http:\/\/blog.whitehorses.nl\/wp-content\/uploads\/2015\/11\/dyn-action1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-4742 alignleft\" src=\"http:\/\/blog.whitehorses.nl\/wp-content\/uploads\/2015\/11\/dyn-action1-300x81.jpg\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" srcset=\"http:\/\/blog.whitehorses.nl\/wp-content\/uploads\/2015\/11\/dyn-action1-300x81.jpg 300w, http:\/\/blog.whitehorses.nl\/wp-content\/uploads\/2015\/11\/dyn-action1-540x146.jpg 540w, http:\/\/blog.whitehorses.nl\/wp-content\/uploads\/2015\/11\/dyn-action1.jpg 617w\" alt=\"dyn-action1\" width=\"300\" height=\"81\" \/><\/a><\/p>\n<p>The True action is of type \u201cJavascript\u201d and contains the following code:<\/p>\n<div>\n<div id=\"highlighter_840000\" class=\"syntaxhighlighter nogutter  js\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"js plain\">apex.server.process (<\/code><code class=\"js string\">\"refreshEmp\"<\/code><code class=\"js plain\">, {<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"js spaces\">\u00a0<\/code><code class=\"js plain\">dataType:<\/code><code class=\"js string\">\"text\"<\/code><code class=\"js plain\">, success: <\/code><code class=\"js keyword\">function<\/code><code class=\"js plain\">(pData){ $(<\/code><code class=\"js string\">\"table#EmpTable\"<\/code><code class=\"js plain\">).replaceWith(pData); }});<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>That\u2019s all! The javascript function apex.server.process calls your application process \u201crefreshEmp\u201d, but the trick is to feed the result from the pl\/sql procedure ( put in pData) to a jQuery function, which replaces the content of your current table with the new data. It\u2019s fast, and very non-obtrusive to the user.<\/p>\n<p>There is one last thing. You might notice that other dynamic actions in the region will stop working after the AJAX call. That is because, when refreshing that part of the page, you lose all event handlers\u00a0to the DOM objects there. In jQuery you solve that by replacing the $(\u201c.whatever\u201d).click(function() { \/\/other stuff }) by $(\u201c.whatever\u201d).on(\u2018click\u2019, function() {}) . It\u2019s a tiny bit easier in APEX.<\/p>\n<p>Remember the\u00a0<em>Advanced<\/em> section in your Dynamic Action? There\u2019s a select list called \u201cevent scope\u201d. Go to the dynamic actions that trigger on the AJAX region and change the Event Scope to \u201cDynamic\u201d. Also enter the ID\u00a0of your region (that\u2019s the one you set in the Region properties) as a jQuery Selector, e.g. #EMPREGION. \u00a0You\u2019re set and done now! I think it\u2019s a big improvement over reloading the entire page.<\/p>\n<!--CusAds0-->\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>Not all regions in APEX can be refreshed using the PPR mechanism. Especially PL\/SQL regions are difficult to refresh with AJAX. Although\u2026 the fact that the HTML in a\u00a0PL\/SQL\u00a0region is\u00a0constructed in PL\/SQL may be in your advantage. With the use of a tiny bit of JQuery and an application process, you will be able to&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"link","meta":{"footnotes":""},"categories":[141,3,20],"tags":[136,130,131,143,6,429,389,390,428],"class_list":["post-472","post","type-post","status-publish","format-link","hentry","category-apexapplication-express","category-oracle-blog","category-web-and-beyond","tag-ajax","tag-apex","tag-application-express","tag-jquery","tag-oracle","tag-partial-page-refresh","tag-plsql","tag-ppr","tag-region","post_format-post-format-link"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/comments?post=472"}],"version-history":[{"count":3,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions"}],"predecessor-version":[{"id":687,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions\/687"}],"wp:attachment":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/media?parent=472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/categories?post=472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/tags?post=472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}