tag:blogger.com,1999:blog-52155514878169811402024-03-13T19:37:57.267-07:00ORA-00001: Unique constraint violatedRandom ramblings and raves of relational relevance!Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger126125tag:blogger.com,1999:blog-5215551487816981140.post-46732818950366146732023-10-11T01:51:00.000-07:002023-10-11T01:51:00.670-07:00Joel Kallman Day 2023: APEX Automated Backups #joelkallmanday<p> I'm writing a short blog post today as part of the <a href="https://oracle-base.com/blog/2023/10/01/joel-kallman-day-2023-announcement/">Joel Kallman Day 2023</a>.</p><p><br /></p><p>Some time ago (I think it was around the APEX v20.1 release), APEX got a new built-in backup feature that takes an automatic backup of your applications each day. Only applications that have changed will be backed up, and a maximum of 30 days of backups are kept (this is configurable, it can be disabled, or you can set a retention period of between 1 and 30 days).</p><p>You can view the list of backup by clicking on "Manage Backups" on the Application Builder main page. <br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgp9Aji_E5yuczGXMv5_nHvzY6HGkfro-cXrBk3E2yETwHS-6tR-EM3FdxPmyQm4JldA71_PIDE-yKuAFvXI7JBj1JHhGpQdhmN3LqozjIUk-sEbghrSoSHWj2EI0eVX6Y772z1sypfdrTcrv70JzEvK8RROAVChuCB4YDx_YqAC83l1Mm4Hep1B6HGR5H/s1187/Capture2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="628" data-original-width="1187" height="338" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgp9Aji_E5yuczGXMv5_nHvzY6HGkfro-cXrBk3E2yETwHS-6tR-EM3FdxPmyQm4JldA71_PIDE-yKuAFvXI7JBj1JHhGpQdhmN3LqozjIUk-sEbghrSoSHWj2EI0eVX6Y772z1sypfdrTcrv70JzEvK8RROAVChuCB4YDx_YqAC83l1Mm4Hep1B6HGR5H/w640-h338/Capture2.JPG" width="640" /></a></div><p></p><p> </p><p>This gives you a list of your applications, and information about when the last backup was taken, and the number of backups that have been taken so far (remember that this only increments if there have been actual changes in the application, and that there is a maximum number of backups per app, typically 25 or 30).<br /></p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmX6Nn3NATs1QhghjG51EG-VPRVeQwoVWmg2CS5EIFdAzd40KZ3vUgvFQAit1ltVupvSK6SsyEME8-jRqnyRtUht9hHiBQiIyUmUMLaz6XPtnrEms2A8qiy1zBE_12tzoUdkbM5eSwDvNA0q3YIijDna1-TRCGUcXwte-caPteyYT1u_QU40fhN-O_nfNz/s990/Capture3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="687" data-original-width="990" height="444" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmX6Nn3NATs1QhghjG51EG-VPRVeQwoVWmg2CS5EIFdAzd40KZ3vUgvFQAit1ltVupvSK6SsyEME8-jRqnyRtUht9hHiBQiIyUmUMLaz6XPtnrEms2A8qiy1zBE_12tzoUdkbM5eSwDvNA0q3YIijDna1-TRCGUcXwte-caPteyYT1u_QU40fhN-O_nfNz/w640-h444/Capture3.JPG" width="640" /></a></div><p></p><p> </p><p>By clicking on the application name, you can see the details of each backup, including when it was taken. There is a dropdown menu which allows you to see additional details of each backup (what was changed in the app that caused it to be backed up).</p><p>From here, you can also download the app, or restore it (either as a new app, or to replace the existing version of the app). <br /></p><p><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlgoMGUNNB-jaRXkLLx0-X7p_PPIavgsZsibVwTkfz9-TwjEKTAbLUyLXnRuLvM-D-Dz2M7cm39FR5tY5KIlluDNYKE3qA46MVw71M4Ks81mYHQyhYEckRL8N5Nio9TdaUi2H8h0UL8QAalCa7Oy8dbn1ppaZzw1IrZsfEtQ-bkq-eDaLHy96plu0qpMNH/s1090/Capture4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="382" data-original-width="1090" height="224" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlgoMGUNNB-jaRXkLLx0-X7p_PPIavgsZsibVwTkfz9-TwjEKTAbLUyLXnRuLvM-D-Dz2M7cm39FR5tY5KIlluDNYKE3qA46MVw71M4Ks81mYHQyhYEckRL8N5Nio9TdaUi2H8h0UL8QAalCa7Oy8dbn1ppaZzw1IrZsfEtQ-bkq-eDaLHy96plu0qpMNH/w640-h224/Capture4.JPG" width="640" /></a></div><br /> <p></p><p>This automated backup feature is very convenient, and allows you to recover from mistakes by rolling back to a previous version of the app.</p><p><b>IMPORTANT NOTE:</b> You should not rely on this backup feature as the only way to keep your application definitions safe. Remember that the backups are stored in the same database as the rest of your development environment, so if something happens to that database and you are not able to recover it, you will not be able to restore your application backups either. I would only rely on this backup feature to quickly recover from mistakes, but the recommended way to keep your application definitions is still to do a (manual or automated) export of the app to a file (or split into multiple files), and then to store those files in a version control system like Git (and backup your Git repos!).<br /></p><p><br /></p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-88302666062975964502023-02-27T23:54:00.003-08:002023-02-28T05:32:31.516-08:00Restrict access to APEX Builder based on client IP when using Apache and ORDS<p> When running Oracle Application Express (APEX) in production, the best practice is to deploy the "Runtime-Only" environment, instead of a "Full Development" environment:</p><p>From <a href="https://docs.oracle.com/en/database/oracle/application-express/21.2/htmig/about-apex-runtime-environment.html">the docs</a>:</p><p></p><blockquote><p>As with any software development life cycle, Oracle strongly recommends that you have different environments for development, testing/QA, and production. For testing and production instances, Oracle APEX supports the ability to install just a runtime version of Oracle APEX. This runtime environment minimizes the installed footprint and privileges and improves application security since in a runtime instance developers cannot inadvertently update a production application.<br /><br />An Oracle APEX runtime environment enables you to run production applications, but it does not provide a Web interface for administration. A runtime environment only includes the packages necessary to run your application, making it a more hardened environment. You administer the Oracle APEX runtime environment using SQL*Plus or SQL Developer and the APEX_INSTANCE_ADMIN API. <br /></p><p></p></blockquote><p><br /></p><p>If you DO NOT follow this best practice, and for whatever reason decide to install a full development environment in production, at least you can harden it somewhat by restricting access to the APEX Builder and internal administration apps to your own IP address.</p><p>You can do this by setting the APEX instance parameter "RESTRICT_IP_RANGE". Again, according to <a href="https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/Available-Parameter-Values.html#GUID-75DCF658-5A76-4E81-B12D-04E254A3D80A">the docs</a>:</p><p></p><blockquote><p>To restrict access to the APEX development environment and Administration Services to a specific range of IP addresses, enter a comma-delimited list of IP addresses. If necessary, you can use an asterisk (*) as a wildcard, but do not include additional numeric values after wildcard characters. For example, 138.*.41.2 is not a valid value.</p></blockquote><p> </p><p>However, the above will not work if you are using a proxy (such as Apache HTTPD) in front of ORDS. The reason is that your real client IP (for example 1.2.3.4) is not forwarded from Apache to ORDS, so to APEX the "REMOTE_ADDR" appears as the server's address (typically "127.0.0.1" if Apache and ORDS are on the same machine), and not the real client IP.</p><p><br /></p><p>To work around this, there is another option available from APEX 20.1: The instance parameter "RESTRICT_DEV_HEADER". Note that this instance parameter is undocumented at the time of this writing, but Christian Neumuller on the APEX team has confirmed that this is a doc bug that will be fixed soon.</p><p>RESTRICT_DEV_HEADER works by setting a custom header in your Apache setup, which is then forwarded to ORDS and APEX. If this header is present (the actual value does not matter), the APEX Builder and internal administration apps are blocked and return a "403 Forbidden" error if you try to access them.</p><p>To get this working, you have to modify your Apache configuration, check the real client IP, and set the header accordingly:</p><script src="https://gist.github.com/mortenbra/f0596daebe04bc7f16245c929073c94d.js"></script><p> </p><p>Remember to restart Apache to pick up the new settings. <br /></p><p>Then set up the following in APEX:<br /></p><script src="https://gist.github.com/mortenbra/bfdacd9c790522e7e7962b566ffd9152.js"></script><p><br /></p><p>Then try to access your APEX Builder from a non-trusted IP, and you should get a "403 Forbidden" error.<br /></p><p><br /></p><p>References:</p><ul style="text-align: left;"><li>APEX_INSTANCE_ADMIN package, available parameters: <a href="https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/Available-Parameter-Values.html#GUID-75DCF658-5A76-4E81-B12D-04E254A3D80A">docs</a> <br /></li><li>Apache RequestHeader: <a href="https://httpd.apache.org/docs/2.4/mod/mod_headers.html#requestheader">docs</a> <br /></li><li>If/Then/Else syntax in Apache: <a href="https://blogs.apache.org/httpd/entry/new_in_httpd_2_4">Blog post</a> and <a href="https://httpd.apache.org/docs/2.4/de/expr.html#unnop">official docs</a><br /></li></ul><p> </p><p> </p><p> </p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2tag:blogger.com,1999:blog-5215551487816981140.post-82747640157170293562022-12-19T10:05:00.004-08:002022-12-19T23:42:21.554-08:00Dynamic Content Region in Oracle APEX 22.2<p>There's a <a href="https://apex.oracle.com/en/platform/features/whats-new-222/">new version of Oracle Application Express (APEX)</a> in town, and one of the new "features" is a new region type called "Dynamic Content" that allows you to output content using PL/SQL.</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikBKxEMcE0ds7el-68eAyqU3WfxtBPIl0vng0tkV5m8NV1DJmRpjY3PJhFsShEb11NO8CiI9gI9Z21WFAnH3HMmvCfYjSkuXXrB1newXsTxmjZV-VVFyLDTPUzgn6VKpZ79VTlzjtuyqH1LKHLKPzxD_aUsWYF-eHSqkOU1-i9LOFyBPwoBFtFOwPq7Q/s1311/Capture5.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="450" data-original-width="1311" height="220" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikBKxEMcE0ds7el-68eAyqU3WfxtBPIl0vng0tkV5m8NV1DJmRpjY3PJhFsShEb11NO8CiI9gI9Z21WFAnH3HMmvCfYjSkuXXrB1newXsTxmjZV-VVFyLDTPUzgn6VKpZ79VTlzjtuyqH1LKHLKPzxD_aUsWYF-eHSqkOU1-i9LOFyBPwoBFtFOwPq7Q/w640-h220/Capture5.JPG" width="640" /></a></div><br />What happened to the existing "PL/SQL Dynamic Content" region type that does exactly this, you might ask? It's still there, but now it's marked as "Legacy" and requires an extra mouse click just to be able to select it, and to make you feel bad for using it.<p></p><p>So why a new region type to replace the old one? The stated reason is to allow the region to be refreshed, but as readers of my blog will know, there are already ways of doing that, as demonstrated in <a href="https://ora-00001.blogspot.com/2012/11/apex-plugin-execute-plsql-code-and-return-content-to-page.html">this region plugin that I released in 2012</a>.</p><p>In order to make the region refreshable, the APEX team had to change the way you use it. You no longer call the "HTP" package to add your content to a global output buffer. Instead, you have to concatenate and return your content as a string (varchar2 or clob). The way it's suggested in the online help is like this:<br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxTbJjBbasHNbIqGS6o9QQFbht4VANOS5YYpkE0ERLogpjgDF_bcYdeyeWiqCa4zudubPW9NesIiQDEHHWOk3jy33APosTeqpDUBAm_s1xSg-2moot1R58OqFJf3JeKCwkSqVaxCnEBwSp6CV3bNNElUZIYkgBr1jzR_WmQXLS5K22lttDCEb4KyhLTg/s764/Capture6.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="428" data-original-width="764" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxTbJjBbasHNbIqGS6o9QQFbht4VANOS5YYpkE0ERLogpjgDF_bcYdeyeWiqCa4zudubPW9NesIiQDEHHWOk3jy33APosTeqpDUBAm_s1xSg-2moot1R58OqFJf3JeKCwkSqVaxCnEBwSp6CV3bNNElUZIYkgBr1jzR_WmQXLS5K22lttDCEb4KyhLTg/w640-h358/Capture6.JPG" width="640" /></a></div><br />There are several things I don't like about this:<p></p><ul style="text-align: left;"><li>You have to declare a local variable</li><li>You have to clutter the code with concatenation</li><li>You cannot split your code into subroutines unless you pass around your local variable holding the final result <br /></li><li>You have to rewrite every piece of existing code (if you want to use the new region type)<br /></li></ul><p>Instead, I want to be able to (continue to) write code like this:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh89jFvx5F0qS5clgl3jrbeXldFNS_zDPSGp54IZO5-tn-ViIsLiyho4y241TG0oUXX2BzFvgtyUEu9XqY3Ft3E1sm_92YPEzRoUwncTKnsZldES9dehPXV-HGBOUxlANIrmiUOhxPjMV5iYqAJeSnE_v8ijruWk9JzJ7kw3bEDdRhJG8r2eTNbSNjUNw/s1094/Capture7.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="297" data-original-width="1094" height="174" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh89jFvx5F0qS5clgl3jrbeXldFNS_zDPSGp54IZO5-tn-ViIsLiyho4y241TG0oUXX2BzFvgtyUEu9XqY3Ft3E1sm_92YPEzRoUwncTKnsZldES9dehPXV-HGBOUxlANIrmiUOhxPjMV5iYqAJeSnE_v8ijruWk9JzJ7kw3bEDdRhJG8r2eTNbSNjUNw/w640-h174/Capture7.JPG" width="640" /></a></div><p>Notice the use of "apex_htp" in the above code; this is a package that does not (yet) exist, but read on! <br /></p><p>Or, to give a more complex and real-world example which uses several subprocedures that all write out to the global buffer:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh90vyBVO21u1dYlWHIqrApH_cZIAdK9ruk6Jvf262RAEsQVEPE9kYRqyqwooHWiD6Y-EZQ826BpNE5io1MZKtCflh9A9UYZZPhyhZa-teTGAM7Oy9WR_yY7sSfxLH9wQYXvr71j0IoV1sGXTQm1JGZ3lW82f6mf5ktVph4I2SjVIOQxbYRuDqfrxw5pQ/s1171/Capture8.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="776" data-original-width="1171" height="424" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh90vyBVO21u1dYlWHIqrApH_cZIAdK9ruk6Jvf262RAEsQVEPE9kYRqyqwooHWiD6Y-EZQ826BpNE5io1MZKtCflh9A9UYZZPhyhZa-teTGAM7Oy9WR_yY7sSfxLH9wQYXvr71j0IoV1sGXTQm1JGZ3lW82f6mf5ktVph4I2SjVIOQxbYRuDqfrxw5pQ/w640-h424/Capture8.JPG" width="640" /></a></div><br /><p></p><h2 style="text-align: left;">What I think the APEX team should have done</h2><p>When the APEX team decided that they needed a new region type with a different architecture, instead of just throwing the existing region type into the murky "legacy" territory, they should have made the transition as smooth as possible, by offering a drop-in replacement to the existing HTP package.</p><p>I immediately <a href="https://twitter.com/mortenbraten/status/1579765199367974912">suggested this</a> when I saw the latest APEX version, and as I said on Twitter I think it's a strange decision to hide away the existing method as "legacy" when you are replacing it with a worse dev experience that is not yet fully baked.<br /></p><p>To fix this, <a href="https://apexapps.oracle.com/pls/apex/apex_pm/fr/r/FR-2896">I've submitted an idea to the APEX Ideas app</a>, to suggest that the APEX team include a new package in the next APEX version that can be used as a drop-in replacement for the HTP package. Using this package with existing code should be as easy as replacing "htp.p" with "apex_htp.p" and adding a single RETURN statement at the end of the region process code.<br /></p><p>The spec for such a package could look like the below image. I've already made a working prototype for myself, calling it "xtp", and with a (temporary) synonym I can also call it using "apex_htp", but the point of this blog post (and the submitted idea) is to get this included in APEX itself, where it should have been from day 1 of APEX 22.2. <a href="https://apexapps.oracle.com/pls/apex/apex_pm/fr/r/FR-2896">Go and vote today for this idea</a> if you are using PL/SQL Dynamic Content regions!<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJZ2V-znGLWaD9UdCDmkDw1-PnS9lOtQcKiMD8SRcE9SrGH87ZdC9JS-DSbcYFmjCsmT2k2wiR0coB8WRNndtZ_rO4fuqgM2I8nlrVot58MUWeDUn_Ydgmil-Xdpg4UdCZhiXV0HJtjiExv4avtBoDmRCNa-w4jyJWiznr0PVtG71SpRoY1xAB7LjV0Q/s654/Capture4.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="654" data-original-width="620" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJZ2V-znGLWaD9UdCDmkDw1-PnS9lOtQcKiMD8SRcE9SrGH87ZdC9JS-DSbcYFmjCsmT2k2wiR0coB8WRNndtZ_rO4fuqgM2I8nlrVot58MUWeDUn_Ydgmil-Xdpg4UdCZhiXV0HJtjiExv4avtBoDmRCNa-w4jyJWiznr0PVtG71SpRoY1xAB7LjV0Q/w606-h640/Capture4.JPG" width="606" /></a></div><br /><p>For those interested in the (just a quick prototype) package body, <a href="https://github.com/mortenbra/plsql-sample-code/blob/main/xtp.pkb">here it is</a> on GitHub.<br /></p><p><br /></p><p><br /></p><p><br /></p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-77398053176138210822022-10-11T02:11:00.004-07:002022-10-11T02:15:26.041-07:00Joel Kallman Day 2022: APEX Caching #joelkallmanday<p> I'm writing a short blog post today as part of the <a href="https://oracle-base.com/blog/2022/09/27/joel-kallman-day-2022-announcement/">Joel Kallman Day</a>.</p><p></p><p><br /></p><p>"Server-Side Caching" is probably an under-utilized feature of Oracle APEX. It's very easy to use, and can have an amazing impact on the performance of your APEX application, and make it (even) more scalable.<br /></p><p> </p><h3 style="text-align: left;">What is APEX Server Caching? <br /></h3><p>Here's how the help text explains it:<br /></p><p></p><p>"<i>Select how the Oracle APEX engine can cache HTML text on the server side. If caching can be used, it emits the cached result instead of computing it again. This cuts down on expensive computation time on the server. This feature is unrelated to the browser's caching capabilities.</i>"<br /></p><p>In other words, if you have a report, or a chart, or some content generated dynamically by PL/SQL, the APEX engine can store the final result (the HTML text) of that content and just display that instead of having to re-run a query or execute your code.<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0HxHjrUydcwHfHLLTFoYK0BZijm2a4Jqc2evOAyoc42fEX1z9SguXwynsbHS1V2KkgbKdIKO-Hs9mqbLTvLlib0nQalaQ5BlQhhw2WSFkUhDP3wV9JYvI5K6G-lUbhbCB0fh_DkTvyn9FFcn-HOjOo9IUopFOe-InQ52tzbgzg8CHKXcHF3hGro0sig/s463/Capture15.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="366" data-original-width="463" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0HxHjrUydcwHfHLLTFoYK0BZijm2a4Jqc2evOAyoc42fEX1z9SguXwynsbHS1V2KkgbKdIKO-Hs9mqbLTvLlib0nQalaQ5BlQhhw2WSFkUhDP3wV9JYvI5K6G-lUbhbCB0fh_DkTvyn9FFcn-HOjOo9IUopFOe-InQ52tzbgzg8CHKXcHF3hGro0sig/s16000/Capture15.JPG" /></a></div><br /><p><br /></p><h3 style="text-align: left;">What can be cached?<br /></h3><p>You can cache whole Pages, or individual Regions.</p><p> </p><h3 style="text-align: left;">Cache settings</h3><p>You can choose how to cache (by session, by user, or for everyone) and how long to cache. You can also select one or more items; if the value of these items change, the cache will automatically be invalidated and refreshed on the next view. You can also combine this with a more complex Condition.<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhU8K7EOwImsHePebd3z87Zxe5xGUoK_m2Blswb19ZJYqMvVO_IyXB2ML8tRYVKEK3hCkCM3kAzgPE-plKzdLX2utBd6okWyCDCwBujHZNVtNJH_hbCqlhy788M3610RzmIyTupqrkfLkPWZmkkoMK8QotCC-m5m3VxeiQJgxv447z6aIfSGqWtwi6gdA/s612/Capture16.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="192" data-original-width="612" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhU8K7EOwImsHePebd3z87Zxe5xGUoK_m2Blswb19ZJYqMvVO_IyXB2ML8tRYVKEK3hCkCM3kAzgPE-plKzdLX2utBd6okWyCDCwBujHZNVtNJH_hbCqlhy788M3610RzmIyTupqrkfLkPWZmkkoMK8QotCC-m5m3VxeiQJgxv447z6aIfSGqWtwi6gdA/s16000/Capture16.JPG" /></a></div><br /><p><br /></p><h3 style="text-align: left;">APEX API packages related to caching<br /></h3><p>The following packages are related to caching:</p><ul style="text-align: left;"><li>APEX_PAGE.PURGE_CACHE (<a href="https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/PURGE_CACHE-Procedure.html#GUID-DA08D745-1D62-4DFE-A6CF-2F4E0D1B316E">doc link</a>)</li><li>APEX_REGION.PURGE_CACHE (<a href="https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/PURGE_CACHE-Procedure-2.html#GUID-9FC1EBE9-C11B-4CB7-9477-F667F9E1021A">doc link</a>)</li><li>APEX_UTIL.CACHE_* (<a href="https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/APEX_UTIL.html#GUID-5ECE5C10-1A88-4D37-8A7D-C51925ADB2B9">doc link</a>)<br /></li></ul><p></p><p><br /></p><h3 style="text-align: left;">APEX Dictionary View</h3><p>The view APEX_APPLICATION_CACHING gives you information about caching, useful for debugging and monitoring.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-XYKzP2LBM9OXPCZVDRCVGLTA2ejoM6pGCQd5xf9NKxMT7lhOUgpWZho-nwg5Z-1_aO9gZJx9RB96D66Opdrb90BNhBv4yisZPH2HAEMCItZshTTg7CL8zj2nzems2E4NwtoNmE_wvYKAYxqtxW_x_2sPeWHp5qRh5N5g3GQA_G-LcYjS15F7KcT1Bg/s585/Capture17.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="585" data-original-width="456" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-XYKzP2LBM9OXPCZVDRCVGLTA2ejoM6pGCQd5xf9NKxMT7lhOUgpWZho-nwg5Z-1_aO9gZJx9RB96D66Opdrb90BNhBv4yisZPH2HAEMCItZshTTg7CL8zj2nzems2E4NwtoNmE_wvYKAYxqtxW_x_2sPeWHp5qRh5N5g3GQA_G-LcYjS15F7KcT1Bg/s16000/Capture17.JPG" /></a></div><br /><p><br /></p><p><br /></p><h3 style="text-align: left;">Bonus: Tip</h3><p>On pages where you use caching, create a button to clear the cache immediately (and perhaps conditionally display the button for admins/developers). This will make your life as a developer easier. Remember that any changes you as a developer make to reports, etc. will no longer be immediately visible if they are cached!<br /></p><p><br /></p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-32939615115671626432021-10-11T05:42:00.001-07:002021-10-11T06:51:36.128-07:00Joel Kallman Day 2021: Oracle Database Express Edition (XE) 21c<p>The "Oracle Tech Network Appreciation Day" has been renamed to "<a href="https://oracle-base.com/blog/2021/10/04/joel-kallman-day-announcement/">Joel Kallman Day</a>" in honor of Joel Kallman, who along with Mike Hichwa was one of the original developers of Oracle Application Express (APEX). Joel suddenly and tragically passed away during the pandemic earlier this year.</p><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSbPTnNKDDvNjZSYTz4t2B3jt_4gOAb6ZXfKnaDFCmWpWAiQgvDICm4AlOmlWsKBfOBhFf39TL7i01e4pqG6Ht0pYnt2sRSR_E_aV0Xl3XNT5Wxnt6cV8u6X8HX7iWK4_hR6WZS20_qpW3/s2048/joel.jpg" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="1596" data-original-width="2048" height="498" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSbPTnNKDDvNjZSYTz4t2B3jt_4gOAb6ZXfKnaDFCmWpWAiQgvDICm4AlOmlWsKBfOBhFf39TL7i01e4pqG6Ht0pYnt2sRSR_E_aV0Xl3XNT5Wxnt6cV8u6X8HX7iWK4_hR6WZS20_qpW3/w640-h498/joel.jpg" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Joel at APEX World in Rotterdam, 2019<br /></td></tr></tbody></table><p> </p><p>The "Joel Kallman Day" is about appreciating some piece of Oracle tech. In 2016, I wrote about the free <a href="https://ora-00001.blogspot.com/2016/10/otn-appreciation-day-oracle-express-edition-xe.html">Oracle Express Edition (XE) database</a>. It just so happens that the latest Oracle XE version, 21c, was released for Linux a month ago, and for Windows just a few days ago.</p><p>Therefore, my blog post for Joel Kallman Day 2021 is about Oracle Express Edition, a great (and free!) way to get started with Oracle, PL/SQL, APEX and databases in general. You can download the software from <a href="http://oracle.com/xe">oracle.com/xe</a> and there's also docs and videos showing how to install and get started.</p><p>To give the new version a test drive, I created a Windows Server VM on Microsoft Azure, and then downloaded and installed XE 21c along with APEX and the <a href="https://github.com/mortenbra/thoth-gateway">Thoth Gateway</a> for IIS.</p><p>The Azure VM size I selected for this test was "Standard B2s (2 vcpus, 4 GiB memory)". In Azure it costs about USD 40 per month.<br /></p><p>Just for fun, here are some timings:</p><p></p><p><br /><span style="font-family: courier;">Time to...<br />... provision new VM in Azure: 5 minutes<br />... first login to VM: 2 minutes<br />... download a real browser: 1 minute<br />... download 21c XE: 30 seconds<br />... unzip 21c XE: 30 seconds<br />... install 21c XE: 15 minutes<br /><br />... download APEX 21.1: 1 minute<br />... unzip APEX 21.1: 3 minutes<br />... install APEX 21.1: 8 minutes<br /><br />... install IIS: 2 minutes<br />... install Thoth Gateway: 1 minute<br /><br />... first login in APEX (total time): 39 minutes<br /></span></p><p><br /></p><p>Here's a screenshot showing APEX 21.1 in action on Oracle Database XE 21c on Windows Server 2019 with IIS and Thoth Gateway. The page rendering time is not too shabby! :-)<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRB8zV6uAeNNMuirKmuprFGgjT8Iv1tON7r3FCdbZlMp0LFGXwrUG6NtvY-t9f65z3vOqeh_iUso8X14ctv5uexdCInwdHisoK__OPUEoAtJcRDUod3W1LKVeXraayR_cdwu8GwPjUkCTl/s1240/Capture2.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="713" data-original-width="1240" height="368" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRB8zV6uAeNNMuirKmuprFGgjT8Iv1tON7r3FCdbZlMp0LFGXwrUG6NtvY-t9f65z3vOqeh_iUso8X14ctv5uexdCInwdHisoK__OPUEoAtJcRDUod3W1LKVeXraayR_cdwu8GwPjUkCTl/w640-h368/Capture2.PNG" width="640" /></a></div><br /><p><br /></p><p></p><p>So, a big thanks to Oracle for providing a great, free database, and thanks to Joel and everybody on the APEX team for their excellent work. #letswreckthistogether</p><p><br /></p><p><br /></p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-5935740851785261262021-07-29T09:00:00.001-07:002021-07-29T09:05:47.393-07:00Ten years since Mythbusters: Stored Procedures Edition<p>Today it's exactly ten years since I published my blog post called <a href="https://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html">Mythbusters: Stored Procedures Edition</a>. </p><p>Using "stored procedures" (ie storing and executing code in your database, next to the data, instead of in a middle tier or client) is a practice that is provably both efficient and secure, yet it is often dismissed as "wrong".</p><p>My original blog post looks at the usual reasons given against the use of stored procedures for "business logic" (or for anything at all, really) and explains why they tend to be myths (or outright lies), repeated so many times that they are taken as the truth. Take a moment to <a href="https://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html">read the article</a> now.<br /></p><div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTVJoDvUMkY_g68SVzK4b8813k3XBfrR5JNo1ALywElvxJo3UMEwc6eCQNba0UwFUTt_pzQi8RWCHLfgyXomHzDyFc2guWYmKp54YpKYE8t-k2bG_hRRVGw6Q8d46i-mMdm55_z5ZYSr7D/s0/mythbusters.jpg" style="display: block; padding: 1em 0px; text-align: center;"><img alt="" border="0" data-original-height="225" data-original-width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTVJoDvUMkY_g68SVzK4b8813k3XBfrR5JNo1ALywElvxJo3UMEwc6eCQNba0UwFUTt_pzQi8RWCHLfgyXomHzDyFc2guWYmKp54YpKYE8t-k2bG_hRRVGw6Q8d46i-mMdm55_z5ZYSr7D/s0/mythbusters.jpg" /></a></div>
<p>Publishing the article proved that advocating for the use of stored procedures, which had gone out of fashion already by 2011, was controversial. It caused an immediate spike in my blog traffic, receiving almost 20 000 page views in a single day (at the time, my blog had perhaps a few hundred page views per day on average). The article was picked up by <a href="https://news.ycombinator.com/item?id=2832246">Hacker News</a> and generated a lively discussion there.<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhefXtyC1gLdrsyi1Xfn94paS2vdCt5ohtVFBp2ItDmIQ1JxnJN_ZCaThsteNbvOBbtG__1zwwrxD2RHSm4xtSnhdf4YQo1l6iZx9MWShJ4F3oSpmB7MxYMAxx8pNxqwcZz8fd6sGLbm4N1/s457/Capture1.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="334" data-original-width="457" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhefXtyC1gLdrsyi1Xfn94paS2vdCt5ohtVFBp2ItDmIQ1JxnJN_ZCaThsteNbvOBbtG__1zwwrxD2RHSm4xtSnhdf4YQo1l6iZx9MWShJ4F3oSpmB7MxYMAxx8pNxqwcZz8fd6sGLbm4N1/s16000/Capture1.PNG" /></a></div><p>So, where are we in 2021, ten years later?</p><p>Mainstream development (still) uses imperative, object-oriented 3GL languages (Java, .NET, etc) running in the middle tier or client, dragging data out from the database to process it, with data access usually abstracted through an ORM. Which, predictably, <a href="https://www.youtube.com/watch?v=8jiJDflpw4Y">results in suboptimal performance</a>.<br /></p><p>The myths about stored procedures are still prevalent. Ask a random Java/.NET developer today, and I bet he will claim that it's impossible to version control stored procedures (myth #1). In that case, please send him a link to that 10-year old article...</p><p><br /></p><p><br /></p><p> </p><p> </p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-70224478256539281772020-11-08T04:09:00.000-08:002020-11-08T04:09:40.288-08:00New version of Quick PL/SQL, a code generator for PL/SQL<p>I've released a new version of Quick PL/SQL, the code generator for PL/SQL based on the same concept as QuickSQL in APEX, but for PL/SQL code. <a href="https://ora-00001.blogspot.com/2018/08/quick-plsql-code-generator-for-plsql.html">The initial version was released in August 2018.</a></p><p>The online version is available (as before) here: <a href="https://tinyurl.com/quickplsql2">tinyurl.com/quickplsql2</a></p><p>The source code is available on my <a href="https://github.com/mortenbra/quick-plsql">GitHub</a>.<br /></p><p>This release (version 1.2) has a few improvements. The first is syntax highlighting of the generated code (right hand side of the page). This relies on the CodeMirror library which is bundled with APEX. Unfortunately it only supports SQL, not PL/SQL, so not all code is properly highlighted, but it's better than nothing.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQQgS9vq9536ywEs0Yj94t_Mqqi_rKr3ya7wAYcMcNi0bZNwqoHsdruMeu_Wxxz3M-GAaOQ4WVbPhkOYFEQWq3POFnvX4cGO40xQB7ZlZzj8fy5q_9sNCkjM38CTk_KaAIK0KXlClGUeq4/s1465/Capture1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="702" data-original-width="1465" height="307" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQQgS9vq9536ywEs0Yj94t_Mqqi_rKr3ya7wAYcMcNi0bZNwqoHsdruMeu_Wxxz3M-GAaOQ4WVbPhkOYFEQWq3POFnvX4cGO40xQB7ZlZzj8fy5q_9sNCkjM38CTk_KaAIK0KXlClGUeq4/w640-h307/Capture1.JPG" width="640" /></a></div><br /><p><br /></p><p>The second improvement is a "Quick CRUD" button which opens a dialog that allows you to specify a table name (and optionally the name of the primary key column and the package name):</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2fFZymHR9zAAOIs0SHheYXNENy8vN7TP3notzCl-46LWd0uBCe6wXhua2ryiymtFCSGW8m9ymuLGjaohH29PbKUY6Gaxy_TT5qlr7pTXRZ9voLoGCqmMUne-AiUZCNmEZYVUD0y5Rh19y/s658/Capture2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="658" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2fFZymHR9zAAOIs0SHheYXNENy8vN7TP3notzCl-46LWd0uBCe6wXhua2ryiymtFCSGW8m9ymuLGjaohH29PbKUY6Gaxy_TT5qlr7pTXRZ9voLoGCqmMUne-AiUZCNmEZYVUD0y5Rh19y/s16000/Capture2.JPG" /></a></div><br /><p>Based on this it creates the markup required for a complete "CRUD package" in the Input section, which saves you from remembering the markup syntax, and saves you from typing a lot of boilerplate markup as well.<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisGv2cNPrNS-OXN9dd2q2-niPcnykVFUbk46PVwyu_n38DNOcLJSkpkDW2eVwgUoBDLti8yxAyLsIs_Kv6Xe1V_zJEF_ZxLAs9OVRJ1bndeSxssp1SekKhyphenhyphent9n0JvoQYGL475-Xkz6qT5Q/s728/Capture3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="362" data-original-width="728" height="318" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisGv2cNPrNS-OXN9dd2q2-niPcnykVFUbk46PVwyu_n38DNOcLJSkpkDW2eVwgUoBDLti8yxAyLsIs_Kv6Xe1V_zJEF_ZxLAs9OVRJ1bndeSxssp1SekKhyphenhyphent9n0JvoQYGL475-Xkz6qT5Q/w640-h318/Capture3.JPG" width="640" /></a></div><p>Then, as before, just click on "Generate" to have the final PL/SQL generated for you.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7SPb_UcgGWDi8AkEKQ8gViizg9r8wE1EMGXeH4Qw050nl4O35sSRcPqE27ELVcBZVPsvhedIwkTNxEcc-TDTBI_GMpgEfWp-j_EHPo5eFGFa8rLea-Psi1zrhSeEsG-m4blgxPmer5-yE/s727/Capture4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="634" data-original-width="727" height="558" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7SPb_UcgGWDi8AkEKQ8gViizg9r8wE1EMGXeH4Qw050nl4O35sSRcPqE27ELVcBZVPsvhedIwkTNxEcc-TDTBI_GMpgEfWp-j_EHPo5eFGFa8rLea-Psi1zrhSeEsG-m4blgxPmer5-yE/w640-h558/Capture4.JPG" width="640" /></a></div><br /><p><br /></p><p><br /></p><p>Enjoy! :-)</p><p><br /></p><p><br /></p>Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com1tag:blogger.com,1999:blog-5215551487816981140.post-70935671584937268332019-12-08T09:54:00.001-08:002019-12-08T09:54:17.547-08:00Translation enhancements in APEX 18 and 19The Oracle Application Express (APEX) documentation summarizes the translation process thus: <br />
<br />
"To translate an application developed in App Builder, you must map the primary and target language, seed and export text to a translation file, translate the text, apply the translation file, and publish the translated application."<br />
<br />
When you go to Shared Components and then click "Translate Application", this process is illustrated by a list:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjowPcR9UC49-HTbspXGUyRdw2kmdxuvQEisWgQIT0oqIpJSfIWq0_qB2nCHXfB8Z3IuY5sH3a7O3Fmz_NzOFGQs5TBwoypPEpTAVSMD26wtrfIv2b7NS7kNgpLZJoaAPVlz7qhTGW2EkGv/s1600/Capture1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="447" data-original-width="629" height="452" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjowPcR9UC49-HTbspXGUyRdw2kmdxuvQEisWgQIT0oqIpJSfIWq0_qB2nCHXfB8Z3IuY5sH3a7O3Fmz_NzOFGQs5TBwoypPEpTAVSMD26wtrfIv2b7NS7kNgpLZJoaAPVlz7qhTGW2EkGv/s640/Capture1.JPG" width="640" /></a></div>
So the "default" approach is to download the <a href="https://en.wikipedia.org/wiki/XLIFF">XLIFF</a> file and use a third-party tool to do the translation by editing that file.<br />
<br />
However, it is also possible to edit the translations directly from within APEX. Click on "Translation Repository" under Translation Utilities to access this editor.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQvUCgK__C5KlgALY00iAACSqNPAZjkj9FgR7zxuVvCS4gPGrZ2be5Lq_3z3jZhkPlwk2b4Gm97HU3gk4r5v_FWeRiM91t8oiwacuziBJkCF728iEXNiDalytTzWrLb8GDtQOopMYvwTkc/s1600/Capture2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="241" data-original-width="480" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQvUCgK__C5KlgALY00iAACSqNPAZjkj9FgR7zxuVvCS4gPGrZ2be5Lq_3z3jZhkPlwk2b4Gm97HU3gk4r5v_FWeRiM91t8oiwacuziBJkCF728iEXNiDalytTzWrLb8GDtQOopMYvwTkc/s640/Capture2.JPG" width="640" /></a></div>
<br />
<br />
A few simple, but very useful enhancements have been made to the Translation Repository pages in the last couple of APEX versions.<br />
<br />
In APEX 19, there is now a new column available called "Translated" which indicates whether the original string has been translated or not. Using this column it is easy to filter out the strings already translated.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLuwWMAKTmNE8PKN9c-1FhoM10K17ILxkSTBoXwzKu-mo-zoSDncWmnVO49AsU-clzNwNycGKCSiJjXtS0uS5Ck7DraletotpQwTuP1XVqHzABZxCOAKaHLFzj-gaFeXkxVh40SxlENG75/s1600/Capture3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="623" data-original-width="1468" height="270" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLuwWMAKTmNE8PKN9c-1FhoM10K17ILxkSTBoXwzKu-mo-zoSDncWmnVO49AsU-clzNwNycGKCSiJjXtS0uS5Ck7DraletotpQwTuP1XVqHzABZxCOAKaHLFzj-gaFeXkxVh40SxlENG75/s640/Capture3.JPG" width="640" /></a></div>
<br />
<br />
<br />
The popup edit window for the translation has a checkbox, added in APEX 18, that allows you to update all existing occurrences of the source string with a new translation. This is useful if you have, for example, 100 buttons with the label "Save Changes" because you only need to do the translation once.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifEV-6DzCQXwBfgmjmB3JFwFtBsYeSZPvqF8aSvh2KFkUDXgvwUUQiK_ceu0sm0lCIiFkuTEeBDn0McPhQnV78Y3ETNXhUWpYptB7nxFqhISOM5EyY2k_oCrkV1ka5PTrXYTHP4UhmdP8Y/s1600/Capture4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="501" data-original-width="839" height="382" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifEV-6DzCQXwBfgmjmB3JFwFtBsYeSZPvqF8aSvh2KFkUDXgvwUUQiK_ceu0sm0lCIiFkuTEeBDn0McPhQnV78Y3ETNXhUWpYptB7nxFqhISOM5EyY2k_oCrkV1ka5PTrXYTHP4UhmdP8Y/s640/Capture4.JPG" width="640" /></a></div>
<br />
<br />
Also in APEX 18, a "Grid Edit" page was added so you could edit the translations using a tabular form. In APEX 19, this tabular form was replaced with an interactive grid.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjne9FiYs0EXww9LGH2IkLGG4N1uzOXVqKowdOWiimYGY9zBW549PEdlUQhzYCm2tHBEwhv_DRq7zhKPdxqZtL7_U05pfzXTE7JRbG-q7wNsnEpexf5j_xLAdqfmvWVsMJdehzP9v9TrHyG/s1600/Capture5.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="693" data-original-width="1472" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjne9FiYs0EXww9LGH2IkLGG4N1uzOXVqKowdOWiimYGY9zBW549PEdlUQhzYCm2tHBEwhv_DRq7zhKPdxqZtL7_U05pfzXTE7JRbG-q7wNsnEpexf5j_xLAdqfmvWVsMJdehzP9v9TrHyG/s640/Capture5.JPG" width="640" /></a></div>
<br />
<br />
This interactive grid also includes the "Translated" column that can be used to filter out the translations which are already done.<br />
<br />
Note that sorting by the "Translate From" or "Translate To" columns in the interactive grid is not possible (if you try you'll get an "ORA-00932: inconsistent datatypes: expected - got NCLOB" error). But the list of available columns include "Translate From (Partial)" and "Translate To (Partial)" which extracts the first part of the string as a regular varchar2 (usually the strings to translate are not that long so you'll see the full string in the "Partial" column). If you need to sort the strings (I find it useful to sort alphabetically by the source string in order to find identical strings and use copy/paste to update the translation), use these "Partial" columns to set up the sorting. <br />
<br />
<br />
Thanks to <a href="https://joelkallman.blogspot.com/">Joel Kallmann</a> for taking the time to add these small but very useful enhancements into APEX based on my feedback.<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-38025437198435046672019-06-16T08:28:00.001-07:002019-06-16T08:55:56.998-07:00APEX custom Theme Style performanceWhen you create an application in Oracle Application Express (APEX) using the Universal Theme, the application comes with a few default "Theme Styles" which are essentially various stylesheets that you can switch between to go from the default blue (called "Vita") to, for example, the dark grey style (called "Vita - Slate"). <br />
<br />
You can use the Theme Roller in Oracle Application Express (APEX) to customize the colors and other settings, and by saving these you create your own Theme Styles. In <a href="https://www.youtube.com/watch?v=2b-3dAaWAJw">this video</a>, Shakeeb from the Oracle APEX team demonstrates how to use the Theme Roller, and he also briefly mentions that the generated stylesheet for custom theme styles can be placed into the filesystem for better performance.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvPojxX5ZETLGfhnzamrJSmPgWwpzNy8S3PvsKfIN5_GyrgP3EJLK5UbWefLhSiC-xFK1srPxtIRNy_XqE3Zwozpnbwvmajrqa9_J9j-iGhyphenhyphennY5yPsKB54_VjBb5weT5vUkQDFhJ1z0AYl/s1600/Screen+Shot+2019-06-16+at+17.06.09.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="271" data-original-width="631" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvPojxX5ZETLGfhnzamrJSmPgWwpzNy8S3PvsKfIN5_GyrgP3EJLK5UbWefLhSiC-xFK1srPxtIRNy_XqE3Zwozpnbwvmajrqa9_J9j-iGhyphenhyphennY5yPsKB54_VjBb5weT5vUkQDFhJ1z0AYl/s1600/Screen+Shot+2019-06-16+at+17.06.09.png" /></a></div>
<br />
<br />
I recorded <a href="https://youtu.be/0Y9atvrvMj0">a video</a> myself to discuss the details of how custom Theme Styles affect the performance of your APEX application, and how you can move the Theme Style generated stylesheets to a web server to maximize performance and minimize bandwidth consumption:<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/0Y9atvrvMj0/0.jpg" frameborder="0" height="400" src="https://www.youtube.com/embed/0Y9atvrvMj0?feature=player_embedded" width="640"></iframe></div>
<br />
<br />
Another issue that I did not mention in the video is that when you pick an icon for your application, APEX creates an app-icon.svg file (for the logo itself) and app-icon.css (that references the svg file), and places both those files in your Static Application Files.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlcZ8fXUGH8Dw_zb8VyE8NXEEKzo7vBFRl8zf1HyaWohPfWVK9mWoUMM27LGk6DFjxuDf-4Qs5wOb8bkJfzdxxSMuH9cHo9R49jLHhYyysYeu4aEy63zgrbQwUagePOm58ObKzTc7I6nEf/s1600/Screen+Shot+2019-06-16+at+17.37.38.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="238" data-original-width="963" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlcZ8fXUGH8Dw_zb8VyE8NXEEKzo7vBFRl8zf1HyaWohPfWVK9mWoUMM27LGk6DFjxuDf-4Qs5wOb8bkJfzdxxSMuH9cHo9R49jLHhYyysYeu4aEy63zgrbQwUagePOm58ObKzTc7I6nEf/s640/Screen+Shot+2019-06-16+at+17.37.38.png" width="640" /></a></div>
Now since these files (and all other Static Application Files) are stored in the database, they require a database hit to retrieve, instead of fetching them from (and caching them on) the web server filesystem.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWtfKlEg9kDek2efvew2tZKuAhewHkVRXSXS8ANVyjpJj_HOPCundj3jWzZ0jx_8BQxrvGlQBJOafJtCAHDYF847DvDo_q6FjAuzjo5IltI6RQ7Zv8Bx-NjV3tedQ3hNfwQlulP9y8iBx1/s1600/Screen+Shot+2019-06-16+at+17.35.43.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="335" data-original-width="1161" height="184" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWtfKlEg9kDek2efvew2tZKuAhewHkVRXSXS8ANVyjpJj_HOPCundj3jWzZ0jx_8BQxrvGlQBJOafJtCAHDYF847DvDo_q6FjAuzjo5IltI6RQ7Zv8Bx-NjV3tedQ3hNfwQlulP9y8iBx1/s640/Screen+Shot+2019-06-16+at+17.35.43.png" width="640" /></a></div>
<br />
<br />
So if you want to maximize performance, I suggest that you remove the reference to the logo CSS file from your application (and delete the files from the Static Application Files).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV5K_I4aE5TPNczcNs4YZIZXJYyoMnKEvBkFUYMOTfuMFDoL2I4EGJT2sZ2LKX0nK-EABT2SGjPniv0tNJcZWgWkkVSj0ARGcr1_8GvrwxyGQAmcC_SJP4CUEF6O6C9fWeap-WyJFNJZqP/s1600/Screen+Shot+2019-06-16+at+17.37.09.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="263" data-original-width="696" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV5K_I4aE5TPNczcNs4YZIZXJYyoMnKEvBkFUYMOTfuMFDoL2I4EGJT2sZ2LKX0nK-EABT2SGjPniv0tNJcZWgWkkVSj0ARGcr1_8GvrwxyGQAmcC_SJP4CUEF6O6C9fWeap-WyJFNJZqP/s640/Screen+Shot+2019-06-16+at+17.37.09.png" width="640" /></a></div>
<br />
If you really want that logo icon, you could of course place the .svg and .css files on the web server and reference them from there instead.<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-57104676772107736732019-04-25T04:47:00.003-07:002019-04-25T04:47:22.025-07:00Thoth Gateway version 1.4.3 now availableI've updated the <a href="https://github.com/mortenbra/thoth-gateway">Thoth Gateway</a> (a PL/SQL web gateway similar to mod_plsql and ORDS, but written in .NET that allows you to run APEX and PL/SQL Web Toolkit applications using Microsoft IIS web server).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8wxto59IE-Ut1B-OZAEvWJAMefXFWRccs5oCNBAuIvzmUemdpvNGnXu9VjOEgesVrLBnyVcxXh3rsLUei5DyHp2DTONE6KHJkshEvMmIldt2-IYirHkb8d9M4qTfj-2SlWDgJK072E_MY/s1600/thoth-gateway-logo.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="160" data-original-width="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8wxto59IE-Ut1B-OZAEvWJAMefXFWRccs5oCNBAuIvzmUemdpvNGnXu9VjOEgesVrLBnyVcxXh3rsLUei5DyHp2DTONE6KHJkshEvMmIldt2-IYirHkb8d9M4qTfj-2SlWDgJK072E_MY/s1600/thoth-gateway-logo.jpg" /></a></div>
<br />
<br />
The latest release is <a href="https://github.com/mortenbra/thoth-gateway/releases/tag/1.4.3">version 1.4.3</a>. It fixes a few minor issues, but more importantly it is compiled against the latest ODP.NET Managed Driver.<br />
<br />
Among other things, this version of the Managed Driver supports Oracle Advanced Security Option (ASO) encryption; without this certain operations such as APEX file uploads would cause "OracleInternal.Network.NetworkException: ORA-12537: Network Session: End of file" errors against an Oracle 18c database.<br />
<br />
When upgrading to Thoth Gateway v1.4.3 or later, please make sure you also copy the Oracle.ManagedDataAccess.dll file in addition to the PLSQLGatewayModule.dll file to make sure you have the correct library.<br />
<br />
Enjoy!<br />
<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com10tag:blogger.com,1999:blog-5215551487816981140.post-21839812511197004032019-01-30T10:44:00.000-08:002019-01-30T10:45:05.819-08:00APEX Plugin: Execute PL/SQL code and return content to page (updated)Oracle APEX has a built-in Dynamic Action called "Execute PL/SQL Code" that executes a block of PL/SQL code on the server via an Ajax call (ie does not do a regular submit and reload of the whole page).<br />
<br />
But what if you want to do something on the server AND also return some content back to the client? Back in 2012 I released an APEX Dynamic Action plugin called "Execute PL/SQL Code and Return Content" that allows you to do just that. Read the <a href="https://ora-00001.blogspot.com/2012/11/apex-plugin-execute-plsql-code-and-return-content-to-page.html">original blog post</a> for more information.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7kEVql-DevzphBWmy89_IduYocT_8_o-XYVDdiq4fNm-emE3ySG0Gnv8mTAoT7Oehw7Gr53an8aMrTJpoY4aZGJMmglVM66CioQ0jAiUxeiLIRd6_RR2yO2xCL6a_-w6srqkQu5SIzJkL/s1600/pexels-photo-164531.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="750" data-original-width="1125" height="266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7kEVql-DevzphBWmy89_IduYocT_8_o-XYVDdiq4fNm-emE3ySG0Gnv8mTAoT7Oehw7Gr53an8aMrTJpoY4aZGJMmglVM66CioQ0jAiUxeiLIRd6_RR2yO2xCL6a_-w6srqkQu5SIzJkL/s400/pexels-photo-164531.jpeg" width="400" /></a></div>
<br />
<br />
Since then, the APEX framework has evolved and the old Ajax workhorse function called "htmldb_Get" has been deprecated and replaced with functions in the "apex.server" namespace. Hence I have updated my plugin to use the modern API.<br />
<br />
You can find the updated version (1.1) of the plugin on <a href="https://github.com/mortenbra/apex-plugins">my GitHub page for APEX plugins</a>. Note that the new plugin has been exported from APEX 18.1 and therefore requires at least that version to import and use.<br />
<br />
Enjoy! :-)<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com6tag:blogger.com,1999:blog-5215551487816981140.post-92024354830246878762018-08-09T03:10:00.003-07:002020-08-16T04:18:04.933-07:00Quick PL/SQL, a code generator for PL/SQL based on simple markupMaybe you've heard about "<a href="https://apex.oracle.com/en/quicksql/">Quick SQL</a>", a utility (previously a separate application, now part of APEX 18.1 itself) that generates SQL scripts based on a simple markup language. Quick SQL is a real time-saver and allows you to go from idea to prototype to working application in an instant.<br />
<br />
Inspired by Quick SQL, I've created a similar utility, called "Quick PL/SQL", that does the same thing, except it generates PL/SQL code (packages with functions and procedures, with standard comment blocks, formatting, etc.) based on a simple markup.<br />
<br />
I've recorded a couple of videos to show the tool in action.<br />
<br />
Here is an introduction which explains the basic input syntax and the output options:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/ED0soNWu580/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/ED0soNWu580?feature=player_embedded" width="320"></iframe></div>
<br />
<br />
And here is another video which explains how to create standard CRUD-style (Create, Read, Update, Delete) APIs for your tables:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/10BBNh15jlc/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/10BBNh15jlc?feature=player_embedded" width="320"></iframe></div>
<br />
<br />
Try it out for yourself here: <a href="https://apex.oracle.com/pls/apex/f?p=QUICKPLSQL:HOME&c=MULEDEV">https://apex.oracle.com/pls/apex/f?p=QUICKPLSQL:HOME&c=MULEDEV</a> or via this shortcut: <a href="https://tinyurl.com/quickplsql2">https://tinyurl.com/quickplsql2</a><br />
<br />
Of course, I wouldn't mind if this functionality becomes available as part of the QuickSQL utility that is built into APEX! :-)<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com8tag:blogger.com,1999:blog-5215551487816981140.post-7017383236009697992018-03-19T05:35:00.000-07:002018-03-19T05:35:14.736-07:00Thoughts about the APEX_UTIL packageThe APEX_UTIL package in Oracle Application Express is a mixed bag of "miscellaneous" stuff.<br />
<br />
From <a href="https://docs.oracle.com/database/apex-5.1/AEAPI/APEX_UTIL.htm#AEAPI101">the documentation</a>: "<i>You can use the APEX_UTIL package to get and set session state, get files, check authorizations for users, reset different states for users, get and purge cache information and also to get and set preferences for users.</i>"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj87ISRDJEC6Ytv-hbtqL2OmlRelcd5dl3SgwUkeuCSwY3GUWF3GH8LVDswj_NwpeojscLQTcvRPpKuJyG7AhaPECl5P640RLFHfUHNpjmQCAT2vWjzQWIY0jcLRmA5zcJfsdZ2mK7CSj3W/s1600/Capture2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="460" data-original-width="639" height="287" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj87ISRDJEC6Ytv-hbtqL2OmlRelcd5dl3SgwUkeuCSwY3GUWF3GH8LVDswj_NwpeojscLQTcvRPpKuJyG7AhaPECl5P640RLFHfUHNpjmQCAT2vWjzQWIY0jcLRmA5zcJfsdZ2mK7CSj3W/s400/Capture2.PNG" width="400" /></a></div>
<br />It's clear that, over time, a lot of stuff has been placed here that really belongs elsewhere, or in its own package. By splitting up this package, it should be easier for the developer to find the functions and procedures he is looking for.<br /><br />Here are my thoughts on how the APEX_UTIL package could be refactored. I'm not saying remove all this from the package in the next APEX release, but move the code to new/other packages and just leave wrappers in the APEX_UTIL package that call the new package, so that old code continues to work. Mark these as deprecated in the docs, and maybe remove after a few more releases.<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">Move to a new APEX_CACHE package:<br /><br /> CACHE_GET_DATE_OF_PAGE_CACHE Function<br /> CACHE_GET_DATE_OF_REGION_CACHE Function<br /> CACHE_PURGE_BY_APPLICATION Procedure<br /> CACHE_PURGE_BY_PAGE Procedure<br /> CACHE_PURGE_STALE Procedure<br /> CLEAR_APP_CACHE Procedure<br /> CLEAR_PAGE_CACHE Procedure<br /> CLEAR_USER_CACHE Procedure<br /> PURGE_REGIONS_BY_APP Procedure<br /> PURGE_REGIONS_BY_NAME Procedure<br /> PURGE_REGIONS_BY_PAGE Procedure<br /> <br />Move to a new APEX_USER package:<br /><br /> CHANGE_CURRENT_USER_PW Procedure<br /> CHANGE_PASSWORD_ON_FIRST_USE Function<br /> CREATE_USER Procedure<br /> CREATE_USER_GROUP Procedure<br /> CURRENT_USER_IN_GROUP Function<br /> DELETE_USER_GROUP Procedure Signature 1<br /> DELETE_USER_GROUP Procedure Signature 2<br /> EDIT_USER Procedure<br /> END_USER_ACCOUNT_DAYS_LEFT Function<br /> EXPIRE_END_USER_ACCOUNT Procedure<br /> EXPIRE_WORKSPACE_ACCOUNT Procedure<br /> EXPORT_USERS Procedure<br /> FETCH_USER Procedure Signature 1<br /> FETCH_USER Procedure Signature 2<br /> FETCH_USER Procedure Signature 3<br /> GET_FIRST_NAME Function<br /> GET_LAST_NAME Function<br /> GET_USER_ID Function<br /> GET_USER_ROLES Function<br /> GET_USERNAME Function<br /> GET_EMAIL Function<br /> SET_USERNAME Procedure<br /> SET_EMAIL Procedure<br /> SET_FIRST_NAME Procedure<br /> SET_LAST_NAME Procedure<br /> LOCK_ACCOUNT Procedure<br /> PASSWORD_FIRST_USE_OCCURRED Function<br /> UNEXPIRE_END_USER_ACCOUNT Procedure<br /> UNEXPIRE_WORKSPACE_ACCOUNT Procedure<br /> UNLOCK_ACCOUNT Procedure<br /> WORKSPACE_ACCOUNT_DAYS_LEFT Function<br /> STRONG_PASSWORD_CHECK Procedure<br /> STRONG_PASSWORD_VALIDATION Function<br /> GET_ACCOUNT_LOCKED_STATUS Function<br /> REMOVE_USER Procedure<br /> RESET_AUTHORIZATIONS Procedure [DEPRECATED]<br /> PUBLIC_CHECK_AUTHORIZATION Function [DEPRECATED]<br /> RESET_PASSWORD Procedure<br /> RESET_PW Procedure<br /> GET_AUTHENTICATION_RESULT Function<br /> SET_AUTHENTICATION_RESULT Procedure<br /> SET_CUSTOM_AUTH_STATUS Procedure<br /> GET_GROUPS_USER_BELONGS_TO Function<br /> GET_GROUP_ID Function<br /> GET_GROUP_NAME Function<br /> SET_GROUP_GROUP_GRANTS Procedure<br /> SET_GROUP_USER_GRANTS Procedure<br /> IS_LOGIN_PASSWORD_VALID Function<br /> IS_USERNAME_UNIQUE Function<br /> GET_ATTRIBUTE Function<br /> SET_ATTRIBUTE Procedure<br /> <br />Move to a new APEX_PRINT package:<br /><br /> DOWNLOAD_PRINT_DOCUMENT Procedure Signature 1<br /> DOWNLOAD_PRINT_DOCUMENT Procedure Signature 2<br /> DOWNLOAD_PRINT_DOCUMENT Procedure Signature 3<br /> DOWNLOAD_PRINT_DOCUMENT Procedure Signature 4<br /> GET_PRINT_DOCUMENT Function Signature 1<br /> GET_PRINT_DOCUMENT Function Signature 2<br /> GET_PRINT_DOCUMENT Function Signature 3<br /> GET_PRINT_DOCUMENT Function Signature 4<br /> <br />Move to APEX_IR package (this has already been done):<br /><br /> IR_CLEAR Procedure [DEPRECATED]<br /> IR_DELETE_REPORT Procedure [DEPRECATED]<br /> IR_DELETE_SUBSCRIPTION Procedure [DEPRECATED]<br /> IR_FILTER Procedure [DEPRECATED]<br /> IR_RESET Procedure [DEPRECATED]<br /><br />Move to APEX_SESSION package:<br /><br /> GET_SESSION_LANG Function<br /> GET_SESSION_STATE Function<br /> GET_SESSION_TERRITORY Function<br /> GET_SESSION_TIME_ZONE Function<br /> SET_SESSION_HIGH_CONTRAST_OFF Procedure<br /> SET_SESSION_HIGH_CONTRAST_ON Procedure<br /> SET_SESSION_LANG Procedure<br /> SET_SESSION_LIFETIME_SECONDS Procedure<br /> SET_SESSION_MAX_IDLE_SECONDS Procedure<br /> SET_SESSION_SCREEN_READER_OFF Procedure<br /> SET_SESSION_SCREEN_READER_ON Procedure<br /> SET_SESSION_STATE Procedure<br /> SET_SESSION_TERRITORY Procedure<br /> SET_SESSION_TIME_ZONE Procedure<br /> GET_EDITION Function<br /> SET_EDITION Procedure<br /> SET_SECURITY_GROUP_ID Procedure<br /> FETCH_APP_ITEM Function<br /> GET_NUMERIC_SESSION_STATE Function<br /> GET_CURRENT_USER_ID Function<br /> IS_HIGH_CONTRAST_SESSION Function<br /> IS_HIGH_CONTRAST_SESSION_YN Function<br /> IS_SCREEN_READER_SESSION Function<br /> IS_SCREEN_READER_SESSION_YN Function<br /> SET_CURRENT_THEME_STYLE Procedure [DEPRECATED]<br /> GET_DEFAULT_SCHEMA Function<br /> SET_WORKSPACE_Procedure<br /><br />Move to APEX_EXPORT package:<br /><br /> GET_SUPPORTING_OBJECT_SCRIPT Function<br /> GET_SUPPORTING_OBJECT_SCRIPT Procedure<br /> <br />Move to APEX_PAGE package: <br /><br /> GET_HIGH_CONTRAST_MODE_TOGGLE Function<br /> GET_SCREEN_READER_MODE_TOGGLE Function<br /> SHOW_HIGH_CONTRAST_MODE_TOGGLE Procedure<br /> SHOW_SCREEN_READER_MODE_TOGGLE Procedure<br /><br />Leave in APEX_UTIL (for now):<br /><br /> CLOSE_OPEN_DB_LINKS Procedure<br /> COUNT_CLICK Procedure<br /> CUSTOM_CALENDAR Procedure<br /> INCREMENT_CALENDAR Procedure<br /> SUBMIT_FEEDBACK Procedure<br /> SUBMIT_FEEDBACK_FOLLOWUP Procedure<br /> GET_FEEDBACK_FOLLOW_UP Function<br /> STRING_TO_TABLE Function [DEPRECATED]<br /> TABLE_TO_STRING Function [DEPRECATED]<br /> SAVEKEY_NUM Function<br /> SAVEKEY_VC2 Function<br /> KEYVAL_NUM Function<br /> KEYVAL_VC2 Function<br /> HOST_URL Function<br /> URL_ENCODE Function<br /> REDIRECT_URL Procedure<br /> PREPARE_URL Function<br /> GET_HASH Function<br /> GET_SINCE Function<br /> GET_PREFERENCE Function<br /> SET_PREFERENCE Procedure<br /> REMOVE_PREFERENCE Procedure<br /> REMOVE_SORT_PREFERENCES Procedure<br /> GET_BUILD_OPTION_STATUS Function Signature 1<br /> GET_BUILD_OPTION_STATUS Function Signature 2<br /> SET_BUILD_OPTION_STATUS Procedure<br /> GET_BLOB_FILE_SRC Function<br /> GET_FILE Procedure<br /> GET_FILE_ID Function<br /> HTML_PCT_GRAPH_MASK Function<br /> FIND_SECURITY_GROUP_ID Function<br /> FIND_WORKSPACE Function<br /> GET_APPLICATION_STATUS Function<br /> GET_GLOBAL_NOTIFICATION Function<br /> SET_GLOBAL_NOTIFICATION Procedure<br /> SET_APP_BUILD_STATUS Procedure<br /> SET_APPLICATION_STATUS Procedure</span><br /> <br />
<br />
These are just my suggestions, but I do hope the APEX developer team will do some kind of refactoring (even if not exactly as suggested above) in future releases of APEX.<br />
<br />
If you would like to see it done differently, please leave feedback in the comment field below! :-)<br />
<br />
<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2tag:blogger.com,1999:blog-5215551487816981140.post-39218936837588243062018-02-25T05:33:00.000-08:002018-02-25T05:33:47.185-08:00APEX Authentication with Microsoft accountIn the upcoming Oracle Application Express (APEX) 5.2 release, support has been added for so-called "Social Sign-In" authentication. This means that your users can login to an APEX application using an external authentication provider. In APEX 5.2, there is built-in support for Google and Facebook accounts. There is also support for "Generic OAuth Providers" and "OpenID Connect Providers". This blog post describes how you can use the "OpenID Connect Provider" to set up authentication with a Microsoft account, such as an Outlook.com account.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVZ752t2Pf7hSqW-qsa8IreMyNZY39VEL4NDziZAgFxaTYjZAUdtBCNsoFlUZNkjrd13VOVYgvZ_t8vxMPaaJTFp0lyT5KMfAMCjpmJ2xY_CTjWloTVydvDi7T7tUhyphenhyphenp4oDWmcrDruGwlV/s1600/Screen+Shot+2018-02-25+at+11.50.12.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="257" data-original-width="433" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVZ752t2Pf7hSqW-qsa8IreMyNZY39VEL4NDziZAgFxaTYjZAUdtBCNsoFlUZNkjrd13VOVYgvZ_t8vxMPaaJTFp0lyT5KMfAMCjpmJ2xY_CTjWloTVydvDi7T7tUhyphenhyphenp4oDWmcrDruGwlV/s1600/Screen+Shot+2018-02-25+at+11.50.12.png" /></a></div>
<br />
<br />
Here is how to do it:<br />
<br />
<h3>
A. Register the APEX application with Microsoft </h3>
<br />
1. Go to Microsoft Application Registration Portal. Note that you need a Microsoft account, such as an Outlook.com email account, to login and use this portal: <br />
<br />
<a href="https://apps.dev.microsoft.com/">https://apps.dev.microsoft.com/</a><br />
<br />
2. Add an app (enter a descriptive name, your users will see this name during the consent prompt in step 20 below). An Application ID gets generated. Click "Generate New Password" to generate a password.<br />
<br />
3. Click "Add platform" and choose "Web". Enter the APEX callback URL as the "Redirect URL", for the APEX 5.2 Early Adopter this is:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">https://apexea.oracle.com/pls/apex/apex_authentication.callback</span><br />
<br />
4. Add the URL of the APEX app as the "Home Page URL":<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br />https://apexea.oracle.com/pls/apex/f?p=your_app_alias:10</span><br />
<br />
5. Click "Save"<br />
<br />
<br />
<h3>
B. Setup APEX Credentials</h3>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhe42kg9GRn4Onx3P8m2_g9r_LPNJ6W6Bqz9pMUuJZhS5WROh9uryonl8_n0uvNb4mQrnxA1IEFnI9Qxu_nTdF1ppJ0Z2t2qzs-P3OPxG6JJxuVdGitErV8aipgf-pybymC-3xTAEss50w2/s1600/Screen+Shot+2018-02-25+at+11.53.00.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="579" data-original-width="762" height="484" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhe42kg9GRn4Onx3P8m2_g9r_LPNJ6W6Bqz9pMUuJZhS5WROh9uryonl8_n0uvNb4mQrnxA1IEFnI9Qxu_nTdF1ppJ0Z2t2qzs-P3OPxG6JJxuVdGitErV8aipgf-pybymC-3xTAEss50w2/s640/Screen+Shot+2018-02-25+at+11.53.00.png" width="640" /></a></div>
<br />
<br />
6. In APEX, go to Shared Components and click on "Credentials"<br />
<br />
7. Create a new Credential and give it a name (for example "Azure OpenID Credentials"). Select "OAuth2 Client Credentials Flow" as the credential type.<br />
<br />
8. Add the Application ID from step 2 above as the "Client ID", and the password from step 2 above as the "Client Secret".<br />
<br />
9. Save the credentials.<br />
<br />
<h3>
C. Setup APEX Authentication Scheme</h3>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ndgF1OD0hbbInbk2Zao79nFTWA63cQz2Y6ahwNWqdsn5tpTSBiHaYc3JFeNr219kUqVbrXhqL-kcLl5kw0mnCmcTcDBFKx8kfRq7qh8fxCd3j87BmJ-mpiKiQLEX7KGpkYmsZccv0NcA/s1600/Screen+Shot+2018-02-25+at+11.51.39.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="644" data-original-width="795" height="516" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ndgF1OD0hbbInbk2Zao79nFTWA63cQz2Y6ahwNWqdsn5tpTSBiHaYc3JFeNr219kUqVbrXhqL-kcLl5kw0mnCmcTcDBFKx8kfRq7qh8fxCd3j87BmJ-mpiKiQLEX7KGpkYmsZccv0NcA/s640/Screen+Shot+2018-02-25+at+11.51.39.png" width="640" /></a></div>
<br />
<br />
10. Go to Shared Components and click on "Authentication Schemes"<br />
<br />
11. Create a new authentication scheme and give it a name (for example "Azure AD OpenID"). Select "Social Sign-In" as the scheme type.<br />
<br />
12. As credential store, select the credentials created in step 9 ("Azure OpenID Credentials").<br />
<br />
13. As authentication provider, select "OpenID Connect Provider".<br />
<br />
14. As discovery URL, use the following:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration</span><br />
<br />
15. As scope, type "email" (without the quotes)<br />
<br />
16. As username attribute, type "email" (without the quotes)<br />
<br />
17. Save the authentication scheme.<br />
<br />
<h3>
D. Test the Login</h3>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAbe50-j64nGD2LKLyGgNltMGm-hhcT5TiNxT3AqZyS7N9r-QJmDHeomOmDcMDbR2o6iaNYJ03RJ4yJU45_Ri3dMvXeS_uN0kCofQL8thMBRyyvGnSCz0_lfCj5bTgtlsvz_lHOXaSSJ16/s1600/Screen+Shot+2018-02-25+at+14.19.57.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="497" data-original-width="627" height="506" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAbe50-j64nGD2LKLyGgNltMGm-hhcT5TiNxT3AqZyS7N9r-QJmDHeomOmDcMDbR2o6iaNYJ03RJ4yJU45_Ri3dMvXeS_uN0kCofQL8thMBRyyvGnSCz0_lfCj5bTgtlsvz_lHOXaSSJ16/s640/Screen+Shot+2018-02-25+at+14.19.57.png" width="640" /></a></div>
<br />
<br />
18. Go to the home page of your application:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">https://apexea.oracle.com/pls/apex/f?p=your_app_alias:10</span><br />
<br />
19. You should be redirected to a Microsoft login page. Log in with a Microsoft account (such as yourname@outlook.com).<br />
<br />
20. You should be prompted to allow the APEX application to log you in and retrieve your email address to identify you. Accept this.<br />
<br />
21. You should see the home page of your APEX application, and the value of APP_USER should now be equal to the email address you logged in with at Microsoft.<br />
<br />
<h3>
</h3>
<h3>
Switching between authentication schemes in the same APEX session</h3>
<br />
APEX version 5.2 also includes a new attributte for authentication schemes called "Switch in Session" that can be either Enabled or Disabled. If enabled, the current session's authentication scheme can be changed by passing APEX_AUTHENTICATION=scheme name in a URL's request parameter.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnWhejicaBSFUZLF6RFMsJmlriiiOJd75fs0Kx2VLQIF_5l51xmtMwr-pjIxqV5yvOpJvkAeyVb-Vde-hLioIobl0y-ppE7UIP2DSyyuCUzkYRmKJNwAAUef8mJW2kKd8PnqrXFgPdUTSX/s1600/Screen+Shot+2018-02-25+at+11.51.09.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="335" data-original-width="1063" height="201" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnWhejicaBSFUZLF6RFMsJmlriiiOJd75fs0Kx2VLQIF_5l51xmtMwr-pjIxqV5yvOpJvkAeyVb-Vde-hLioIobl0y-ppE7UIP2DSyyuCUzkYRmKJNwAAUef8mJW2kKd8PnqrXFgPdUTSX/s640/Screen+Shot+2018-02-25+at+11.51.09.png" width="640" /></a></div>
<br />
<br />
You can use this to present users with multiple login options. For example, you can have the standard APEX Authentication (or your own table-based authentication) set as the current authentication scheme, and then add another button to the login page which switches the authentication scheme to the Microsoft scheme (or Facebook, or Google, etc).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjR_5zLCPYMkrm4S1N87sqL6s0wMmcq7XqgQOvso36ApFTpBZJN-rqX9qptt00uSz-e7uR8z_dskYCxiOTatiOeMbN3xC3tMy8IshdKbQyBBIrLBA6IXhkkE-z6WZecPHksp9mDl7LSxjQd/s1600/Screen+Shot+2018-02-25+at+11.56.13.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="647" data-original-width="1292" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjR_5zLCPYMkrm4S1N87sqL6s0wMmcq7XqgQOvso36ApFTpBZJN-rqX9qptt00uSz-e7uR8z_dskYCxiOTatiOeMbN3xC3tMy8IshdKbQyBBIrLBA6IXhkkE-z6WZecPHksp9mDl7LSxjQd/s640/Screen+Shot+2018-02-25+at+11.56.13.png" width="640" /></a></div>
<br />
The screenshot above shows an example of a standard APEX login page where I have added an extra button (called "LOGIN_MICROSOFT"), set the icon CSS class to "fa-windows" to get the Windows logo on the button, and set the action attribute of the button to redirect to page 10 (the home page of the application) and also setting the request of the link to "APEX_AUTHENTICATION=name_of_microsoft_auth_scheme". Clicking this button should redirect to a Microsoft page for login, and then redirect the user back to the requested page.<br />
<br />
Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com11tag:blogger.com,1999:blog-5215551487816981140.post-57249803882901421752018-02-21T11:56:00.000-08:002018-02-21T11:56:30.834-08:00Using the Slack webhook API from PL/SQL<a href="https://slack.com/">Slack</a> is a web-based chat room popular with many companies. Slack also has an API that can be used to post messages to a given "channel" or chat room. The simplest API offered is the <a href="https://api.slack.com/incoming-webhooks">"webhook" integration,</a> which is "a simple way to post messages from external sources into Slack. They make use of normal HTTP requests with a JSON payload that includes the message text and some options."<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-CoHo9noOnUgDBxEsG0qhvv_etsO4LOcEA47WW0AZJaP8LhLJikpw5Y0N9_26h0s-OQGJBc8-N93jZHFai7ZD682260FkGGEGY71wsUtIsEx19wO6hkbF27Bb248GjOPzHYUNp4jlJ9R2/s1600/t3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="254" data-original-width="734" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-CoHo9noOnUgDBxEsG0qhvv_etsO4LOcEA47WW0AZJaP8LhLJikpw5Y0N9_26h0s-OQGJBc8-N93jZHFai7ZD682260FkGGEGY71wsUtIsEx19wO6hkbF27Bb248GjOPzHYUNp4jlJ9R2/s640/t3.JPG" width="640" /></a></div>
<br />
<br />
To create a new Slack webhook, login to Slack and go to "Apps" in the left sidebar menu. Click "Manage apps..." and click "Custom Integrations" and then "Incoming Webhooks". Click on "Add Configuration" to create a new webhook. Specify the channel the webhook will post in, and click "Add incoming webhooks integration".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCEMTwJTvdsF3o7dK44vId5lQ-Esgv4Ro27Ts1bvVwxQ6j0OJ7Y5zJnbv5zCAz1G-f1-R_ZJY29aIqkC2hH918r623-jMQPKDG9eb2B3zVgsJimpsy3i6D8S5SyXNnu4W-bfBt07FM7yk3/s1600/t1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="899" data-original-width="1256" height="458" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCEMTwJTvdsF3o7dK44vId5lQ-Esgv4Ro27Ts1bvVwxQ6j0OJ7Y5zJnbv5zCAz1G-f1-R_ZJY29aIqkC2hH918r623-jMQPKDG9eb2B3zVgsJimpsy3i6D8S5SyXNnu4W-bfBt07FM7yk3/s640/t1.JPG" width="640" /> </a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Next, take a note of the Webhook URL:</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOXDjQRC-2PU2GNVRWDRaXYLL26TpgFxaEp0IXc77b95bd-cyM6XGYOd4UsATNk0xadTNy1gcvoiwGKCWyC8UfFazHNfxTmUafF2cNB7DABN09SpxbMBlkG0cQW0iHWl99TUWjU2_Qz845/s1600/t2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="791" data-original-width="1276" height="396" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOXDjQRC-2PU2GNVRWDRaXYLL26TpgFxaEp0IXc77b95bd-cyM6XGYOd4UsATNk0xadTNy1gcvoiwGKCWyC8UfFazHNfxTmUafF2cNB7DABN09SpxbMBlkG0cQW0iHWl99TUWjU2_Qz845/s640/t2.JPG" width="640" /></a></div>
<br />
<br />
Then go to the <a href="https://github.com/mortenbra/alexandria-plsql-utils">Alexandria PL/SQL Utility Library</a> and install the <a href="https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/slack_util_pkg.pks">SLACK_UTIL_PKG</a> package in your database schema. Modify your database Network ACL settings to include "hooks.slack.com" (port 443) to allow connections to the Slack site from the database.<br />
<br />
Then use the package like this (see also <a href="https://github.com/mortenbra/alexandria-plsql-utils/blob/master/demos/slack_util_pkg_demo.sql">demo script</a>):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbYeicajmhF2qf7UlDp1-3pCUN53vqUyceT2of8CZ92MdEqxsUR_oOMvcJ2itcqqyea0I7PRA_DZdudHz5S311l5818OUm9Q5KGXFpwDNdVnfMm_Px5PLKhL70zv80qQwe_HYzv1Hb6968/s1600/Screen+Shot+2018-02-21+at+20.47.35.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="198" data-original-width="792" height="159" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbYeicajmhF2qf7UlDp1-3pCUN53vqUyceT2of8CZ92MdEqxsUR_oOMvcJ2itcqqyea0I7PRA_DZdudHz5S311l5818OUm9Q5KGXFpwDNdVnfMm_Px5PLKhL70zv80qQwe_HYzv1Hb6968/s640/Screen+Shot+2018-02-21+at+20.47.35.png" width="640" /></a></div>
<br />
<br />In addition to application-specific messages you could perhaps also set up the database (via a background job) to notify you when you are about to run out of disk space, when the number of APEX page views reach a certain threshold, when a specific user logs in, and so on. Use your imagination! :-)<br />
<br />
<br />
<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2tag:blogger.com,1999:blog-5215551487816981140.post-59263761809758144522018-02-10T08:33:00.000-08:002018-02-10T08:33:23.738-08:00Pivot Table plugin for APEX (Updated)Some years ago I created an APEX region plugin for displaying the results of a query as a Pivot Table. <a href="https://ora-00001.blogspot.com/2013/10/pivot-table-plugin-for-apex.html">You can read the original blog post about that here.</a><br />
<br />
The plugin proved quite popular, but people were having problems using it with APEX 5 due to a jQuery versioning conflict.<br />
<br />
I've now updated the plugin so it works with APEX, both 5.0 and 5.1. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp_SkVdwjRQlhArTaaiVAXVLu83k8a2Yi8DRJy3YEP0OTCWZYbC5eS9_7T3aqm5ibPkZWoYPadacxIrYf-M3KrvH0sWc91XdeZABme6-oQZbtbS5B4fI-27gCEeHpts3yOWqyE19prxJ7U/s1600/Screen+Shot+2018-02-10+at+17.01.15.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="636" data-original-width="1157" height="351" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp_SkVdwjRQlhArTaaiVAXVLu83k8a2Yi8DRJy3YEP0OTCWZYbC5eS9_7T3aqm5ibPkZWoYPadacxIrYf-M3KrvH0sWc91XdeZABme6-oQZbtbS5B4fI-27gCEeHpts3yOWqyE19prxJ7U/s640/Screen+Shot+2018-02-10+at+17.01.15.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
To use it, simply add a region to the page, change the region type to "Pivot Table" and write an SQL query as the region source:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivV5fRid8VmsFgnlh-RHh5JhD-himMY7MFO1c6bZxr_wEEvfzzdL1R4hoe6saZPNbmwwldfUiVHg1yVhQwaMLaPJ8PaR5EFRVUZc3E2QX3tnxVszSdAwD6m_yPDnYu_tbuixC98uTyIyS4/s1600/Screen+Shot+2018-02-10+at+17.01.52.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="360" data-original-width="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivV5fRid8VmsFgnlh-RHh5JhD-himMY7MFO1c6bZxr_wEEvfzzdL1R4hoe6saZPNbmwwldfUiVHg1yVhQwaMLaPJ8PaR5EFRVUZc3E2QX3tnxVszSdAwD6m_yPDnYu_tbuixC98uTyIyS4/s1600/Screen+Shot+2018-02-10+at+17.01.52.png" /></a></div>
<br />
<br />
On the region attributes page, specify the options. I've added some help text to explain the various options. Note that you can have more than one Pivot Table region on the same page, but be sure to use a unique name in the "DOM Element Name" attribute for each pivot table.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-8r998a6SqvQm2ggMiDZJ_CZJB3Xw04aOTDBaRhencnuJJSALA87aX4JjCObo57yjQ-BMmihodxU5yk1q1IrBvDtknBmARlhqqvqOJeQQILUGn7daepo-Mpp1FMO6IgQ_fgZe7RrrZzvF/s1600/Screen+Shot+2018-02-10+at+17.03.00.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="380" data-original-width="922" height="262" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-8r998a6SqvQm2ggMiDZJ_CZJB3Xw04aOTDBaRhencnuJJSALA87aX4JjCObo57yjQ-BMmihodxU5yk1q1IrBvDtknBmARlhqqvqOJeQQILUGn7daepo-Mpp1FMO6IgQ_fgZe7RrrZzvF/s640/Screen+Shot+2018-02-10+at+17.03.00.png" width="640" /></a></div>
<br />
I've also added/enabled some extra features such as spreadsheet
export (via TSV - tab separated values) and charting.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieiXsEotMwXg3zIAOjywVLfyWo7IgR0zPCeT0G0PLFJUu5xr9lJlM3X2FrAmEwmGNUSGDgdJlr54CuiMBjkumWX0qKl1_hT33ttGobhR_sV2vFCk9lAzSF-rxeazTdtvFDrLsDxXqaNNUE/s1600/Screen+Shot+2018-02-10+at+17.27.25.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="635" data-original-width="1186" height="342" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieiXsEotMwXg3zIAOjywVLfyWo7IgR0zPCeT0G0PLFJUu5xr9lJlM3X2FrAmEwmGNUSGDgdJlr54CuiMBjkumWX0qKl1_hT33ttGobhR_sV2vFCk9lAzSF-rxeazTdtvFDrLsDxXqaNNUE/s640/Screen+Shot+2018-02-10+at+17.27.25.png" width="640" /></a></div>
<br />
<br />
It should be noted that the plugin is just an APEX wrapper for the <a href="https://github.com/nicolaskruchten/pivottable">Javascript Pivot Table component by Nicolas Kruchten</a> who did all the hard work. I merely packaged it up for use with APEX.<br />
<br />
You can download the plugin from <a href="https://github.com/mortenbra/apex-plugins">my APEX plugins page</a>. Enjoy! :-)<br />
<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com15tag:blogger.com,1999:blog-5215551487816981140.post-50501052350471881242017-10-18T04:10:00.001-07:002017-10-18T07:52:02.856-07:00Oracle XE 12c becomes Oracle XE 18c<a href="http://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html">Oracle Database, Express Edition (XE)</a> is a free version of the Oracle Database, currently available as version 11g (11.2) for Windows and Linux.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3PXY65orhWoeAMDLZWNWXDUzMMzAcJZ2MtwoL-i8fDP4otpxDD4_YiPZ1w0l2XCTES7ZLi2aehbEJ9WH8a-VljbxfL2scJQVxTnZnsbbQKAl5c2E6eCKKAUnIc0ZqPLD3j_kCChfv75OL/s1600/oracle_xe.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="128" data-original-width="128" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3PXY65orhWoeAMDLZWNWXDUzMMzAcJZ2MtwoL-i8fDP4otpxDD4_YiPZ1w0l2XCTES7ZLi2aehbEJ9WH8a-VljbxfL2scJQVxTnZnsbbQKAl5c2E6eCKKAUnIc0ZqPLD3j_kCChfv75OL/s1600/oracle_xe.png" /></a></div>
A planned new version of Oracle XE based on 12c (12.2) was <a href="http://ora-00001.blogspot.com/2013/07/first-mentions-of-oracle-12c-xe-express.html">first mentioned back in 2013</a>. Now we are in 2017 and Oracle has changed its version numbering, aiming for yearly releases of the database, which means that after 12c comes 18c (in 2018) and 19c (in 2019) and so on.<br />
<br />
According to information coming out at this year's Oracle OpenWorld, the next version of Oracle Database Express Edition (XE) will therefore be Oracle XE 18c.<br />
<br />
Below are the details known so far about this upcoming version. <b>Disclaimer: I've collected this information from tweets and blogs, so <strike>nothing is official yet. Time will tell as to what is true</strike>. UPDATE: Gerald Venzl from Oracle <a href="https://twitter.com/GeraldVenzl/status/920650648692989952">has confirmed</a> the below to be "all true" :-)</b><br />
<br />
<ul>
<li>The next version of Oracle Express Edition (XE) will be 18c. (Source: <a href="https://twitter.com/chrisrsaxon/status/915225979290763264">Chris Saxon, Twitter</a>)</li>
<li><strike>Oracle XE 18c is expected in Q1 of 2018.</strike> (Source: <a href="https://technology.amis.nl/2017/10/03/oracle-open-world-day-2-highlights/">AMIS blog</a>). UPDATE: Oracle XE 18c "is currently planned between March and August 2018 and might change". (Source: <a href="https://twitter.com/GeraldVenzl/status/920650648692989952">Gerald Venzl, Twitter</a>)</li>
<li>There will be yearly releases of Oracle Express Edition (XE), ie Oracle XE 19c in 2019, etc. (Source: <a href="https://twitter.com/FranckPachot/status/915029087155929088">Franck Pachot, Twitter</a>).</li>
<li>There will be simultaneous releases of XE for Linux and Windows. (Source: <a href="https://twitter.com/GeraldVenzl/status/920657090485436417">Gerald Venzl, Twitter</a>) </li>
<li>Limits for XE 18c will be 2 GB of memory, 12GB of storage (with basic/advanced compression bringing real capacity up to around 40GB), 2 CPUs and 4 pluggable databases. (Source: <a href="https://technology.amis.nl/2017/10/03/oracle-open-world-day-2-highlights/">AMIS blog</a> and <a href="https://twitter.com/lucasjellema/status/915554939086106624">Lucas Jellema, Twitter</a>)</li>
<li>Express Edition (XE) will actually include "nearly all" of the features from Enterprise Edition (EE)! (Source: <a href="https://twitter.com/FranckPachot/status/915029087155929088">Franck Pachot, Twitter</a> and <a href="https://twitter.com/chrisrsaxon/status/915225979290763264">Chris Saxon, Twitter</a>).</li>
<li>Express Edition (XE) will still be free for both development and production. (Source: <a href="https://twitter.com/chrisrsaxon/status/915225979290763264">Chris Saxon, Twitter</a>).</li>
<li>There will be no support (except through community/forums) for XE, and no bug fixes/patches. Still, with a yearly release cycle that means bugs will be fixed by upgrading to the latest release. (Source: <a href="https://twitter.com/rjbdba/status/915045323398975489">Franck Pachot and Bob Bryla, Twitter</a>)</li>
</ul>
<br />
<br />
If most, or even some, of the above is true, this is really great news! I understand we should thank <a href="https://twitter.com/GeraldVenzl">Gerald Venzl</a> at Oracle for this, as he is the guy working on bringing us all this goodness! Thanks in advance, Gerald! :-)<br />
<br />
<br />
<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com13tag:blogger.com,1999:blog-5215551487816981140.post-58477888032066435662017-10-09T23:47:00.001-07:002017-10-09T23:50:17.050-07:00ODC Appreciation Day: The PL/SQL LanguageLike last year, <a href="https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/">Tim Hall of oracle-base.com fame</a> suggested we should all do an "ODC Appreciation Day" in honor of the Oracle Developer Community (ODC), by blogging about our favorite Oracle product or feature.<br />
<br />
My personal favorite, after <a href="http://ora-00001.blogspot.com/2016/10/otn-appreciation-day-oracle-express-edition-xe.html">the database itself</a>, is the PL/SQL language that runs inside the database.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQKXpEq_vThRsMI_GZFryZddzL057YEVCDyguvMtLhfr_WCeiwvA4JAXzIz9vg2Ex8C6L-g21QE2nqpfAHr_jsvqGTWOTixmYz-CDt8-wCLvW-lkfArYNgy06IVYqzcrF8n37SdUt6r2Dc/s1600/plsql.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="315" data-original-width="505" height="249" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQKXpEq_vThRsMI_GZFryZddzL057YEVCDyguvMtLhfr_WCeiwvA4JAXzIz9vg2Ex8C6L-g21QE2nqpfAHr_jsvqGTWOTixmYz-CDt8-wCLvW-lkfArYNgy06IVYqzcrF8n37SdUt6r2Dc/s400/plsql.png" width="400" /></a></div>
<br />
<br />
Here's what's great about it:<br />
<ul>
<li>Simple (and therefore easy to understand and quick to learn)</li>
<li>Runs everywhere the Oracle database runs (any operating system)</li>
<li>Seamlessly integrated with SQL</li>
<li>Great performance, not least because it runs in the database server alongside your data, thus <a href="https://www.youtube.com/watch?v=8jiJDflpw4Y">eliminating a lot of mid-tier overhead</a> </li>
</ul>
<br />
I made a presentation a while back that goes into greater detail about what makes PL/SQL great, take a look at <a href="http://mortenbra.github.io/plsql-the-good-parts/">PL/SQL: The Good Parts</a>.<br />
<br />
<br />
Thanks ODC!<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-62467766659062150042017-03-21T01:19:00.002-07:002021-05-20T01:32:18.496-07:00Using VS Code for PL/SQL developmentI've been using Sublime Text as my main editor for PL/SQL development for many years, but I'm now in the process of switching to <a href="https://code.visualstudio.com/">Visual Studio Code</a> (VS Code).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsxRsIDl_zzt3LRrzGMK7YwGgSooDPKUaBWLbSOU5RlpkCCMj6pSvs4AAAoXrs8gtiyolG9nm5ZhWw-kfUZBUF6EIR_AsWpLmuo8X3YYCnRm6IE3Db3VdFLYaMFoo9QVBm3DFinvQ2WZCD/s1600/Screen+Shot+2017-03-20+at+18.16.26.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsxRsIDl_zzt3LRrzGMK7YwGgSooDPKUaBWLbSOU5RlpkCCMj6pSvs4AAAoXrs8gtiyolG9nm5ZhWw-kfUZBUF6EIR_AsWpLmuo8X3YYCnRm6IE3Db3VdFLYaMFoo9QVBm3DFinvQ2WZCD/s640/Screen+Shot+2017-03-20+at+18.16.26.png" width="640" /></a></div>
<br />
<br />
Some good reasons to use VS Code:<br />
<ul>
<li>Multi-platform (Windows, OS X, Linux)</li>
<li>Free, open source</li>
<li>Lightweight, fast (enough)</li>
<li>Large ecosystem of <a href="https://marketplace.visualstudio.com/VSCode">extensions</a></li>
<li>Built-in <a href="https://code.visualstudio.com/docs/editor/versioncontrol">Git support</a></li>
<li>Can be adapted to PL/SQL coding via a <a href="https://marketplace.visualstudio.com/items?itemName=xyz.plsql-language">plsql language extension</a> (syntax highlighting, go to/peek definition, go to symbol) and PL/SQL compilation using sqlplus via a <a href="https://code.visualstudio.com/docs/editor/tasks">Task Runner</a> (see below for PL/SQL specifics)</li>
</ul>
<br />
<span style="font-size: large;">Installing VS Code and extensions</span><br />
<br />
Download VS Code from <a href="https://code.visualstudio.com/">https://code.visualstudio.com/</a> and run the installer. <br />
<br />
Start VS Code and click the Extensions icon.<br />
<ul>
<li>Search for "plsql" and install the "xyz.plsql-language" extension</li>
</ul>
The following are all optional but recommended: <br />
<ul>
<li>Search for "git history" and install the "donjayamanne.githistory" extension</li>
<li><strike>Search for "better merge" and install the "pprice.better-merge" extension</strike> (update 24.09.2017: this extension is now <a href="https://github.com/Microsoft/vscode/pull/27150">part of VS Code itself</a> and does not need to be installed separately)<strike><br /></strike></li>
<li>Search for "git lens" and install the "eamodio.gitlens" extension</li>
<li>Search for "tag" and install the "formulahendry.auto-close-tag" extension</li>
<li>Search for "blackboard" and install the "gerane.Theme-Blackboard" extension</li>
<li>Search for "material icon" and install the "PKief.material-icon-theme" extension</li>
<li>Search for "plsql" and install the "apng.orclapex-autocomplete" extension (added 02.01.2018) </li>
</ul>
Click on "Reload" to restart VS Code with the new extensions loaded.<br />
<br />
Your list of extensions should now look something like this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhyszTS4X-Mc7lMCM7iLIJC2Bm2y3gZ8Ww87zSrxWGhr2veSTr5Vk2O0XSdgvVbTVbTs2ShF3YFJatrSWrNXeEvW2kO76mEnsfqRkkbHZ_KFh3Hhh6fN-mel5XkO83gYxXEkWsej77RrQy/s1600/Screen+Shot+2017-03-20+at+18.23.48.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhyszTS4X-Mc7lMCM7iLIJC2Bm2y3gZ8Ww87zSrxWGhr2veSTr5Vk2O0XSdgvVbTVbTs2ShF3YFJatrSWrNXeEvW2kO76mEnsfqRkkbHZ_KFh3Hhh6fN-mel5XkO83gYxXEkWsej77RrQy/s1600/Screen+Shot+2017-03-20+at+18.23.48.png" /></a></div>
<br />
Click File, Preferences, Color Theme and select the "Blackboard" theme.<br />
Click File, Preferences, File Icon Theme and select the "Material Icon" theme.<br />
<br />
<span style="font-size: large;">Configuring a Task Runner to compile PL/SQL code</span><br />
<br />
Click File, Open Folder and open a folder containing your PL/SQL code.<br />
Click View, Command Palette and enter "task" then select "Configure Task Runner", select "Others".<br />
<br />
Copy the following text and paste it into the <span style="font-family: "courier new" , "courier" , monospace;">tasks.json</span> file:<br />
<br />
<script src="https://gist.github.com/mortenbra/05d88cd0181a3dd1b0436f32c51d264a.js"></script><br />
<br />
Adjust the connection string as appropriate to your environment.<br />
<br />
Copy the following text and save it as <span style="font-family: "courier new" , "courier" , monospace;">_show_errors.sql </span>in the project root folder:<br />
<br />
<script src="https://gist.github.com/mortenbra/3204a125e3da1008e19b36bf94586950.js"></script><br />
<br />
Linux and Mac: Copy the following text and save it as <span style="font-family: "courier new" , "courier" , monospace;">_run_sqlplus.sh</span> in the project root folder (remember to <span style="font-family: "courier new" , "courier" , monospace;">chmod +x</span> the file to make it executable).<br />
<br />
<script src="https://gist.github.com/mortenbra/9af4b0211782161619c0bab01ed7031a.js"></script><br />
<br />
Windows: Copy the following text and save it as <span style="font-family: "courier new" , "courier" , monospace;">_run_sqlplus.bat</span> in the project root folder.<br />
<br />
<script src="https://gist.github.com/mortenbra/bda9b46f1af45ffa8a72d49739d7782e.js"></script> <br />
<br />
Optionally create a <span style="font-family: "courier new" , "courier" , monospace;">login.sql</span> file in project root folder and add:<br />
<br />
<script src="https://gist.github.com/mortenbra/6a10aa6b09fb470e6b91bc3b5adb19af.js"></script><br />
<br />
<span style="font-size: large;">Editing code</span><br />
<br />
The <a href="https://marketplace.visualstudio.com/items?itemName=xyz.plsql-language">PL/SQL language extension by xyz</a> provides syntax highlighting for PL/SQL, as well as a couple of very useful code navigation features.<br />
<br />
You can go to a "symbol" (ie a function or procedure) inside a package by pressing Shift+Ctrl+O and typing the name of the symbol: <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFoCjJokUFcaZncNVXeYriw5vWcM02f6NqV2ytZAfyUxXcDWF7OuwFDI3IFqlqwdF6l0UacQwXCdZdpf_yk1DCYbm73stq1pDnLem0z4uSEXnFMtjs5SBHzgOxcmjAWhjBFWYZtrpFxi2y/s1600/DocumentSymbol.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="458" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFoCjJokUFcaZncNVXeYriw5vWcM02f6NqV2ytZAfyUxXcDWF7OuwFDI3IFqlqwdF6l0UacQwXCdZdpf_yk1DCYbm73stq1pDnLem0z4uSEXnFMtjs5SBHzgOxcmjAWhjBFWYZtrpFxi2y/s640/DocumentSymbol.gif" width="640" /></a></div>
<br />
You can go to the definition of a function or procedure by pressing F12 when the cursor is on the function or procedure name (or right-click on the function or procedure name and select either "Go to definition", or "Peek definition" to see the definition in a popup window without leaving the current file).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3C6BS3ETmzYDnh2KVnk6kY6JRZ1thF2WUeHWNi52pI5Na90I6TD9I1RV9jZzeWkVbYe1x87h45SUX5fG3bWMtbrE77rnfTaRL73F42v_2v2E08dhqsseNfsiPBx2ZE3mJ56-6e7OxkA9a/s1600/Definition.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="458" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3C6BS3ETmzYDnh2KVnk6kY6JRZ1thF2WUeHWNi52pI5Na90I6TD9I1RV9jZzeWkVbYe1x87h45SUX5fG3bWMtbrE77rnfTaRL73F42v_2v2E08dhqsseNfsiPBx2ZE3mJ56-6e7OxkA9a/s640/Definition.gif" width="640" /></a></div>
<br />
<br />
<br />
<span style="font-size: large;">Snippets</span><br />
<br />
You can define your own snippets for frequently used code; see <a href="https://code.visualstudio.com/docs/editor/userdefinedsnippets">this how-to article</a>. I recommend that you create snippets for frequently used code blocks such as if/then/else statements, case statements, and larger code blocks such as the skeleton for a package, procedure or function.<br />
<br />
<span style="font-size: large;">Building code</span><br />
<br />
To build (compile) the current file into the database, press Shift+Ctrl+B (or click View, Command Palette, and type "build" to search for the relevant command).<br />
<br />
The build task runs sqlplus and passes it the filename of the current file. This creates or replaces the object in the database (you can see the commands being executed in the "Output" pane in VS Code). The build task then queries <span style="font-family: "courier new" , "courier" , monospace;">user_errors</span> to get any errors and warnings from the database, and these are shown in the "Problems" pane in VS Code.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv97Nszn_y6GDkdJGw3nCbl73HIeI99yphyphenhyphenuSUz89s8AsHStInqpFEOZS2HFY-hxwkFWZq_FOedkXTQ6Sh3JmXvCfRrrRgZJBCUQyHRzjvOp5ZmFRcpxFY3na6WAtBz_lkdNNDwFqAkyPy/s1600/Screen+Shot+2017-03-21+at+08.50.30.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv97Nszn_y6GDkdJGw3nCbl73HIeI99yphyphenhyphenuSUz89s8AsHStInqpFEOZS2HFY-hxwkFWZq_FOedkXTQ6Sh3JmXvCfRrrRgZJBCUQyHRzjvOp5ZmFRcpxFY3na6WAtBz_lkdNNDwFqAkyPy/s640/Screen+Shot+2017-03-21+at+08.50.30.png" width="617" /></a></div>
<br />
You can click on each problem to jump to the relevant line of code. You'll also see squiggly lines under the errors in the code itself, and you can hover over the text to see a tooltip containing the error message.<br />
<br />
Note that since the build task queries the <span style="font-family: "courier new" , "courier" , monospace;">user_errors</span> view without any filters, you get to see all errors in the schema, not just the errors for the current file. I actually like this, because it instantly shows me if there are any other problems in the schema that I might not otherwise be aware of.<br />
<br />
Also, the build task assumes that you have one file per database object (ie separate files for package specifications and package bodies), and that the filenames match the database object names. This makes sense to me and I assume that's how most people organize their files, but you can tweak the <span style="font-family: "courier new" , "courier" , monospace;">_show_errors.sql</span> script to generate output appropriate to your setup.<br />
<br />
<br />
<span style="font-size: large;">Version control with Git</span><br />
<br />
VS Code has great built-in support for Git. I recommend installing a couple of git-related extensions (see above), but other than that, I'll just refer you to <a href="https://code.visualstudio.com/docs/editor/versioncontrol">the official tutorial for using Git in VS Code</a>.<br />
<br />
<br />
That's it, enjoy using VS Code for PL/SQL development! :-)<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com20tag:blogger.com,1999:blog-5215551487816981140.post-10668821983974968612017-01-21T05:30:00.000-08:002017-01-21T05:30:36.301-08:00APEX Plugin: Calendar Heatmap RegionI was looking at the GitHub "contribution chart", which shows number of commits per day as a heatmap calendar. I thought this type of chart would be cool to have as an APEX plugin, so I went ahead and implemented it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIVzobEy7IkAP82rqjV0f9uRaj2G3TwtFaYAmQyVO7UF5lqWZRp8oXaPgPHeNdHrPC2y9Mk3jkehmZWbSSt-zBq2m-SRdaebBAPNiTwOhD_g8m0fAFZrED3-6R7CsLGGep7tfFt0t5Wrgt/s1600/Screen+Shot+2017-01-20+at+18.23.37.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="379" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIVzobEy7IkAP82rqjV0f9uRaj2G3TwtFaYAmQyVO7UF5lqWZRp8oXaPgPHeNdHrPC2y9Mk3jkehmZWbSSt-zBq2m-SRdaebBAPNiTwOhD_g8m0fAFZrED3-6R7CsLGGep7tfFt0t5Wrgt/s640/Screen+Shot+2017-01-20+at+18.23.37.png" width="640" /></a></div>
<br />
<br />
Here is a video I recorded to walk through how the plugin was made:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/NiUvGBvEIHo/0.jpg" frameborder="0" height="400" src="https://www.youtube.com/embed/NiUvGBvEIHo?feature=player_embedded" width="640"></iframe></div>
<br />
<br />
Note: For an alternative implementation, see the "<a href="http://www.oracle.com/webfolder/technetwork/jet/jetCookbook.html?component=dataVisualizations&demo=horizontalBlockCalendar">Block Calendar" in Oracle JET</a>. As far as I can tell, there is no built-in support for this chart in APEX, so you would have to write your own wrapper to use it as a plugin.<br />
<br />
<br />
You can download the plugin and PL/SQL code from <a href="https://github.com/mortenbra/apex-plugins">my GitHub repository</a>.<br />
<br />
Enjoy! :-)<br />
<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2tag:blogger.com,1999:blog-5215551487816981140.post-60801433597609544242016-12-05T05:50:00.000-08:002016-12-05T05:50:30.831-08:00APEX plugin files and 404 not found in ORDS <br />
After installing APEX 5.0.4 and ORDS 3.0.8 I had an issue where APEX plugins that have static files bundled with the plugin (files that at runtime get mapped to URLs such as <span style="font-family: "Courier New",Courier,monospace;">/ords/your_workspace_name/r/your_app_id/files/plugin/123456789/v1/filename</span>) were giving a 404 "not found" error from ORDS. Well, you actually have to open the console in the web browser to see the 404 errors, and then if you try to open the file directly you get a 404 page from ORDS:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtji0qomLbymkhSpK7qVBbv-X9cBzMExFN9iKlPxYJhLtceMvygkfdvdQ5f-EUglAyfmzI4x50L43oJLYWUpX16atIR5TVpjbRqrci4DJFUO9LX_9ztI0wyil8bFbAEsd5FHR78Z2Mpdfm/s1600/ORDS-404.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="142" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtji0qomLbymkhSpK7qVBbv-X9cBzMExFN9iKlPxYJhLtceMvygkfdvdQ5f-EUglAyfmzI4x50L43oJLYWUpX16atIR5TVpjbRqrci4DJFUO9LX_9ztI0wyil8bFbAEsd5FHR78Z2Mpdfm/s320/ORDS-404.jpg" width="320" /></a></div>
<br />
I had seen this problem before, and it is typically because you have not run the <span style="font-family: "Courier New",Courier,monospace;">apex_rest_config.sql</span> script as part of your APEX installation, <a href="http://www.inside-oracle-apex.com/apex-5-0-there-are-issues-with-the-configuration-of-the-static-files-in-your-environment/">as explained by Patrick Wolf here</a> and also <a href="https://docs.oracle.com/cd/E59726_01/install.50/e39144/listener.htm#HTMIG29335">in the docs</a>.<br />
<br />
<br />However, in my case I was fairly certain I had already run the <span style="font-family: "Courier New",Courier,monospace;">apex_rest_config.sql</span> script. I re-ran it just in case (and restarted Tomcat), but I still got the error.<br />
<br />
I then inspected the Tomcat log files (look in <span style="font-family: "Courier New",Courier,monospace;">/usr/share/tomcat8/latest/logs/catalina.out</span>) and found the following:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">SEVERE [ajp-nio-8009-exec-1] . *** The pool named: |apex|pu| is reporting no APEX workspaces are enabled for RESTful Services,<br /> but the pool named: |apex|al| is reporting 1 workspaces are enabled.<br /><b>This implies APEX was installed after Oracle REST Data Services. </b>Please run the following command and restart Oracle REST Data Services:<br /> java -jar ords.war validate --database apex<br /><b>APEX based RESTful Services, including those used to serve static resources will not function until this has been done.</b></span><br />
<br />
<br />
So I did as instructed and ran this command<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">java -jar ords.war validate --database apex</span><br />
<br />
and then restarted Tomcat<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">service tomcat restart</span><br />
<br />
And that resolved this issue.<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2tag:blogger.com,1999:blog-5215551487816981140.post-44900508232239675092016-10-10T23:42:00.001-07:002016-10-10T23:42:16.938-07:00OTN Appreciation Day: Oracle Express Edition (XE)<a href="https://oracle-base.com/blog/2016/09/28/otn-appreciation-day/">Tim Hall of oracle-base.com fame</a> suggested we should all do an "OTN Appreciation" day in honor of the <a href="http://www.oracle.com/technetwork/index.html">Oracle Technology Network (OTN)</a>, by blogging about our favorite Oracle product or feature.<br />
<br />
<br />
My personal favorite is Oracle Express Edition (XE), the free version of the world's best relational database.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf2ea_VbZvVyHYeL8PgEFcqeYqFLS_2DAwONwQpeGhUZoqju9-qgIqY4wQDy-0Ty4mG5kX_IYteuOOxMdLnJgIN7X9ASAy-r74dVbBk7sFrK5Zvsv5GDfQ-6DSN5uzNDDoZlakOlK25kUZ/s1600/oracle-db-express-edition.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf2ea_VbZvVyHYeL8PgEFcqeYqFLS_2DAwONwQpeGhUZoqju9-qgIqY4wQDy-0Ty4mG5kX_IYteuOOxMdLnJgIN7X9ASAy-r74dVbBk7sFrK5Zvsv5GDfQ-6DSN5uzNDDoZlakOlK25kUZ/s1600/oracle-db-express-edition.png" /></a></div>
<br />
<br />
Here's what's great about it:<br />
<br />
<ul>
<li><a href="http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html">Available for both Windows and Linux</a> at the OTN website</li>
<li>Easy to install (InstallShield on Windows, rpm package on Linux), only takes a few minutes</li>
<li>Lightweight, requires very little resources to run</li>
<li><a href="http://ora-00001.blogspot.com/2015/07/installing-oracle-xe-ords-and-apex-on-centos-linux-part-four.html">Great performance</a> </li>
<li>Comes preinstalled with <a href="https://apex.oracle.com/">APEX</a> on it</li>
<li>It's <a href="http://daust.blogspot.com/2012/08/clarification-on-oracle-xe-licensing.html">free for any usage</a>, including production applications </li>
</ul>
<br />
Oracle Express Edition is currently available in version 11gR2 (11.2). Based on <a href="http://www.freelists.org/post/oracle-l/Oracle-XE-12c,1">hints dropped by various folks</a> at Oracle, there <a href="http://ora-00001.blogspot.com/2013/07/first-mentions-of-oracle-12c-xe-express.html">will be a 12c version of Oracle XE</a>, but it is probably a year or two away yet.<br />
<br />
The <a href="http://docs.oracle.com/cd/E17781_01/index.htm">XE documentation</a> is a great starting point for those new to Oracle, with several "2-day guides" to database administration and development tasks.<br />
<br />
Enjoy Oracle XE and <a href="https://twitter.com/hashtag/ThanksOTN?src=hash">#ThanksOTN</a> for this great product! :-)<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com0tag:blogger.com,1999:blog-5215551487816981140.post-80310248671015913512016-09-11T06:12:00.002-07:002016-09-11T08:13:11.487-07:00Using inline dialogs in APEX 5APEX 5 has built-in support for dialog boxes. These come in two varieties; dialog pages (which show another standard APEX page in a dialog box using an iframe) and inline dialogs (which show a region on the current page as a dialog box). John Snyders goes into <a href="http://hardlikesoftware.com/weblog/2015/05/22/apex-5-0-dialogs/">great technical detail</a> about APEX dialogs and the differences between the two types.<br />
<br />
This post is a quick summary of how to use inline dialogs. As John points out, you should consider inline dialogs if the content is mainly static, don't need validation or submit processing, and must display quickly.<br />
<br />
<br />
Start by adding a region to the "Inline Dialogs" display position, and set the template to "Inline Dialog". Set the static id of the region to "my_inline_dialog".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwPm7hC-P5gldzWCNJOqHmEDyc9dB2kWpwGA8q2WEdd-kvMFAiwecxi8TjLI-xll14zmQL5XgFan9jTeBoGhbyGZexedHDViO2ig7lPlb_s1yp1fR3jWELnoYQlPtgqTzQwCpOQ15YB9cX/s1600/inline1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwPm7hC-P5gldzWCNJOqHmEDyc9dB2kWpwGA8q2WEdd-kvMFAiwecxi8TjLI-xll14zmQL5XgFan9jTeBoGhbyGZexedHDViO2ig7lPlb_s1yp1fR3jWELnoYQlPtgqTzQwCpOQ15YB9cX/s640/inline1.png" width="640" /></a></div>
<br />
When the page is rendered, the region in the "Inline Dialogs" display position will be initially hidden. To display the dialog, create a button and define a dynamic action on it to execute the following Javascript code:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">openModal('my_inline_dialog');</span><br />
<br />
<br />
As shown below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipDtYIjL9axxT6XyAh0-1C7QgVmZl4CinJsodg6OxgfiId9e2azlLWPBIapCa7wJe_59QpHpgj6wmB5aq9LrnNAXMCnetPfU-Df_lHQV_NLg5CUA5HpJaWoTG8prBAQlvLnMvB5ZV937Ci/s1600/inline2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="224" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipDtYIjL9axxT6XyAh0-1C7QgVmZl4CinJsodg6OxgfiId9e2azlLWPBIapCa7wJe_59QpHpgj6wmB5aq9LrnNAXMCnetPfU-Df_lHQV_NLg5CUA5HpJaWoTG8prBAQlvLnMvB5ZV937Ci/s640/inline2.png" width="640" /></a></div>
<br />
<br />
Add a close button to the inline dialog region and create a corresponding dynamic action on it to trigger the following Javascript code:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">closeModal();</span><br />
<br />
As shown below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtmlhiNf1mMe_2owmbl7uOa7kp2fvs7GYRBkYPDXJwC1FTvKmzh1ZWpdctRpG3Fph7BDWYDBn-k5qTTDeB-PONh4bF4HZCkdinB3cJqfP7mFwASgJONmf22EjpMq_UsD7eSGs26Ndm1Cul/s1600/inline7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtmlhiNf1mMe_2owmbl7uOa7kp2fvs7GYRBkYPDXJwC1FTvKmzh1ZWpdctRpG3Fph7BDWYDBn-k5qTTDeB-PONh4bF4HZCkdinB3cJqfP7mFwASgJONmf22EjpMq_UsD7eSGs26Ndm1Cul/s640/inline7.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
By the way, these Javascript functions are just short helper functions that are included in the Universal Theme (theme 42), wrappers for jQuery functions:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnKslNEfbCXUY9LAbuLfATC9Fz9rgJfRKtk1gVBqDLn96CyvmI6rAJ-8_hUm2nB3B066o-4l5q4kRhsuo5Bic3MNHzLYeLmvnP3I6NKhdc2zhfFB86ID0OrURkMoFGvR8U4WCpmWLvZVg3/s1600/inline6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnKslNEfbCXUY9LAbuLfATC9Fz9rgJfRKtk1gVBqDLn96CyvmI6rAJ-8_hUm2nB3B066o-4l5q4kRhsuo5Bic3MNHzLYeLmvnP3I6NKhdc2zhfFB86ID0OrURkMoFGvR8U4WCpmWLvZVg3/s640/inline6.png" width="640" /></a></div>
<br />
<br />
When you run the page the dialog region is invisible:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqM-xELoVn7C5CT29V-2iMfzzYYoaizrETNfcvWSOxO-yFOeUbuHqAc40qS2qLIF4vFbRrm7tXAfwt7TvsgR5k3L0m5YwTj_NiFWTrrd_dqipySgLFDbIH7XrqnnzmZlnHqjV81yCKiovY/s1600/inline4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqM-xELoVn7C5CT29V-2iMfzzYYoaizrETNfcvWSOxO-yFOeUbuHqAc40qS2qLIF4vFbRrm7tXAfwt7TvsgR5k3L0m5YwTj_NiFWTrrd_dqipySgLFDbIH7XrqnnzmZlnHqjV81yCKiovY/s640/inline4.png" width="640" /></a></div>
<br />
<br />
When you click on the show button, the dialog appears:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdXdvWv0hs2uh4533fX5Wkovq8qsvJJCLTGg3g0oFxsrc_ts4MEo8zwdcvsLtN_NwV5DEu_GWWLEFFD2hdnLkfHngO9H8uTsPQt0zq6MxDZNMkoCWrKeaiMBSha6QBzIM-kgxZ-k81H7jw/s1600/inline5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="270" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdXdvWv0hs2uh4533fX5Wkovq8qsvJJCLTGg3g0oFxsrc_ts4MEo8zwdcvsLtN_NwV5DEu_GWWLEFFD2hdnLkfHngO9H8uTsPQt0zq6MxDZNMkoCWrKeaiMBSha6QBzIM-kgxZ-k81H7jw/s640/inline5.png" width="640" /></a></div>
<br />
This is a simple example, but remember that you can put anything in the dialog region (reports, charts, lists, PL/SQL dynamic regions, etc), including sub regions. Also remember that whatever content you put in this region will be loaded when the page loads, it will just be hidden until the dialog is displayed. If you need to refresh the content of the dialog, you can use a report region with a dynamic action refresh. To refresh PL/SQL dynamic content, consider using <a href="http://ora-00001.blogspot.com/2012/11/apex-plugin-execute-plsql-code-and-return-content-to-page.html">my dynamic action plugin</a>.<br />
<br />
<br />
Further references on APEX inline dialogs:<br />
<ul>
<li><a href="http://everythingapex.blogspot.com/2015/07/apex-50-getting-down-with-dialogs-part-1.html">Bruce Clark on (Inline) Dialogs</a></li>
<li><a href="http://orclapextips.blogspot.com/2016/07/oracle-apex-5-modal-window-with.html">Rodrigo Mesquita on Modal Window with Universal Theme</a></li>
</ul>
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com1tag:blogger.com,1999:blog-5215551487816981140.post-38086307204615704882016-08-13T08:08:00.000-07:002016-08-13T08:08:56.114-07:00Using the PayPal REST API from PL/SQLDo you need to accept payments for goods and services via your (APEX) application and would you prefer to handle the payments in the database via PL/SQL? Then this blog post is for you... :-)<br />
<br />
Almost a decade ago (in 2007), Oracle released a whitepaper on <a href="http://www.oracle.com/technetwork/developer-tools/apex/integrating-application-express-wit-129259.pdf">Integrating Application Express with PayPal Payments Pro</a> which used PayPal's Name Value Pair (NVP) API.<br />
<br />
In the years since then, PayPal has made available a new API which is based on REST principles.<br />
<br />
I've created a package called PAYPAL_UTIL_PKG to use the PayPal REST API from PL/SQL.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcFahmD3gxfiwWd9LuWy0IIjIlbzB58M_f0NV-viLmGnnYXQ77Y1-ye2HKfC0UjwGYeUP08ROiEeq-MOBSu0CDTMQ3Ha112KXGK19In-x-r2xavFjCJnBkLrHBzwZyvog76A530M9TUXp4/s1600/paypal.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="100" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcFahmD3gxfiwWd9LuWy0IIjIlbzB58M_f0NV-viLmGnnYXQ77Y1-ye2HKfC0UjwGYeUP08ROiEeq-MOBSu0CDTMQ3Ha112KXGK19In-x-r2xavFjCJnBkLrHBzwZyvog76A530M9TUXp4/s320/paypal.png" width="320" /></a></div>
<br />
Note: For an alternative to PayPal, check out Trent Schafer's posts on <a href="http://apextips.blogspot.no/2016/02/accepting-payments-with-stripe-in-apex.html">using Stripe from PL/SQL</a>.<br />
<br /><span style="font-size: large;">Sign up for a PayPal Developer Account</span><br />
<br />
To use the PayPal API you need to sign up for a PayPal account. Go to <a href="https://developer.paypal.com/">https://developer.paypal.com/</a> and sign up. After logging in as a developer, you must register your app to get an API key that you can use for calls to the API. Follow the instructions in the <a href="https://developer.paypal.com/docs/integration/direct/make-your-first-call/">Making your first call</a> article and run the samples using <span style="font-family: "Courier New",Courier,monospace;">curl</span> to get a feel for how the API works, before you start on the PL/SQL integration.<br />
<br />
Note that PayPal provides both a so-called "sandbox" environment for testing, as well as a "live" environment for production. Each environment has its own set of API keys.<br />
<br />
See also the <a href="https://developer.paypal.com/webapps/developer/docs/api/">PayPal API docs</a> for further reference and troubleshooting. <br />
<br />
<span style="font-size: large;">Installing the PL/SQL package</span><br />
<br />
The PAYPAL_UTIL_PKG is part of the <a href="https://github.com/mortenbra/alexandria-plsql-utils">Alexandria Utility Library for PL/SQL</a>.
Download the source or clone the repository from GitHub and install the
package in your database. (Note: To install with minimal dependencies,
run the scripts install_core.sql and install_paypal.sql from the /setup
folder.)<br />
<br />
A note about the code: To support usage in APEX 4, the PAYPAL_UTIL_PKG package does not currently use the APEX_JSON package provided with APEX 5. For the time being, the package uses its own JSON-parsing routines. In the future, the package might be refactored to use APEX_JSON.<br />
<br />
<br />
<span style="font-size: large;">Using the PayPal PL/SQL API</span><br />
<br />
The following diagram (made with <a href="http://websequencediagrams.com/">websequencediagrams.com</a>) illustrates a typical process flow for accepting a payment. The process is further explained below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDx_C4um2df6q8IjUs0VoX7tKsStgg_IS5u0Txld8qFo41CYBAmyva0RAo9TpD5V83gZpiLNLOYKMjOPQaeA3aMb4LX66H3-NGaPRfCj-Jspmp6Gd-XFUkCiHDbGTbBNVlGHGIil1VRiVK/s1600/paypal_api_sequence_diagram.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDx_C4um2df6q8IjUs0VoX7tKsStgg_IS5u0Txld8qFo41CYBAmyva0RAo9TpD5V83gZpiLNLOYKMjOPQaeA3aMb4LX66H3-NGaPRfCj-Jspmp6Gd-XFUkCiHDbGTbBNVlGHGIil1VRiVK/s640/paypal_api_sequence_diagram.png" width="556" /></a></div>
<br />
<br />
First, the user somehow initiates the payment process, typically by clicking a button in your APEX application that runs a PL/SQL process. It is recommended that you create your own package for your application-specific payment logic (in other words, don't put the PL/SQL code inline in the APEX process, just call a procedure in your package and maintain all the logic in your package instead of in the APEX user interface).<br />
<br />
Note: While developing and testing, you may want to use PayPal's sandbox environment, in that case call the <span style="font-family: "Courier New",Courier,monospace;">switch_to_sandbox</span> procedure before any other call to the API.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivr8T9vMUda-8RzNMyvyp0x74b8iHeH6JWiyz1Swtxtti9MuY0shqPO5s9kEzNHDeKQChlCAV7wlO828hGEQ_OMMUrlZ26t-g5xwwYYASY7LSO_OgmD8cOIL9U6fE1bEmcC8CsOlxUNHPu/s1600/Screen+Shot+2016-08-13+at+16.22.40.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivr8T9vMUda-8RzNMyvyp0x74b8iHeH6JWiyz1Swtxtti9MuY0shqPO5s9kEzNHDeKQChlCAV7wlO828hGEQ_OMMUrlZ26t-g5xwwYYASY7LSO_OgmD8cOIL9U6fE1bEmcC8CsOlxUNHPu/s1600/Screen+Shot+2016-08-13+at+16.22.40.png" /></a></div>
<br />
Next, we call <span style="font-family: "Courier New",Courier,monospace;">get_access_token</span> with your API key (which actually consists of both a <span style="font-family: "Courier New",Courier,monospace;">client_id</span> and a <span style="font-family: "Courier New",Courier,monospace;">secret</span>) to get a valid OAuth token to use for subsequent calls to the API.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv0CMQvap6-0XwH64mg_F2sy2VBIqEfTvAEMXt75sVrgHkoBIwdHn57pXvDOuEK_6l19CI-R3Qx9ER_JgiOnh4r-sDDO2OeofcVGo_Q7yPFMKuXUxizPO_b_jkeze5yEq9yER4iR-soCUh/s1600/Screen+Shot+2016-08-13+at+16.19.21.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv0CMQvap6-0XwH64mg_F2sy2VBIqEfTvAEMXt75sVrgHkoBIwdHn57pXvDOuEK_6l19CI-R3Qx9ER_JgiOnh4r-sDDO2OeofcVGo_Q7yPFMKuXUxizPO_b_jkeze5yEq9yER4iR-soCUh/s1600/Screen+Shot+2016-08-13+at+16.19.21.png" /></a></div>
<br />
Then, we call <span style="font-family: "Courier New",Courier,monospace;">create_payment</span> with the details of the transaction (amount, currency, description) as well as the URLs that we want PayPal to return the user to after he has confirmed the payment (<span style="font-family: "Courier New",Courier,monospace;">return_url</span>) or cancelled the transaction (<span style="font-family: "Courier New",Courier,monospace;">cancel_url</span>). These URLs will typically be REST endpoints that you have created using ORDS (more about this later).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgixuaTSJUe_rwEA5gFQ7xcjMHwJ8gwC6Hx8ImyaShtpYLiJ-oJZoic3raik142Nz0pfdY6X6ozBQkYhs9hmA7rBm8KjPB16oQCDpImsCP6sfduTs1VbwBAAhW8hFgagXCOu8dQJtDKWEvy/s1600/Screen+Shot+2016-08-13+at+16.19.27.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgixuaTSJUe_rwEA5gFQ7xcjMHwJ8gwC6Hx8ImyaShtpYLiJ-oJZoic3raik142Nz0pfdY6X6ozBQkYhs9hmA7rBm8KjPB16oQCDpImsCP6sfduTs1VbwBAAhW8hFgagXCOu8dQJtDKWEvy/s1600/Screen+Shot+2016-08-13+at+16.19.27.png" /></a></div>
<br />
The <span style="font-family: "Courier New",Courier,monospace;">create_payment</span> function will return a <span style="font-family: "Courier New",Courier,monospace;">t_payment</span> record. You should save the returned fields in a database table associated with the user's purchase, so you can retrieve the information later. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYQSeJQSdOQemEFwnR6u33NXswSRkkh8rrc39PCGmzaVOYwrnfG3pZDtQtD_U2uXL9IEMvJR3KrRFii2wN66dZBAiXUOoqwx3n6-WM1ItRNci-WUmPdXjWlPXRnM9sypZWm2IlxjaiWbzP/s1600/Screen+Shot+2016-08-13+at+16.25.19.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYQSeJQSdOQemEFwnR6u33NXswSRkkh8rrc39PCGmzaVOYwrnfG3pZDtQtD_U2uXL9IEMvJR3KrRFii2wN66dZBAiXUOoqwx3n6-WM1ItRNci-WUmPdXjWlPXRnM9sypZWm2IlxjaiWbzP/s1600/Screen+Shot+2016-08-13+at+16.25.19.png" /></a></div>
<br />
The <span style="font-family: "Courier New",Courier,monospace;">approval_url</span> field is a PayPal page. We need to redirect the user from our APEX application to this PayPal URL so that the user can login to PayPal and confirm the payment. To do the redirect, add <span style="font-family: "Courier New",Courier,monospace;">owa_util.redirect_url(approval_url)</span> as the final statement in your procedure.<br />
<br />
The browser redirects the user to PayPal, which shows the details of the transaction to the user. If the user approves the transaction, PayPal redirects the user to the URL that you specified as the <span style="font-family: "Courier New",Courier,monospace;">return_url</span> parameter in the call to <span style="font-family: "Courier New",Courier,monospace;">create_payment()</span> above. If the user instead decides to cancel the transaction, PayPal will redirect the user to the <span style="font-family: "Courier New",Courier,monospace;">cancel_url</span> that you specified.<br />
<br />
Let's assume that the user approves the payment, and PayPal redirects the user to the <span style="font-family: "Courier New",Courier,monospace;">return_url</span> that was specified when the payment was created. A payer ID is appended to the return URL, as PayerID. The URL looks like this:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">http://return_url?token=EC-60U79048BN7819909&PayerID=7E7KGXCWTTMU2</span><br />
<br />
To execute the payment after the user's approval, make a call to
<span style="font-family: "Courier New",Courier,monospace;">execute_payment</span> and pass the <span style="font-family: "Courier New",Courier,monospace;">payer_id</span> received via the return URL. You also need to pass the <span style="font-family: "Courier New",Courier,monospace;">payment_id</span>, which was returned from the previous call to <span style="font-family: "Courier New",Courier,monospace;">create_payment</span> (and which you stored in a database table for use later, right?). But how do you know which payment_id is identified with this specific transaction? You would typically generate a unique URL which includes some kind of identifier (such as <span style="font-family: "Courier New",Courier,monospace;">https://servername/payments/confirm/1234</span> where <span style="font-family: "Courier New",Courier,monospace;">1234</span> is some internal ID that you use to keep track of your user's different purchases). Using this example, PayPal would redirect the user to the following URL:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">http://servername/payments/confirm/1234?token=EC-60U79048BN7819909&PayerID=7E7KGXCWTTMU2</span><br />
<br />
To handle requests to this URL, you would set up a GET handler in ORDS so you can parse out the various parts of the request URL, including your own id (1234) which you can use to look up the payment_id, as well as the payer_id.<br />
<br />
Confused? Perhaps PayPal's description <a href="https://developer.paypal.com/docs/integration/web/accept-paypal-payment/">here</a> will clarify the process.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEged9hc-r1hsTLtBMMIvzveAt2esgAgsMHBBmMlJlWmxbcHiTHytjzscomsJUXlj6WibDtSq9OkNh0Tnjo38XeZWOkSE5uEU_erKEp9BSBoyKGDzXOjSf4oYMMwZpZUukvyyd9ZV2th3u83/s1600/Screen+Shot+2016-08-13+at+16.19.33.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="114" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEged9hc-r1hsTLtBMMIvzveAt2esgAgsMHBBmMlJlWmxbcHiTHytjzscomsJUXlj6WibDtSq9OkNh0Tnjo38XeZWOkSE5uEU_erKEp9BSBoyKGDzXOjSf4oYMMwZpZUukvyyd9ZV2th3u83/s640/Screen+Shot+2016-08-13+at+16.19.33.png" width="640" /></a></div>
<br />
When you execute the payment, the user's PayPal account is charged, and you get a <span style="font-family: "Courier New",Courier,monospace;">t_payment</span> record returned.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9yj_oHnWkwiglxSCnC2LQiTDdlRKUzmLLhAuWMaElfAQtnRf0Tdob6b1Vr6MgD5ZUeSvcF1RkFHNPRFOTOwJLOmbjhbmc121bUGiMqNdkKkpL6XiX1XbthlJ_N5AmjYK_sx5_VWSek7ik/s1600/Screen+Shot+2016-08-13+at+16.25.19.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9yj_oHnWkwiglxSCnC2LQiTDdlRKUzmLLhAuWMaElfAQtnRf0Tdob6b1Vr6MgD5ZUeSvcF1RkFHNPRFOTOwJLOmbjhbmc121bUGiMqNdkKkpL6XiX1XbthlJ_N5AmjYK_sx5_VWSek7ik/s1600/Screen+Shot+2016-08-13+at+16.25.19.png" /></a></div>
<br />
You can then check the value of the <span style="font-family: "Courier New",Courier,monospace;">state</span> field in the payment record. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdzJm6_PxwUI8QcJTIIBmAf48sXyIMHyiGfbmiqWoKj03PnMNnXD0tiu7MLIAdr4pGzVngX48rozmzwDmv_X9gsLa2xjzOZ2QcWcvSBHnHNXLoorxYzUidyqN9_U5C9mHIwSVNrkneZ8SR/s1600/Screen+Shot+2016-08-13+at+16.53.36.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdzJm6_PxwUI8QcJTIIBmAf48sXyIMHyiGfbmiqWoKj03PnMNnXD0tiu7MLIAdr4pGzVngX48rozmzwDmv_X9gsLa2xjzOZ2QcWcvSBHnHNXLoorxYzUidyqN9_U5C9mHIwSVNrkneZ8SR/s1600/Screen+Shot+2016-08-13+at+16.53.36.png" /></a></div>
<br />
If the state is equal to <span style="font-family: "Courier New",Courier,monospace;">"approved"</span>, then the payment is OK and you can provide whatever goods or services the user has purchased (or at least show a confirmation page to notify the user that the transaction has succeeded).<br />
<br />
The money should already be in your PayPal account! :-) <br />
<br />
<br />
<br />
<br />
Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2tag:blogger.com,1999:blog-5215551487816981140.post-53505851043927310932016-07-23T04:06:00.000-07:002016-07-23T04:06:46.687-07:00Minimal privileges for Amazon S3 backup userThis is a follow-up to an old post I did about <a href="http://ora-00001.blogspot.com/2012/08/database-schema-backup-to-the-cloud-with-plsql.html">how to backup Oracle database schemas to Amazon S3 using PL/SQL</a>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiURJ9ZAlwu3MFNmlcWPq_tPimk5uz9PpRcfcowK2S4gDeTmkxvONXI5KrQwr0W5rm3q3fZb7_MjxMLxvYr7v8h28NBSw7M8ezvU4ewF0FXkkHB7RS22a5ITwNaPodmz573inEJOD4opCuW/s1600/S3-.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiURJ9ZAlwu3MFNmlcWPq_tPimk5uz9PpRcfcowK2S4gDeTmkxvONXI5KrQwr0W5rm3q3fZb7_MjxMLxvYr7v8h28NBSw7M8ezvU4ewF0FXkkHB7RS22a5ITwNaPodmz573inEJOD4opCuW/s320/S3-.png" width="320" /></a></div>
<br />
In short, the packages provided in the <a href="https://github.com/mortenbra/alexandria-plsql-utils">Alexandria Utility Library for PL/SQL</a> allow you to set up a schema-level backup of files from your database to Amazon's <a href="https://aws.amazon.com/s3/">Simple Storage Service (S3)</a>.<br />
<br />
At the end of that article I mentioned that you should use <a href="https://aws.amazon.com/iam/">AWS Identity and Access Management (IAM)</a> to create a separate backup user that has minimal privileges, to reduce the risk if the password ("Secret Access Key" in S3 terminology) is ever exposed. By creating a separate user which only has upload ("PutObject") permissions on your S3 folder, and no privileges to list file contents, delete or download files, you can limit the damage that someone with a stolen password can do.<br />
<br />
Here's how to set this up:<br />
<br />
<ol>
<li>Go to your AWS admin console.</li>
<li>Go to S3 service and create a new bucket (your-bucket-name).</li>
<li>Create a folder called "backup".</li>
<li>Optionally, create subfolders "backup/schemas" and "backup/apps".</li>
<li>Go to IAM service.</li>
<li>Go to Groups and create a new group ("MySiteBackupUsers"). Do not add any of the default policies.</li>
<li>Go to Permissions for the group and under "Inline Policies", click on "Create Group Policy". Choose "Custom Policy".</li>
<li>Policy Name: "UploadFilesToBackup".</li>
<li>Add the following policy definition: </li>
</ol>
<span style="font-family: "Courier New",Courier,monospace;"> {<br /> "Statement": [<br /> {<br /> "Action": [<br /> "s3:PutObject"<br /> ],<br /> "Effect": "Allow",<br /> "Resource": "arn:aws:s3:::your-bucket-name/backup/*"<br /> }<br /> ]<br />}</span><br />
<br />
Here is a screenshot:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF4b3u4bJ947ggH7WuVDWj77daQfpinMO9jYzGJj5AhLV6qb5ySzJoOVgHFOBbcwKRpBkWyTq92rLv1vdlT_X_-NrXX5ZsSu3PnmOvoVpxHzLMOx2nXKsi5K0607FwJwHihXORsyHqKPyq/s1600/aws_iam1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="339" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF4b3u4bJ947ggH7WuVDWj77daQfpinMO9jYzGJj5AhLV6qb5ySzJoOVgHFOBbcwKRpBkWyTq92rLv1vdlT_X_-NrXX5ZsSu3PnmOvoVpxHzLMOx2nXKsi5K0607FwJwHihXORsyHqKPyq/s640/aws_iam1.JPG" width="640" /></a></div>
<br />
<br />
Then go to Users and click "Create New Users". Enter a user name ("MySiteBackupUser"). After the user has been created, click on "Show User Security Credentials" and copy the values (Access Key ID and Secret Access Key) into your backup script settings.<br />
Finally, add the user to the newly created user group (add user "MySiteBackupUser" to group "MySiteBackupUsers").<br /><br />That's it, you now have a minimally privileged user account that you can use for your S3 backup scripts from PL/SQL.<br />
<br />
<br />Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.com2