tag:blogger.com,1999:blog-246861532024-03-16T00:36:56.737-07:00Anton NielsenThis blog is mostly random experiences with Oracle technologies. Of those, it is mostly specific solutions to isolated problems. If you would like assistance with anything related to Oracle technologies click on the My Work link to the right. I work for <a href="https://insum.ca">Insum</a> which has some of the best Oracle technologists around.Anton Nielsenhttp://www.blogger.com/profile/01507583135719899159noreply@blogger.comBlogger94125tag:blogger.com,1999:blog-24686153.post-6445011819833076292022-02-28T07:36:00.003-08:002022-02-28T07:36:24.773-08:00Think Time after Viewing an APEX Interactive Report (IR)<p> I had someone ask me how to get the think time after someone views an Interactive Report. I think the query below does it.</p><p><span style="font-family: courier;">with page_views_with_previous_view_id as </span></p><p><span style="font-family: courier;"> (select application_id, page_id, apex_user, apex_session_id, page_view_type, view_timestamp, </span></p><p><span style="font-family: courier;"> lead(view_timestamp, 1) OVER (ORDER BY view_timestamp desc) AS prev_view_ts</span></p><p><span style="font-family: courier;"> from apex_workspace_activity_log al</span></p><p><span style="font-family: courier;"> where application_id = :APP_ID</span></p><p><span style="font-family: courier;"> )</span></p><p><span style="font-family: courier;"> select pv.*,</span></p><p><span style="font-family: courier;"> pv.view_timestamp - ppv.view_timestamp think_time,</span></p><p><span style="font-family: courier;"> ppv.interactive_report_id ppv_ir_id</span></p><p><span style="font-family: courier;"> from page_views_with_previous_view_id pv -- page_view</span></p><p><span style="font-family: courier;"> inner join apex_workspace_activity_log ppv -- previous_page_view</span></p><p><span style="font-family: courier;"> on ppv.view_timestamp = pv.prev_view_ts</span></p><p><span style="font-family: courier;"> and ppv.application_id = :APP_ID</span></p><p><span style="font-family: courier;"> and ppv.apex_session_id = pv.apex_session_id</span></p><p><span style="font-family: courier;"> and ppv.page_id = 2</span></p><p><span style="font-family: courier;"> and ((ppv.interactive_report_id = 38006896117622159377) -- if you hard code the ID</span></p><p><span style="font-family: courier;"> or -- use the region static id</span></p><p><span style="font-family: courier;"> (ppv.interactive_report_id = (select pir.interactive_report_id </span></p><p><span style="font-family: courier;"> from apex_application_page_ir pir </span></p><p><span style="font-family: courier;"> inner join apex_application_page_regions pr on pr.region_id = pir.region_id</span></p><p><span style="font-family: courier;"> where pir.application_id = :APP_ID </span></p><p><span style="font-family: courier;"> and pir.page_id = 2</span></p><p><span style="font-family: courier;"> and pr.static_id ='ANTON_IR_STATIC_ID')</span></p><p><span style="font-family: courier;"> )</span></p><p><span style="font-family: courier;"> or</span></p><p><span style="font-family: courier;"> (ppv.page_view_type = 'Rendering')</span></p><p><span style="font-family: courier;"> )</span></p><p><span style="font-family: courier;"> order by pv.view_timestamp desc</span></p><p><br /></p><p><br /></p><p>Maybe this will help another person as well.</p>Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com2tag:blogger.com,1999:blog-24686153.post-7286657022054755172019-11-19T13:28:00.002-08:002019-11-20T07:25:33.386-08:00APEX 19.1 to 19.2 upgrade issue - Error: ORA-20001: Error fetching column value: ORA-20987: Invalid LOV type specified.I can't say for certain that this came up during the 19.1 to 19.2 upgrade. It is possible it showed up during an earlier upgrade but that it was just caught during regression testing from 19.1 to 19.2. In any case, you may come across the following APEX error in an Interactive Report region (and possibly other places):<br />
<br />
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; line-height: 1.5;">report error:
ORA-20001: Error fetching column value: ORA-20987: Invalid LOV type specified.</pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; line-height: 1.5;"></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; line-height: 1.5;"><span style="color: black; font-family: "times"; font-size: small; white-space: normal;">The debug error stack might include:</span></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; line-height: 1.5;"></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; line-height: 1.5;"><pre class="l2" style="box-sizing: border-box; color: red; font-family: SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; font-size: 11px; font-weight: bold; line-height: 14px; white-space: pre-wrap;">Error Stack: ORA-20001: Error fetching column value: ORA-20987: Invalid LOV type specified.
Backtrace: ORA-06512: at "APEX_190200.WWV_RENDER_REPORT3", line 7623</pre>
<pre class="l2" style="box-sizing: border-box; color: red; font-family: SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; font-size: 11px; font-weight: bold; line-height: 14px; white-space: pre-wrap;">Error: ORA-20001: Error fetching column value: ORA-20987: Invalid LOV type specified.</pre>
</pre>
<pre class="l1" style="box-sizing: border-box; color: red; font-family: SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; font-size: 11px; font-weight: bold; line-height: 14px; white-space: pre-wrap;"></pre>
<pre class="l1" style="box-sizing: border-box; color: red; font-family: SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; font-size: 11px; font-weight: bold; line-height: 14px; white-space: pre-wrap;">```</pre>
<pre class="l1" style="box-sizing: border-box; color: red; font-family: SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; font-size: 11px; font-weight: bold; line-height: 14px; white-space: pre-wrap;"></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"><pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="color: black; font-family: "times"; font-size: small; white-space: normal;">In short, the error is due to a SQL Query LOV that uses the same column for the display and return values, for example:</span></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="color: black; font-family: "times"; font-size: small; white-space: normal;">
</span></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="color: black; font-size: small; white-space: normal;"><span style="font-family: "courier new" , "courier" , monospace;">select col1, col1 </span></span></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="font-family: "courier new" , "courier" , monospace;"> from my_table</span></pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="color: black; font-family: "times"; font-size: small; white-space: normal;">
</span></pre>
<pre style="background-color: white; box-sizing: border-box; font-weight: 400; line-height: 1.5;"><span style="font-family: "times";"><span style="white-space: normal;">This really should include column aliases (at least one) so that the sql statement doesn't have the same column resulting twice:</span></span></pre>
<pre style="background-color: white; box-sizing: border-box; font-weight: 400; line-height: 1.5;"><span style="font-family: "times";"><span style="white-space: normal;">
</span></span></pre>
<pre style="background-color: white; box-sizing: border-box; font-weight: 400; line-height: 1.5;"><pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"><pre style="box-sizing: border-box; color: #404040; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="color: black; font-size: small; white-space: normal;"><span style="font-family: "courier new" , "courier" , monospace;">select col1 d, col1 r</span></span></pre>
<pre style="box-sizing: border-box; color: #404040; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"> from my_table</pre>
</pre>
</pre>
<pre style="background-color: white; box-sizing: border-box; color: #404040; font-family: Menlo, Consolas, mono-space; font-size: 1.2rem; font-weight: 400; line-height: 1.5; white-space: pre-wrap;"><span style="color: black; font-family: "times"; font-size: small; white-space: normal;">APEX 19.2 appears to make this mandatory.</span></pre>
<span style="font-family: inherit;">
</span></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"></pre>
<pre class="l1" style="box-sizing: border-box; line-height: 14px;"><span style="font-family: inherit; font-size: large;">Update: This is a documented change.</span></pre>
<pre class="l1" style="box-sizing: border-box; line-height: 14px;"><span style="font-family: inherit; font-size: large;">
</span></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"><a href="https://docs.oracle.com/en/database/oracle/application-express/19.2/htmrn/index.html#HTMRN-GUID-7E820117-3C25-404D-88D8-B675C9519063">https://docs.oracle.com/en/database/oracle/application-express/19.2/htmrn/index.html#HTMRN-GUID-7E820117-3C25-404D-88D8-B675C9519063</a></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"></pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;"><div style="background-color: #fcfbfa; box-sizing: border-box; color: #1a1816; font-family: "Oracle Sans", -apple-system, system-ui, "Segoe UI", "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-stretch: normal; font-weight: 400; line-height: 1.44; margin-bottom: 0.5em; white-space: normal;">
To help you identify such cases, you can run the following script which outputs any possible problematic inline or shared List of Values:</div>
<pre class="pre codeblock hljs" style="background: rgb(255, 255, 255); border-radius: 6px; border: 2px solid rgb(222, 218, 214); box-sizing: border-box; font-size: 16px; font-stretch: normal; font-weight: 400; line-height: 1.44; margin-bottom: 1em; margin-top: 1em; overflow-wrap: normal; overflow: auto; padding: 18px; word-break: normal;"><code style="background-attachment: inherit; background-clip: inherit; background-image: inherit; background-origin: inherit; background-position: inherit; background-repeat: inherit; background-size: inherit; border-radius: 0px; box-sizing: border-box; color: #1a1816; font-family: "Courier New", Courier, monospace; font-stretch: normal; line-height: 1.44; padding: 0px;">apex/utilities/check_lovs_for_errors.sql</code></pre>
</pre>
<pre class="l1" style="box-sizing: border-box; font-weight: bold; line-height: 14px;">Many thanks to jmjcloud for pointing this out.</pre>
Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com1tag:blogger.com,1999:blog-24686153.post-54149443312140150872019-09-18T12:20:00.001-07:002019-09-18T12:21:57.553-07:00What Info is Available from my APEX Social Sign-In Provider?Sorry for the brevity, but I hope this helps someone. If you have configured APEX Social Sign-In with Google, Facebook, Okta, Azure--pick your poison--you may have struggled with figuring out what user information is available to you. If you know the name of the value you are looking for, it is pretty easy to get, but figuring out just what is being returned is not documented anywhere (at least not that I can find). So, here is what you can do...<br />
<div>
<br /></div>
<div>
<ul>
<li>Create an application item A_SOCIAL_INFO</li>
<li>Edit your Social Sign-In authentication scheme and add the following pl/sql </li>
</ul>
<span style="font-family: "courier new" , "courier" , monospace;">procedure post_authentication is</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span><span style="font-family: "courier new" , "courier" , monospace;">l_index varchar2(32767);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">l_kind number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">l_value varchar2(32767);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span><span style="font-family: "courier new" , "courier" , monospace;"> l_index := apex_json.g_values.first;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> :A_SOCIAL_INFO := '-- Start -- <br>';</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> for i in 1..(apex_json.g_values.count) loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_kind := apex_json.g_values(l_index).kind ;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> if l_kind = 4 then</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_value := apex_json.g_values(l_index).number_value;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> elsif l_kind = 5 then</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_value := apex_json.g_values(l_index).varchar2_value;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> else</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_value := null;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> end if; </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> :A_SOCIAL_INFO := :A_SOCIAL_INFO || l_index || ': ' || l_kind || ':' || l_value || '<br><br>';</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_index := apex_json.g_values.next(l_index);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> :A_SOCIAL_INFO := :A_SOCIAL_INFO || '-- End -- <br>';</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">exception</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> when others then</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> :A_SOCIAL_INFO := :A_SOCIAL_INFO || ' err: ' ||sqlerrm;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">end;</span><br />
<ol><br /></ol>
<ul>
<li>Add <span style="font-family: "courier new" , "courier" , monospace;">post_authentication</span> as your Post-Authentication Procedure Name</li>
<li>Create a region on a page with region source &A_SOCIAL_INFO.</li>
</ul>
<div>
<br /></div>
<div>
Run the application with a new session. Review the results on your page.</div>
</div>
<div>
<br /></div>
<div>
I have noticed that some IdP's will not send information unless you explicitly request it as an additional attribute. In your AuthN scheme, add name,address to the <span style="background-color: white; color: #404040; font-family: , , "segoe ui" , "roboto" , "oxygen" , "ubuntu" , "cantarell" , "fira sans" , "droid sans" , "helvetica neue" , sans-serif; font-size: 12px; text-align: right;">Additional User Attributes.</span></div>
<div>
<span style="background-color: white; color: #404040; text-align: right;"><span style="font-family: inherit;"><br /></span></span></div>
<div>
<span style="background-color: white; color: #404040; text-align: right;"><span style="font-family: inherit;">You can then use </span></span><span style="background-color: white; color: #4c4c4c; font-family: monospace; font-size: 12px;">apex_json.get_varchar2 </span><span style="background-color: white; color: #4c4c4c;"><span style="font-family: inherit;">to get any values based upon what you see as names in the result.</span></span></div>
<div>
<span style="background-color: white; color: #4c4c4c;"><span style="font-family: inherit;"><br /></span></span></div>
<div>
<span style="background-color: white; color: #4c4c4c;"><span style="font-family: inherit;">I realize this is brief and lacks any pictures. If you have questions, feel free to leave a comment.</span></span><br />
<span style="background-color: white; color: #4c4c4c;"><span style="font-family: inherit;"><br /></span></span>
<span style="background-color: white; color: #4c4c4c;"><span style="font-family: inherit;">p.s. I'll try to get specific posts up soon for Azure, Google, Facebook, Okta, etc.</span></span></div>
Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com1tag:blogger.com,1999:blog-24686153.post-23802264564946491052019-06-27T14:29:00.001-07:002021-05-19T06:05:12.844-07:00Oracle APEX Social Sign-In with Microsoft Azure Active Directory (Again)As you probably know, in our industry nothing stays the same for long. Less than six months ago I posted about this topic and provided screen shots of the Microsoft UI for registering an application. That UI has been deprecated, due to be gone shortly. I also found that it lacked some of the features that can be found in the MS Azure portal. So, here is a revision to my January 31, 2019 post.<br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">My co-worker Adrian Png has written a few great posts on social sign-on with APEX. I'd like to provide a quick cookbook with some interesting details here. If you get a chance, take a look at Adrian's posts:</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">https://fuzziebrain.com/content/id/1709/</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">https://fuzziebrain.com/content/id/1711/</span><br />
<br />
<h2>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Here is the quick cookbook.</span></h2>
On the Microsoft side, you will need a few things. I'll give examples below:<br />
<br />
APEX URL: <span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">https://myapex.com/ords/</span><br />
Redirect URI: [APEX URL]/apex_authentication.callback<br />
<br />
You may also want a home page url, logo, terms of service and privacy policy urls. Those are optional though.<br />
<br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">So, let's assume your <b>APEX url </b>is</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">https://myapex.com/ords/</span><br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Log in to the Azure Portal</span><br />
<a href="https://portal.azure.com/">https://portal.azure.com</a><br />
<br />
<h4>
Navigate to Azure Active Directory > App registrations</h4>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-EdVhaYnlTJ43VIqeDENw1o57HiPRomNTiTbzumrLoFXGxZAyo-14nz-LFe3GQiaFWLZnONkYsduGjgzlyvQsdH0qHnmO7kthIJb-37lDL5TI7TcdvPxAJGuEAXeYUhWGU2Ke/s1600/Screen+Shot+2019-06-27+at+2.31.25+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="226" data-original-width="476" height="151" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-EdVhaYnlTJ43VIqeDENw1o57HiPRomNTiTbzumrLoFXGxZAyo-14nz-LFe3GQiaFWLZnONkYsduGjgzlyvQsdH0qHnmO7kthIJb-37lDL5TI7TcdvPxAJGuEAXeYUhWGU2Ke/s320/Screen+Shot+2019-06-27+at+2.31.25+PM.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<h4>
Click New Registration and fill it in</h4>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLYi1vef08c41uSu8h3LDUEPYHwJZvAIHquVAsXN5ttCJkPpI6K48cdjdCx68yMXGiVPhVr6mmP7-rI84XV8ykxP_5d84QndiZcXHwXQg8wlbriOn0B8L1ZGT2gu1wPsVaD3Ir/s1600/Screen+Shot+2019-06-27+at+2.33.46+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="521" data-original-width="728" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLYi1vef08c41uSu8h3LDUEPYHwJZvAIHquVAsXN5ttCJkPpI6K48cdjdCx68yMXGiVPhVr6mmP7-rI84XV8ykxP_5d84QndiZcXHwXQg8wlbriOn0B8L1ZGT2gu1wPsVaD3Ir/s320/Screen+Shot+2019-06-27+at+2.33.46+PM.png" width="320" /></a></div>
Name is anything you would like to identify your application.<br />
<br />
Read the help on Supported account types and choose accordingly. Note: this is an improvement over the earlier registration option described in my January 2019 post.<br />
<br />
Redirect URI is your APEX url followed by /apex_authentication.callback:<br />
https://myapex.com/ords/apex_authentication.callback<br />
<br />
<h4>
Copy your Application (Client) ID </h4>
You will need it later<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOb2HKTY8PciABN_9rsP1x6IVwT4fWOZISKmQWF8E6qZL64Bub0dxCGTDkbmQc99SMswwai4frKcoJ7cWwwaa7BFitotuusKYUohbuX_feCYcwF5VDAzPfoyPvNdewoV7mU7ra/s1600/Screen+Shot+2019-06-27+at+2.44.37+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="232" data-original-width="530" height="139" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOb2HKTY8PciABN_9rsP1x6IVwT4fWOZISKmQWF8E6qZL64Bub0dxCGTDkbmQc99SMswwai4frKcoJ7cWwwaa7BFitotuusKYUohbuX_feCYcwF5VDAzPfoyPvNdewoV7mU7ra/s320/Screen+Shot+2019-06-27+at+2.44.37+PM.png" width="320" /></a></div>
<br />
You can optionally add information under the branding section.<br />
<h2>
</h2>
<h2>
Navigate to Certificates and Secrets</h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-aNjLKAp6F5yg4zHSs4zQmPBHXBhpy64RcijzcqiV-NqcJNXHrLTZ2kughxWpLFktf6fdTqmORaWnzE3wPM9CR6Uob3DiaEbNYDKQV9MDoBlwTS9KJzeK6_ujpTN7tUhq8l-_/s1600/Screen+Shot+2019-06-27+at+2.39.01+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="553" data-original-width="1285" height="137" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-aNjLKAp6F5yg4zHSs4zQmPBHXBhpy64RcijzcqiV-NqcJNXHrLTZ2kughxWpLFktf6fdTqmORaWnzE3wPM9CR6Uob3DiaEbNYDKQV9MDoBlwTS9KJzeK6_ujpTN7tUhq8l-_/s320/Screen+Shot+2019-06-27+at+2.39.01+PM.png" width="320" /></a></div>
<br />
<br />
<br />
<h4>
Click New Client Secret and add it</h4>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrIuZsB6rA3Mw6M-oMNRNEwU05VsjHKRNQPh6q1wJf7yX51vMLtiKANB1yTJmtA7TGYynMD2JRQBWDOPCUL2fEWV8e2P6pMEgfBrIcS7DzvAqnEed33vwctitMe_MRmEGFlMHd/s1600/Screen+Shot+2019-06-27+at+2.42.13+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="294" data-original-width="464" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrIuZsB6rA3Mw6M-oMNRNEwU05VsjHKRNQPh6q1wJf7yX51vMLtiKANB1yTJmtA7TGYynMD2JRQBWDOPCUL2fEWV8e2P6pMEgfBrIcS7DzvAqnEed33vwctitMe_MRmEGFlMHd/s320/Screen+Shot+2019-06-27+at+2.42.13+PM.png" width="320" /></a></div>
<br />
<br />
Copy the value of your secret. <span style="color: red;">NOTE: You won't have another opportunity to copy this. Make sure you get it now.</span><br />
<br />
<br />
The application will need to be able to read minimal information to work. You need to either consent to this on your users' behalf, or allow them to do so themselves. In order to consent, you can edit the user settings and set the first item below to Yes.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQs5S8QVpda5AOJbxFQc494d3hyt2JF7bfilLQpBLdxErbI5HG24G5T9Mgx-av6YFpsSGqSZhb_8zK0tH1OQQfrCxhTZnv5MCXXpKU3485sOGfNZU3dwQZwJn-z89N-fl4N_rL/s1600/Screen+Shot+2019-06-27+at+5.21.32+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="515" data-original-width="1289" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQs5S8QVpda5AOJbxFQc494d3hyt2JF7bfilLQpBLdxErbI5HG24G5T9Mgx-av6YFpsSGqSZhb_8zK0tH1OQQfrCxhTZnv5MCXXpKU3485sOGfNZU3dwQZwJn-z89N-fl4N_rL/s320/Screen+Shot+2019-06-27+at+5.21.32+PM.png" width="320" /></a></div>
<br />
<br />
If you want to consent on the users behalf, follow this document:<br />
<a href="https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent">https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent</a><br />
<br />
In particular, you would call the URL below, replacing the appropriate elements.<br />
<br />
// Line breaks are for legibility only.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">GET https://login.microsoftonline.com/{tenant}/adminconsent?</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">client_id=6731de76-14a6-49ae-97bc-6eba6914391e</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">&state=12345</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">&redirect_uri=http://localhost/myapp/permissions</span><br />
<br />
<br />
<br />
If you are just doing the Microsoft Azure Active Directory / Office365 portion, you are done. Just give the Application ID and Client Secret to your APEX developers and they will take it from here.<br />
<br />
<br />
<h3>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">OK, on to the APEX installation portion.</span></h3>
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Here's an important item...the client secret (or password) that we got from the application registration is a long string. I'll use this as my example: myMsFtsecret{hasSpecial~Characters. That's a great secret, but it won't work just as you see it. You'll get the following error:</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: #fef7e0; color: #333333; font-family: "helvetica neue" , "segoe ui" , "helvetica" , "arial" , sans-serif; font-size: 14px;">AADSTS50012: Invalid client secret is provided. Trace ID: 830c95a3-9bb7-9de1-8a22dcca1600 Correlation ID: 572ef7ea--4329-9674-9e9e3468982a Timestamp: 2019-01-15 22:05:41Z</span><br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">The problem is that the secret needs to be url encoded. So, let's do that first.</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Log into the APEX builder and navigate to SQL Workshop > SQL Commands and do what needs to be done:</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">select apex_util.url_encode('</span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">myMsFtsecret{hasSpecial~Characters</span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">') the_stuff</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"> from dual</span><br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">That's the stuff:</span><br />
<span style="background-color: #f9f9f9; color: #404040; font-family: , , "segoe ui" , "roboto" , "oxygen" , "ubuntu" , "cantarell" , "fira sans" , "droid sans" , "helvetica neue" , sans-serif; font-size: 13.3333px;">myMsFtsecret%7BhasSpecial%7ECharacters</span><br />
<span style="background-color: #f9f9f9; color: #404040; font-family: , , "segoe ui" , "roboto" , "oxygen" , "ubuntu" , "cantarell" , "fira sans" , "droid sans" , "helvetica neue" , sans-serif; font-size: 13.3333px;"><br /></span>
<span style="background-color: white; font-family: , , "segoe ui" , "roboto" , "oxygen" , "ubuntu" , "cantarell" , "fira sans" , "droid sans" , "helvetica neue" , sans-serif; font-size: 13.3333px;">It's reasonable to think that the application ID might need to be encoded as well. I've never seen an application id with special characters, though.</span><br />
<span style="background-color: white; font-family: , , "segoe ui" , "roboto" , "oxygen" , "ubuntu" , "cantarell" , "fira sans" , "droid sans" , "helvetica neue" , sans-serif; font-size: 13.3333px;"><br /></span>
<br />
<h3>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Now navigate to App Builder > Workspace Utilities > Web Credentials. Create credential.</span></h3>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Enter the elements as described. It's worth reading the help.</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2JnUI3axq-lG9TrrT38cS4vMjvn23O5nSuaCbt1xyE2PRQUG3dVlcZJ1_VqRNaqP-G5IDpy55gyjqwbPLSmVFpvlrmlMLgXLVkfdqOs84OLTBuT2Z82L0silcXrmFZxjghbhi/s1600/Screen+Shot+2019-06-27+at+4.34.35+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="654" data-original-width="829" height="252" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2JnUI3axq-lG9TrrT38cS4vMjvn23O5nSuaCbt1xyE2PRQUG3dVlcZJ1_VqRNaqP-G5IDpy55gyjqwbPLSmVFpvlrmlMLgXLVkfdqOs84OLTBuT2Z82L0silcXrmFZxjghbhi/s320/Screen+Shot+2019-06-27+at+4.34.35+PM.png" width="320" /></a></div>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">I put the secret in the comments so you can see it.</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Almost there. Now go to your application and create an authentication scheme.</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJYHvQAVAC4UgRE8NfCLsHGqOStVumyzPg-nPv5ffcL2WGftpzYF4AnPpS9atwYwpMmYhiYZKELzC5AafPVnzNt-_1V5MPTOef41WtvO7N_pWWOc7Y9xkvpW3d2t1QCZYw9pxa/s1600/Screen+Shot+2019-06-27+at+4.39.43+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="782" data-original-width="937" height="267" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJYHvQAVAC4UgRE8NfCLsHGqOStVumyzPg-nPv5ffcL2WGftpzYF4AnPpS9atwYwpMmYhiYZKELzC5AafPVnzNt-_1V5MPTOef41WtvO7N_pWWOc7Y9xkvpW3d2t1QCZYw9pxa/s320/Screen+Shot+2019-06-27+at+4.39.43+PM.png" width="320" /></a></div>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Name: Your Choice</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Scheme Type: Social Sign-In</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Credential Store: MSAzureCred (the one you created)</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Authorization Endpoint URL*: https://login.microsoftonline.com/common/oauth2/v2.0/authorize</span><br />
Note: depending on who you are allowing to log into your application, you may need to change /common/ to be /yourCompanyDomain/. For example, it may need to be<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">https://login.microsoftonline.com/</span><b style="color: #333333; font-family: georgia, serif; font-size: 13px;">insum.ca</b><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">/oauth2/v2.0/authorize</span><br />
<span style="color: #333333; font-family: "georgia" , serif;"><span style="font-size: 13px;"><br style="background-color: white;" /></span></span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Token Endpoint URL*: https://login.microsoftonline.com/common/oauth2/v2.0/token</span><br />
Note: depending on who you are allowing to log into your application, you may need to change /common/ to be /yourCompanyDomain/. For example, it may need to be<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">https://login.microsoftonline.com/</span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><b>insum.ca</b></span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">/oauth2/v2.0/token</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">User Info Endpoint URL: https://graph.microsoft.com/v1.0/me</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Scope: User.Read</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Username Attribute: userPrincipalName</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
Convert User Name to Upper Case: Yes -- you'll thank me for this if you use APEX ACLs.<br />
<br />
<h4>
* This is the error mentioned above that you get if you use /common/ when you should use /yourDomain/:</h4>
==============<br />
<div aria-level="1" class="row text-title" data-bind="text: svr.strHeaderText" role="heading" style="background-color: white; box-sizing: border-box; color: #404040; font-family: "segoe ui", "helvetica neue", "lucida grande", roboto, ebrima, "nirmala ui", gadugi, "segoe xbox symbol", "segoe ui symbol", "meiryo ui", "khmer ui", tunga, "lao ui", raavi, "iskoola pota", latha, leelawadee, "microsoft yahei ui", "microsoft jhenghei ui", "malgun gothic", "estrangelo edessa", "microsoft himalaya", "microsoft new tai lue", "microsoft phagspa", "microsoft tai le", "microsoft yi baiti", "mongolian baiti", "mv boli", "myanmar text", "cambria math"; font-size: 1.5rem; font-weight: 600; line-height: 1.75rem; margin: 16px 0px 12px; padding: 0px;">
Sign in</div>
<div class="row text-body" data-bind="hasFocus: true" style="background-color: white; box-sizing: border-box; color: #262626; font-size: 15px; line-height: 1.25rem; margin: 16px 0px 12px; padding: 0px;" tabindex="-1">
<div class="text-block-body no-margin-top" data-bind="text: svr.unsafe_strTopMessage" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; padding: 0px;">
Sorry, but we’re having trouble signing you in.</div>
</div>
<div class="row text-body" data-bind="text: svr.strExceptionClassName" style="background-color: white; box-sizing: border-box; color: #262626; font-size: 15px; line-height: 1.25rem; margin: 16px 0px 12px; padding: 0px;">
</div>
<div class="row text-body" data-bind="text: unsafe_exceptionMessage" style="background-color: white; box-sizing: border-box; color: #262626; font-size: 15px; line-height: 1.25rem; margin: 16px 0px 12px; padding: 0px;">
AADSTS50194: Application 'your-app-id'(YourAppName) is not configured as a multi-tenant application. Usage of the /common endpoint is not supported for such applications created after '10/15/2018'. Use a tenant-specific endpoint or configure the application to be multi-tenant.</div>
==============<br />
<br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">You might be done...if you have the root cert for https://graph.microsoft.com/v1.0/me already in your wallet. If not get your dba to add the certificate from https://graph.microsoft.com/v1.0/me to the Oracle wallet.</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">The other option is to follow Adrian's post (link at the top) to use a reverse proxy instead.</span><br />
<br style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;" />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">That does it! Maybe...</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<br />
<h2>
Another error you might get is:</h2>
<br />
<br />
<div aria-level="1" class="row text-title" data-bind="text: str['CT_STR_Consent_Error_Header']" role="heading" style="background-color: white; box-sizing: border-box; color: #404040; font-family: "Segoe UI", "Helvetica Neue", "Lucida Grande", Roboto, Ebrima, "Nirmala UI", Gadugi, "Segoe Xbox Symbol", "Segoe UI Symbol", "Meiryo UI", "Khmer UI", Tunga, "Lao UI", Raavi, "Iskoola Pota", Latha, Leelawadee, "Microsoft YaHei UI", "Microsoft JhengHei UI", "Malgun Gothic", "Estrangelo Edessa", "Microsoft Himalaya", "Microsoft New Tai Lue", "Microsoft PhagsPa", "Microsoft Tai Le", "Microsoft Yi Baiti", "Mongolian Baiti", "MV Boli", "Myanmar Text", "Cambria Math"; font-size: 1.5rem; font-weight: 600; line-height: 1.75rem; margin: 16px 0px 12px; padding: 0px;">
Need admin approval</div>
<div class="table row-app-info" style="background-color: white; box-sizing: border-box; color: #262626; display: table; font-family: "Segoe UI Webfont", -apple-system, "Helvetica Neue", "Lucida Grande", Roboto, Ebrima, "Nirmala UI", Gadugi, "Segoe Xbox Symbol", "Segoe UI Symbol", "Meiryo UI", "Khmer UI", Tunga, "Lao UI", Raavi, "Iskoola Pota", Latha, Leelawadee, "Microsoft YaHei UI", "Microsoft JhengHei UI", "Malgun Gothic", "Estrangelo Edessa", "Microsoft Himalaya", "Microsoft New Tai Lue", "Microsoft PhagsPa", "Microsoft Tai Le", "Microsoft Yi Baiti", "Mongolian Baiti", "MV Boli", "Myanmar Text", "Cambria Math"; font-size: 15px; max-width: 100%; table-layout: auto; width: 352px;">
<div class="table-cell" style="box-sizing: border-box; display: table-cell; vertical-align: middle;">
<div class="row app-name" data-bind="text: unsafe_appName" style="box-sizing: border-box; color: #404040; font-family: "Segoe UI", "Helvetica Neue", "Lucida Grande", Roboto, Ebrima, "Nirmala UI", Gadugi, "Segoe Xbox Symbol", "Segoe UI Symbol", "Meiryo UI", "Khmer UI", Tunga, "Lao UI", Raavi, "Iskoola Pota", Latha, Leelawadee, "Microsoft YaHei UI", "Microsoft JhengHei UI", "Malgun Gothic", "Estrangelo Edessa", "Microsoft Himalaya", "Microsoft New Tai Lue", "Microsoft PhagsPa", "Microsoft Tai Le", "Microsoft Yi Baiti", "Mongolian Baiti", "MV Boli", "Myanmar Text", "Cambria Math"; font-size: 0.9375rem; font-weight: 600; line-height: 1.25rem; margin: 0px; padding: 0px;">
YourAppName</div>
<div data-bind="text: svr.strAppRootDomain" style="box-sizing: border-box;">
yourDomain</div>
</div>
</div>
<div class="row text-body" style="background-color: white; box-sizing: border-box; color: #262626; font-family: "Segoe UI Webfont", -apple-system, "Helvetica Neue", "Lucida Grande", Roboto, Ebrima, "Nirmala UI", Gadugi, "Segoe Xbox Symbol", "Segoe UI Symbol", "Meiryo UI", "Khmer UI", Tunga, "Lao UI", Raavi, "Iskoola Pota", Latha, Leelawadee, "Microsoft YaHei UI", "Microsoft JhengHei UI", "Malgun Gothic", "Estrangelo Edessa", "Microsoft Himalaya", "Microsoft New Tai Lue", "Microsoft PhagsPa", "Microsoft Tai Le", "Microsoft Yi Baiti", "Mongolian Baiti", "MV Boli", "Myanmar Text", "Cambria Math"; font-size: 15px; line-height: 1.25rem; margin: 16px 0px 12px; padding: 0px;">
<div class="text-block-body overflow-hidden no-margin-top" data-bind="text: str['CT_STR_Consent_Error_Description']" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; overflow: hidden; padding: 0px;">
YourAppName needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.</div>
<div class="text-block-body overflow-hidden no-margin-top" data-bind="text: str['CT_STR_Consent_Error_Description']" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; overflow: hidden; padding: 0px;">
<br /></div>
<div class="text-block-body overflow-hidden no-margin-top" data-bind="text: str['CT_STR_Consent_Error_Description']" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; overflow: hidden; padding: 0px;">
If this happens, it is because your organization does not allow users to consent to the application reading their information. In this case, you will need to have an AD Admin (or someone with privs) log in to AD and either give users the ability to consent to the application reading data, or consent for everyone (see document below).</div>
<div class="text-block-body overflow-hidden no-margin-top" data-bind="text: str['CT_STR_Consent_Error_Description']" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; overflow: hidden; padding: 0px;">
<a href="https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent">https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent</a></div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjA6FQrmphOgI_qwq7La5lnUKqxAA8tHKVJ9T0FE54-D8iqstXoSVkZOfBUH5byHmQIL2f6skxyLrzpcBfGD-UfBvf3VVFAMtNMHfc5zMOc765frbNzomrW4jmp1F940YhqnS8G/s1600/Screen+Shot+2019-06-27+at+5.21.32+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="515" data-original-width="1289" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjA6FQrmphOgI_qwq7La5lnUKqxAA8tHKVJ9T0FE54-D8iqstXoSVkZOfBUH5byHmQIL2f6skxyLrzpcBfGD-UfBvf3VVFAMtNMHfc5zMOc765frbNzomrW4jmp1F940YhqnS8G/s320/Screen+Shot+2019-06-27+at+5.21.32+PM.png" width="320" /></a></div>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span><br />
<br />
<br />
<h3>
</h3>
<div class="row text-body" style="background-color: white; box-sizing: border-box; color: #262626; font-family: "Segoe UI Webfont", -apple-system, "Helvetica Neue", "Lucida Grande", Roboto, Ebrima, "Nirmala UI", Gadugi, "Segoe Xbox Symbol", "Segoe UI Symbol", "Meiryo UI", "Khmer UI", Tunga, "Lao UI", Raavi, "Iskoola Pota", Latha, Leelawadee, "Microsoft YaHei UI", "Microsoft JhengHei UI", "Malgun Gothic", "Estrangelo Edessa", "Microsoft Himalaya", "Microsoft New Tai Lue", "Microsoft PhagsPa", "Microsoft Tai Le", "Microsoft Yi Baiti", "Mongolian Baiti", "MV Boli", "Myanmar Text", "Cambria Math"; font-size: 15px; line-height: 1.25rem; margin: 16px 0px 12px; padding: 0px;">
<div class="text-block-body overflow-hidden no-margin-top" data-bind="text: str['CT_STR_Consent_Error_Description']" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; overflow: hidden; padding: 0px;">
<br /></div>
<div class="text-block-body overflow-hidden no-margin-top" data-bind="text: str['CT_STR_Consent_Error_Description']" style="box-sizing: border-box; font-size: 0.9375rem; line-height: 1.25rem; margin-bottom: 12px; overflow: hidden; padding: 0px;">
<br /></div>
</div>
Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com7tag:blogger.com,1999:blog-24686153.post-52023094574444560822019-02-27T13:24:00.002-08:002019-02-27T13:24:40.136-08:00Automatically Navigate to Form from Interactive Report - Oracle APEXHow much is a click worth?<br />
<br />
I was recently asked if I could have an APEX form page load automatically when an Interactive Report (IR) resulted in exactly one row returned. I spent about 5 minutes coming up with a solution, followed by about 15 minutes working with Jorge Rimblas to improve my solution.<br />
<br />
This assumes you are using an IR with the link built within the IR Attributes. To do this with your own column you need to set a Static ID and use that in place of "LINK" in the code below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJrl0FB6_EAxOqCCvkESx3RBiHhmU4BxEfePW_33E9OffTSd6XEeWuDz1Q1tQbjJnhh404qOH5AUbqJXxKzivGPcYLKmZdzdPiUpHIr5oVJrmrPaQqpyhvHyAWTJeCDF5mzH5o/s1600/Screen+Shot+2019-02-27+at+4.17.02+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="251" data-original-width="608" height="132" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJrl0FB6_EAxOqCCvkESx3RBiHhmU4BxEfePW_33E9OffTSd6XEeWuDz1Q1tQbjJnhh404qOH5AUbqJXxKzivGPcYLKmZdzdPiUpHIr5oVJrmrPaQqpyhvHyAWTJeCDF5mzH5o/s320/Screen+Shot+2019-02-27+at+4.17.02+PM.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
Create a Dynamic Action (DA)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga5DaBKFN6eeQ0wGlXOUMsGhjTkmg2hX0stI3KLL9ByHmDvzTqtDT5l0MMMyVhOPsD52i1KNOISw-Oq50PpMHsS4PKApSwpvvFy2oc5ATLQ5IwIc6ZFjKzyMCvqqZuR61AhkCq/s1600/Screen+Shot+2019-02-27+at+4.18.26+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="151" data-original-width="330" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga5DaBKFN6eeQ0wGlXOUMsGhjTkmg2hX0stI3KLL9ByHmDvzTqtDT5l0MMMyVhOPsD52i1KNOISw-Oq50PpMHsS4PKApSwpvvFy2oc5ATLQ5IwIc6ZFjKzyMCvqqZuR61AhkCq/s320/Screen+Shot+2019-02-27+at+4.18.26+PM.png" width="320" /></a></div>
It will be On Refresh of your IR<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwlrouQe2UgJZK87QXnqQJfD-3ooY8IGmAawoQV1DyJVe_-jRy6N5HDZISbiyvsTJ20X9S94Hibk_gy_fY2XvM1glExTev9DcyGY6mTW_K_uIv6h753Yu4J_cKm43M3sUX_SQv/s1600/Screen+Shot+2019-02-27+at+4.18.41+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="363" data-original-width="606" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwlrouQe2UgJZK87QXnqQJfD-3ooY8IGmAawoQV1DyJVe_-jRy6N5HDZISbiyvsTJ20X9S94Hibk_gy_fY2XvM1glExTev9DcyGY6mTW_K_uIv6h753Yu4J_cKm43M3sUX_SQv/s320/Screen+Shot+2019-02-27+at+4.18.41+PM.png" width="320" /></a></div>
<br />
<br />
And it will have the Client-side Condition, Javascript expression<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">($("td[headers='LINK']").length==1)</span><br />
<br />
<br />
Have a True action of Execute Javascript Code<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ0D0W-bUJa82dtITzV0o1b2WFWW8mDnjL_niGfJzhOqzDKNCqjhueaPt5nglZVT7CmCIU-2K4o-a_iw5U8LRKMY2MC6wZfaGr8zWMSHhTrw3iyVS22KmwyTb6UlrTR-ZbYkcC/s1600/Screen+Shot+2019-02-27+at+4.21.38+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="671" data-original-width="531" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ0D0W-bUJa82dtITzV0o1b2WFWW8mDnjL_niGfJzhOqzDKNCqjhueaPt5nglZVT7CmCIU-2K4o-a_iw5U8LRKMY2MC6wZfaGr8zWMSHhTrw3iyVS22KmwyTb6UlrTR-ZbYkcC/s320/Screen+Shot+2019-02-27+at+4.21.38+PM.png" width="253" /></a></div>
<br />
It will have Code<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">apex.navigation.redirect($("td[headers='LINK'] a").attr("href"));</span><br />
<br />
<br />
If the target of the link is a modal window, you may even want to set Fire on Initialization to Yes. If the target is NOT a modal, leave this set to No as you'll have a tough time getting to the report otherwise.<br />
<br />
<br />
<br />
<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-2528982501151958622019-02-20T10:26:00.003-08:002019-02-20T10:26:48.213-08:00Peer Code Review Presentation at RMOUG Training Days 2019I just finished giving my first presentation at RMOUG Training Days 2019. So far it has been a great event. The venue is very nice and convenient. The presentations cover interesting topics and are presented by experts. The organization has been flawless. A big thank you to the RMOUG team.<br />
<br />
During my presentation I mentioned that I would provide a link to the artifacts of my presentation on this blog...so here it is:<br />
<a href="https://github.com/ainielse/rando/tree/master/peer_review">https://github.com/ainielse/rando/tree/master/peer_review</a><br />
<br />
There are three files:<br />
<br />
<ul>
<li>The presentation itself (<a class="js-navigation-open" href="https://github.com/ainielse/rando/blob/master/peer_review/a_nielsen_peer_code_rmoug.pptx" id="0d9b3c8d6bcebb464748ef62a4f8b05d-17e4235c8ea918ed388025d91b5f25877e32720b" style="background-color: #f6f8fa; box-sizing: border-box; color: #0366d6; font-family: -apple-system, system-ui, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 14px; outline-width: 0px; white-space: nowrap;" title="a_nielsen_peer_code_rmoug.pptx">a_nielsen_peer_code_rmoug.pptx</a>)</li>
<li>An APEX application for tracking peer code reviews (<a class="js-navigation-open" href="https://github.com/ainielse/rando/blob/master/peer_review/f_peer_review.sql" id="13e621f4b87fcc74040409428fa2d751-118999ab6215ebfdbda37e872f87a69f5e6440de" style="background-color: white; box-sizing: border-box; color: #0366d6; font-family: -apple-system, system-ui, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 14px; text-decoration-line: none; white-space: nowrap;" title="f_peer_review.sql">f_peer_review.sql</a>) that contains supporting objects and seed data</li>
<li>An APEX application to maintain the seed data (<a class="js-navigation-open" href="https://github.com/ainielse/rando/blob/master/peer_review/f_peer_review_admin.sql" id="d4ca96a0851976579146f7d7f36ee19e-ee4ce0bebcf7d8b50a1f8c71358b418d6dd302e6" style="background-color: white; box-sizing: border-box; color: #0366d6; font-family: -apple-system, system-ui, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 14px; text-decoration-line: none; white-space: nowrap;" title="f_peer_review_admin.sql">f_peer_review_admin.sql</a>)</li>
</ul>
<div>
I'd love to hear any stories of organizations putting this into practice.</div>
<div>
<br /></div>
<div>
Finally, a plug for my session tomorrow: APEX, ORDS and IoT. I hope to see you there.</div>
Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-88235098368240389782019-02-12T09:51:00.001-08:002019-02-12T09:51:19.784-08:00Oracle APEX Consolidated HelpI recently had the need to show "most" of the application help on a single page in my APEX application. In order to make the procedure generic and easy to use, I didn't want to create any additional database objects--just a procedure to output what I needed. I decided to create a separate APEX Navigation List to create the hierarchy of pages that I want to include in the consolidated help page. The easiest way was to copy my "Desktop Navigation Menu" and then modify it. Of course, I could have just used "Desktop Navigation Menu" if it met my needs. The result looks like this<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsHUtMyXR4Q9l_JnITb-UOMaVdFoDDp8G1oRrOptdrW98grirT5VsUiPMQ-TBXo4qiGAMsbeAq4YXX5u5bEfMThJvdvtBtOADt4rGL4ja_st_QfprkxL-sLdb4G3pmWxabW9Pw/s1600/Screen+Shot+2019-02-12+at+12.42.37+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1155" data-original-width="1600" height="231" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsHUtMyXR4Q9l_JnITb-UOMaVdFoDDp8G1oRrOptdrW98grirT5VsUiPMQ-TBXo4qiGAMsbeAq4YXX5u5bEfMThJvdvtBtOADt4rGL4ja_st_QfprkxL-sLdb4G3pmWxabW9Pw/s320/Screen+Shot+2019-02-12+at+12.42.37+PM.png" width="320" /></a></div>
<br />
<br />
<br />
<br />
Others may need to do the same thing so I'm providing the code here. I apologize that I don't have good code formatting on this blog. I'm providing it as a standalone procedure, but you should put it in a package.<br />
<br />
<a href="https://github.com/ainielse/rando/blob/master/output_consolidated_help.sql">https://github.com/ainielse/rando/blob/master/output_consolidated_help.sql</a>Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com3tag:blogger.com,1999:blog-24686153.post-76554694274709639312019-02-07T12:04:00.001-08:002021-05-19T06:05:34.291-07:00Oracle APEX Less Social Sign-In with MS Azure and Office 365After my last blog post a natural question came up: what if I'm a little anti-social?<br />
<br />
Specifically, what if I do NOT want to make my APEX application available to everyone on the planet that has (or is willing to get) a Microsoft Azure / Office 365 account? What if I only want people in my company, that uses Azure AD, to be able to log in?<br />
<br />
There are several ways you can do this, and I recommend you employ at least two. No, recommend is too lenient, <b>I insist</b> you employ at least two :). The first is to change the way you call the Microsoft OAuth2 provider. Instead of using the values in my last blog post:<br />
<br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Authorization Endpoint URL: https://login.microsoftonline.com/<b>common</b>/oauth2/v2.0/authorize</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Token Endpoint URL: https://login.microsoftonline.com/<b>common</b>/oauth2/v2.0/token</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
Use the following:<br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Authorization Endpoint URL: https://login.microsoftonline.com/<b>yourCompanyDomain</b>/oauth2/v2.0/authorize</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Token Endpoint URL: https://login.microsoftonline.com/</span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><b>yourCompanyDomain</b></span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">/oauth2/v2.0/token</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>
For Insum, this would be insum.ca:<br />
<br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Authorization Endpoint URL: https://login.microsoftonline.com/<b>insum.ca</b>/oauth2/v2.0/authorize</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">Token Endpoint URL: https://login.microsoftonline.com/</span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><b>insum.ca</b></span><span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;">/oauth2/v2.0/token</span><br />
<span style="background-color: white; color: #333333; font-family: "georgia" , serif; font-size: 13px;"><br /></span>The method above does NOT secure your application to just your domain. It just makes it harder for someone to use another domain. A savvy user can bypass that by just typing "common" in the url.<br />
<br />
The <b>REAL</b> step to secure your application is to do one or both of the following:<br />
<br />
<ol>
<li>Create an Authentication Scheme sentry function that makes sure the username includes @yourdomain</li>
<li>Create Authorization Scheme that makes sure the username includes @yourdomain and apply it to the application.</li>
</ol>
<div>
<br /></div>
<div>
I am often logged into multiple Azure AD accounts at the same time. By adding yourCompanyDomain to the Endpoint URLs you have the added bonus that users do not need to select a login each time they go to your application. Microsoft will detect the correct one to use.<br />
<br />
Edit:<br />
<br />
You can also log into the Azure Portal<br />
<a href="https://portal.azure.com/">https://portal.azure.com</a><br />
and edit the manifest of your application.<br />
<br />
Azure Active Directory > App Registrations > [Your Application] > Manifest<br />
<br />
Change<br />
<br />
<span style="font-family: Courier New, Courier, monospace;"><span style="white-space: pre;"> </span>"signInAudience": "AzureADandPersonalMicrosoftAccount",</span><br />
<br />
to<br />
<br />
<span style="font-family: Courier New, Courier, monospace;"><span style="white-space: pre;"> </span>"signInAudience": "AzureADMyOrg",</span></div>
Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com3tag:blogger.com,1999:blog-24686153.post-142287578951553762019-01-31T14:20:00.001-08:002021-05-19T06:04:40.408-07:00Oracle APEX Social Sign-In with Microsoft AzureMy co-worker Adrian Png has written a few great posts on social sign-in with APEX. I'd like to provide a quick cookbook with some interesting details here. If you get a chance, take a look at Adrian's posts:<br />
https://fuzziebrain.com/content/id/1709/<br />
https://fuzziebrain.com/content/id/1711/<br />
<br />
Here is the quick cookbook.<br />
<br />
Let's assume your APEX url is<br />
https://myapex.com/ords/<br />
<br />
Log in to the Azure registration portal<br />
https://apps.dev.microsoft.com/<br />
<br />
and click "Add an app"<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s1600/Screen+Shot+2019-01-31+at+4.44.33+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="159" data-original-width="725" height="69" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s320/Screen+Shot+2019-01-31+at+4.44.33+PM.png" width="320" /></a></div>
Enter a name and click "Create"<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_zuuyqMD4XX4DWeDFsx0zuxKwstS3Rx7pg2O-EkCSHJtiuJWD_E7i5sZS25dccZmany4gBBQxEaRt1eVdR4GhyaHgXe-b4Gb1keegh_QNKdPQsiS67pRskSZovoP7dVepP0EI/s1600/Screen+Shot+2019-01-31+at+4.45.28+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="421" data-original-width="602" height="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_zuuyqMD4XX4DWeDFsx0zuxKwstS3Rx7pg2O-EkCSHJtiuJWD_E7i5sZS25dccZmany4gBBQxEaRt1eVdR4GhyaHgXe-b4Gb1keegh_QNKdPQsiS67pRskSZovoP7dVepP0EI/s320/Screen+Shot+2019-01-31+at+4.45.28+PM.png" width="320" /></a></div>
Click "Generate New Password"<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s1600/Screen+Shot+2019-01-31+at+4.44.33+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="159" data-original-width="725" height="69" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s320/Screen+Shot+2019-01-31+at+4.44.33+PM.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3vh7Qktq_ADR3LcuViNu0ayMK5WB9kW41n1Kt7KrRm-mNSoqIvO1RY0lM7Oa4JUO46qBEY6U5Qd0bFJAz14c3WMdxUtR62FCuUAyWcNZ8NeqqelNbdwLupZQvgiihTn1DeoVI/s1600/Screen+Shot+2019-01-31+at+4.46.54+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="474" data-original-width="960" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3vh7Qktq_ADR3LcuViNu0ayMK5WB9kW41n1Kt7KrRm-mNSoqIvO1RY0lM7Oa4JUO46qBEY6U5Qd0bFJAz14c3WMdxUtR62FCuUAyWcNZ8NeqqelNbdwLupZQvgiihTn1DeoVI/s320/Screen+Shot+2019-01-31+at+4.46.54+PM.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<br />
COPY THAT PASSWORD! You won't have another chance!<br />
hbUChicago1990){(upzadLTF3%<br />
<br />
While you're at it, grab the application ID:<br />
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;">dbf9c4ac-a7d1-4885-84c4-5b80777703f3</span><br />
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;"><br /></span>
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;">(Careful readers may see that I altered the secret and application ID in the text. I never saved the pic above on Azure, but figured I'd change it in the text anyway.)</span><br />
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;"><br /></span>
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;">Click "Add Platform" and choose Web</span><br />
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s1600/Screen+Shot+2019-01-31+at+4.44.33+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="159" data-original-width="725" height="69" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s320/Screen+Shot+2019-01-31+at+4.44.33+PM.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEio59OGFzuxnMnfr2ujhp8M893BhF5N-4ZrYfENK_qDCV-kKp4KpLAOJqsm62TjWTNyE5OSBgYJDMZlBmnwDhUYZ05gbWWtK_0_8frDexl0eT8WKPKFz8Sx-myUu3zvDbpXTv-_/s1600/Screen+Shot+2019-01-31+at+4.48.57+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="469" data-original-width="958" height="156" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEio59OGFzuxnMnfr2ujhp8M893BhF5N-4ZrYfENK_qDCV-kKp4KpLAOJqsm62TjWTNyE5OSBgYJDMZlBmnwDhUYZ05gbWWtK_0_8frDexl0eT8WKPKFz8Sx-myUu3zvDbpXTv-_/s320/Screen+Shot+2019-01-31+at+4.48.57+PM.png" width="320" /></a></div>
<span face=""segoe ui" , , "tahoma" , "verdana" , "arial" , sans-serif" style="background-color: white; font-size: 16px;"><br /></span>
<br />
<br />
Enter<br />
Redirect URLs<br />
https://myapex.com/ords/apex_authentication.callback<br />
<br />
and here is the magic!! The logout URL can NOT have a ? in it. I tried several times to use something like this:<br />
https://myapex.com/ords/f?p=logout<br />
and I got a very vague error when trying to save:<br />
<br />
<div class="modal-header ng-scope" style="border-bottom: 1px solid transparent; box-sizing: border-box; font-family: "Segoe UI", wf_segoe-ui_normal, Tahoma, Verdana, Arial, sans-serif; font-size: 16px; min-height: 11.5625px; padding: 10px;">
<h3 class="modal-title ng-binding" id="dialogTitle" name="6000" style="box-sizing: border-box; font-family: "Segoe UI Light", wf_segoe-ui_light, Tahoma, Verdana, Arial, sans-serif; font-size: 26px; font-weight: normal; line-height: 1.5625; margin: 0px;">
There's a temporary problem</h3>
</div>
<div aria-describedby="dialogDesc" aria-labelledby="dialogTitle" class="modal-body ng-scope" role="alertdialog" style="box-sizing: border-box; font-family: "Segoe UI", wf_segoe-ui_normal, Tahoma, Verdana, Arial, sans-serif; font-size: 16px; padding: 10px; position: relative;">
<div class="row" style="box-sizing: border-box;">
<div class="has-error" style="border-color: red; box-sizing: border-box;">
<span aria-hidden="false" class="p-t-sm ng-binding" data-ng-show="message" id="dialogDesc" style="box-sizing: border-box; padding-top: 5px;">There's a temporary problem with the service. Please try again. If you continue to get this message, try again later.</span><br />
<div class="help-block ng-binding" style="box-sizing: border-box; color: #ba141a; font-size: 13px; line-height: 1.53846; margin-bottom: 10px; margin-top: 0px;">
</div>
</div>
<div class="help-block" style="box-sizing: border-box; color: #737373; font-size: 13px; line-height: 1.53846; margin-bottom: 10px; margin-top: 0px;">
Error Info: <client-debug-info class="ng-isolate-scope" style="box-sizing: border-box;">Thu, 31 Jan 2019 21:51:42 GMT | 8H3Ce | /P35Z</client-debug-info></div>
</div>
</div>
The problem is the logout URL but it's not at all obvious. So, you'll need to figure out another way to logout. I used an apache rewrite rule, which allows me to do this:<br />
https://myapex.com/logmeout<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s1600/Screen+Shot+2019-01-31+at+4.44.33+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="159" data-original-width="725" height="69" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s320/Screen+Shot+2019-01-31+at+4.44.33+PM.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiS_D4fSbSyC-73XonMjB56UskHXxYx2E4pZLkb7HRZync_Ru30rJ-ksjxJJGQlBUfKJonxq9Zs5l3ouODxI-Fwk1SurUxWcZuehIeZL2ihzxXjv1A2wv3XDZyU9hwTZZgCPhj/s1600/Screen+Shot+2019-01-31+at+4.53.18+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="449" data-original-width="657" height="218" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiS_D4fSbSyC-73XonMjB56UskHXxYx2E4pZLkb7HRZync_Ru30rJ-ksjxJJGQlBUfKJonxq9Zs5l3ouODxI-Fwk1SurUxWcZuehIeZL2ihzxXjv1A2wv3XDZyU9hwTZZgCPhj/s320/Screen+Shot+2019-01-31+at+4.53.18+PM.png" width="320" /></a></div>
Add a home page url if you want:<br />
https://myapex.com/ords/f?p=myapp<br />
<br />
Scroll to the bottom and save your changes.<br />
<br />
At this point the Microsoft portion is functional but users will get a notice asking them if they consent to share some information with the application. It's not a big deal, but you can avoid that notice by having an admin consent for everyone in the organization. There is certainly a way to do it within the Azure admin...well, I would guess there is, but I couldn't find it. I did discover this method, though. Go to the following URL:<br />
<br />
<br />
https://login.microsoftonline.com/common/adminconsent?client_id=<APPLICATION_ID>&state=12345&prompt=admin_consent<br />
<br />
in our example<br />
<br />
https://login.microsoftonline.com/common/adminconsent?client_id=dbf9c4ac-a7d1-4885-84c4-5b80777703f3&state=12345&prompt=admin_consent<br />
<br />
log in as an admin and consent.<br />
<br />
<br />
OK, on to the APEX installation portion.<br />
<br />
Here's an important item...the client secrete (or password) that we got from the application registration is hbUChicago1990){(upzadLTF3%. That's a great secret, but it won't work just as you see it. You'll get the following error:<br />
<br />
<span face=""helvetica neue" , "segoe ui" , "helvetica" , "arial" , sans-serif" style="background-color: #fef7e0; font-size: 14px;">AADSTS50012: Invalid client secret is provided. Trace ID: 830c95a3-9bb7-9de1-8a22dcca1600 Correlation ID: 572ef7ea--4329-9674-9e9e3468982a Timestamp: 2019-01-15 22:05:41Z</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPNVhwtsp5Y2rqHc6xB7a5xVBRv-8yFJfFXyKxvhflqJvn1fAd5HuaTPmxMYoYfOihxdWOTQ6T2tbSc1kemDMHY5Io6rDxtf1XIz7pykDuxKG0xCBq4BeAonGCtAXrHuUhyphenhyphen91e/s1600/Screen+Shot+2019-01-31+at+5.07.02+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="184" data-original-width="558" height="105" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPNVhwtsp5Y2rqHc6xB7a5xVBRv-8yFJfFXyKxvhflqJvn1fAd5HuaTPmxMYoYfOihxdWOTQ6T2tbSc1kemDMHY5Io6rDxtf1XIz7pykDuxKG0xCBq4BeAonGCtAXrHuUhyphenhyphen91e/s320/Screen+Shot+2019-01-31+at+5.07.02+PM.png" width="320" /></a></div>
<br />
<br />
The problem is that the secret needs to be url encoded. So, let's do that first.<br />
<br />
Log into the APEX builder and navigate to SQL Workshop > SQL Commands and do what needs to be done:<br />
<br />
select apex_util.url_encode('hbUChicago1990){(upzadLTF3%') the_stuff<br />
from dual<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBuoC9PDkL5C9pHZKjKne6NEznQ-g6lO6-JyLADDpPPVcnw4J5Q3pWsE-vbxj7Ft5O7VDxQkh1AI-p5PJOwvizsNGxnnkn89wAGnkj1UaUd9OHgKOH6dD5pJCdJ4WXCwhvnLNz/s1600/Screen+Shot+2019-01-31+at+5.09.32+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="310" data-original-width="511" height="194" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBuoC9PDkL5C9pHZKjKne6NEznQ-g6lO6-JyLADDpPPVcnw4J5Q3pWsE-vbxj7Ft5O7VDxQkh1AI-p5PJOwvizsNGxnnkn89wAGnkj1UaUd9OHgKOH6dD5pJCdJ4WXCwhvnLNz/s320/Screen+Shot+2019-01-31+at+5.09.32+PM.png" width="320" /></a></div>
<br />
<br />
That's the stuff:<br />
<br />
<span face=""helvetica neue" , "segoe ui" , "helvetica" , "arial" , sans-serif" style="background-color: #fafafa; color: #404040; font-size: 13.3333px;">hbUChicago1990)%7B(upzadLTF3%25</span><br />
<br />
<br />
<br />
Now navigate to App Builder > Workspace Utilities > Web Credentials. Create credential.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s1600/Screen+Shot+2019-01-31+at+4.44.33+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="159" data-original-width="725" height="69" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg9xjEaVtkcHHdSD8t6PWYn7cJ4_yHkLL96TbtqvINj1G3MsiVSADuaJ1uhKLUvV-9KqaYCsDYWgl27QKQNeBQLZbHLKEDFT4k2CJorqLlz2rjv0fqo1__8lrn27pSssDy7VwE/s320/Screen+Shot+2019-01-31+at+4.44.33+PM.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh3XZ_fKEyVyCOUioyNJONoNS-2X1WqqXKQwlVgzqacldzXxdC2U0uiv5ORA21Ml8kQBqdbLuDY-ZYoCc9-UPl2gLBPBX8btQe5sq9aWmMIlZfHvc_0OY63fao-v5eK4pbDsa7/s1600/Screen+Shot+2019-01-31+at+5.12.11+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="561" data-original-width="820" height="218" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh3XZ_fKEyVyCOUioyNJONoNS-2X1WqqXKQwlVgzqacldzXxdC2U0uiv5ORA21Ml8kQBqdbLuDY-ZYoCc9-UPl2gLBPBX8btQe5sq9aWmMIlZfHvc_0OY63fao-v5eK4pbDsa7/s320/Screen+Shot+2019-01-31+at+5.12.11+PM.png" width="320" /></a></div>
I put the secret in the comments so you can see it.<br />
<br />
Almost there. Now go to your application and create an authentication scheme.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDV3-D3sxi42L7mTwkBMUPyfxRz1on7zFML5pxIDCtr_Bsa16a-IUU0hns0mAyuRxRfmsoODIzGBB5IPCr0t-l5P9pOZ_zuoD5boFHsuYpBj093GreqZgNvcEpCVpOlVh6t8e9/s1600/Screen+Shot+2019-01-31+at+5.14.29+PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="654" data-original-width="860" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDV3-D3sxi42L7mTwkBMUPyfxRz1on7zFML5pxIDCtr_Bsa16a-IUU0hns0mAyuRxRfmsoODIzGBB5IPCr0t-l5P9pOZ_zuoD5boFHsuYpBj093GreqZgNvcEpCVpOlVh6t8e9/s320/Screen+Shot+2019-01-31+at+5.14.29+PM.png" width="320" /></a></div>
<br />
<br />
<br />
Name: Your Choice<br />
Scheme Type: Social Sign-In<br />
Credential Store: MyAppAzure (the one you created)<br />
Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize<br />
Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token<br />
User Info Endpoint URL: https://graph.microsoft.com/v1.0/me<br />
Scope: User.Read<br />
Username Attribute: userPrincipalName<br />
<br />
You might be done...if you have the root cert for https://graph.microsoft.com/v1.0/me already in your wallet. If not get your dba to add the certificate from https://graph.microsoft.com/v1.0/me to the Oracle wallet.<br />
<br />
The other option is to follow Adrian's post (link at the top) to use a reverse proxy instead.<br />
<br />
That does it!<br />
<br />
<br />
<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com3tag:blogger.com,1999:blog-24686153.post-66630981778407396202018-09-18T14:31:00.001-07:002018-09-18T14:31:37.382-07:00Exactly a YearWow, this is awful. It looks like it has been OVER a year since I last made a blog post. That's really inexcusable. OVER a year would be way too long. Fortunately, it turns out that I did not go OVER a year between blog posts. I just switched to the Insum blog. You can read my APEX Sitemap blog there:<br />
<br />
<a href="https://insum.ca/search-engine-optimization-with-apex-creating-a-google-sitemap/">https://insum.ca/search-engine-optimization-with-apex-creating-a-google-sitemap/</a><br />
<br />
As astute readers will note, I did not cross the year boundary between blog posts.Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-38387814654474023832017-09-14T14:01:00.000-07:002017-09-14T18:48:38.235-07:00What is the Most Important Skill for an Oracle APEX Consultant?I've recently had the opportunity to reflect on what makes a great, not just good, but great Oracle APEX consultant. Michael, a principal at C2, recently announced that he's moving on to another opportunity. Michael has been with C2 for over a decade and we will miss him--though we're certain we'll stay in touch. Over the years I've seen many a client shed a tear, literal salty trembling teardrops, when Michael rolled off a project. It's part of the consulting life cycle--if we do a project correctly, we eventually hand it off and move on. I've seen those same clients rejoice when Michael returned to accomplish something new, just to shed another tear at his inevitable departure. I admit, I anticipate the same will happen with me upon his last day.<br />
<br />
As I recalled these moments, wondering how to fill Michael's role on our team, I reflected on what made Michael both so well liked and so effective. Fulfilling the buzz-words of the day, Michael is certainly an accomplished full-stack developer on Oracle's low-code Application Express development platform. He is not our absolute best SQL, PL/SQL, HTML, Java, Javascript, [pick your tech] developer. He is accomplished in most, though, and knows when and who to ask when he needs the absolute best. This certainly makes him highly effective. It makes him good--really good, as good as can be expected. But it's not what makes him great.<br />
<br />
Better than just about anyone, Michael listens. Michael listens because he respects the person who is talking, he understands that the business user's time is valuable, that the client knows what they want--even if they need a little assistance formulating what that is. Michael, hears what someone is saying, processes it, reformulates it, solutions in his mind just enough to know if he needs to ask a follow up question, but not so much as to miss the next point. Michael takes notes, because he values the client's time--he wants to get exactly what was said. Occasionally, Michael checks in to clarify that he understood a statement, or to make sure that the solution he very loosely formulated meets an expectation--or perhaps might meet an expectation enough that it might save significant development time. It's that balance that sets Michael apart as a great APEX consultant. He would be great with any technology. APEX has a unique position in the development world--it's ability to rapidly transform a business requirement into a functioning solution. Michael's ability to sprinkle just a little bit of solutioning into his thought process, while actively listening, while making sure that the client knows he is listening, while taking notes and respecting the value of other peoples' time, makes him a great APEX consultant.<br />
<br />
It also makes Michael a great friend. Godspeed.Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com1tag:blogger.com,1999:blog-24686153.post-84569360544761266542017-05-18T15:56:00.001-07:002017-05-18T15:56:11.336-07:00Moving your APEX Workspace to a CloneLast September I posted a list of steps to take when <a href="http://c2anton.blogspot.com/2016/09/cloning-oracle-database-with-apex.html" target="_blank">cloning an Oracle database that has APEX installed</a>. The scenario is common for some, especially EBS customers--clone production and turn it into development. You have fresh data that way and are sure that you are developing against the exact configuration of the production environment. I'm not suggesting this as a best practice--I'm just recognizing that it happens.<br />
<br />
In addition to the steps I described in September, I recently ran into the need to migrate Workspace users and Team Development content from an existing development instance into the new production clone. If you've followed the best practice of initially exporting your workspace from Dev and migrating it to Test and Production, moving users and other content into the clone is pretty easy. By having the same workspace in Dev, Test and Production you automatically also have it in the Production clone.<br />
<br />
I won't say this process is supported, but we have used it successfully.<br />
<br />
Step 1: Log into the APEX_ADMIN application (INTERNAL workspace).<br />
<br />
Step 2: Export the workspace that has the Team Development content you wish to migrate.<br />
<br />
Step 3: Edit the export file and remove the following<br />
<br />
The section that begins: <span style="font-family: Courier New, Courier, monospace;">prompt Creating workspace ABC</span><br />
Any groups that already exist or that you don't wish to create in the clone: <span style="font-family: Courier New, Courier, monospace;">wwv_flow_api.create_user_groups </span><br />
Any users that exist or that you don't wish to create: <span style="font-family: Courier New, Courier, monospace;">wwv_flow_fnd_user_api.create_fnd_user</span><br />
<br />
Step 4: Run the edited file in the APEX_nnnnnn user of the clone.<br />
<br />
That's it. Your Team Dev content should be there.<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-7520092723202746422017-05-04T10:41:00.003-07:002017-05-05T12:22:42.614-07:00The Benefits of an IoT Cloud InfrastructureI recently presented on the Internet of Things at the <a href="http://info.insum.ca/en/oracle-developer-day-montreal" target="_blank">Montreal Oracle Developer Day</a>. It was a great day of Oracle tech and I had an engaged and enthusiastic group of participants. The session was less than on hour long, yet during this time a few attendees assembled an <a href="http://concept2completion.com/iot" target="_blank">IoT thermostat</a> while I created an Oracle REST Data Services (ORDS) JSON service to interact with device. I also created an APEX application to control the device and report on the data it passed to the service. I ended the session with an overview of the benefits of an IoT Cloud Infrastructure--specifically Oracle's IoT Cloud Service. After the session a couple people asked me why I used an <a href="https://electricimp.com/" target="_blank">Electric Imp</a> instead of a Raspberry Pi. These questions let me know that I didn't do enough to highlight the benefits of the infrastructure services.<br />
<br />
I often do this presentation as a hands-on lab during which every participant builds a physical device. I may have 60 or more devices to prepare for a single session. While that is a lot of devices, it pales in comparison to the volume of devices that a typical IoT product might involve. How many Nest and Ecobee thermostats are in the wild? Fitbits? Initially programming the devices, hooking them up to a wifi network, communicating with the devices through firewalls and across wide area networks, pushing patches to the devices are big concerns when dealing with the scale associated with IoT. A few years ago, I chose the Electric Imp as a device because it has a built-in infrastructure for handling these kinds of concerns. A Raspberry Pi is a great device, but it doesn't have the built-in IoT support that comes with an Electric Imp.<br />
<br />
Oracle is addressing these requirements through its <a href="https://cloud.oracle.com/iot" target="_blank">IoT Cloud Service (IOTCS).</a> The benefits of using an IoT infrastructure include the following:<br />
<br />
<ul>
<li>Device Virtualization</li>
<li>High Speed Bi-Directional Messaging</li>
<li>Device Management</li>
<li>Stream Processing, Data Enrichment, Event Store</li>
<li>Integration via applications and APIs</li>
</ul>
<br />
If you are looking to build a product, it's time to consider an IoT platform. I'll discuss more about the Oracle IoTCS in a future post.<br />
<br />
For now, though, I want to encourage people to play with whatever physical to software interface is fun to work with. Inexpensive and effective sensors are readily available. Electric Imp, Raspberry Pi, Arduino, Zensio, Sparkfun Thing and many other platforms make it easy to build up a device. Have fun.<br />
<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com4tag:blogger.com,1999:blog-24686153.post-7447268854640209462017-04-18T15:55:00.000-07:002017-04-18T15:55:14.615-07:00XML Parsing failed with LPX-00261: invalid URL<br />
As promised in my last post, I'll address the parsing error you get when you attempt to convert the text returned by the NCBI efetch utilities into an XMLType. The Oracle XML parser within the database treats the XML comment "<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">" as an indicator to validate the XML against the DTD located at "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd". In many, maybe even most, cases this is fine. If you are generating an XML payload you may want to validate it before sending it out to avoid the embarrassment of sending an invalid message. If you are retrieving it from the source, though, odds are good it will be valid and validating it against the DTD may just be extra overhead. It's also possible that the XML parser doesn't understand the DTD. At least one version of the database has a bug related to exactly how that comment is formatted. In my case there are at least two issues related to validating against the DTD, and I have no need for it. Below is the query and the associated error.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">select xmltype(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">'<?xml version="1.0" ?></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd"></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><TaxaSet><Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <TaxId>33208</TaxId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <ScientificName>Metazoa</ScientificName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <OtherNames></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <GenbankCommonName>metazoans</GenbankCommonName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <BlastName>animals; animals</BlastName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Synonym>Animalia</Synonym></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <CommonName>multicellular animals</CommonName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </OtherNames></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <ParentTaxId>33154</ParentTaxId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Rank>kingdom</Rank></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Division>Invertebrates</Division></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <GeneticCode></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <GCId>1</GCId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <GCName>Standard</GCName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </GeneticCode></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <MitoGeneticCode></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <MGCId>1</MGCId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <MGCName>Standard</MGCName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </MitoGeneticCode></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Lineage>cellular organisms; Eukaryota; Opisthokonta</Lineage></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <LineageEx></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <TaxId>131567</TaxId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <ScientificName>cellular organisms</ScientificName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Rank>no rank</Rank></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <TaxId>2759</TaxId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <ScientificName>Eukaryota</ScientificName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Rank>superkingdom</Rank></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <TaxId>33154</TaxId></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <ScientificName>Opisthokonta</ScientificName></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <Rank>no rank</Rank></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </LineageEx></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <CreateDate>1995/02/27 09:24:00</CreateDate></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <UpdateDate>2017/02/16 16:52:33</UpdateDate></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> <PubDate>1992/05/26 01:00:00</PubDate></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"></Taxon></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"></TaxaSet>') the_xml</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> from dual;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ORA-31011: XML parsing failed </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ORA-19213: error occurred in XML processing at lines 2 </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">LPX-00261: invalid URL https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd</span><br />
<br />
Fortunately, you can instruct the database to NOT validate.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">alter session set events='31156 trace name context forever, level 2'</span><br />
<br />
will instruct the database to skip the validation. You can then use xmlTable, xmlQuery, etc. without receiving an error.<br />
<br />
Note: you may need to "grant alter session" to the user in order for this to work, particularly if using an execute immediate within a package to do the alter session command.<br />
<br />
<br />
<br />
<div>
<br /></div>
Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-62483714317538636092017-04-15T09:55:00.003-07:002017-04-15T10:03:15.647-07:00The Man in the MiddleI recently ran into an Oracle database bug (version 12c). It's definitely a bug, and there may even be a patch for it, maybe. We've all been in this position, though. There's definitely a bug. We've identified something similar in the Oracle support site bug database. There's a patch for it. How long will it take to get the patch installed? Can I even install the patch on an Oracle Database Appliance (or Exadata or whatever other special circumstance)? What happens if the patch doesn't fix the specific issue?<br />
<br />
Here is my scenario. I need to interact with the National Institutes of Health taxonomy database. It has REST based services--check them out:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=taxonomy&term=Metazoa&usehistory=n</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=taxonomy&id=33208</span><br />
<br />
So, super easy, I can use apex_web_service.make_rest_request to make the request, parse the response and do some science!<br />
<br />
Let's see what happens:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> apex_web_service.make_rest_request(</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> p_url => 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=taxonomy&id=33208',</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> p_http_method => 'GET'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ) taxonomy_info</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> from dual</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-29273: HTTP request failed</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-28860: Fatal SSL error</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "SYS.UTL_HTTP", line 368</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "SYS.UTL_HTTP", line 1118</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 636</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 760</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "APEX_050100.WWV_FLOW_WEBSERVICES_API", line 236</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at line 1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">29273. 00000 - "HTTP request failed"</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">*Cause: The UTL_HTTP package failed to execute the HTTP request.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">*Action: Use get_detailed_sqlerrm to check the detailed error message.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Fix the error and retry the HTTP request.</span><br />
<br />
Now, I know that I've used this same kind of call from this same database before, so I do a little more digging and try this:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> apex_web_service.make_rest_request(</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> p_url => 'https://oracle.com',</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> p_http_method => 'GET'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> from dual</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-29273: HTTP request failed</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-29024: Certificate validation failure</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "SYS.UTL_HTTP", line 1258</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 717</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 760</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at "APEX_050100.WWV_FLOW_WEBSERVICES_API", line 236</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-06512: at line 1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">29273. 00000 - "HTTP request failed"</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">*Cause: The UTL_HTTP package failed to execute the HTTP request.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">*Action: Use get_detailed_sqlerrm to check the detailed error message.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Fix the error and retry the HTTP request.</span><br />
<br />
It's a different error, but still and error! Now I'm getting concerned. I assume it has to do with using https because both errors reference SSL/TLS. I try a variety of http and https sites. Most work fine, but occasionally I get one of the two errors above. I think they are actually two different errors. The second can probably be fixed by adding some certs to the db wallet, but the first, the one I really care about, appears to need a database patch: Patch 24666032. I'm not sure that would fix it, and for a variety of reasons, I can't get the patch installed and tested in a timely fashion.<br />
<br />
But...the scientists have work to do and I don't want to stand in the way of science. So, I developed a hack. Please don't judge me. Of course, the title of this post has already given it away. It's seems so obvious when you already know the answer, but it took some pondering to come up with it: the man in the middle. While I am the man in the middle, between the scientists and the data, I need another man in the middle to solve the problem. That MITM is an Apache Reverse Proxy.<br />
<br />
As I mentioned, I know this database can use apex_web_service.make_rest_request, definitely with my own internal development web server. I set up a reverse proxy on our internal apache server to route the request to the NIH REST server.<br />
<br />
<br />
<ol>
<li>Added an entry to our internal DNS server<br /><span style="font-family: "courier new" , "courier" , monospace;">[internal IP address] ncbi.concept2completion.com</span></li>
<li>Created a virtual host entry on our internal Apache web server and restarted<br /><span style="font-family: "courier new" , "courier" , monospace;"><VirtualHost *:443><br /> ServerName ncbi.concept2completion.com<br /> <br /> ProxyRequests on<br /> SSLProxyEngine on<br /> ProxyPass / https://eutils.ncbi.nlm.nih.gov/<br /> ProxyPassReverse / https://eutils.ncbi.nlm.nih.gov/<br /></VirtualHost></span></li>
<li>Pointed the apex_web_service.make_rest_request to our internal server.</li>
</ol>
<div>
<br /></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> apex_web_service.make_rest_request(</span><br />
<span style="font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace;"> p_url => 'https://</span><span style="font-family: "courier new" , "courier" , monospace;">ncbi.concept2completion.com</span><span style="font-family: "courier new" , "courier" , monospace;">/entrez/eutils/efetch.fcgi?db=taxonomy&id=33208',</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> p_http_method => 'GET'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ) taxonomy_info</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> from dual</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ;</span></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><?xml version="1.0" ?></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd"></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><TaxaSet><Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <TaxId>33208</TaxId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <ScientificName>Metazoa</ScientificName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <OtherNames></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <GenbankCommonName>metazoans</GenbankCommonName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <BlastName>animals; animals</BlastName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Synonym>Animalia</Synonym></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <CommonName>multicellular animals</CommonName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </OtherNames></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <ParentTaxId>33154</ParentTaxId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Rank>kingdom</Rank></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Division>Invertebrates</Division></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <GeneticCode></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <GCId>1</GCId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <GCName>Standard</GCName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </GeneticCode></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <MitoGeneticCode></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <MGCId>1</MGCId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <MGCName>Standard</MGCName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </MitoGeneticCode></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Lineage>cellular organisms; Eukaryota; Opisthokonta</Lineage></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <LineageEx></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <TaxId>131567</TaxId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <ScientificName>cellular organisms</ScientificName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Rank>no rank</Rank></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <TaxId>2759</TaxId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <ScientificName>Eukaryota</ScientificName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Rank>superkingdom</Rank></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <TaxId>33154</TaxId></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <ScientificName>Opisthokonta</ScientificName></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <Rank>no rank</Rank></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </LineageEx></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <CreateDate>1995/02/27 09:24:00</CreateDate></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <UpdateDate>2017/02/16 16:52:33</UpdateDate></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> <PubDate>1992/05/26 01:00:00</PubDate></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"></Taxon></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"></TaxaSet></span></div>
<div>
<br /></div>
</div>
<div>
<br /></div>
<div>
A hack? Yes, but done in the name of science.</div>
<div>
<br /></div>
<div>
Stay tuned for my next post on how to solve the parsing issue with the XML shown above. Also, we now have a pl/sql API for interacting with the NIH efetch service--it's extensive and provides access to the taxonomy lineage, synonyms, etc. Contact me if you are interested in the API.</div>
<style type="text/css">
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo}
p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; min-height: 13.0px}
span.s1 {font-variant-ligatures: no-common-ligatures}
</style>Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com1tag:blogger.com,1999:blog-24686153.post-75677087790382906742017-01-04T15:58:00.001-08:002017-01-05T15:00:06.869-08:00ORDS Quirks - slashes and feedsReturning to the stated purpose of this blog, "specific solutions to isolated problems," I've run into a few quirks with Oracle REST Data Services recently. The first is simply figuring out exactly what version of ORDS I am running. There are (at least) two components, the mid-tier java (ords.war) and the database metadata and packages (in ORDS_METADATA). Does anyone know the best way to get these? (Edit: See comments for answer to this.)<br />
<br />
Next, I've run into quirks around exactly where to put slashes. In an earlier post (http://c2anton.blogspot.com/2016/06/super-quick-oracle-rest-service-with.html) I demonstrated how to use the APIs to create ORDS modules, templates and handlers. I tried to use the exact commands in a newer ORDS version and ran into issues when using bind variables. I kept getting THE "ORACLE REST DATA SERVICES" banner along with "404 not found" whenever I created a template and handler with a bind variable.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLMwA6uYco9iHn9zLjHrYQ6SI-kirK4dzFTDYmqdbMDkTfII6g8oLVmWhK6KAdAr4djzQGqJ_CqhCaGzJRgC3h30Doe_w2QyMVfvAAp1KaJ2ei068NzER8RoJxhkAUGQu32r26/s1600/Screen+Shot+2017-01-04+at+6.57.47+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="163" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLMwA6uYco9iHn9zLjHrYQ6SI-kirK4dzFTDYmqdbMDkTfII6g8oLVmWhK6KAdAr4djzQGqJ_CqhCaGzJRgC3h30Doe_w2QyMVfvAAp1KaJ2ei068NzER8RoJxhkAUGQu32r26/s320/Screen+Shot+2017-01-04+at+6.57.47+PM.png" width="320" /></a></div>
<br />
<br />
This used to work:<br />
<br />
<ol style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;">
<li><span style="font-family: "courier new" , "courier" , monospace;">begin</span></li>
<span style="font-family: "courier new" , "courier" , monospace;"> ORDS.DEFINE_MODULE(<br /> p_module_name => 'antonModule',<br /> p_base_path => '/antonmodule',<br /> p_items_per_page => 25,<br /> p_status => 'PUBLISHED',<br /> p_comments => NULL );<br /><br /> commit;<br />end;</span><span style="font-family: "courier new" , "courier" , monospace;"><div>
<br /></div>
</span><span style="font-family: "courier new" , "courier" , monospace;"><div>
<pre><span style="font-family: "courier new" , "courier" , monospace;">begin
ORDS.DEFINE_TEMPLATE(
p_module_name => 'antonModule',
p_pattern => '/sqltest/{abc}/{def}',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL );
commit;
end;</span></pre>
</div>
</span></ol>
<br />
But in the new version I have to put a trailing slash in define_module ( <span style="background-color: white; color: #333333; font-family: "courier new" , "courier" , monospace; font-size: 13px;">p_base_path => '/antonmodule/'</span> ) and remove it from the beginning of p_pattern in define_template ( <span style="background-color: white; color: #333333; font-family: "courier new" , "courier" , monospace; font-size: 13px;">p_pattern => 'sqltest/{abc}/{def}'</span> ).<br />
<br />
It took my more time that I wish to admit to figure that out.<br />
<br />
*****************************<br />
<br />
The next issue is with the feed handler. The feed should create a $ref of this format<br />
<br />
https://mymachine/ords/myuser/mymodule/myfeed/{uid}<br />
<br />
but it is creating this<br />
https://mymachine/ords/myuser/mymodule/{uid}<br />
<br />
Maybe I just need to upgrade (edit: I upgraded to latest ORDS, 3.0.9. I still have the same issues.), but yikes, these two issues have made for a long day today. Hopefully this helps someone spend a little less time on this than I did :)<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com2tag:blogger.com,1999:blog-24686153.post-74620777118675295432016-09-19T14:29:00.000-07:002016-09-19T14:31:29.057-07:00Cloning an Oracle Database with APEX applicationsAbout a year ago I asked a question about cloning Oracle Databases in the APEX section of the Oracle Community. The question is here (no need to click the link, all of the content is also in this blog post):<br />
<br />
https://community.oracle.com/thread/3786014<br />
<br />
I haven't received much traction, so I'm reformulating it as a recommendation. Perhaps I'll get more feedback in this form. I'll also point out an element I think is a bug in APEX...you'll have to read to the end to see that.<br />
<br />
It is a common practice to clone a production database and utilize the clone for testing or development purposes. This happens a great deal with eBusiness Suite implementations, but also with many other installations. Below is a sample list of steps (with limited guidance) that should be done to avoid side effects with APEX applications.<br />
<br />
<br />
<ol>
<li>Use RMAN or your favorite technology to backup and restore the production database, but DO NOT START.</li>
<li>Change the database SID and name if not done above.</li>
<li>Set JOB_QUEUE_PROCESSES to 0. This is a key step to make sure that when you start the database things don't start "happening."</li>
<li>Start the database.</li>
<li>Assuming you are running a runtime only environment in Production, you will likely want to install the APEX builder in this new clone. Run apxdvins.sql to upgrade the runtime into a full development environment.</li>
<li>Log into the INTERNAL workspace and modify instance settings: Instance URL, Image URL, SMTP server settings (if you wish to use a different SMTP server), Print Server settings, any other settings you want.</li>
<li>Navigate to the Manage Instance > Mail Queue and delete anything in the queue. The clone may have happened while things were in the queue.</li>
<li>Manage Instance > Interactive Report Descriptions: Delete all of the Interactive Report subscriptions. This is also a key step to ensure that you don't have emails going out to production users from your development or test environment.</li>
<li>Manage Instance > Session State: Purge all session state. There could be sensitive production data that you don't want left around in session state.</li>
<li>Modify any settings specific to your own applications, e.g. web service URLs, lookup values, etc.</li>
<li>Reset JOB_QUEUE_PROCESSES to appropriate value.</li>
</ol>
<br />
<br />
It would also be great to have Oracle provide a script that does the above things (with the exception of #10, of course).<br />
<br />
I promised to call out a bug. Item #7 should delete all of the interactive report subscriptions, but it doesn't--at least not in APEX v5.0.4.00.12. The list of report subscriptions skips any application<br />
<br />
<span style="background-color: white; color: #222222; font-family: "arial" , sans-serif; font-size: 12.8px;">where build_status = 'RUN_AND_HIDDEN'</span><br />
<br />
This is (at least) packaged applications that have not been "unlocked." It turns out that deleting these subscriptions is NOT EASY. I originally thought a script like this might do it:<br />
<br />
-- this would need to be done in each workspace because it uses the workspace views<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> for irRec in (select notify_id from APEX_APPLICATION_PAGE_IR_SUB) loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> APEX_IR.DELETE_SUBSCRIPTION(</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> p_subscription_id => irRec.notify_id);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">end;</span><br />
<br />
Unfortunately, APEX_APPLICATION_PAGE_IR_SUB doesn't see the subscriptions. It has the same issue as the page--it won't show subscriptions for applications with build_status = 'RUN_AND_HIDDEN'.<br />
<br />
I tried a few other things, but in the end, the only way I could get rid of these was to just delete them from the underlying table, run as the APEX_050000 schema:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">delete from wwv_flow_worksheet_notify;</span><br />
<br />
<br />
<br />
<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com2tag:blogger.com,1999:blog-24686153.post-78357366958138178342016-06-23T09:14:00.001-07:002016-06-23T09:14:32.900-07:00Query to find users granted an ACL -- the natural question after seeing ORA-24247You may have encountered ORA-24247: network access denied by access control list (ACL) and wondered "who has access to what from my database?" I extended a query from the Oracle documentation to give me the results I wanted: ACL Name, Username, host, lower port, upper port, and if granted connect and resolve.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">with privs as (</span><br />
<span style="font-family: Courier New, Courier, monospace;"> SELECT acl, u.username, host, lower_port, upper_port, </span><br />
<span style="font-family: Courier New, Courier, monospace;"> DECODE(</span><br />
<span style="font-family: Courier New, Courier, monospace;"> DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'connect'),</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 1, 'GRANTED', 0, 'DENIED', null) conn_privilege,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> DECODE(</span><br />
<span style="font-family: Courier New, Courier, monospace;"> DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'resolve'),</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 1, 'GRANTED', 0, 'DENIED', null) res_privilege </span><br />
<span style="font-family: Courier New, Courier, monospace;"> FROM dba_network_acls a, dba_users u</span><br />
<span style="font-family: Courier New, Courier, monospace;"> )</span><br />
<span style="font-family: Courier New, Courier, monospace;">select *</span><br />
<span style="font-family: Courier New, Courier, monospace;"> from privs</span><br />
<span style="font-family: Courier New, Courier, monospace;"> where conn_privilege is not null</span><br />
<span style="font-family: Courier New, Courier, monospace;"> or res_privilege is not null</span><br />
<span style="font-family: Courier New, Courier, monospace;"> order by acl, username</span><br />
<span style="font-family: Courier New, Courier, monospace;"> ;</span><br />
<br />
<br />
It's nothing special, but can be a handy query.<br />
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-40771928924307598832016-06-13T13:44:00.000-07:002016-06-21T13:42:37.221-07:00Super Quick Oracle REST Service with OAuth2 and client_credentialsI had the need to allow system A to talk to system B via a REST service. The data was sensitive and powers above me requested that system A use OAuth2 to connect to system B. This REST service call does not involve an end user, it's system A pulling a CSV extract from system B. There are many ways to protect this, but the decision was to use OAuth2. Below is a cookbook on how to do this. This example assumes you have ORDS 3.x installed with the ORDS_PUBLIC_USER and ORDS_METADA schemas configured.<br />
<br />
I am running all commands as the user ANTON.<br />
<br />
<ol>
<li>For this example I will create a data source for our query:<br /><span style="font-family: "courier new" , "courier" , monospace;">-- create the table in the ANTON database schema<br />create table anton_table (c1 varchar2(500), c2 varchar2(500) );<br /><br />-- insert some sample data<br />insert into anton_table (c1, c2)<br /> select owner, table_name<br /> from all_tables<br /> where rownum <= 20;<br /><br />-- commit<br />commit;<br /></span></li>
<li>Enable REST on the ANTON schema:<br /><br /><span style="font-family: "courier new" , "courier" , monospace;">DECLARE<br /> PRAGMA AUTONOMOUS_TRANSACTION;<br />BEGIN<br /><br /> ORDS.ENABLE_SCHEMA(p_enabled => TRUE,<br /> p_schema => 'ANTON',<br /> p_url_mapping_type => 'BASE_PATH',<br /> p_url_mapping_pattern => 'anton',<br /> p_auto_rest_auth => FALSE);<br /> <br /> commit;<br /><br />END;<br /><br />-- check to see that it worked<br />select id, parsing_schema from user_ords_schemas; <br /><br /></span></li>
<li><span style="font-family: "courier new", courier, monospace;"><span style="font-family: inherit;">Define a REST Module:</span><br /><br /><span style="font-family: courier new, courier, monospace;">begin</span></span></li>
<span style="font-family: "courier new" , "courier" , monospace;"> ORDS.DEFINE_MODULE(<br /> p_module_name => 'antonModule',<br /> p_base_path => '/antonmodule',<br /> p_items_per_page => 25,<br /> p_status => 'PUBLISHED',<br /> p_comments => NULL );<br />
<br /> commit;<br />end;<br /><br /><br /> </span>
<li>Define a template. This is a URL pattern associated with the module "antonModule." Bind variables are contained in squiggly brackets: {bindVariableName}.<br /><span style="font-family: "courier new" , "courier" , monospace;"><br />
</span><pre><span style="font-family: "courier new" , "courier" , monospace;">begin
ORDS.DEFINE_TEMPLATE(
p_module_name => 'antonModule',
p_pattern => '/sqltest/{abc}/{def}',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL );
commit;
end;</span>
<span style="font-size: large;"><b style="font-family: "Times New Roman"; white-space: normal;">This is interesting so take note</b><span style="font-family: "times new roman"; white-space: normal;">! Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!</span></span>
</pre>
</li>
<li>Define a REST Handler based upon a sql query that takes two bind variables (abc and def) and returns a CSV file:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">begin <br /> ORDS.DEFINE_HANDLER(<br /> p_module_name => 'antonModule',<br /> p_pattern => '/sqltest/{abc}/{def}',<br /> p_method => 'GET',<br /> p_source_type => ords.source_type_csv_query,<br /> p_source => q'[select c1, c2 from anton_table where c1 = :abc and c2 = :def ]',<br /> p_items_per_page => 25,<br /> p_mimes_allowed => NULL ,<br /> p_comments => NULL ); <br />
<br /> commit;<br />end;</span><br />
<br /><b>This is worth repeating:</b> Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!<br /></li>
<li>At this point you can test your service:<br /><br />http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE<br />
<br />
</li>
<li> Define a privilege to protect it with OAuth2:<br /><br /><span style="font-family: "courier new" , "courier" , monospace;">declare<br /><br />l_roles owa.vc_arr;<br />l_patterns owa.vc_arr;<br />l_modules owa.vc_arr;<br />begin<br /> <br /> -- l_roles intentionally left empty<br /> <br /> -- populate arrays<br /> l_modules(1) := 'antonModule';<br /> <br /><br /> ORDS.DEFINE_PRIVILEGE(<br /> p_privilege_name => 'antonpriv',<br /> p_roles => l_roles,<br /> p_patterns => l_patterns,<br /> p_modules => l_modules,<br /> p_label => 'antonTestingPriv',<br /> p_description => 'anton testing priv',<br /> p_comments => null);<br /><br /> commit;<br /><br />end; <br /></span></li>
<li>Now you will find it protected:<br /><br />http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE<br /></li>
<li>Create a client that is allowed to access it:<br /><br /><span style="font-family: "courier new" , "courier" , monospace;">begin<br /> OAUTH.create_client(<br /> p_name => 'antonclient',<br /> p_grant_type => 'client_credentials',<br /> p_owner => 'anton',<br /> p_description => NULL,<br /> --p_origins_allowed => NULL, -- param name depends on ORDS version<br /> p_allowed_origins => NULL, -- param name depends on ORDS version<br /> p_redirect_uri => 'http://localhost:8080/redirect',<br /> p_support_email => 'anton@somewhere.com',<br /> p_support_uri => 'http://localhost:8080/support',<br /> p_privilege_names => 'antonpriv');<br /> <br /> commit;<br />end;<br /></span></li>
<li>Get the client_id and client_secret. You will need to log in as a user that has access to select from the ords_metadata tables (e.g. ORDS_METADATA or SYSTEM).<br /><br /><span style="font-family: "courier new" , "courier" , monospace;">select * from ords_metadata.oauth_clients;<br /></span></li>
<li>If you want to be able to do this from an http (not https) URL (which you should NEVER do in production--this is just for testing!!):<br /><br />-- turn off need for SSL<br />1. Locate the folder where the Oracle REST Data Services configuration is stored.<br />2. Edit the file named defaults.xml.<br />3. Add the following setting to the end of this file just before the </properties> tag.<br />4. <entry key="security.verifySSL">false</entry><br />5. Save the file.<br />6. Restart Oracle REST Data Services if it is running.<br /></li>
<li>Test getting a bearer token<br /><br />curl -i -d "grant_type=client_credentials" --user "[client_id]:[client_secret]" http://localhost:8080/ords/anton/oauth/token<br /><br />You should receive a response like this:<br /><br />HTTP/1.1 200 OK<br />Content-Type: application/json<br />X-Frame-Options: SAMEORIGIN<br />Transfer-Encoding: chunked<br /><br />{"access_token":"Zc9b9HDoP9rUKB189Bf1Yg..","token_type":"bearer","expires_in":3600}<br /><br /><br />Notes:<br />-- curl is a tool for making http(s) requests from the command line<br />-- -i allows you do to this as http -- though you SHOULD use https<br />-- -d allows you to pass in data<br />-- --user allows you to pass a user:password for basic authentication<br />-- then pass the appropriate URL to get a bearer token<br /></li>
<li>Test getting your CSV<br /><br />curl -i --header "Authorization: Bearer [token from step 10]" http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE --output anton.csv</li>
</ol>
<br />Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com0tag:blogger.com,1999:blog-24686153.post-60146175765147331912016-04-15T08:35:00.001-07:002016-04-15T08:35:33.259-07:00LOBs Over a Database Link<br />
There are lots of pluses and minuses to db links, but they are certainly easy and used in the right context they work very well. I admit that I sometimes use them when there is a better technical solution--just because it is so easy and the better solution may not be worth the time.<br />
<br />
The case of LOBs over db links can be tricky. You can't select a lob over a db link in SQL or PL/SQL:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select my_blob</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from my_table@mylink;</span><br />
<br />
results in<br />
<br />
<span style="background-color: white; line-height: 13.2px; white-space: pre;"><span style="font-family: "courier new" , "courier" , monospace;">ORA-22992: cannot use LOB locators selected from remote tables</span></span><br />
<span style="background-color: white; line-height: 13.2px; white-space: pre;"><span style="font-family: "courier new" , "courier" , monospace;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white; line-height: 13.2px; white-space: pre;">There are several techniques that work. You CAN do </span></span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">insert into my_local_table (the_blob)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> select my_blob</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from my_table@mylink;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="background-color: white; line-height: 13.2px; white-space: pre;">There is another interesting technique here:</span><br />
<span style="background-color: white; line-height: 13.2px; white-space: pre;"><br /></span>
<span style="background-color: white; line-height: 13.2px; white-space: pre;">https://technology.amis.nl/2012/07/02/select-a-blob-across-a-database-link-without-getting-ora-22992/</span><br />
<span style="background-color: white; line-height: 13.2px; white-space: pre;"><br /></span>
<br />
We recently had a requirement to just show the first few hundred characters of a lob over a db link. It was a complicated query and the developer wrote something like this:<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select local.c1, local.c2, remote.c3</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , dbms_lob.substr(remote.my_blob, 200, 1) blob200</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from local_table local</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> inner join remote_table@mylink remote on remote.c2 = local.c2;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
This worked fine in the development and test environment. In production it gave the ORA-22992. It depended on how the optimizer chose to run the query. If the dbms_lob.substr ran on the remote database it was fine, but if it had to pull the blob to the local db it was a problem.<br />
<br />
<br />
We solved it by forcing the the <span style="font-family: "courier new" , "courier" , monospace;">dbms_lob.substr </span>to run on the remote node:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select local.c1, local.c2, remote.c3</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , dbms_lob</span><span style="font-family: "courier new" , "courier" , monospace;"><span style="font-family: "courier new" , "courier" , monospace;">@mylink</span>.substr(remote.my_blob, 200, 1) blob200</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from local_table local</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> inner join remote_table@mylink remote on remote.c2 = local.c2;</span>Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com1tag:blogger.com,1999:blog-24686153.post-46664570712221012902016-03-31T15:11:00.002-07:002021-02-12T14:46:16.375-08:00apex_web_service.make_rest_request not working with POSTI recently encountered a web service that I was unable to use with POST and apex_web_service. I was using a statement like this:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">declare</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_clob CLOB;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">BEGIN</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_clob := apex_web_service.make_rest_request(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_url => 'http://myMachine/myService',</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_http_method => 'POST',</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_parm_name => apex_util.string_to_table('param1:param2'),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_parm_value => apex_util.string_to_table('xyz:xml'));</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">END;</span><br />
<br />
I've used this many times in the past, but this particular service would not recognize the parameters passed in p_parm_name and p_parm_value. I was able to use curl with the same transaction.<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">curl -X POST -d "param1=xyz&param2=xml" http://myMachine/myService</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: inherit;">I must say, it was VERY frustrating. I finally enabled full logging on Apache using mod_dumpio. </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">(Apache 2.4.x)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"># uncomment<br />LoadModule dumpio_module modules/mod_dumpio.so</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"># add</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> <IfModule dumpio_module></span><div><span style="font-family: courier new, courier, monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>#LogLevel debug</span></div><div><span style="font-family: courier new, courier, monospace;"><br /></span><div><span style="font-family: courier new, courier, monospace;"> </span><span style="font-family: "courier new", courier, monospace;"># Apache 2.4</span><span style="font-family: courier new, courier, monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>LogLevel dumpio:trace7 </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DumpIOInput On</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DumpIOOutput On</span></div><div><span style="font-family: courier new, courier, monospace;"><br /></span></div><div><span style="font-family: courier new, courier, monospace;"> </span><span style="font-family: "courier new", courier, monospace;"># does not work in 2.4</span><span style="font-family: courier new, courier, monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>#DumpIOLogLevel debug </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </IfModule></span><span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">I reviewed the logs to find out if </span><span style="font-family: "courier new" , "courier" , monospace;">apex_web_service.make_rest_request </span><span style="font-family: inherit;">was doing something different than </span><span style="font-family: "courier new" , "courier" , monospace;">curl</span><span style="font-family: inherit;">. I say "if," but clearly something had to be different. In the logs I found this line from curl but not from apex_web_service:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">mod_dumpio: dumpio_in (data-HEAP): Content-Type: application/x-www-form-urlencoded\r\n</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">I was able to get things working by adding the </span><span style="font-family: "courier new" , "courier" , monospace;">Content-Type</span><span style="font-family: inherit;"> header as shown below.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">declare</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_clob CLOB;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">BEGIN</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> apex_web_service.g_request_headers(1).name := 'Content-Type';</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded'; </span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> l_clob := apex_web_service.make_rest_request(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_url => 'http://myMachine/myService',</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_http_method => 'POST',</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_parm_name => apex_util.string_to_table('param1:param2'),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> p_parm_value => apex_util.string_to_table('xyz:xml'));</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">END;</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">I hope this helps someone!</span></div></div>Anton Nielsenhttp://www.blogger.com/profile/16136858710780919863noreply@blogger.com12tag:blogger.com,1999:blog-24686153.post-16202749387087581632015-01-08T18:09:00.000-08:002016-09-27T06:46:05.046-07:00AVOID the VOIDI've recently been involved with extending a number of systems that have pre-built data models. I'm generally unhappy with these data models for a variety of reasons. There are many great academic texts on data modeling. I will try to put together a bibliography in an upcoming post. For now, I'll start by discussing the "never delete data" trend. It is generally coupled with the use of a column to indicate that the data should have been deleted (typically a column named VOID) but was instead allowed, indeed required, to linger forever in the table.<br />
<br />
There are typically two arguments in favor of the "never delete, add a VOID column" data model: I want to know what happened from a traceability perspective, and, I want to be able to do incremental extracts to populate some other system and need to know if I need to VOID the row in the other system.<br />
<br />
<h3>
Example without VOID </h3>
It's easiest to deal with a concrete example, so let's make one. Assume we have an employee table that stores data about employees. For the purposes of my argument (and because it probably makes sense) let us assume we require a unique SSN for each employee. Typically this table would look like this:<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">CREATE table "EMPLOYEE" (<br /> "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,<br /> "SSN" VARCHAR2(32) NOT NULL,<br /> "LAST_NAME" VARCHAR2(128) NOT NULL,<br /> "FIRST_NAME" VARCHAR2(128),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;"><span style="font-family: "courier new" , "courier" , monospace;">"SALARY" NUMBER,</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "OTHER_INFO" VARCHAR2(256),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "LAST_UPDATED" DATE NOT NULL,<br /> constraint "EMPLOYEE_PK" primary key ("ID")<br />)<br />/<br /><br />alter table "EMPLOYEE" add<br />constraint "EMPLOYEE_SSN_UK" <br />unique ("SSN")<br />/</span> <br />
<br />
Because we want to be able to do incremental updates, we need the LAST_UPDATED column to be not null and we need to ensure it is always set correctly. There are many reasons to avoid triggers; just do an internet search for "Tom Kyte triggers" to see a number of valid arguments. For this purpose, though, I will add a trigger:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">create </span><span class="plsql-keyword">or </span><span class="plsql-keyword">replace </span><span class="plsql-keyword">trigger </span><span class="plsql-literal">"EMPLOYEE_BRIUT"</span><br /><span class="plsql-word">BEFORE</span><br /><span class="plsql-keyword">insert </span><span class="plsql-keyword">or </span><span class="plsql-keyword">update </span><span class="plsql-keyword">on </span><span class="plsql-literal">"EMPLOYEE"</span><br /><span class="plsql-keyword">for </span><span class="plsql-word">each </span><span class="plsql-keyword">row</span><br /><span class="plsql-keyword">begin</span><br /><br /><span class="whitespace"> </span><span class="plsql-operator">:</span><span class="plsql-keyword">new</span><span class="plsql-word">.last_updated </span><span class="plsql-operator">:= </span><span class="plsql-function">sysdate</span><span class="plsql-separator">;</span><br /><br /><span class="plsql-keyword">end</span><span class="plsql-separator">;</span></span><br />
<br />
Note that I have told the database that SSN will be unique by adding <span style="font-family: "courier new" , "courier" , monospace;">EMPLOYEE_SSN_UK. </span><br />
The database will automatically also create an unique index of the same name.<br />
<br />
Let's explore what happens if two users attempt to insert employees with the same SSN.<br />
<br />
User A (note lack of commit):<br />
<span style="font-family: "courier new" , "courier" , monospace;">insert into employee (ssn, last_name)<br /> values ('123456789', 'Smith');</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> 1 rows inserted.</span><br />
<br />
<br />
User B:<br />
<span style="font-family: "courier new" , "courier" , monospace;">insert into employee (ssn, last_name)<br /> values ('123456789', 'Smith');</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> ....waiting....</span><br />
<br />
User A:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">commit;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">committed.</span><br />
<br />
User B:<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">Error starting at line : 1 in command -<br />insert into employee (ssn, last_name)<br /> values ('123456789', 'Smith')<br />Error report -<br />SQL Error: ORA-00001: unique constraint (ANTON.EMPLOYEE_SSN_UK) violated<br />00001. 00000 - "unique constraint (%s.%s) violated"<br />*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.<br /> For Trusted Oracle configured in DBMS MAC mode, you may see<br /> this message if a duplicate entry exists at a different level.<br />*Action: Either remove the unique restriction or do not insert the key.</span><br />
<br />
<br />
The database recognized that the SSN was a duplicate and did not allow User B to insert. Had User A issued a rollback, User B could have continued, but once User A had committed the record, User B received an error.<br />
<br />
This functionality was completed with a single line of code:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">alter table "EMPLOYEE" add constraint "EMPLOYEE_SSN_UK" unique ("SSN")</span><br />
<br />
If I were coding an API and wanted to capture the error, it would require one additional line of code (assuming you already have the keyword EXCEPTION in your API):<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">exception</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> when </span><span class="text13"><span style="font-family: "courier new" , "courier" , monospace;">DUP_VAL_ON_INDEX then ... do something</span></span><br />
<br />
Later you realize that this person NEVER should have been entered into the employee table. This was not an employee, it was a customer. You issue the following command:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">delete from employee<br /> where ssn = '123456789';<br /> <br />commit;</span><br />
<br />
Later still, this customer becomes an employee. You issue the following:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">insert into employee (ssn, last_name)<br /> values ('123456789', 'Smith');</span> <br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">commit;</span><br />
<br />
This works just fine and no additional code is required. <br />
<h3>
Example With VOID </h3>
Let us assume that someone convinces you to disallow any deletes. Instead you are asked to add a VOID column. The VOID column will contain a V if the record is "void," else it will be null.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> CREATE table "EMPLOYEE_NO_DELETE" (<br /> "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,<br /> "SSN" VARCHAR2(32) NOT NULL,<br /> "LAST_NAME" VARCHAR2(128) NOT NULL,<br /> "FIRST_NAME" VARCHAR2(128),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "SALARY" NUMBER,<br /> "OTHER_INFO" VARCHAR2(256),<br /> "VOID" VARCHAR2(1),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "LAST_UPDATED" DATE NOT NULL,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> constraint "EMPLOYEE_ND_PK" primary key ("ID")<br />)<br />/</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">create </span><span class="plsql-keyword">or </span><span class="plsql-keyword">replace </span><span class="plsql-keyword">trigger </span><span class="plsql-literal">"EMPLOYEE_ND_BRIUT"</span><br /><span class="plsql-word">BEFORE</span><br /><span class="plsql-keyword">insert </span><span class="plsql-keyword">or </span><span class="plsql-keyword">update </span><span class="plsql-keyword">on </span><span class="plsql-literal">"EMPLOYEE_NO_DELETE"</span><br /><span class="plsql-keyword">for </span><span class="plsql-word">each </span><span class="plsql-keyword">row</span><br /><span class="plsql-keyword">begin</span><br /><br /><span class="whitespace"> </span><span class="plsql-operator">:</span><span class="plsql-keyword">new</span><span class="plsql-word">.last_updated </span><span class="plsql-operator">:= </span><span class="plsql-function">sysdate</span><span class="plsql-separator">;</span><br /><br /><span class="plsql-keyword">end</span><span class="plsql-separator">;</span></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<br />
Given the scenario listed above, we won't be able to add the unique constraint on SSN. If we were to do so, we would not be able to add them employee the second time as there would already be an employee record with that same SSN. Perhaps we could get away with making SSN + VOID unique.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">alter table "EMPLOYEE_NO_DELETE" add constraint "EMPLOYEE_ND_SSN_V_UK" unique ("SSN", VOID)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<br />
That seems to do the trick.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">insert into employee_no_delete (ssn, last_name)<br /> values ('123456789', 'Smith');</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">1 rows inserted.<br /> <br />update employee_no_delete <br /> set void = 'V'<br /> where ssn = '123456789';<br /> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">1 rows updated.</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> <br />insert into employee_no_delete (ssn, last_name)<br /> values ('123456789', 'Smith');<br /> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">1 rows inserted. </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> <br />commit; </span><br />
<br />
We still have all of the great features around row locking on uniqueness provided by the database. <br />
<br />
Of course, if your users are anything like mine, you will find that Mr. Smith has once again been added as employee but he is really a customer. So...<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">update employee_no_delete <br /> set void = 'V'<br /> where ssn = '123456789';</span><br />
<br />
Ah, but here we get<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">Error starting at line : 1 in command -<br />update employee_no_delete <br /> set void = 'V'<br /> where ssn = '123456789'<br />Error report -<br />SQL Error: ORA-00001: unique constraint (ANTON.EMPLOYEE_ND_SSN_V_UK) violated<br />00001. 00000 - "unique constraint (%s.%s) violated"<br />*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.<br /> For Trusted Oracle configured in DBMS MAC mode, you may see<br /> this message if a duplicate entry exists at a different level.<br />*Action: Either remove the unique restriction or do not insert the key.</span><br />
<br />
We already have a row with this combination of SSN and VOID. There is no choice but to remove the constraint, and, along with it, all of the multi-user concurrency features provided by the database.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">alter table "EMPLOYEE_NO_DELETE" drop constraint<br />"EMPLOYEE_ND_SSN_V_UK"<br />/ </span><br />
<br />
By dropping the constraint we lose more than we can possibly recover by adding our own code. Nevertheless, let's try. <br />
<br />
Now we have to add code to ensure that two users (in two separate sessions) never insert or update rows to cause a duplicate SSN. That means we must insist that all updates happen through an API. You might argue that only updates that include the SSN must go through the API. There are edge cases where this could cause deadlocks--and more importantly, it would be difficult to allow updates to everything except SSN. Hence, we have a new rule: All updates must go through our API. This rule also means that, unless we code special additional APIs, all processing is row by row (AKA slow by slow). Want to give everyone a 10% raise? That means updating each row rather than issuing a single update. Our API is also somewhat complicated. We must ensure that there is only one insert or update that involves SSN at a time--across sessions. As we don't have much insight into the happenings of another session, we'll need some way to track this. In order to serialize the inserts and any updates that might change the SSN, we must lock the EMPLOYEE_NO_DELETE table--the whole table. This means before each insert or update we must issue<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">lock table employee_no_delete in share mode nowait;</span><br />
<br />
We might consider using WAIT instead of NOWAIT, especially as we assume that there will be a lot of table locks.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">lock table employee_no_delete in share mode wait 10;</span> <br />
<h4>
Alternative Method </h4>
I've seen this implemented manually by creating another table that tracks table names--and then the API must lock the appropriate row.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">CREATE table TABLE_WITH_VOID_COLUMN (<br /> TABLE_NAME VARCHAR2(256),<br /> constraint "TABLE_NAME_PK" primary key ("TABLE_NAME")<br />)<br />/</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">insert into table_with_void_column (table_name)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> values ('EMPLOYEE_NO_DELETE</span><span style="font-family: "courier new" , "courier" , monospace;"><span style="font-family: "courier new" , "courier" , monospace;"></span>');</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">commit;</span> <br />
<br />
We would never actually update this row, but would lock it in order to interact between sessions.<br />
<br />
That method involves creating a new table and code to accomplish something Oracle already provides. Obviously, that is something we are already attempting by adding the VOID column and coding around it, so I'm not surprised to see custom table locking implemented by the same folks who implement custom delete handling.<br />
<br />
<h4>
Back to the API</h4>
In order to ensure that a row is never deleted and that we never have a duplicate SSN, we need an API such as the one below.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">create or replace package employee_ND_api is<br /><br />procedure ins ( p_emp_rec in employee_no_delete%rowtype) ;<br /><br />end employee_ND_api; </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<span style="font-family: "courier new" , "courier" , monospace;">create or replace package body employee_ND_api is<br /><br />procedure ins ( p_emp_rec in employee_no_delete%rowtype) is<br /><br />duplicate_ssn exception;<br />l_count number;<br /><br />begin<br /><br /> lock table employee_no_delete in share mode wait 10;<br /> begin<br /><br /> select 1 into l_count<br /> from dual<br /> where exists (select 1 from employee_no_delete e<br /> where e.ssn = p_emp_rec.ssn);<br /><br /> -- oops we found a row already there<br /> raise duplicate_ssn;<br /><br /> exception when no_data_found then null; -- ok to continue<br /> insert into employee_no_delete values p_emp_rec;<br /> end; <br /><br />-- note: We can not commit. There should be a full transaction, <br />-- actions before and after this action, that need to be <br />-- committed together. Hence, we lock the row in<br />-- table_with_void_column until the whole transaction<br />-- completes.<br />end ins;<br /><br />end employee_ND_api; </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<br />
The update routine would be slightly more complicated as we must also lock the row we intend to update, but the INS routine above points to some issues already. We have now locked the entire EMPLOYEE_NO_DELETE table<span style="font-family: "courier new" , "courier" , monospace;">.</span> As noted, we can't commit the newly inserted record as there may be other DML that needs to occur--inserts or updates to other rows or data in other tables. Hence, the entire table remains locked until the final commit or rollback. No other session can insert or update any row of EMPLOYEE_NO_DELETE until we complete.<br />
<br />
Moreover, there may be many tables--perhaps all tables--in our system with this same requirement. Hence, when we attempt to update data in another table in our unified transaction, we will need to take the same approach--lock th<span style="font-family: inherit;">e entire table. </span>Unless every transaction in our system always follows the same order, we will certainly run in to deadlocks on a frequent basis: one session will lock EMPLOYEE_NO_DELETE, another will lock DEPT_NO_DELETE, the first will attempt to lock DEPT_NO_DELETE but be blocked. Then the second will attempt to lock EMPLOYEE_NO_DELETE and the database will detect a deadlock--forcing a rollback of one of the sessions. There is no way to avoid this.<br />
<br />
<h3>
The Incremental Update Requirement</h3>
Clearly EMPLOYEE_NO_DELETE, with its void column, has problems. The requirement to do incremental updates of another system, though, remains. If we return to the EMPLOYEE table and allow the row to be deleted using the EMPLOYEE table rather than setting VOID = 'V' in the EMPLOYEE_NO_DELETE table, how does the incremental update routine know to remove (or void) the row?<br />
<br />
This is trivial with the use of a trigger on the EMPLOYEE table. Whenever a delete occurs, write a row to another table to indicate the delete. I'll mention Tom Kyte's dislike of triggers here again. I generally agree with Tom on this point. I don't even like the trigger I used above to populate the last_updated column. In the case of audit tables, though, I think a trigger is absolutely warranted. We are not changing any data in the base table and there are no foreign keys or even constraints on the audit table. Users would only ever be granted SELECT on that table. This is the place for a trigger. Depending upon our audit requirements, we might just indicated who took the action, when and whether it was an insert, update or delete. If we really need traceability, though, it's easy to capture the whole image of the row. I'll do that for this example:<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">-- create the audit table </span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">CREATE table "EMPLOYEE_AUDIT" (<br /> "ID" NUMBER,<br /> "SSN" VARCHAR2(32) NOT NULL,<br /> "LAST_NAME" VARCHAR2(128) NOT NULL,<br /> "FIRST_NAME" VARCHAR2(128),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;"><span style="font-family: "courier new" , "courier" , monospace;">"SALARY" NUMBER,</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "OTHER_INFO" VARCHAR2(256),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "LAST_UPDATED" DATE NOT NULL,<br /> "ROW_ACTION" VARCHAR2(32) <br />)<br />/</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">-- create the trigger</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">create </span><span class="plsql-keyword">or </span><span class="plsql-keyword">replace </span><span class="plsql-keyword">trigger </span><span class="plsql-literal">"EMPLOYEE_ARIUT"</span><br /><span class="plsql-word">AFTER</span><br /><span class="plsql-keyword">insert </span><span class="plsql-keyword">or </span><span class="plsql-keyword">update or delete </span><span class="plsql-keyword">on </span><span class="plsql-literal">"EMPLOYEE"</span><br /><span class="plsql-keyword">for </span><span class="plsql-word">each </span><span class="plsql-keyword">row</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">declare</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">l_action varchar2(32); </span><br /><span class="plsql-keyword">begin</span></span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> if inserting then l_action := 'INSERT';</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> elsif updating then l_action := 'UPDATE';</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> else l_action := delete;</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> </span><br /> insert into employee_audit (id, ssn, last_name, first_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , salary, other_info, last_updated, row_action)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> values (:new.id, :new.ssn, :new.last_name, :new.first_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , :new.salary, :new.other_info, <span class="plsql-operator">:</span><span class="plsql-keyword">new</span><span class="plsql-word">.last_updated, l_action);</span><br /><br /><span class="plsql-keyword">end</span><span class="plsql-separator">;</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-separator">/ </span></span><br />
<br />
The incremental routine can simply query the deleted row to gather the data. With sufficient data in the audit table, we can create a view that looks exactly like EMPLOYEE_NO_DELETE, but without its inherent shortcomings (nay, fatal flaws).<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">--create a view that has the deleted row</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">create or replace view employee_with_void </span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">select id, ssn, last_name, first_name, salary</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> , other_info, last_updated, null void</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> from employee</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">union </span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">select id, ssn, last_name, first_name, salary</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> , other_info, last_updated, 'V' void</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> from employee_audit</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword"> where row_action = 'DELETE' </span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span class="plsql-keyword">/</span></span><br />
<br />
The audit table can provide much better information if we need it. With just a LAST_UPDATED column (and no audit table), the incremental routine would never know about multiple changes that occur between the incremental runs. It may not need to, but if it does, the audit table provides that ability.<br />
<br />
In fact (not supposition, but absolute fact) the right method is to allow the row to be deleted. This provides true data protection, performs better and requires far less code which is also far less complicated.<br />
<h3>
Still Unconvinced?</h3>
OK, maybe you don't have the requirement for any unique constraints. First, I don't buy that argument for well over 90% of tables. If you are doing incremental updates in another system, you need some way to identify where to apply the incremental update--that would be the logical unique key. But, for the sake of argument, we will assume that you don't have any unique keys. That means that you don't need to be concerned about multi-user concurrency issues as much. What about that VOID column, though? Will you allow a row to be "unvoided?" Will you allow any updates to a voided record? If not, you would definitely need an API to keep that from happening. Given every possible argument and every leniency of requirement, it will still be less complicated for developers and users of the data to AVOID the VOID. For every query implemented by every user or developer save the incremental update routine the query must contain<br />
<br />
where void is null<br />
<br />
Indexes may need to include the VOID column--you'll need to do the analysis on a table by table basis.<br />
<br />
You definitely need an API and likely slow by slow processing everywhere.<br />
<br />
And here is the worst part, even the incremental load process is more complicated--if you want to get it right. If you just have two columns for audit/delete purposes, LAST_UPDATED and VOID, you lack the fidelity of knowing a row ever existed or what its incremental states were. Take this scenario, for example:<br />
<br />
midnight: incremental process runs <br />
9:00 am User adds employee Smith, SSN = 123456789<br />
9:15 am User updates Smith to Jones, SSN = 123459999<br />
10:00 am Payroll runs and pays Jones $500<br />
noon User updates Jones to Smith, SSN = 123456789<br />
1:00 pm User voids the record<br />
midnight: incremental process runs <br />
<br />
All that the last incremental process sees is that there was a row for Smith, SSN = 123456789 and that it was voided. What is the incremental process expected to do with that information?<br />
<br />
Need I mention foreign keys? If you have either a parent or a child record, how do you handle the relationship? Obviously a parent record can't be deleted; it would have to be voided. All child records would have to be voided as well. The cascading all has to be coded for--not to mention the locking of all of the cascading. At this point I have to ask: why did your company spend so much money on an Oracle database? I doubt it was to hire developers to code the same features again--with less functionality. <br />
<br />
Adding a table is easy. Creating a view is easy. All subsequent code benefits from these constructs. Why does the VOID persist? If anyone has a reason for a VOID column, let me know in the comments. Until then, please join with me in this movement--AVOID the VOID.<br />
<br />
<br />
Anton Nielsenhttp://www.blogger.com/profile/01507583135719899159noreply@blogger.com3tag:blogger.com,1999:blog-24686153.post-56297401637101833372014-10-06T15:43:00.001-07:002014-10-06T15:43:21.226-07:00Boston APEX MeetupIt's been a long time since I posted, but this seems like as good a reason as any to start up again. C2 is sponsoring an APEX meetup in the Boston area. You can find the info here:<br />
<a href="http://www.meetup.com/Boston-Apex-Meetup/events/206851272/">http://www.meetup.com/Boston-Apex-Meetup/events/206851272/</a><br />
<br />I'm looking forward to catching up with other members of the Oracle and APEX communities. Stay tuned for more blog posts as well!<br />
<br />
AntonAnton Nielsenhttp://www.blogger.com/profile/01507583135719899159noreply@blogger.com1tag:blogger.com,1999:blog-24686153.post-42004418895423543112011-10-03T14:48:00.000-07:002011-10-03T14:48:50.293-07:00SOAPEX at OOW 2011Recently, I've been doing a lot with Oracle Application Express (APEX) and web services. At Oracle OpenWorld I came across a presentation on just this topic. Douwe Pieter van den Bos, an Oracle Ace, presented on using APEX with the Oracle SOA Suite. I have used SOA Suite in the past, and quite like it, but recently the web services I've been using are SOAP based services that I have very little influence on. They are not built with or deployed on the Oracle SOA Suite.<br />
<br />
The SOAPEX presentation gave a nice overview of how to set up APEX to use web services and made the smart recommendation to build your web service references in a single application (SOAPEX) and then use the inherit/subscription model of APEX to keep things up to date.<br />
<br />
My specific challenges have been a little more difficult, specifically in the need to consume very large and complicated web services--so complex (or possibly overly complicated) that APEX is unable to parse the wsdl. More on this later...Anton Nielsenhttp://www.blogger.com/profile/01507583135719899159noreply@blogger.com3tag:blogger.com,1999:blog-24686153.post-67458633153355328202011-10-03T14:40:00.000-07:002011-10-03T14:40:14.459-07:00Oracle 12c DatabaseIt's definitely about the cloud at Oracle OpenWorld 2011. In many ways Oracle has always promoted it's database in the private cloud--long before the term cloud (private or public) became popular. At previous OpenWorlds, Larry Ellison poked fun at the cloud, noting that the notion isn't new. Well, even Oracle must bend to the popularity of the term cloud. I haven't yet heard an official name for the next database release, but I'm betting on Oracle 12c.Anton Nielsenhttp://www.blogger.com/profile/01507583135719899159noreply@blogger.com2