tag:blogger.com,1999:blog-20264678719150210672024-03-13T06:14:16.207-05:00Relational Design GenerationAdvancing automated software engineering for relational technology (Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.)Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-2026467871915021067.post-26811458842764510872024-02-23T11:38:00.003-06:002024-02-23T11:44:15.507-06:00Synergy with Wayland Using Chromium<p> This is a rather specific issue.</p><p></p><ul style="text-align: left;"><li>Running Synergy 1.11 on Ubuntu.</li><li>Sharing Keyboard/Mouse from Ubuntu to Other Computers.</li><li>Upgraded Ubuntu to 22.04.</li><li>Synergy stopped sharing.</li></ul><div><br /></div><div>The problem wasn't the network or the configuration because</div><div><ul style="text-align: left;"><li>Synergy successfully connected from all clients to the server.</li><li>The server showed all Synergy clients successfully connected.</li></ul><div><br /></div><div><b><u>The odd thing is Chromium.</u></b> If I start Chromium on the Ubuntu 22.04 server, the keyboard sharing starts working. If I stop Chromium on the Ubuntu 22.04 server, the keyboard sharing stops working. I also noticed the log messages on the Synergy client including "LANGUAGE DEBUG" warnings for every keystroke while the cursor was moved to the client. I assume Chromium setup some debugging channel that Synergy was able to use. (I did not research this.)</div></div><div><br /></div><h3 style="text-align: left;">Takeaway 1:</h3><div>The root cause of the problem appears to be the Ubuntu 22.04 switch to Wayland. Synergy doesn't support Wayland, but there are plans:</div><div><ul style="text-align: left;"><li><a href="https://github.com/symless/synergy-core/issues/4090#issuecomment-1570853103">Synergy in the libei** library!</a></li><li><a href="https://symless.com/synergy/roadmap">Wayland support for Linux with libEI</a></li></ul><div><br /></div></div><div></div><p></p><h3>Takeaway 2:</h3><div>Synergy has an interim fix. Don't use Wayland with Ubuntu...</div><div><ul style="text-align: left;"><li><a href="https://symless.com/synergy/help/synergy-on-ubuntu-21-04">Ubuntu 22: Edit config file</a></li></ul></div><div><br /></div>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-72955123866943277602024-02-15T08:25:00.001-06:002024-02-15T08:29:31.596-06:00Unit Testing a Thick Database<h3 style="text-align: left;"><b><u>Why Unit Test a Database?</u></b></h3><p>In the Java (OO) world, unit testing specifically avoids testing persistence. The persistence engine (database) is mocked so it doesn't interfere with the purity of the testing. Also, Java (OO) purists attempt to remove all logic from the database. However, we typically don't find such purity in practice.</p><p><br /></p><h3 style="text-align: left;"><b><u>What is a Thick Database?</u></b></h3><p>A Thick Database is a database with lots of logic built-in. <a href="https://www.dulcian.com/development/thick-database/">Dulcian.com</a> does a great job of explaining what it is and why it's useful. In practice, we tend to see some logic implemented in the database for a variety of reasons.</p><p><br /></p><h3 style="text-align: left;"><b><u>What is a Unit in the Database?</u></b></h3><p>Most references define a "unit" as something like the smallest bit of logic in a system. For this discussion, "unit" will refer to an interface. Interfaces in an Oracle database include the following:</p><ul style="text-align: left;"><li>Packages</li><ul><li>Procedures (Public and Internal)</li><li>Functions (Public and Internal)</li></ul><li>Procedures</li><li>Functions</li><li>Types with Methods</li><li>Tables</li><ul><li>Check Constraints</li><li>DML Triggers</li></ul><li>View Triggers</li><li>Session Triggers</li><li>Database Triggers</li></ul><div><br /></div><p></p><p></p><h3 style="text-align: left;"><b><u>Is a Unit Test Framework Required?</u></b></h3><div>No. However, a unit test framework does allow easier integration with CI/CD. Examples of database unit test frameworks include:</div><div><ul style="text-align: left;"><li><a href="https://www.utplsql.org/index.html">utPLSQL</a></li><li><a href="https://wtplsql.org/">wtPLSQL</a></li><li><a href="https://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_testing.htm">SQL*Developer Unit Testing</a></li></ul><div><br /></div>Alternatively, here is a bare-bones package that is useful when a framework is not available.<br /><br /><span style="font-family: courier; font-size: x-small;">create package simple_ut</span></div><div><span style="font-family: courier; font-size: x-small;">as</span></div><div><span style="font-family: courier; font-size: x-small;"> C_FALSE constant number := -1;</span></div><div><div><span style="font-family: courier; font-size: x-small;"> C_TRUE constant number := 0;</span></div><div><span style="font-size: x-small;"><span style="font-family: courier;"> C_BRIEF_OUTPUT</span><span style="font-family: courier;"> constant number := 0;</span></span></div><div><span style="font-size: x-small;"><span style="font-family: courier;"> C_NORMAL_OUTPUT</span><span style="font-family: courier;"> constant number := 1;</span></span></div><div><span style="font-family: courier; font-size: x-small;"> C_VERBOSE_OUTPUT constant number := 2;</span></div><div><span style="font-family: courier; font-size: x-small;"> g_output_mode number := C_NORMAL_OUTPUT;<br /></span></div><div><span style="font-family: courier; font-size: x-small;"> procedure ut_announce</span></div><div><span style="font-family: courier; font-size: x-small;"> (in_str in varchar2);</span></div><div><span style="font-family: courier; font-size: x-small;"> function ut_assert</span></div><div><span style="font-family: courier; font-size: x-small;"> (in_test_name in varchar2</span></div><div><span style="font-family: courier; font-size: x-small;"> ,in_test_string in varchar2)</span></div><div><span style="font-family: courier; font-size: x-small;"> return number;<br /></span></div></div><div><span style="font-family: courier; font-size: x-small;"> procedure ut_assert</span></div><div><div><span style="font-family: courier; font-size: x-small;"> (in_test_name in varchar2</span></div><div><span style="font-family: courier; font-size: x-small;"> ,in_test_string in varchar2);</span></div></div><div><span style="font-family: courier; font-size: x-small;"> procedure demo;</span></div><div><span style="font-family: courier; font-size: x-small;">end simple_ut;</span></div><div><span style="font-family: courier; font-size: x-small;">/</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"><span style="font-family: courier;">create package body </span><span style="font-family: courier;">simple_ut</span></span></div><div><span style="font-family: courier; font-size: x-small;">as</span></div><div><div><span style="font-family: courier; font-size: x-small;"> procedure ut_announce</span></div><div><span style="font-family: courier; font-size: x-small;"> (in_str in varchar2)</span></div></div><div><span style="font-family: courier; font-size: x-small;"> is</span></div><div><span style="font-family: courier; font-size: x-small;"> begin</span></div><div><span style="font-family: courier; font-size: x-small;"> dbms_output.put_line('');</span></div><div><span style="font-size: x-small;"><span style="font-family: courier;"> dbms_output.put_line('</span><span style="font-family: courier;">==========</span><span style="font-family: courier;">==========</span><span style="font-family: courier;">==========</span><span style="font-family: courier;">==========</span><span style="font-family: courier;">');</span></span></div><div><span style="font-family: courier; font-size: x-small;"> dbms_output.put_line('=== ' || $$PLSQL_UNIT || ' ' || in_str);</span></div><div><span style="font-family: courier; font-size: x-small;"> end ut_announce;</span></div><div><span style="font-family: courier; font-size: x-small;"> function ut_assert</span></div><div><span style="font-family: courier; font-size: x-small;"> (in_test_name in varchar2</span></div><div><span style="font-family: courier; font-size: x-small;"> ,in_test_string in varchar2)</span></div><div><span style="font-family: courier; font-size: x-small;"> return number<br /> is</span></div><div><span style="font-family: courier; font-size: x-small;"> l_result number;<br /> begin</span></div><div><span style="font-family: courier; font-size: x-small;"> execute immediate 'BEGIN' || CHR(10) ||</span></div><div><span style="font-family: courier; font-size: x-small;"> 'if ' || in_test_string || CHR(10) ||</span></div><div><span style="font-family: courier; font-size: x-small;"> 'then' || CHR(10) ||</span></div><div><span style="font-family: courier; font-size: x-small;"> ':n1 := ' || C_TRUE || ';' || CHR(10) ||</span></div><div><span style="font-family: courier; font-size: x-small;"> 'else' || CHR(10) ||</span></div><div><span style="font-size: x-small;"><span style="font-family: courier;"> ':n1 := </span><span style="font-family: courier;">' || C_FALSE || '</span><span style="font-family: courier;">;' || CHR(10) ||</span></span></div><div><span style="font-family: courier; font-size: x-small;"> 'end if;' || CHR(10) ||</span></div><div><span style="font-family: courier; font-size: x-small;"> 'END;' using out l_result;</span></div><div><span style="font-family: courier; font-size: x-small;"> return l_result;<br /> end ut_assert;</span></div><div><span style="font-family: courier; font-size: x-small;"> procedure ut_assert</span></div><div><div><span style="font-family: courier; font-size: x-small;"> (in_test_name in varchar2</span></div><div><span style="font-family: courier; font-size: x-small;"> ,in_test_string in varchar2)</span></div><div><span style="font-family: courier; font-size: x-small;"> is</span></div><div><span style="font-family: courier; font-size: x-small;"> begin</span></div><div><span style="font-family: courier; font-size: x-small;"> if ut_assert(in_test_name, in_test_string) = C_TRUE</span></div><div><span style="font-family: courier; font-size: x-small;"> then</span></div><div><span style="font-family: courier; font-size: x-small;"> if g_output_mode != C_BRIEF_OUTPUT</span></div><div><span style="font-family: courier; font-size: x-small;"> then</span></div><div><span style="font-family: courier; font-size: x-small;"> DBMS_OUTPUT.PUT_LINE(' -) PASSED: ' || in_test_name);</span></div><div><span style="font-family: courier; font-size: x-small;"> if g_output_mode = C_VERBOSE_OUTPUT</span></div><div><span style="font-family: courier; font-size: x-small;"> then</span></div><div><span style="font-family: courier; font-size: x-small;"> DBMS_OUTPUT.PUT_LINE(' Details: "' ||</span></div><div><span style="font-family: courier; font-size: x-small;"> replace(in_test_string,CHR(10),';') || '"' );</span></div><div><span style="font-family: courier; font-size: x-small;"> end if;</span></div><div><span style="font-family: courier; font-size: x-small;"> end if;</span></div><div><span style="font-family: courier; font-size: x-small;"> else</span></div><div><span style="font-family: courier; font-size: x-small;"> DBMS_OUTPUT.PUT_LINE('*** FAILED: ' || in_test_name);</span></div><div><span style="font-family: courier; font-size: x-small;"> DBMS_OUTPUT.PUT_LINE(' Details: "' ||</span></div><div><span style="font-family: courier; font-size: x-small;"> replace(in_test_string,CHR(10),';') || '"' );</span></div><div><span style="font-family: courier; font-size: x-small;"> end if;</span></div><div><span style="font-family: courier; font-size: x-small;"> end ut_assert;</span></div><div><span style="font-family: courier; font-size: x-small;"> procedure demo</span></div><div><span style="font-family: courier; font-size: x-small;"> is</span></div><div><span style="font-family: courier; font-size: x-small;"> begin</span></div><div><span style="font-size: x-small;"><span style="font-family: courier;"> </span><span style="font-family: courier;">ut_assert('This test passes', '1 = 1');</span></span></div><div><span style="font-size: x-small;"><span style="font-family: courier;"> </span><span style="font-family: courier;">ut_assert('This test fails', '0 = 1');</span></span></div><div><span style="font-family: courier; font-size: x-small;"> end demo;</span></div><div><span style="font-family: courier; font-size: x-small;">end simple_ut;</span></div><div><span style="font-family: courier; font-size: x-small;">/</span></div><div><br /></div><div><br /></div><h3 style="text-align: left;"><b><u>Where to start?</u></b></h3><ol style="text-align: left;"><li>Pick something to test, like a procedure in a package.</li><li>Define a "No Data" Test Case for that procedure (nothing for the procedure to do).</li><li>Create a Unit Test Package to contain you Unit Test code.</li><li>Create a procedure in the Unit Test Package called "no_data_to_process".</li><li>Write the "no_data_to_process" procedure to call the procedure (from step 1) with without any test data setup.</li><li>Add "ut_assert" procedure calls from the "simple_ut" package to document results.</li><li>Run the "no_data_to_process" procedure and check results in DBMS_OUTPUT.</li><li>Add more tests.</li></ol><div><br /></div><h3 style="text-align: left;"><b><u>Are there any Tips or Tricks?</u></b></h3><div><ol style="text-align: left;"><li>As part of the Test Case, test the data setup (if any) before the "actual" unit test.</li><li>Run the "actual" unit test in a separate procedure with a dedicated Exception Handler.</li><li>Create Unit Test Procedures/Functions to load Test Data into a single record variable.</li><li>Use the single record variable to INSERT Test Data into a table.</li><li>Setup Date/Time sensitive data for each run of a Unit Test.</li><li>Create additional test customers/locations/actors as needed to re-run unit tests.</li><li>Capture Sequence Generator values after the "actual" unit test.</li><li>Include testing of logs after the "actual" unit test.</li><li>Cleanup Test Data only when necessary to re-run unit tests.</li><li>Develop a complete Test Data Set with a variety of Test Cases setup and ready for testing.</li></ol><div><br /></div></div></div><h3><b><u>What is the downside?</u></b></h3><div>If the database logic is unstable (constantly changing), it is very difficult to maintain the unit tests. One strategy is to Unit Test only the functionality that is stable.</div><div><br /></div><h3 style="text-align: left;"><u>What is the Upside?</u></h3><div>Several obvious, but sometimes unexpected outcomes of unit testing:</div><div><ul style="text-align: left;"><li>Testing code that serves no purpose results in removal of useless code.</li><li>Fault insertion testing results in much better error messages and error recovery.</li><li>Thinking through unit test cases results in simplification of overall implementation.</li></ul></div><p></p>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-41539487242733575602024-01-31T22:13:00.001-06:002024-01-31T22:15:25.304-06:00Monitoring Blogger with UptimeRobotI use <a href="https://uptimerobot.com/" target="_blank">UptimeRobot</a> to
monitor this site. The free version of UptimeRobot includes:<div><ul style="text-align: left;"><li>Monitor 50 different URLs.</li><li>Check each URL every 5 minutes.</li><li>Monitor HTTP or HTTPS.</li><li>Test for keyword in response, with upper/lower case option.</li><li>Limit response times.</li><li>Send Email alert on failure.</li><li>Access to their comprehensive monitor dashboard.</li></ul><div><i>Note: I am not paid by UptimeRobot. I do find it really useful.</i></div><div><br /></div><div>It turns out Blogger is suspicious of servers at UptimeRobot. The result reeks havoc when trying to test for a keyword on a Blogger page. Instead of sending the expected page, Blogger sends something like this to UptimeRobot:</div></div><div><br /></div><div><span style="font-family: courier;">Our systems have detected unusual traffic from your computer network. This page checks to see if it&#39;s really you sending the requests, and not a robot.</span></div><div><br /></div><div>Obviously, UptimeRobot is a robot. So, I need a work-around.</div><div><br /></div><div>I use the "Full Response" link in UptimeRobot to review the entire response from Blogger.</div><div><ol style="text-align: left;"><li>Go to <a href="https://uptimerobot.com/" target="_blank">UptimeRobot</a></li><li>Setup a monitor on a Blogger URL and have it fail.</li><li>Click on the new monitor in the navigation stack on the left.</li><li>Click on "Details" at the far right of the "Incidents" region at the bottom.</li><li>A "Full Response" button may be at the top of the pop-up window (Sometimes, it's not there).</li><li>Right-Click and copy the URL for that button.</li><li>Use something like "curl" or "wget" to pull that file for review. (Windows Defender won't allow a browser to download the file in that link.)</li></ol><div>Inside the contents of the "Full Response" file, I look for something unique to my Blogger page and use it for a keyword. Yes, it technically is not loading a page from this site. However, it does confirm Blogger is responding and it is sending something that is unique to this site.</div></div><div><br /></div><div>Why did I make this post? Blogger changed the response page and all my keyword monitors started failing. I need this page for notes if/when it happens again...</div><div><br /></div><div>Note: In order to keep things easy for UptimeRobot, I check all my URLs on a 4 hour interval. Checking these URLs 6 times a day is plenty and it reduces the total load on this free service.</div><div><br /></div><div>Thank you UptimeRobot.</div>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-3555097170093757152022-08-04T17:24:00.023-05:002023-01-16T11:52:24.209-06:00Installing ORDS 22.2 and Oracle APEX 21.2 on WSL-2/Docker<h4 style="text-align: left;">What are we installing?</h4><div><ul style="text-align: left;"><li>Oracle APEX (Application Express)</li><li>ORDS (Oracle REST Data Services)</li><li>On an Oracle Database and Tomcat Server (Using <a href="https://www.reldesgen.com/2022/01/installing-oracle-dbapex-on.html" target="_blank">Installing Oracle Database and Tomcat on WLS-2/Docker</a>)</li></ul><div><br /></div></div><h4 style="text-align: left;">Is this a best practice?</h4><div><ul style="text-align: left;"><li>It is much easier to avoid the APEX/ORDS installation by <a href="https://apex.oracle.com/en/learn/getting-started/" target="_blank">Getting Started with Oracle APEX Free Tier</a></li></ul></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKpd0hy0oO-xFtwJ3s6FsY5CDeMFihY1YiF2WKUAfRcvX_jUUxkaVVR6gEvb6Eu9gHFfaSEB1CFn8eS66A2wHpVkqviOf-YHozvq5HCqm78_PfsnCBIksxIPii_2TBBnIzpes09vJKPjOeUc59fY9pxUwysOsTHD9WEn0xz1pZ5fUyheVMFvYrUjDN/s856/Get_Oracle_APEX_Free_Tier.png" style="margin-left: 1em; margin-right: 1em;"><img alt="Oracle APEX Free Tier" border="0" data-original-height="422" data-original-width="856" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKpd0hy0oO-xFtwJ3s6FsY5CDeMFihY1YiF2WKUAfRcvX_jUUxkaVVR6gEvb6Eu9gHFfaSEB1CFn8eS66A2wHpVkqviOf-YHozvq5HCqm78_PfsnCBIksxIPii_2TBBnIzpes09vJKPjOeUc59fY9pxUwysOsTHD9WEn0xz1pZ5fUyheVMFvYrUjDN/w320-h158/Get_Oracle_APEX_Free_Tier.png" width="320" /></a></div><div><ul style="text-align: left;"><li>To continue with this APEX/ORDS installation, an active Oracle Support Identifier is required to download the latest patch for recent APEX/ORDS releases.</li></ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik-2Q6__Mc8XVnB3wAjfhHryScdrDhujfwqFt1TWIV7-tjSDu4AfRh_-F7pwsRLylh_oxqtcnsTVtmROEhfeWwxM1NJRR6P92ATFk_VlgcThX4l05h-xvTAisnJf21FM25seXFU23tHCtSHPfK5USbnIqkTO-zRQ_9dYyOUkrJvMDKZLIAGOhCo1Eu/s930/Oracle_APEX_21.2_Patch.PNG" style="margin-left: 1em; margin-right: 1em;"><img alt="Oracle APEX 21.2 Patch" border="0" data-original-height="192" data-original-width="930" height="133" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik-2Q6__Mc8XVnB3wAjfhHryScdrDhujfwqFt1TWIV7-tjSDu4AfRh_-F7pwsRLylh_oxqtcnsTVtmROEhfeWwxM1NJRR6P92ATFk_VlgcThX4l05h-xvTAisnJf21FM25seXFU23tHCtSHPfK5USbnIqkTO-zRQ_9dYyOUkrJvMDKZLIAGOhCo1Eu/w640-h133/Oracle_APEX_21.2_Patch.PNG" width="640" /></a></div><ul style="text-align: left;"><li>There are several ways to obtain an active Oracle Support Identifier:</li><ul><li>Ask your employer/client to add you to their support contract.</li><li>Become an <a href="https://www.oracle.com/opn/index.html" target="_blank">Oracle Partner Member</a> (Development Only).</li><li>Purchase an <a href="https://shop.oracle.com/apex/f?p=dstore:home:0" target="_blank">Oracle Database Personal Edition (On-Premises)</a> with Support (Development Only).<br /></li></ul></ul></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA0A-ggC9dr4XJP4uzNODMMIcOHI164Q8a3UbW9ka7UsrmzN_dLDMetYHPk-p4gz3fE3mABk19e71lzvvXQPTSklbrjH3NItTCARahxZXxEh2XEi9JtfLMRutPUlLQN15M1kyceQgXywmELnua3ujp4LG7sS-wnBbl1e95TKoz3p0t6pEnM8Q8iKgY/s906/Oracle_Personal_Edition_OnPremise.PNG" style="margin-left: 1em; margin-right: 1em;"><img alt="Oracle Personal Database OnPremise" border="0" data-original-height="485" data-original-width="906" height="214" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA0A-ggC9dr4XJP4uzNODMMIcOHI164Q8a3UbW9ka7UsrmzN_dLDMetYHPk-p4gz3fE3mABk19e71lzvvXQPTSklbrjH3NItTCARahxZXxEh2XEi9JtfLMRutPUlLQN15M1kyceQgXywmELnua3ujp4LG7sS-wnBbl1e95TKoz3p0t6pEnM8Q8iKgY/w400-h214/Oracle_Personal_Edition_OnPremise.PNG" width="400" /></a></div><h4 style="text-align: left;"><span style="font-weight: normal;"><br /></span></h4><h4 style="text-align: left;">What do we need?</h4><ul style="text-align: left;"><li>Oracle Installation Files for APEX and ORDS</li><ul><li><a href="https://www.oracle.com/tools/downloads/apex-downloads.html" target="_blank">APEX installation zip file</a> (We will be using "<span style="background-color: #cccccc;">apex_21.2_en.zip</span>")</li><li><a href="https://support.oracle.com/epmos/faces/PatchDetail?patchId=33420059" target="_blank">Patch Set Bundle for Oracle APEX 21.2</a> (Requires Oracle Support Login. The simplest way to get an Oracle Support Login is to purchase an Oracle Database Personal Edition License with support at <a href="https://shop.oracle.com/apex/f?p=dstore:home" target="_blank">Oracle Store</a>. We renamed "<span style="background-color: white;">p33420059_212_GENERIC.zip</span>" to "<span style="background-color: #cccccc;">apex_p33420059_212_GENERIC.zip</span>")</li><li><a href="https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html">ORDS installation zip file</a> (We will be using "<span><span style="background-color: #cccccc;">ords-22.2.0.172.1758.zip</span></span>")</li></ul><li>The Windows "W:" drive should be mapped to "<span style="background-color: #cccccc;">\\wsl$\Ubuntu</span>".</li><li>The following requires an Oracle Database and Tomcat server setup and running. For this example, I am using a setup described at <a href="https://www.reldesgen.com/2022/01/installing-oracle-dbapex-on.html">Installing Oracle Database and Tomcat on WSL-2/Docker</a>. I have both of these are running in Docker:</li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div style="text-align: left;"><a href="#"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMvt0dOFv8lI129MvJxWRObr5dKmV7a9pBONyR31Q9W1VBY6JR6RDz5NjNTZSJpXCf8N_5rIa2NdlAF4zyxJWZXxgQx4FHB0uFNMg3rhEhsGBxdSrs_brLPDQymycT-EihBBJ9Lv2XvVH1oxDr1kM_wfHcLlmYAQyN1_1UBmSELYhhwEHKys-jb25n/s1600/DB_and_Tomcat_Docker_Containers.PNG" /></a></div></blockquote><div><ul style="text-align: left;"><li>Note: ORDS 22.2 requires Oracle JDK version 11 or higher. The "tomcat:9.0" docker image includes OpenJDK 11.0.13. <a href="https://www.baeldung.com/oracle-jdk-vs-openjdk" target="_blank">"Oracle JOK and OpenJDK builds are essentially identical from Java 11 onward"</a></li><li>These Docker Volume mappings should also be in place, per the Blog Post:</li></ul></div><div></div><blockquote><div><span style="font-family: courier; font-size: x-small;"><b><u>Container Name</u></b> <u><b>WSL2/Ubuntu Path</b></u> <b><u>Docker Path</u></b></span></div><div><span style="font-family: courier; font-size: small;">OraEE213 /opt/install_files /opt/install_files</span></div><div><span style="font-family: courier; font-size: small;">TomCat9r0 /opt/install_files /opt/install_files</span></div></blockquote><div><h4 style="text-align: left;"><br /></h4><h4 style="text-align: left;"><span style="font-weight: normal;">Note: There is an excellent reference at <a href="https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-installation-on-tomcat-22-onward" target="_blank">Oracle-Base</a> that includes setup of ORDS 22.1 onward and a link to setup ORDS versions previous to 22.1.</span></h4><div><span style="font-weight: normal;"><br /></span></div><div><span style="font-weight: normal;"><br /></span></div><h2 style="text-align: left;">Overview:</h2><div><ol style="text-align: left;"><li>Windows Setup Files for Installation</li><li>Ubuntu Setup Files for Installation</li><li>Create a PDB</li><li>Install APEX in the New PDB</li><li>Patch APEX in the New PDB</li><li>Install APEX Images on Tomcat</li><li>Configure and Install ORDS for the PDB on Tomcat</li></ol><div><br /></div></div><div><div><h3>1) Windows Setup Files for Installation</h3></div><div>Move/Copy these files to C:\tmp</div><div><ul style="text-align: left;"><li><span style="background-color: #cccccc;">apex_21.2_en.zip</span></li><li><span style="background-color: #cccccc;">apex_p33420059_212_GENERIC.zip</span></li><li><span style="background-color: #cccccc;">ords-22.2.0.172.1758.zip</span></li></ul></div><div><br /></div><div><br /><h3>2) Setup APEX/ORDS Files for Installation</h3><div><ol style="text-align: left;"><li>Start the Ubuntu App</li><li>Run These Commands</li></ol><div><span style="font-family: courier; font-size: x-small;">sudo su -</span></div></div><div><span style="font-family: courier; font-size: x-small;">apt install unzip</span></div><div><span style="font-family: courier; font-size: x-small;">mkdir /opt/install_files</span></div><div><span style="font-family: courier; font-size: x-small;">cd /opt/install_files</span></div><div><span style="font-family: courier; font-size: x-small;">#</span></div><div><div><span style="font-family: courier; font-size: x-small;">unzip /mnt/c/tmp/apex_21.2_en.zip</span></div><div><span style="font-family: courier; font-size: x-small;">mv apex apex212</span></div><div><span style="font-family: courier; font-size: x-small;">#</span></div><div><span style="font-family: courier; font-size: x-small;">unzip /mnt/c/tmp/apex_p33420059_212_GENERIC.zip</span></div><div><span style="font-family: courier; font-size: x-small;">mv 33420059 apex212/p33420059</span></div><div><span style="font-family: courier; font-size: x-small;">#</span></div><div><span style="font-family: courier; font-size: x-small;">mv apex212/images apex212_images</span></div><div><span style="font-family: courier; font-size: x-small;">cp -rv apex212/p33420059/images/* apex212_images/</span></div><div><span style="font-family: courier; font-size: x-small;">rm -rf apex212/p33420059/images</span></div><div><div><span style="font-family: courier; font-size: x-small;">#</span></div><div><span style="font-family: courier; font-size: x-small;">chown -Rv 54321:54321 apex212</span></div></div><div><span style="font-family: courier; font-size: x-small;">#</span></div><div><span style="font-family: courier; font-size: x-small;">mkdir ords222</span></div><div><span style="font-family: courier; font-size: x-small;">cd ords222</span></div><div><span style="font-family: courier; font-size: x-small;">unzip /mnt/c/tmp/ords-22.2.0.172.1758.zip</span></div><div><br /></div></div></div><div><br /></div><div><h3>3) Create a PDB</h3><div><ol style="text-align: left;"><li>Open Docker Desktop.</li><li>Find the "OraEE213" container.</li><li>Click the ">_" Icon (CLI) for that container to open a new window.</li><li>Run "<span style="background-color: #cccccc;">sqlplus / as sysdba</span>" to startup SQL*Plus and connect to the database.</li><li>Run the commands below in SQL*Plus:</li></ol><div><div><span style="font-family: courier; font-size: x-small;">create pluggable database "AP212PDB"</span></div><div><span style="font-family: courier; font-size: x-small;"> admin user "PDB_ADMIN" identified by "PDB_ADMIN"</span></div><div><span style="font-family: courier; font-size: x-small;"> default tablespace users</span></div><div><span style="font-family: courier; font-size: x-small;"> datafile </span><span style="font-family: courier; font-size: small;">'/opt/oracle/oradata/EE213CDB/AP212PDB/users01.dbf'</span></div><div><span style="font-family: courier; font-size: small;"> size 5M autoextend on</span></div><div><span style="font-family: courier; font-size: x-small;"> FILE_NAME_CONVERT = ('pdbseed', 'AP212PDB')</span></div><div><span style="font-family: courier; font-size: x-small;"> STORAGE UNLIMITED TEMPFILE REUSE;</span></div></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">alter pluggable database "AP212PDB" open;</span></div><div><br /></div><div>*NOTE:* To remove the new PDB run `drop pluggable database "AP212PDB" including datafiles;`</div><div><br /></div><div><br /></div><h3>4) Install APEX in the New PDB</h3><div><div>Login to the Docker Container that is running the Oracle EE 21.3 Database.<br /><ol><li>Open Docker Desktop.</li><li>Find the "OraEE213" container.</li><li>Click the ">_" Icon (CLI) for that container to open a new window.</li><li>Run "<span style="background-color: #cccccc;">cd /opt/install_files/apex212</span>" to move into the apex folder.</li><li>Run "<span style="background-color: #cccccc;">sqlplus / as sysdba</span>" to startup SQL*Plus and connect to the database.<br /></li><li>Run the commands below in SQL*Plus:</li></ol></div></div><div><span style="font-family: courier; font-size: x-small;">alter session set container = AP212PDB;</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><div><span style="font-family: courier; font-size: x-small;">set serveroutput on size unlimited format wrapped</span></div></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><span style="font-family: courier; font-size: x-small;">@apxsilentins.sql "SYSAUX" "SYSAUX" "TEMP" "/apex212_images/" \</span></div><div><span style="font-family: courier; font-size: x-small;"> "Passw0rd!" "Passw0rd!" "Passw0rd!" "Passw0rd!"</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">select status, owner, count(*)<br /> from dba_objects<br /> where owner in ('APEX_210200', 'FLOWS_FILES', 'APEX_LISTENER')<br /> group by status, owner<br /> order by status, owner;</span></div><div><span style="font-family: courier; font-size: x-small;"><br />alter user APEX_210200 identified by "Passw0rd!" account unlock;</span></div><div><br /></div><div><br /></div><div><div><i>NOTE: apxsilentins.sql values:</i><br /><ul><li><i>SYSAUX - Default Tablespace for APEX application user</i></li><li><i>SYSAUX - </i><i>Default Tablespace for APEX file user</i></li><li><i>TEMP - APEX Temporary Tablespace for Tablespace Group</i></li><li><i>/apex212_images/ - Virtual Directory in Tomcat for APEX Images"</i></li><li><i>Passw0rd! - APEX Public User Account</i></li><li><i><i>Passw0rd! - </i>APEX Listener Account</i></li><li><i><i>Passw0rd! - APEX </i>REST Public User Account</i></li><li><i><i>Passw0rd! - </i>APEX Internal Administrator User Account</i></li></ul></div><div><br /></div><div><h3>5) Patch APEX in the New PDB</h3><div></div><div>Login to the Docker Container that is running the Oracle EE 21.3 Database.<br /><ol><li>Open Docker Desktop.</li><li>Find the "OraEE213" container.</li><li>Click the ">_" Icon (CLI) for that container to open a new window.</li><li>Run "<span style="background-color: #cccccc;">cd /opt/install_files/apex212/</span><span style="background-color: #cccccc;">p33420059</span>" to move into the apex folder.</li><li>Run "<span style="background-color: #cccccc;">sqlplus / as sysdba</span>" to startup SQL*Plus and connect to the database.<br /></li><li>Run the commands below in SQL*Plus:</li></ol></div><div><span style="font-family: courier; font-size: x-small;">alter session set container = AP212PDB;</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">set serveroutput on size unlimited format wrapped</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">@catpatch.sql</span></div><div><br /></div><div><br /></div><div><h3>6) Install APEX Image Files on Tomcat</h3><h3><ol style="font-size: 18.72px; font-weight: 400;"><li style="font-size: medium;">Open Docker Desktop.</li><li><span style="font-size: small;">Find the "<span style="font-family: courier;">TomCat9r0</span>" container.</span></li><li style="font-size: medium;">Click the ">_" Icon (CLI) for that container to open a new window.</li><li style="font-size: medium;">Run "<span style="background-color: #cccccc;">cd /opt/install_files/apex212_images</span>" to move into the APEX Images folder.</li><li style="font-size: medium;">Run "<span style="background-color: #cccccc;">cp -rv . "</span><span style="background-color: #cccccc;">${CATALINA_HOME}/webapps/apex212_images"</span>"</li><li style="font-size: medium;">Confirm images are working using http://127.0.0.1:8080/apex212_images/apex_ui/img/apex-logo.svg. A red oval with the work APEX should appear in the browser.</li></ol><div><span style="font-family: inherit; font-size: small; font-weight: normal;"><br /><br /></span><h3 style="text-align: left;"><span style="font-family: inherit; font-size: small;">7) Configure and Install ORDS for the PDB on Tomcat</span></h3><div><div style="font-size: medium; font-weight: 400;"><i>After I published this BLOG, I re-ran the scripts. I found errors and missing information. I will update as soon as possible.</i></div></div><span><ol style="font-family: inherit; font-size: medium; font-weight: normal; text-align: left;"><li><span style="font-family: inherit; font-size: small; font-weight: normal;">Open Docker Desktop.</span></li><li><span style="font-family: inherit; font-size: small; font-weight: normal;">Find the "TomCat9r0" container.</span></li><li><span style="font-family: inherit; font-size: small; font-weight: normal;">Click the ">_" Icon (CLI) for that container to open a new window.</span></li><li><span style="font-family: inherit; font-size: small; font-weight: normal;">Run the commands below:</span></li></ol></span><span><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">export PATH="$PATH:/opt/install_files/ords222/bin"</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">exec /bin/bash</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">export ORDS_CONFIG=/opt/ords222_config</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">mkdir "${ORDS_CONFIG}"</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">cd "${ORDS_CONFIG}"</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">ords install \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --log-folder /opt/ords222_config/logs \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --feature-db-api true \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --admin-user SYS \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --db-hostname OraEE213 \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --db-port 1521 \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --db-servicename AP212PDB \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --db-user ORDS_PUBLIC_USER \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --feature-rest-enabled-sql true \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --feature-sdw true \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --gateway-mode proxied \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --gateway-user APEX_PUBLIC_USER \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --proxy-user \</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"> --password-stdin <<EOF</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">OraEE213#!</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">Passw0rd!</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">Passw0rd!</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">EOF</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">ords config set security.verifySSL false</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">ords war ords222_ap212pdb.war</span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;"><br /></span></span></div><div><span style="font-weight: normal;"><span style="font-family: courier; font-size: x-small;">cp ords222_ap212pdb.war "${CATALINA_HOME}/webapps/"</span></span></div><div><br /></div></span></div></h3></div></div><div style="text-align: left;"><ul style="text-align: left;"><li><div>Test Oracle APEX URL:</div></li><ul><li>Paste "http://localhost:8080/ords222_ap212pdb" into a Browser</li><li>Workspace: INTERNAL</li><li>Username: ADMIN</li><li>Password: Passw0rd!</li><li>The APEX Administrator Page should appear</li></ul><li>Test Web Based SQL*Developer:</li><ul><li>Paste "http://localhost:8080/ords222_ap212pdb/sql-developer" into a Browser</li><li>Enter a Database User Name when prompted.</li><li>Enter a Database User Password when prompted.</li><li>The "Database Actions | Launchpad" page should appear.</li></ul><li>REST Enable a Database Object:</li><ul><li>Login to APEX as an APEX Developer.</li><li>Open the SQL Workshop.</li><li>Select Object Browser.</li><li>Select a table.</li><li>Click on the REST tab</li><li>"Rest Enable Object": YES</li><li>"Authentication Required": NO</li><li>Click on APPLY</li><li>Copy the RESTful URL</li><li>Past the URL into a Web Browser</li><li>A JSON document should return</li></ul></ul><div><br /></div><h2 style="text-align: left;">Removal</h2><div><div><ol><li>Remove ORDS from Tomcat</li><li>Unplug the PDB</li></ol></div></div></div><h3 style="text-align: left;">1) Remove ORDS from Tomcat</h3><div><div><span style="color: red;">WARNING: This will remove the ORDS installation. It will undo the work done in Step 7.</span></div></div><div><br /></div><div><div><span style="font-family: courier; font-size: x-small;">rm -f "${CATALINA_HOME}/webapps/ords222_ap212pdb.war</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">rm -rf "${CATALINA_HOME}/webapps/ords222_ap212pdb</span></div></div><div><br /></div><div><br /></div><h3 style="text-align: left;">2) Unplug the new PDB</h3><div><span style="color: red;">WARNING: This will remove the PDB from the Database. It will undo the work done in Step 3.</span></div><div><br /></div><span style="font-family: courier; font-size: x-small;">alter pluggable database "AP212PDB" close immediate;<br /><br />alter pluggable database "AP212PDB" unplug</span></div><div><span style="font-family: courier; font-size: x-small;"> into '/opt/oracle/oradata/EE213CDB/AP212PDB/AP212PDB.XML';<br /><br />drop pluggable database "AP212PDB" keep datafiles;<br /><br />zip -q ./AP212PDB_PDB.zip /opt/oracle/oradata/EE213CDB/AP212PDB/*<br /><br />rm -rf /opt/oracle/oradata/EE213CDB/AP212PDB/*<br /></span><br /></div></div></div></div>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-40783864741967029432022-07-09T09:41:00.005-05:002022-07-09T15:58:12.803-05:00Distributed (Offline) Issue (Bug) Tracker<p><a href="https://git-scm.com/" target="_blank">Git</a> is one of the <a href="https://softwareengineering.stackexchange.com/questions/136079/are-there-any-statistics-that-show-the-popularity-of-git-versus-svn" target="_blank">most popular</a> source (version) control tools today.</p><p><a href="https://github.com/about" target="_blank">GitHub</a> and <a href="https://about.gitlab.com/" target="_blank">GitLab</a> are very popular hosting sites for Git Repositories.</p><p>As a developer, I have come to appreciate Git's ability to continue using source control when there is no internet connection. This is especially useful when travelling.</p><p>Both GitHub and GitLab also have Wiki Pages. These are very useful for documenting development procedures and other things that aren't included in product documentation. Because GitHub and GitLab implement Wiki Pages in a Git Repository, the Wiki Repository can be downloaded and updated offline.</p><p>Then there are the Issue Trackers. Both the <a href="https://github.com/features/issues" target="_blank">GitHub Issue Tracker</a> and <a href="https://docs.gitlab.com/ee/user/project/issues/" target="_blank">GitLab Issue Tracker</a> are only available online. Since these Issue Trackers are used to guide and document software development, they need to be available during development activities. However, neither of these Issue Trackers are available offline.</p><p>The need for a distributed issue (bug) tracker is evident from the attention it is getting on the internet. Several attempts have been made, but I have not been able to find anything stable and actively supported.</p><p>To solve this problem, I created a <a href="https://github.com/DDieterich/WikiBasedIssues" target="_blank">Wiki Based Issue Tracker</a> using the GitHub Issue Tracker and basic Linux/GNU tools (bash, awk, curl, sed, jq, ls, head, and date). It includes a utility that will capture GitHub issues and convert them to Wiki Based Issues (designed for one-time conversion of all issues).</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYvHCZK0_2kuDX9IbfxkLTy-_37qOsf9ww7FZjqvDiaeEmFdICW-gh-Muu5DJ6FC5ZtmHjroeEaL45tm_hL8HnqvN2TTyMhFuwASOMFEfFs_GqkmEz9Zk9srRep8BIMwrRVYQVwAsFgoPksxW9_hbORVVFlbi4QTSzLPHKfnQ8oTsloKxtJxo2f3JI/s937/Summary-Report-Screenshot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="579" data-original-width="937" height="365" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYvHCZK0_2kuDX9IbfxkLTy-_37qOsf9ww7FZjqvDiaeEmFdICW-gh-Muu5DJ6FC5ZtmHjroeEaL45tm_hL8HnqvN2TTyMhFuwASOMFEfFs_GqkmEz9Zk9srRep8BIMwrRVYQVwAsFgoPksxW9_hbORVVFlbi4QTSzLPHKfnQ8oTsloKxtJxo2f3JI/w588-h365/Summary-Report-Screenshot.png" width="588" /><br /></a></div><br /><div class="separator" style="clear: both; text-align: center;">Summary Report Screenshot (Partial)</div><p><i>What prevents 2 developers from modifying the same issue?</i></p><p>Nothing. Multiple developers can modify the same issue in the same way multiple developers can modify the same source code file in Git. And, just like Git, the "merge" is where it all gets sorted.</p><p><i>What about a Kanban Board?</i></p><p>A basic issue reporting tool is provided. This tool can be modified to create a Kanban Board style report.</p><p><i>What about an interactive Kanban Board?</i></p><p>Hmmm...</p>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-61175193288358890262022-03-28T10:30:00.123-05:002022-05-02T16:29:29.340-05:00The Decline of Modern Management<p></p><div>I previously posted this for about a month, then took it down. I am not in favor of people doing that, so I am posting it again. I updated it from the original post. I am still not happy with it. I am removing it again. I will leave this as a reference to what I had posted.</div><div><p></p></div>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-87906954122769327062022-01-17T16:23:00.042-06:002022-08-09T14:11:32.113-05:00Installing Oracle Database and Tomcat on WSL-2/Docker<div>What are we installing?</div><div><ul><li>Windows Subsystem for Linux (WSL2)</li><li>Docker Desktop for Windows</li><li>Oracle Database</li><li>Tomcat Web Server</li></ul><div style="text-align: left;">What do we need?</div></div><div><ul style="text-align: left;"><li>Computer/Laptop</li><li>Windows 10 version 2004 and higher (Build 19041 and higher) or Windows 11</li><li>16 Gb Memory Recommended</li></ul><div>Overview:</div><div><ul style="text-align: left;"><li>Setup Windows Subsystem for Linux (WSL2).</li><li>Setup Docker for Windows.</li><li>Load and Run a Docker Image that has an Oracle Database.</li><li>Load and Run a Docker Image that has a Tomcat WebApp Server.</li><li>Failed ORDS Docker Image with Failure Source Identified.</li></ul></div><div><br /></div><h1 style="text-align: left;">Windows Subsystem for Linux (WSL2)</h1><div>This procedure was sourced from <a href="https://docs.microsoft.com/en-us/windows/wsl/install">Microsoft.com: Install WSL</a>:<br /><ul style="text-align: left;"><li>Run CMD as Administrator</li><ul><li><span style="background-color: #cccccc;">wsl -l -v</span></li><ul><li>Should show "NAME STATE VERSION" heading with a line underneath showing "Ubuntu Running 2". If not, run the "wsl --install" in the next step.</li></ul><li><span style="background-color: #cccccc;">wsl --install -d Ubuntu</span></li><ul><li>A reboot will be required to activate the installation.</li><li>The Ubuntu App will run automatically after reboot.</li><li>The Ubuntu App will create a new username and password. - The username and password created in the Ubuntu App is specific to each separate Linux distribution that you install under WSL and has no relationship to your Windows user name.</li><li>If any problems, see <a href="https://docs.microsoft.com/en-us/windows/wsl/troubleshooting#installation-issues">Microsoft.com: installation-issues</a></li></ul><li><span style="background-color: #cccccc;">wsl --shutdown</span> - Run this if you need to shutdown WSL.</li></ul><li>Start the Ubuntu App and Run These Commands</li><ul><li><span style="background-color: #cccccc;">sudo apt update</span> - Updates the list of packages available for Ubuntu.</li><li><span style="background-color: #cccccc;">sudo apt upgrade</span> - Upgrades Ubuntu packages.</li></ul><li>Review and Set advanced options as needed. See <a href="https://docs.microsoft.com/en-us/windows/wsl/wsl-config">Microsoft.com: Advanced settings configuration in WSL</a></li></ul>See also: <a href="https://docs.microsoft.com/en-us/windows/wsl/setup/environment">Microsoft.com: Setup WSL for Development</a></div><div><br /></div><div><br /></div><h1 style="text-align: left;">Docker Desktop for Windows</h1><div><ul style="text-align: left;"><li>Go to <a href="https://www.docker.com/products/docker-desktop">Docker.com: Docker Desktop</a></li><li>Click on "Download for Windows"</li><li>Run "Docker Desktop Installer.exe"</li><li>Open Docker Desktop</li><ul><li>"Settings" --> "Resources" --> "WSL Integration"</li><li>Turn on "ubuntu" for "enable integration with additional distros:"</li><li>Apply & Restart</li></ul><li>Run Ubuntu App</li><ul><li>(Optional) docker run hello-world</li></ul></ul><div><br /></div><h1 style="text-align: left;">Oracle Database</h1></div><div>This procedure will persist the database files outside of the Docker container at "/opt/OraEE213/oradata" in WSL2/Ubuntu. After the database is created, it will re-open with all saved changes if the Docker container is restarted.</div><div><ul style="text-align: left;"><li>Open the Ubuntu App and Run These Commands:</li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">sudo mkdir /opt/OraEE213/oradata</span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">sudo chown 54321 /opt/OraEE213/oradata</span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">docker network create oranet</span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">docker login container-registry.oracle.com</span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">docker pull container-registry.oracle.com/database/enterprise:21.3.0.0</span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div><div><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">docker run -d --restart unless-stopped --network=oranet -p 1521:1521 -p 5500:5500 -e ORACLE_SID=EE213CDB -e ORACLE_PDB=TESTPDB -e 'ORACLE_PWD=OraEE213#!' -v /opt/OraEE213/oradata:/opt/oracle/oradata -v /opt/install_files:/opt/install_files --name OraEE213 container-registry.oracle.com/database/enterprise:21.3.0.0</span></div></blockquote><div><div><ul style="text-align: left;"><li>Run Docker Desktop</li><ul><li>Select "Containers/Apps" in the menu list on the left.</li><li>Click on the "Logs" Icon in the "OraEE213" container line.</li><li>The log of the database installation will appear.</li><li>Wait for this banner before attempting to use the database:</li></ul></ul><div><p class="jss597" style="background-color: #253138; box-sizing: inherit; color: #aed9fa; font-family: "Open SansVariable", "Helvetica Neue", sans-serif; font-size: 12px; margin: 0px; min-height: 18px;"><code class="jss596" style="box-sizing: inherit; overflow-wrap: break-word;">#########################</code></p><p class="jss597" style="background-color: #253138; box-sizing: inherit; color: #aed9fa; font-family: "Open SansVariable", "Helvetica Neue", sans-serif; font-size: 12px; margin: 0px; min-height: 18px;"><code class="jss596" style="box-sizing: inherit; overflow-wrap: break-word;">DATABASE IS READY TO USE!</code></p><p class="jss597" style="background-color: #253138; box-sizing: inherit; color: #aed9fa; font-family: "Open SansVariable", "Helvetica Neue", sans-serif; font-size: 12px; margin: 0px; min-height: 18px;"><code class="jss596" style="box-sizing: inherit; overflow-wrap: break-word;">#########################</code></p></div></div><div style="text-align: left;"><ul style="text-align: left;"><ul><li>(Optional) Click the ">_" Icon (CLI) on the "OraEE213" container line and run "<span style="background-color: #cccccc;">sqlplus / as sysdba</span>" to login to the container database as "SYS".</li><li>(Optional) Connect to the container database from an Oracle client using the connect string "//<span style="background-color: #cccccc;">localhost:1521/EE213CDB</span>" and "system/OraEE213#!" login.</li><li>(Optional) Connect to the pluggable database from an Oracle client using the connect string "//<span style="background-color: #cccccc;">localhost:1521/TESTPDB</span>" and "system/OraEE213#!" login.</li><li>(Optional) Check OEM DB Express at url <span style="background-color: #cccccc;">https://localhost:5500/em/login</span>, Username: SYSTEM, Password: OraEE213#!, Container Name: (Blank)</li></ul></ul><div><br /></div></div><h1 style="text-align: left;">Tomcat Web Server</h1></div><div><div><div><ul><li>Open the Ubuntu App and Run This Command</li><ul><li><span style="background-color: #cccccc;">docker run -d --restart unless-stopped --network=oranet -p 8080:8080 -v /opt/TomCat9r0/webapps:/usr/local/tomcat/webapps -v /opt/install_files:/opt/install_files --name TomCat9r0 tomcat:9.0</span></li></ul><li>Run Docker Desktop</li><ul><li>Select "Containers/Apps" in the menu list on the left.</li><li>Click the ">_" Icon (CLI) on the "TomCat9r0" container line.</li><li>Run these commands:</li></ul></ul><blockquote><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">cp -rf "/usr/local/tomcat/webapps.dist"/* "/usr/local/tomcat/webapps"<br /><br />sed "-i_bak" -e '$s?^</tomcat-users>$?<role rolename="manager-gui"/><user username="manager" password="tomcat" roles="manager-gui"/></tomcat-users>?1' "/usr/local/tomcat/conf/tomcat-users.xml"<br /><br />sed "-i_bak" -e '/RemoteAddrValve/d' -e '/0:0:0:0:0:0:0:1/d' "/usr/local/tomcat/webapps/manager/META-INF/context.xml"<br /><br />sed "-i_bak" -e '/RemoteAddrValve/d' -e '/0:0:0:0:0:0:0:1/d' "/usr/local/tomcat/webapps/host-manager/META-INF/context.xml"</span></blockquote><ul><li>(Optional) Go to "<span style="background-color: #cccccc;">http://localhost:8080/</span>"</li><ul><li>Click on "Manager App" button</li><li>Login with Username "manager" and password "tomcat"</li><li>Confirm Tomcat Web Applications are available and working.</li></ul></ul><div><br /></div></div><blockquote><div></div></blockquote><h1>Next Post: Installing ORDS and Oracle APEX</h1><div>In the next post "Installing ORDS and Oracle APEX on WSL-2/Docker", we look at installing ORDS and Oracle APEX</div><div><br /></div><div><div><h1>Addendum: Failed ORDS Docker image</h1><div>An alternative to the Tomcat installation is using Oracle's ORDS Dockerfile to create a Docker Container with ORDS. However, there are 2 problems with this alternative:</div><div><ol style="text-align: left;"><li>There is a bug in Oracle's Dockerfile (shown below).</li><li>Multiple Docker Containers are required for each ORDS deployment. Each deployment of ORDS requires a different HTTP Port Number. With Tomcat, all ORDS servers share the same HTTP Port Number.</li></ol></div><div>For the source of this section, see <a href="https://container-registry.oracle.com/ords/f?p=113:10">Oracle.com: Container Registry</a>: "Database" --> "ords" --> "Oracle REST Data Services (ORDS) with Application Express"</div><div><ul><li>Open the Ubuntu App and Run These Commands:</li></ul></div></div></div></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">sudo mkdir "/opt/ords21.4.0"</span></div></div></div></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div></div></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">echo 'CONN_STRING=SYS/OraEE213#!@//OraEE213:1521/TESTPDB' > "/tmp/conn_string.txt"</span></div></div></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div></div></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">sudo mv "/tmp/conn_string.txt" "/opt/ords21.4.0"</span></div></div></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;"><br /></span></div></div></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div><div><div style="text-align: left;"><span style="background-color: #cccccc; font-family: courier; font-size: x-small;">docker run --rm --network=oranet -p 8181:8181 -v /opt/ords21.4.0:/opt/oracle/variables --name ords21.4.0 container-registry.oracle.com/database/ords:21.4.0</span></div></div></div></div></blockquote><div><div><div><div><ul style="text-align: left;"><li><b><span style="font-size: medium;">FAILS with this message</span></b>:</li></ul></div><span style="font-family: courier; font-size: x-small;">INFO : This container will start a service running ORDS 21.4.0 and APEX 21.2.0.</span><br /><span style="font-family: courier; font-size: x-small;">INFO : CONN_STRING has been set as variable on container.</span><br /><span style="font-family: courier; font-size: x-small;">INFO : </span><b><span style="font-family: inherit; font-size: medium;">Database connection established.</span></b><br /><span style="font-family: courier; font-size: x-small;">INFO : Apex is not installed on your database.</span><br /><span style="font-family: courier; font-size: x-small;">INFO : Installing APEX on your DB please be patient.</span><br /><span style="font-family: courier; font-size: x-small;">INFO : If you need more verbosity run below command:</span><br /></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><span style="font-family: courier;"><span style="font-size: x-small;">docker exec -it b402760c75a3 tail -f /tmp/install_container.log</span></span></div></blockquote><div><span style="font-family: courier;"><span style="font-size: x-small;">INFO : APEX has been installed.</span><br /><span style="font-size: x-small;">INFO : Configuring APEX.</span><br /><span style="font-size: x-small;">INFO : APEX_PUBLIC_USER has been configured as oracle.</span><br /><span style="font-size: x-small;">INFO : APEX ADMIN password has configured as 'Welcome_1'.</span><br /><span style="font-size: x-small;">INFO : Use below login credentials to first time login to APEX service:</span><br /></span></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><span style="font-family: courier;"><span style="font-size: x-small;">Workspace: internal</span></span></div><div><span style="font-family: courier;"><span style="font-size: x-small;">User: ADMIN</span></span></div><div><span style="font-family: courier;"><span style="font-size: x-small;">Password: Welcome_1</span></span></div></blockquote><div><span style="font-family: courier; font-size: x-small;">INFO : Preparing ORDS.</span><br /><span style="font-family: courier; font-size: x-small;">INFO : Installing ORDS on you database.</span><br /><span style="font-family: courier; font-size: x-small;">Requires to login with administrator privileges to verify Oracle REST Data Services schema.</span><br /><br /><span style="font-family: courier; font-size: x-small;">Connecting to database user: SYS as sysdba url: jdbc:oracle:thin:@////OraEE213:1521/TESTPDB</span><br /><span style="font-family: courier; font-size: x-small;">2022-01-18T00:14:23.731Z </span><span style="font-family: courier; font-size: x-small;">WARNING </span><span style="font-family: inherit; font-size: medium; font-weight: bold;">Failed to connect to user: SYS as sysdba url: jdbc:oracle:thin:@////OraEE213:1521/TESTPDB</span><br /><span style="font-family: courier; font-size: x-small;">IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]" (CONNECTION_ID=DUId2GdqTiaf5/93oFpqsw==)</span></div><div><span style="font-family: courier; font-size: x-small;"><div><br /></div><div>java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]" (CONNECTION_ID=DUId2GdqTiaf5/93oFpqsw==)</div><div>INFO : Starting ORDS....</div><div>2022-01-18T00:14:27.622Z WARNING Failed to connect to user: ORDS_PUBLIC_USER url: jdbc:oracle:thin:@////OraEE213:1521/TESTPDB</div><div>IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]" (CONNECTION_ID=Z2b/FsUxR1C7IzNDt5+RTQ==)</div></span><div><br /></div><b>Problem:</b> The "/opt/oracle/ords/startService.sh" script in the container splits the $CONN_STRING value into DB_USER, DB_PASS, DB_HOST, DB_PORT, and DB_NAME variables. The DB_HOST value retains the leading "//" in front of the hostname in the $CONN_STRING value. The "//" is required for the database connection, but fails when used by ORDS.</div><div><br /><i><span style="font-size: x-small;">update: Changed "\" to "/" on database connect strings.</span></i><br /></div><div><div></div></div></div><div></div></div>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-55851788733382429652019-06-16T00:25:00.000-05:002019-06-16T13:17:52.964-05:00Dual Stack IPv4/IPv6 on Cisco RV042G Dual WAN Router<br />
<div>
<div>
<ul>
<li>Dual Stack IPv4/IPv6 - Both IPv4 and IPv6 are available on the network.</li>
<li>Cisco RV042G - Cisco router for small business networks. (<a href="https://www.amazon.com/CISCO-Dual-Gigabit-WAN-Router/dp/B008CWW6VY">https://www.amazon.com/CISCO-Dual-Gigabit-WAN-Router/dp/B008CWW6VY</a>)</li>
<li>Dual WAN Router - 2 ISPs (Internet Service Providers) are connected to the same router.</li>
</ul>
</div>
</div>
<div>
<br /></div>
<div>
I have several servers on AWS (Amazon Web Services). I pay a fee each month for an Elastic IPv4 address. I use this address for an OpenVPN server to access my EC2 instances.</div>
<div>
<br /></div>
<div>
I found that AWS provides free public IPv6 addresses for all EC2 instances in a VPC (Virtual Private Cloud) networks. The key here is that a public IPv6 address is permanently assigned to the EC2 instance, whether it is running or not. There is no need to pay the fee for an Elastic IP address.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<div>
<a href="https://docs.aws.amazon.com/vpc/latest/userguide/vpc-ip-addressing.html">https://docs.aws.amazon.com/vpc/latest/userguide/vpc-ip-addressing.html</a></div>
</div>
<div>
<br /></div>
<div>
<span style="background-color: white; color: #444444; font-family: "amazon ember" , "open sans" , "helvetica" , "arial" , sans-serif; font-size: 16px;">"No distinction between public and private IP addresses. IPv6 addresses are public"</span></div>
<div>
<br /></div>
<div>
<span style="background-color: white; color: #444444; font-family: "amazon ember" , "open sans" , "helvetica" , "arial" , sans-serif; font-size: 16px;">"An IPv6 address persists when you stop and start your instance, and is released when you terminate your instance."</span></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
I confirmed all my computers, network equipment, and ISPs would work with IPv6. After upgrading my router to a Cisco RV042g, I had everything. However, IPv6 addresses were not working. I found the time to troubleshoot the problem and found a solution.</div>
<div>
<br /></div>
<div>
I have Spectrum (Time Warner Cable / Road Runner) on WAN1 and AT&T on WAN2. Both are on a gigabit connection. AT&T is metered, so I use Spectrum as primary and AT&T as backup. I also route all voice-over-IP traffic through AT&T. Both WANs are configured the same in the IPV6 configuration in the Cisco router.</div>
<div>
<br /></div>
<div>
Note: IPv6 will not automatically fail-over. It looks like IPv6 is not getting rerouted to the backup WAN. IPv6 seems to have an affinity for the last WAN it was using. Resetting the network connection on the computer solves the problem, but that is not automatic. I tried a smaller value for "Router Lifetime" (30 seconds) in the Router Advertisement configuration. The IPv6 traffic did not get rerouted to the backup WAN. I updated to the latest firmware v4.2.3.09 (Jul 2 2018 14:38:22), but IPv6 would not reroute to the backup WAN.<br />
<div>
<br /></div>
<a href="https://blog.ipspace.net/2014/03/can-we-use-ipv6-router-advertisements.html">https://blog.ipspace.net/2014/03/can-we-use-ipv6-router-advertisements.html</a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br />
<br /></div>
<h2>
Enable ICMP (Ping) on Windows 10 Firewall (Inbound and Outbound):</h2>
<div>
I found this command to open ICMP on my Windows firewall<br />
<blockquote class="tr_bq">
netsh firewall set icmpsetting type=all mode=enable</blockquote>
<a href="https://answers.microsoft.com/en-us/windows/forum/all/windows-10-firewall-filtering-icmp/527551d0-e477-4dd6-a0a0-eae724940ba3">https://answers.microsoft.com/en-us/windows/forum/all/windows-10-firewall-filtering-icmp/527551d0-e477-4dd6-a0a0-eae724940ba3</a><br />
<br />
<br />
<br />
Then, I disabled all the rules that allow inbound and outbound ICMPv4.<br />
<br />
<br /></div>
<div>
<br /></div>
<div>
<a href="http://1.bp.blogspot.com/-4G9iJb74nUI/XQYkwmhKyKI/AAAAAAAAfEY/NNx95bUDm2kOOMxitq-_yyUngI4N6j5pwCK4BGAYYCw/s1600/Win10%2BFirewall%2BICMP%2BInbound.PNG" imageanchor="1"><img border="0" src="https://1.bp.blogspot.com/-4G9iJb74nUI/XQYkwmhKyKI/AAAAAAAAfEY/NNx95bUDm2kOOMxitq-_yyUngI4N6j5pwCK4BGAYYCw/s1600/Win10%2BFirewall%2BICMP%2BInbound.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br />
<br />
<a href="http://2.bp.blogspot.com/-fvyVQcVdiN0/XQYk0QEAhOI/AAAAAAAAfEg/al5NCmb1RFgzfhlM2HBwdgGs32vN_5M7gCK4BGAYYCw/s1600/Win10%2BFirewall%2BICMP%2BOutbound.PNG" imageanchor="1"><img border="0" src="https://2.bp.blogspot.com/-fvyVQcVdiN0/XQYk0QEAhOI/AAAAAAAAfEg/al5NCmb1RFgzfhlM2HBwdgGs32vN_5M7gCK4BGAYYCw/s1600/Win10%2BFirewall%2BICMP%2BOutbound.PNG" /></a><br />
<br /></div>
<div>
<br />
<br />
<br />
<br />
<br /></div>
<h2>
Cisco RV042g Configuration (non-default settings for IPv6):</h2>
<div>
<br />
Note: Some of these images were captured with the primary WAN (WAN1) disabled.<br />
<br /></div>
<div>
<br /></div>
<div>
<a href="http://4.bp.blogspot.com/-kxgJab3FmRg/XQXOQwyRAII/AAAAAAAAfCA/4JDLpZSl5KYNmPnA4MTnCCQuexN9mB-3wCK4BGAYYCw/s1600/RV042G%2BWAN2%2BIPV6%2Bon%2BTWC.PNG" imageanchor="1"><img border="0" src="https://4.bp.blogspot.com/-kxgJab3FmRg/XQXOQwyRAII/AAAAAAAAfCA/4JDLpZSl5KYNmPnA4MTnCCQuexN9mB-3wCK4BGAYYCw/s1600/RV042G%2BWAN2%2BIPV6%2Bon%2BTWC.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="http://2.bp.blogspot.com/-r2Jb3E54K1w/XQXOjzEHu6I/AAAAAAAAfCM/ibvcwB4B8Vctx3oqLtewKYw5wMDBEcDAQCK4BGAYYCw/s1600/RV042G%2BEnable%2BDHCP-PD%2Bon%2BTWC.PNG" imageanchor="1"><img border="0" src="https://2.bp.blogspot.com/-r2Jb3E54K1w/XQXOjzEHu6I/AAAAAAAAfCM/ibvcwB4B8Vctx3oqLtewKYw5wMDBEcDAQCK4BGAYYCw/s1600/RV042G%2BEnable%2BDHCP-PD%2Bon%2BTWC.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="http://1.bp.blogspot.com/-Ij68rH9NnfU/XQXMDKB9yVI/AAAAAAAAfBo/VuX3sdI3L0gbCS3qmtXOfcOtEVq-uRLQwCK4BGAYYCw/s1600/RV042G%2BWAN2%2BIPV6%2Bon%2BAT%2526T.PNG" imageanchor="1"><img border="0" src="https://1.bp.blogspot.com/-Ij68rH9NnfU/XQXMDKB9yVI/AAAAAAAAfBo/VuX3sdI3L0gbCS3qmtXOfcOtEVq-uRLQwCK4BGAYYCw/s1600/RV042G%2BWAN2%2BIPV6%2Bon%2BAT%2526T.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="http://1.bp.blogspot.com/-EjllSJaIhHg/XQXMKYc4l2I/AAAAAAAAfBw/vA9mx9zB3w8TfGwtqGstpZD7Wf0I1qsVQCK4BGAYYCw/s1600/RV042G%2BEnable%2BDHCP-PD%2Bon%2BATT.PNG" imageanchor="1"><img border="0" src="https://1.bp.blogspot.com/-EjllSJaIhHg/XQXMKYc4l2I/AAAAAAAAfBw/vA9mx9zB3w8TfGwtqGstpZD7Wf0I1qsVQCK4BGAYYCw/s1600/RV042G%2BEnable%2BDHCP-PD%2Bon%2BATT.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
These are Google's IPv6 DNS Servers:</div>
<div>
<ul>
<li>2001:4860:4860:8844</li>
<li>2001:4860:4860:8888</li>
</ul>
</div>
<div>
<br /></div>
<div>
<a href="http://1.bp.blogspot.com/-PG16V7GLFFg/XQXPEnUzq7I/AAAAAAAAfCk/ZkKs5XJwJBUb-aPTlNiiQMgG_-G-DFyPwCK4BGAYYCw/s1600/RV042G%2BEnable%2BDHCP%2BServer.PNG" imageanchor="1"><img border="0" src="https://1.bp.blogspot.com/-PG16V7GLFFg/XQXPEnUzq7I/AAAAAAAAfCk/ZkKs5XJwJBUb-aPTlNiiQMgG_-G-DFyPwCK4BGAYYCw/s1600/RV042G%2BEnable%2BDHCP%2BServer.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="http://2.bp.blogspot.com/-uRvKK6AYuXc/XQXPZTthtkI/AAAAAAAAfC4/K64yZRLOZuMys53f_YWGxzkDx6EytTtgACK4BGAYYCw/s1600/RV042G%2BEnable%2BRouter%2BAdvertisement.PNG" imageanchor="1"><img border="0" src="https://2.bp.blogspot.com/-uRvKK6AYuXc/XQXPZTthtkI/AAAAAAAAfC4/K64yZRLOZuMys53f_YWGxzkDx6EytTtgACK4BGAYYCw/s1600/RV042G%2BEnable%2BRouter%2BAdvertisement.PNG" /></a></div>
<div>
<br />
<br /></div>
<div>
<br /></div>
<div>
<a href="http://1.bp.blogspot.com/-wIHIy9HRBsQ/XQYrk42Ju0I/AAAAAAAAfFU/U-LshaWCkvUdC4PaPxzOS1dseeyTrRTywCK4BGAYYCw/s1600/RV042G%2BAllow%2BPing%2BICMP.PNG" imageanchor="1"><img border="0" src="https://1.bp.blogspot.com/-wIHIy9HRBsQ/XQYrk42Ju0I/AAAAAAAAfFU/U-LshaWCkvUdC4PaPxzOS1dseeyTrRTywCK4BGAYYCw/s1600/RV042G%2BAllow%2BPing%2BICMP.PNG" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
After configuring IPv6, I was able to contact my Cisco router using this URL:</div>
<div>
<a href="https://[fc00::1]/home.htm">https://[fc00::1]/home.htm</a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br />
<br />
<br /></div>
<h2>
Spectrum (WAN1) IPv6 Test Results:</h2>
<div>
<br />
I ran these tests with the WAN2 port disabled on the router.<br />
<br />
<br />
<br /></div>
<div>
<a href="http://4.bp.blogspot.com/-ejklfi1BeVk/XQXPuNqomUI/AAAAAAAAfDI/YFSWkN5nmjo6eMUoJriJyL1_4ISwgafZQCK4BGAYYCw/s1600/Google%2BIPV6%2BTest%2Bon%2BTWC.PNG" imageanchor="1"><img border="0" height="291" src="https://4.bp.blogspot.com/-ejklfi1BeVk/XQXPuNqomUI/AAAAAAAAfDI/YFSWkN5nmjo6eMUoJriJyL1_4ISwgafZQCK4BGAYYCw/s400/Google%2BIPV6%2BTest%2Bon%2BTWC.PNG" width="400" /></a></div>
<div>
<br /></div>
<div>
<a href="http://4.bp.blogspot.com/-Z7qLwrzQlRU/XQXP7NKsVcI/AAAAAAAAfDQ/-gm2twQOcvw2I1ompwmJKmP1Kk8jBaoWwCK4BGAYYCw/s1600/ipv6-test.com%2Bon%2BTWC.PNG" imageanchor="1"><img border="0" height="376" src="https://4.bp.blogspot.com/-Z7qLwrzQlRU/XQXP7NKsVcI/AAAAAAAAfDQ/-gm2twQOcvw2I1ompwmJKmP1Kk8jBaoWwCK4BGAYYCw/s640/ipv6-test.com%2Bon%2BTWC.PNG" width="640" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="http://2.bp.blogspot.com/-ePCj3eOQLqk/XQXP_-43rLI/AAAAAAAAfDc/v4Xcv6_Q5JUHBMKNrY2166-RyVtYGAQFQCK4BGAYYCw/s1600/test-ipv6.com%2Bon%2BTWC.PNG" imageanchor="1"><img border="0" height="287" src="https://2.bp.blogspot.com/-ePCj3eOQLqk/XQXP_-43rLI/AAAAAAAAfDc/v4Xcv6_Q5JUHBMKNrY2166-RyVtYGAQFQCK4BGAYYCw/s400/test-ipv6.com%2Bon%2BTWC.PNG" width="400" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<h2>
AT&T (WAN2) IPv6 Test Results:</h2>
<div>
<br />
<br />
I ran these tests with the WAN1 port disabled on the router.<br />
<br />
<br />
<br /></div>
<div>
<a href="http://1.bp.blogspot.com/-NPI3A9swXLE/XQXQQOlF2QI/AAAAAAAAfDs/GnEo7tf1TcoeM2lA-dpQxy7XXOiJ8gNEQCK4BGAYYCw/s1600/Google%2BIPV6%2BTest%2Bon%2BATT.PNG" imageanchor="1"><img border="0" height="301" src="https://1.bp.blogspot.com/-NPI3A9swXLE/XQXQQOlF2QI/AAAAAAAAfDs/GnEo7tf1TcoeM2lA-dpQxy7XXOiJ8gNEQCK4BGAYYCw/s400/Google%2BIPV6%2BTest%2Bon%2BATT.PNG" width="400" /></a></div>
<div>
<br /></div>
<div>
<a href="http://4.bp.blogspot.com/-gafmahJ38ks/XQXQTaCnUMI/AAAAAAAAfD0/8CcqGORscf4JpscJBP3YckH-ii5ksXiwACK4BGAYYCw/s1600/ipv6-test.com%2Bon%2BATT.PNG" imageanchor="1"><img border="0" height="475" src="https://4.bp.blogspot.com/-gafmahJ38ks/XQXQTaCnUMI/AAAAAAAAfD0/8CcqGORscf4JpscJBP3YckH-ii5ksXiwACK4BGAYYCw/s640/ipv6-test.com%2Bon%2BATT.PNG" width="640" /></a></div>
<div>
<br /></div>
<div>
<a href="http://4.bp.blogspot.com/-tj6Be_rRvUc/XQXQXjX2L_I/AAAAAAAAfD8/AZKp4WKD30Eo2qqCXFieMtjNi-EQbJ5aQCK4BGAYYCw/s1600/test-ipv6.com%2Bon%2BATT.PNG" imageanchor="1"><img border="0" height="344" src="https://4.bp.blogspot.com/-tj6Be_rRvUc/XQXQXjX2L_I/AAAAAAAAfD8/AZKp4WKD30Eo2qqCXFieMtjNi-EQbJ5aQCK4BGAYYCw/s400/test-ipv6.com%2Bon%2BATT.PNG" width="400" /></a></div>
<div>
<br />
<br />
<br />
<br />
<h2>
"ipconfig" Shows my Ethernet Adapter Configuration:</h2>
<br />
<br />
Note: Unlike IPv4, there are many IPv6 addresses on my network adapter. Also, there are 2 sets of IPv6 addresses, one for each ISP (Dual WAN).<br />
<br />
<ul>
<li>2605:6000... - Spectrum</li>
<li>2600:1700... - AT&T</li>
</ul>
<div>
<br /></div>
<br />
<a href="http://4.bp.blogspot.com/-LoOD_tz-Exg/XQYqPlWxJPI/AAAAAAAAfFI/WJU_H5HlI2Ap9cTitL8pF4fx5mLdSivTQCK4BGAYYCw/s1600/Ethernet%2BAdapter%2Bipconfig.PNG" imageanchor="1"><img border="0" src="https://4.bp.blogspot.com/-LoOD_tz-Exg/XQYqPlWxJPI/AAAAAAAAfFI/WJU_H5HlI2Ap9cTitL8pF4fx5mLdSivTQCK4BGAYYCw/s1600/Ethernet%2BAdapter%2Bipconfig.PNG" /></a><br />
<br /></div>
<div>
</div>
Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-76812032347103218352019-05-26T17:01:00.001-05:002019-05-26T17:01:31.301-05:00Express Edition 18c ORA-12505 listener does not currently know of SID given in connect descriptor<div>
<b><u><span style="font-size: large;">Brief Solution:</span></u></b></div>
<div>
<br /></div>
Oracle Database Express Edition (XE) 18c was easy to setup. However, I was surprised when I tried to login to the database today and received the ORA-12505 error message. The problem was a different IP Address assigned to my computer. These IP addresses are located in 2 configuration files for the TNS Listener.<br />
<div>
<ul>
<li>ORACLE_HOME/network/admin/listener.ora</li>
<li>ORACLE_HOME/network/admin/tnsnames.ora</li>
</ul>
<div>
I was able to connect after correcting the IP address for my computer in both files.</div>
</div>
<div>
<br />
<br /></div>
<div>
<b><u><span style="font-size: large;">More Details:</span></u></b></div>
<div>
<br /></div>
<div>
First, the TNS Listener typically uses the listener.ora file for startup configuration. The default name for the TNS Listener is LISTENER. The LISTENER section of the listener.ora file includes the HOST. This tells the LISTENER what interface/network to open for reading.<br />
<br />
The XE 18c installation used an IP address for this HOST setting. In my case, DHCP assigned a different IP address to my computer when I connected. The old IP address would not allow the TNS Listener to run. After correcting the IP address in the listener.ora file, the TNS Listener ran, but the database would not register with it.</div>
<div>
<br /></div>
<div>
The database parameter LOCAL_LISTENER tells the database where to find the TNS Listener. The XE 18c installation sets this value to LISTENER_XE. When the database attempts to register, it uses the tnsnames.ora file to find the LISTENER_XE alias. Similar to the listener.ora file, the tnsnames.ora file has a LISTENER_XE section that includes a HOST value. After correcting the IP address in the tnsnames.ora file, the database successfully registered with the TNS Listener.</div>
<div>
<br />
<br /></div>
<div>
<b><u><span style="font-size: large;">Oracle Documentation:</span></u></b></div>
<div>
<br /></div>
<div>
<br />
<b>About Service Registration</b> (Net Services Administrator's Guide)</div>
<div>
<br />
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/understanding-oracle-net-architecture.html#GUID-A47F7687-1F80-48B1-8E76-7FF9D410560A">https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/understanding-oracle-net-architecture.html#GUID-A47F7687-1F80-48B1-8E76-7FF9D410560A</a><br />
<br /></div>
<div>
<span style="background-color: white; color: #333333; font-family: "helvetica neue" , "segoe ui" , "roboto" , , sans-serif; font-size: 14px;">If the listener is not running when an instance starts, then the LREG process cannot register the service information. LREG attempts to connect to the listener periodically, but it may take up to 60 seconds before LREG registers with the listener after it has been started. To initiate service registration immediately after the listener is started, use the SQL statement </span><code class="codeph" style="background-attachment: inherit; background-color: white; background-image: inherit; background-position: inherit; background-repeat: inherit; background-size: inherit; border-radius: 4px; box-sizing: border-box; color: #333333; font-family: "courier new", courier, monospace; font-size: 15.4px; padding: 0px;">ALTER SYSTEM REGISTER</code><span style="background-color: white; color: #333333; font-family: "helvetica neue" , "segoe ui" , "roboto" , , sans-serif; font-size: 14px;">.</span></div>
<div>
<br /></div>
<div>
<br />
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: "Times New Roman"; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
</div>
<br />
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: "Times New Roman"; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; letter-spacing: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
<div style="font-weight: 400; margin: 0px;">
<br /></div>
<div style="margin: 0px;">
<b>Overview of Oracle Net Listener</b> (Net Services Administrator's Guide)</div>
</div>
<br />
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-A3263EB4-C3F2-4529-ABC2-ADE749114D33">https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-A3263EB4-C3F2-4529-ABC2-ADE749114D33</a><br />
<br />
<span style="background-color: white; color: #333333; font-family: "Helvetica Neue", "Segoe UI", Roboto, sans-serif-regular, sans-serif; font-size: 14px;">Because the configuration parameters have default values, it is possible to start and use a listener with no configuration. This default listener has a name of </span><code class="codeph" style="background-attachment: inherit; background-clip: inherit; background-color: white; background-image: inherit; background-origin: inherit; background-position: inherit; background-repeat: inherit; background-size: inherit; border-radius: 4px; box-sizing: border-box; color: #333333; font-family: "Courier New", Courier, monospace; font-size: 15.4px; padding: 0px;">LISTENER...</code></div>
<div>
<br />
<br />
<div>
<b>Registering Information with a Local Listener</b> (Net Services Administrator's Guide)</div>
<div>
<br /></div>
<div>
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-0E7C39E3-4627-403A-AE69-E9AA2C7E4C57">https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-0E7C39E3-4627-403A-AE69-E9AA2C7E4C57</a></div>
</div>
<div>
<br /></div>
<div>
<span style="background-color: white; color: #333333; font-family: "Helvetica Neue", "Segoe UI", Roboto, sans-serif-regular, sans-serif; font-size: 14px;">Synchronization occurs when the protocol address of the listener is specified in the </span><code class="codeph" style="background-attachment: inherit; background-clip: inherit; background-color: white; background-image: inherit; background-origin: inherit; background-position: inherit; background-repeat: inherit; background-size: inherit; border-radius: 4px; box-sizing: border-box; color: #333333; font-family: "Courier New", Courier, monospace; font-size: 15.4px; padding: 0px;">listener.ora</code><span style="background-color: white; color: #333333; font-family: "Helvetica Neue", "Segoe UI", Roboto, sans-serif-regular, sans-serif; font-size: 14px;"> file and the location of the listener is specified in the initialization parameter file.</span><br />
<span style="background-color: white; color: #333333; font-family: "Helvetica Neue", "Segoe UI", Roboto, sans-serif-regular, sans-serif; font-size: 14px;">To have the LREG process register with a local listener ..., configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.</span><br />
<br />
<br />
<b>Protocol Parameters</b> (Net Services Reference)<br />
<br />
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/18/netrf/protocol-address-configuration.html#GUID-71A0702F-A5C6-4122-907D-5E9BFA1DCE45">https://docs.oracle.com/en/database/oracle/oracle-database/18/netrf/protocol-address-configuration.html#GUID-71A0702F-A5C6-4122-907D-5E9BFA1DCE45</a><br />
<br />
<span style="background-color: white; color: #333333; font-family: "Helvetica Neue", "Segoe UI", Roboto, sans-serif-regular, sans-serif; font-size: 14px;">The listener and Oracle Connection Manager are identified by protocol addresses.</span><br />
<span style="background-color: white; color: #333333; font-family: "Helvetica Neue", "Segoe UI", Roboto, sans-serif-regular, sans-serif; font-size: 14px;">The following table lists the parameters used by the Oracle protocol support:</span><br />
<br />
<br /></div>
Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-21450021278943847622015-06-17T21:19:00.000-05:002015-12-14T02:02:55.615-06:007 Ways to Log Data in the Oracle DatabaseUpdated 14-Dec-2015: Corrected image for 6. Pipelined Function<br />
<br />
<h2>
Overview</h2>
<ol><span style="color: green;">
<li>Insert into Table</li>
<li>AUTONOMOUS_TRANSACTION</li>
<li>DBMS_APPLICATION_INFO</li>
<li>SET_SESSION_LONGOPS</li>
<li>DBMS_OUTPUT</li>
<li>Pipelined Function</li>
<li>DBMS_PIPE</li>
</span></ol>
Each section contains:
<br />
<ul>
<li>Executable code</li>
<li>An example output of the code</li>
<li>"The Good"</li>
<li>"The Bad"</li>
</ul>
All code has been tested in SQL*Plus on Oracle11gXE.
The following setup must be run as "SYS as SYSDBA". This setup must be complete before running code in any section.<br />
<br />
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create user demo identified by demo
default tablespace users;
grant connect, resource to demo;
grant select on SYS.V_$SESSION to demo;
grant execute on DBMS_LOCK to demo;
grant execute on DBMS_PIPE to demo;
grant create job to demo;
connect demo/demo
create table demo_tab
(dtm timestamp(6)
,usr varchar2(30)
,sid number
,txt varchar2(4000)
,loc varchar2(4000)
);
create trigger demo_tab_bi before insert
on demo_tab for each row
begin
if :new.dtm is null then
:new.dtm := systimestamp;
end if;
if :new.usr is null then
:new.usr := USER;
end if;
if :new.sid is null then
:new.sid := sys_context('USERENV','SID');
end if;
if :new.loc is null then
:new.loc := DBMS_UTILITY.FORMAT_CALL_STACK;
end if;
end demo_tab_bi;
/
create procedure log_demo (in_text in varchar2)
is PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into demo_tab (txt) values (in_text);
commit;
end log_demo;
/
create or replace type pipeline_demo_tab
as table of varchar2(40);
/
create or replace function pipeline_demo
return pipeline_demo_tab pipelined as
begin
pipe row('Logging Demo');
for i in 1 .. 5 loop
dbms_lock.sleep(1); -- delay between iterations
pipe row(' Test: ' || i);
end loop;
end;
/
create package pipe_demo as
PIPE_NAME CONSTANT varchar2(15) := 'Logging Demo';
status number;
procedure log(in_txt in demo_tab.txt%TYPE);
procedure run_server;
procedure stop_server;
end pipe_demo;
/
create package body pipe_demo as
procedure log(in_txt in demo_tab.txt%TYPE) is begin
dbms_pipe.reset_buffer;
dbms_pipe.pack_message('LOG');
dbms_pipe.pack_message(to_char(systimestamp,'YYYYMMDDHH24MISS.FF6'));
dbms_pipe.pack_message(USER);
dbms_pipe.pack_message(to_number(sys_context('USERENV', 'SID')));
dbms_pipe.pack_message(DBMS_UTILITY.FORMAT_CALL_STACK);
dbms_pipe.pack_message(in_txt);
status := dbms_pipe.send_message(PIPE_NAME);
end log;
procedure run_server is
cmd_buff varchar2(30);
dtm_buff varchar2(25);
usr_buff demo_tab.usr%TYPE;
sid_buff demo_tab.sid%TYPE;
loc_buff demo_tab.loc%TYPE;
txt_buff demo_tab.txt%TYPE;
begin
insert into demo_tab (txt) values ('Server started');
commit;
loop
loop -- Allow the PIPE Read timeout to prevent "freezing"
status := dbms_pipe.receive_message (PIPE_NAME, 10); -- seconds
exit when status != 1; -- Status 1 is a timeout error
end loop;
if status != 0 then
insert into demo_tab (txt) values ('Received status ' || status);
commit; exit;
end if;
dbms_pipe.unpack_message(cmd_buff);
if upper(cmd_buff) = 'LOG' then
dbms_pipe.unpack_message(dtm_buff);
dbms_pipe.unpack_message(usr_buff);
dbms_pipe.unpack_message(sid_buff);
dbms_pipe.unpack_message(loc_buff);
dbms_pipe.unpack_message(txt_buff);
insert into demo_tab (dtm, usr, sid, loc, txt)
values (to_timestamp(dtm_buff,'YYYYMMDDHH24MISS.FF6'),
usr_buff, sid_buff, loc_buff, txt_buff);
commit;
else
insert into demo_tab (txt) values ('Received ' || cmd_buff);
commit; exit;
end if;
end loop;
end run_server;
procedure stop_server is begin
dbms_pipe.reset_buffer;
dbms_pipe.pack_message('STOP');
status := dbms_pipe.send_message(PIPE_NAME);
end stop_server;
end pipe_demo;
/</span></pre>
<h2>
<span style="color: green;">
1. Insert into Table</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap
insert into demo_tab (txt) values ('I want to log this text');
select dtm, usr, sid, txt from demo_tab order by dtm desc;
rollback;
select dtm, usr, sid, txt from demo_tab order by dtm desc;</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-VU8JAKvOmEM/VYD7Qz1IGQI/AAAAAAAARkY/LHT0l4BwNqI/s1600/log_demo1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-VU8JAKvOmEM/VYD7Qz1IGQI/AAAAAAAARkY/LHT0l4BwNqI/s1600/log_demo1.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Simple.</li>
<li>Reasonably fast.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Not necessarily persistent - a ROLLBACK can remove uncommitted records from the DEMO_TAB table.</li>
<li>Unavailable to "other" sessions until COMMIT.</li>
</ul>
<h2>
<span style="color: green;">
2. Autonomous_Transaction</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap
BEGIN
log_demo('No rollback on this record');
rollback;
END;
/
select dtm, usr, sid, txt from demo_tab order by dtm desc;</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-AWWSyX_t3co/VYD7-haXSkI/AAAAAAAARkk/ATtTY1kSdkU/s1600/log_demo2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-AWWSyX_t3co/VYD7-haXSkI/AAAAAAAARkk/ATtTY1kSdkU/s1600/log_demo2.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Persistent - a ROLLBACK will not remove records from the DEMO_TAB table.</li>
<li>Immediately available to "other" sessions.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Requires a procedure or a package.</li>
<li>Performance penalty - Redolog Buffer Flush during COMMIT.</li>
</ul>
<h2>
<span style="color: green;">
3. DBMS_APPLICATION_INFO</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
column sid format 99999
column client_info format a20
column module format a20
column action format a30
BEGIN
DBMS_APPLICATION_INFO.set_client_info
(client_info => 'Logging Demo');
DBMS_APPLICATION_INFO.set_module
(module_name => 'Demo3'
,action_name => 'Temporary Entry');
END;
/
select sid, client_info, module, action from V$SESSION
where client_info = 'Logging Demo';</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-ygB3fKzBgwE/VYD8RfLowyI/AAAAAAAARks/9s1zvdUkP3I/s1600/log_demo3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-ygB3fKzBgwE/VYD8RfLowyI/AAAAAAAARks/9s1zvdUkP3I/s1600/log_demo3.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Simple.</li>
<li>Very fast.</li>
<li>Immediately available to "other" sessions.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Security - Need access to V$SESSION</li>
<li>Not persistent - V$SESSION data is automatically removed from memory.</li>
<li>Not really a log - Only 1 V$SESSION entry per session.</li>
</ul>
<h2>
<span style="color: green;">
4. SET_SESSION_LONGOPS</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
column sid format 99999
column opname format a30
column sofar format 9999
column units format a5
DECLARE
ri binary_integer := dbms_application_info.set_session_longops_nohint;
sn binary_integer; -- slno
BEGIN
DBMS_APPLICATION_INFO.set_session_longops
(rindex => ri, slno => sn, units => 'un',sofar => 0,
op_name => 'Logging Demo', totalwork => 1);
DBMS_APPLICATION_INFO.set_session_longops
(rindex => ri, slno => sn , sofar => 1,
totalwork => 1);
END;
/
select sid, opname, sofar, totalwork, units from v$session_longops
where opname = 'Logging Demo' order by start_time desc;</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-4CRf3k73sDE/VYF8_P7wwRI/AAAAAAAARmA/L2vqvlZOvCQ/s1600/log_demo4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-4CRf3k73sDE/VYF8_P7wwRI/AAAAAAAARmA/L2vqvlZOvCQ/s1600/log_demo4.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Very Fast.</li>
<li>Automatically calculates TIME_REMAINING.</li>
<li>Immediately available to "other" sessions.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Security - Need access to V$SESSION_LONGOPS.</li>
<li>Can require some thought to properly setup.</li>
<li>Not persistent - V$SESSION_LONGOPS data is automatically removed from memory.</li>
</ul>
<h2>
<span style="color: green;">
5. DBMS_OUTPUT</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
set serveroutput on
BEGIN
DBMS_OUTPUT.put_line('Logging Demo');
for i in 1 .. 5 loop
dbms_lock.sleep(1); -- delay between iterations
DBMS_OUTPUT.put_line(' Test: ' || i);
end loop;
END;
/</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-AIu_WJQMets/VYD9Jy_RSwI/AAAAAAAARlE/z5afTPT_w0s/s1600/log_demo5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-AIu_WJQMets/VYD9Jy_RSwI/AAAAAAAARlE/z5afTPT_w0s/s1600/log_demo5.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Simple.</li>
<li>Very Fast.</li>
<li>Can read DBMS_OUTPUT buffer within session PL/SQL.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Not persistent - Stored in DBMS_OUTPUT buffers.</li>
<li>Client must wait for call to complete before reading buffer.</li>
<li>Never available to "other" sessions.</li>
</ul>
<h2>
<span style="color: green;">
6. Pipelined Function</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
set arraysize 1
select * from table(pipeline_demo);</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-_vISIF4vNlo/Vm519LuzZqI/AAAAAAAAY2I/cb8LYgUy6b0/s1600/log_demo6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-_vISIF4vNlo/Vm519LuzZqI/AAAAAAAAY2I/cb8LYgUy6b0/s1600/log_demo6.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Fast.</li>
<li>Results returned while PL/SQL is executing.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Not persistent - Results returned via SQL SELECT.</li>
<li>Must hide DML behind autonomous_transaction</li>
<li>Strange way to run PL/SQL, using an SQL SELECT.</li>
<li>Never available to "other" sessions.</li>
</ul>
<h2>
<span style="color: green;">
7. DBMS_PIPE</span></h2>
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap
BEGIN
DBMS_SCHEDULER.create_job
(job_name => 'pipe_demo_server'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN pipe_demo.run_server; END;'
,enabled => TRUE);END;
/
-- PAUSE HERE to allow the job to start
select job_name, session_id from user_scheduler_running_jobs;
BEGIN
log_demo('Sending message on pipe.');
pipe_demo.log('This is the pipe message');
log_demo('Sent message on pipe.');
pipe_demo.stop_server;
END;
/
-- PAUSE HERE to allow the job to complete
select job_name, session_id from user_scheduler_running_jobs;
select dtm, usr, sid, txt from demo_tab order by dtm desc;</span></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-ekCtqnlT2v0/VYF-bYjEDLI/AAAAAAAARmM/ttTWdxxNSc0/s1600/log_demo7.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-ekCtqnlT2v0/VYF-bYjEDLI/AAAAAAAARmM/ttTWdxxNSc0/s1600/log_demo7.PNG" /></a></div>
<br />
<h3>
<span style="color: blue;">The Good:</span></h3>
<ul>
<li>Very Fast.</li>
<li>If configured, can be available to multiple "other" sessions.</li>
</ul>
<h3>
<span style="color: red;">The Bad:</span></h3>
<ul>
<li>Complicated to Setup.</li>
<li>Not persistent while in the pipe.</li>
<li>Reading a pipe can hang, causing the database to "freeze'.</li>
</ul>
<h2>
Cleanup</h2>
Cleanup code must be run as "SYS" or "SYSTEM".<br />
<br />
<pre><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">BEGIN
demo.pipe_demo.stop_server;
END;
/
drop user demo cascade;</span></pre>
<br />
<br />Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-3300994822785527922015-06-03T21:48:00.000-05:002016-12-29T14:27:23.325-06:00PL/SQL Call Graphs from DBA_IDENTIFIERS DataNote: This article was formerly called "PL/SQL Call Trees from DBA_IDENTIFIERS Data". For the purpose of this article, Call Tree, Call Graph, and Call Hierarchy are all the same. (<a href="http://en.wikipedia.org/wiki/Call_graph">http://en.wikipedia.org/wiki/Call_graph</a>)<br />
<br />
<a href="http://stevenfeuersteinonplsql.blogspot.com/">Steven Feuerstein</a> has an excellent article on "<a href="http://www.oracle.com/au/products/database/o50plsql-165471.html">Use PL/Scope to analyze your PL/SQL</a>". The PL/Scope functionality became available in Oracle11g. Its basic function is to populate the DBA_IDENTIFIERS table with all PL/SQL identifiers found while compiling database objects. Steven's post goes on to show an "Identifier Hierarchy" of variables, parameters, and other components of a particular package body.<br />
<br />
Many years after Steven's post, I began supporting some rather complex PL/SQL in Oracle11g. I found a great tool called <a href="http://www.conquestsoftwaresolutions.com/page/cleardb_at_a_glance">ClearDB Documenter 4</a> that would (among other things) display a "PL/SQL Call Tree". This was extremely handy for understanding the relationships between the many packages on the system. However, I wanted a way to get the PL/SQL Call Tree in an SQL report that I could further manipulate. I was able to coax the needed information from DBA_IDENTIFIERS, but it wasn't easy.<br />
<br />
At the heart of the problem is a lack of documentation on the contents of DBA_IDENTIFIERS. After some reverse-engineering, I developed these guidelines for the contents of DBA_IDENTIFIERS.<br />
<ol>
<li>OWNER, OBJECT_NAME, and OBJECT_TYPE are unique database objects.</li>
<li>USAGE_ID is a unique location and usage within a unique database object.</li>
<li>USAGE_CONTEXT_ID points to a USAGE_ID within a unique database object.</li>
<li>USAGE_CONTEXT_ID = 0 for top level database object "DECLARATION".</li>
<li>USAGE_ID with "CALL" identifies the calling program location.</li>
<li>SIGNATURE with "CALL" identifies the called program name and type.</li>
<li>SIGNATURE with "DECLARATION" is globally unique.</li>
<li>SIGNATURE with "DEFINITION" can be redefined and may not exist for all objects.</li>
<li>SIGNATURE is the same for package specification and body.</li>
<li>DBA_IDENTIFIERS is indexed on OBJ# and SIGNATURE.</li>
<li>OBJECT_NAME matches NAME and OBJECT_TYPE matches TYPE for functions and procedures that are not in packages.</li>
<li>"DECLARATION" and "DEFINITION" for synonyms and triggers match OBJECT_NAME with NAME and OBJECT_TYPE with TYPE.</li>
<li>"DECLARATION" for packages match OBJECT_NAME with NAME and OBJECT_TYPE with TYPE</li>
<li>"DEFINITION" for packages match OBJECT_NAME with NAME and OBJECT_TYPE = "PACKAGE BODY" and TYPE = "PACKAGE"</li>
</ol>
With these guidelines, I was able to construct some queries for the PL/SQL call tree. However, the queries took too long to run. So, I developed these actions to enable faster access to the data in DBA_IDENTIFIERS...<br />
<br />
<br />
<b>UPDATE (28-Mar-2016)</b>: Added this section to recompile all source.<br />
<h3>
Recompile All Source</h3>
(Run this as system)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> for buff in (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select username from dba_users</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> -- where nvl(oracle_maintained,'N' = 'N') -- for 12c</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> and ( owner not in ('ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'DIP',</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> 'FLOWS_FILES', 'HR', 'MDSYS', 'ORACLE_OCM',</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> 'ORDS_METADATA', 'ORDS_PUBLIC_USER', 'OUTLN', </span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> 'SCOTT', 'SYS', 'SYSTEM', 'XDB', 'XS$NULL')</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> or owner not like 'APEX\_' escape '\'); -- for 11gXE</span><br />
<span style="font-family: "courier new", courier, monospace; font-size: xx-small;"> ) loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> DBMS_UTILITY.compile_schema (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> schema => buff.username,</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> compile_all => TRUE,</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> reuse_settings => FALSE);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> for trig in (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select object_name from dba_objects</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where object_type = 'TRIGGER'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and owner = buff.username )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> execute immediate 'ALTER TRIGGER ' || buff.username ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '.' || trig.object_name || ' COMPILE';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> exception when others then null;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">end;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">/</span><br />
<br />
<br />
<br />
<b>UPDATE (28-Mar-2016)</b>: Added this function to compute un-named PL/SQL block start line in trigger source.<br />
<h3>
Create Trigger Offset Function</h3>
<b>UPDATE (29-Dec-2016)</b>: Added this grant for the function below.<br />
<div>
(Run this as sys): grant select on dba_source to system with grant option;</div>
<div>
<br /></div>
(Run this as system)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create FUNCTION temp_trigger_offset</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (dout_name_in in varchar2</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,dout_type_in in varchar2</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,dout_owner_in in varchar2)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> return number</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> --AUTHID DEFINER</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> AUTHID CURRENT_USER</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">IS</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">BEGIN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> if nvl(dout_type_in,'BOGUS') <> 'TRIGGER' then</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> --dbms_output.put_line('TEMP_TRIGGER_OFFSET: Not a TRIGGER: ' || dout_type_in);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> return 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end if;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> for buff in (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select line, text from dba_source</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where name = dout_name_in</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and type = dout_type_in</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and owner = dout_owner_in</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> order by line )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> if regexp_instr(buff.text,</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '(^declare$' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '|^declare[[:space:]]' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '|[[:space:]]declare$' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '|[[:space:]]declare[[:space:]])', 1, 1, 0, 'i') <> 0</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> OR</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> regexp_instr(buff.text,</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '(^begin$' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '|^begin[[:space:]]' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '|[[:space:]]begin$' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '|[[:space:]]begin[[:space:]])', 1, 1, 0, 'i') <> 0 </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> then</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> --dbms_output.put_line('TEMP_TRIGGER_OFFSET: ' || buff.line - 1');</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> return buff.line - 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end if;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> --dbms_output.put_line('TEMP_TRIGGER_OFFSET: Did not find DECLARE or BEGIN');</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> return 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">END temp_trigger_offset;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">/</span><br />
<br />
<br />
<b>UPDATE (13-Jan-2016)</b>: In order to capture all cross-schema references, run these statements as SYSTEM or another DBA user. Tables are created with the prefix "TEMP_" in the name. There is a CLEANUP at the bottom.<br />
<br />
<b>UPDATE (28-Mar-2016)</b>: Added SYSAUX tablespaces, TEMP_DBA_SOURCE table, and table/column comments.<br />
<h3>
Copy Some Tables</h3>
Make a copy of DBA_IDENTIFIERS and index it for speed. (Run this as system)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
create table temp_dba_identifiers</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> as<br />
select * from dba_identifiers;<br />
create unique index temp_dba_identifiers$uk1<br />
on temp_dba_identifiers (owner, object_name, object_type, usage_id)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
create index temp_dba_identifiers$ix1<br />
on temp_dba_identifiers (signature)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on table temp_dba_identifiers is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Static, indexed version of information about the identifiers in all stored objects in the database';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.OWNER is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Required owner of the identifier';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.NAME is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Name of the identifier';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.SIGNATURE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Signature of the identifier';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.TYPE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Type of the identifier';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.OBJECT_NAME is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Required name of the object where the identifier action occurred';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.OBJECT_TYPE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Type of the object where the identifier action occurred';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.USAGE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Type of the identifier usage: DECLARATION, DEFINITION, CALL, REFERENCE, ASSIGNMENT';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.USAGE_ID is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Unique key for the identifier usage within the object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.LINE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Line number of the identifier action';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.COL is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Column number of the identifier action';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_identifiers.USAGE_CONTEXT_ID is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Context USAGE_ID of the identifier usage';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">--comment on column temp_dba_identifiers.ORIGIN_CON_ID is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">-- 'The ID of the container where the data originates. Possible values include: 0: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)';</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> </span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> -- Oracle 12c</span><br />
<br />
Make a copy of DBA_SOURCE and index it for speed.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
create table temp_dba_source</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> as<br />
select * from dba_source</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
create index temp_dba_source$ix1<br />
on temp_dba_source (owner, name, type, line)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on table temp_dba_source is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Static, indexed version of text source descriptions of all stored objects in the database';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_source.OWNER is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Required owner of the object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_source.NAME is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Required name of the object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_source.TYPE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_source.LINE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Required line number of this line of source';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_source.TEXT is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Text source of the stored object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">--comment on column temp_dba_source.ORIGIN_CON_ID</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">-- 'The ID of the container where the data originates. Possible values include: 0: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)'; -- Oracle 12c</span><br />
<br />
Make a copy of DBA_DEPENDENCIES and index it for speed.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create table temp_dba_dependencies</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> as</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select * from dba_dependencies</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where referenced_type in ('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW')</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> -- and owner not in (select username from dba_users</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> -- where oracle_maintained = 'Y' ); -- for 12c</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> and ( owner not in ('ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'DIP',</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> 'FLOWS_FILES', 'HR', 'MDSYS', 'ORACLE_OCM',</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> 'ORDS_METADATA', 'ORDS_PUBLIC_USER', 'OUTLN', </span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> 'SCOTT', 'SYS', 'SYSTEM', 'XDB', 'XS$NULL')</span><br />
<span style="font-family: courier new, courier, monospace; font-size: xx-small;"> or owner not like 'APEX\_' escape '\'); -- for 11gXE</span><br />
<span style="font-family: "courier new", courier, monospace; font-size: xx-small;">create index temp_dba_dependencies$ix1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> on temp_dba_dependencies (owner, name, type)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_dba_dependencies$ix2</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> on temp_dba_dependencies (referenced_owner, referenced_name, referenced_type)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on table temp_dba_dependencies is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Static, indexed version of all dependencies between selected objects (TABLE,VIEW,SYNONYM,MATERIALIZED VIEW) in the database. This view does not display the SCHEMAID column';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.OWNER is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Required owner of the object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.NAME is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Name of the object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.TYPE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Type of the object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.REFERENCED_OWNER is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Owner of the referenced object (remote owner if remote object)';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.REFERENCED_NAME is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Name of the referenced object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.REFERENCED_TYPE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Type of the referenced object';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.REFERENCED_LINK_NAME is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Name of the link to the parent object (if remote)';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_dba_dependencies.DEPENDENCY_TYPE is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Indicates whether the dependency is a REF dependency (REF) or not (HARD)';</span><br />
<br />
<h3>
Main Call Tree Table</h3>
Create the main call tree table<br />
<ul>
<li>SRC - identifiers are all about this, the source code.</li>
<li>TGT - The target of the SRC usage, like a reference or a call</li>
<li>CTX - The current context of the source code, what SRC this SRC is under</li>
<li>PAR - The parent context is a function, procedure, or package this CTX is under</li>
</ul>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
create table temp_ident_assocs</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> as<br />
select tgt.owner tgt_object_owner<br />
,tgt.object_type tgt_object_type<br />
,tgt.object_name tgt_object_name<br />
,tgt.name tgt_name<br />
,tgt.type tgt_type<br />
,src.owner src_object_owner<br />
,src.object_type src_object_type<br />
,src.object_name src_object_name<br />
,src.usage par_usage -- BOGUS placed holder data<br />
,src.name par_name -- BOGUS placed holder data<br />
,src.type par_type -- BOGUS placed holder data<br />
,ctx.usage ctx_usage<br />
,ctx.name ctx_name<br />
,ctx.type ctx_type<br />
,src.usage src_usage<br />
,src.name src_name<br />
,src.type src_type<br />
,case when src.object_type = 'TRIGGER'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> then src.line + temp_trigger_offset</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (src.object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src.object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src.owner )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> else src.line end src_line<br />
,src.col src_col<br />
,dbas.text src_text<br />
,tgt.signature tgt_signature<br />
,ctx.signature par_signature<br />
,ctx.signature ctx_signature<br />
,src.signature src_signature<br />
,src.usage_id src_usage_id<br />
from temp_dba_identifiers src<br />
left join temp_dba_identifiers tgt on (tgt.signature = src.signature)<br />
and tgt.usage = 'DECLARATION'<br />
left join temp_dba_identifiers ctx on (ctx.owner = src.owner<br />
and ctx.object_name = src.object_name<br />
and ctx.object_type = src.object_type<br />
and ctx.usage_id = src.usage_context_id)<br />
left join temp_dba_source dbas on (dbas.owner = src.owner<br />
and dbas.type = src.object_type<br />
and dbas.name = src.object_name<br />
and dbas.line = src.line);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix1<br /> on temp_ident_assocs (tgt_object_name, tgt_object_type, tgt_object_owner)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix2<br /> on temp_ident_assocs (src_object_owner, src_object_name, src_object_type, src_line)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix3<br /> on temp_ident_assocs (src_object_name, src_object_type, src_object_owner, src_usage_id)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix4<br /> on temp_ident_assocs (tgt_signature, src_usage)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix5<br /> on temp_ident_assocs (src_signature)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix6<br /> on temp_ident_assocs (ctx_signature)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">;</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_ident_assocs.tgt_object_owner is<br />
'Target database object owner that is being called or referenced';<br />
comment on column temp_ident_assocs.tgt_object_type is<br />
'Target database object type that is being called or referenced';<br />
comment on column temp_ident_assocs.tgt_object_name is<br />
'Target database object name that is being called or referenced';<br />
comment on column temp_ident_assocs.tgt_name is<br />
'Target element name (procedure, function, etc.) being called or referenced';<br />
comment on column temp_ident_assocs.tgt_type is<br />
'Target element type (procedure, function, etc.) being called or referenced';<br />
comment on column temp_ident_assocs.src_object_owner is<br />
'Database object owner that is the source of the call or reference';<br />
comment on column temp_ident_assocs.src_object_type is<br />
'Database object type that is the source of the call or reference';<br />
comment on column temp_ident_assocs.src_object_name is<br />
'Database object name that is the source of the call or reference';<br />
comment on column temp_ident_assocs.par_usage is<br />
'Parent context usage (call, reference, etc) of the source of the call or reference';<br />
comment on column temp_ident_assocs.par_name is<br />
'Parent context name of the source of the call or reference';<br />
comment on column temp_ident_assocs.par_type is<br />
'Parent context type of the source of the call or reference';<br />
comment on column temp_ident_assocs.ctx_usage is<br />
'Context usage (call, reference, etc) of the source of the call or reference';<br />
comment on column temp_ident_assocs.ctx_name is<br />
'Context name of the source of the call or reference';<br />
comment on column temp_ident_assocs.ctx_type is<br />
'Context type of source of the call or reference';<br />
comment on column temp_ident_assocs.src_usage is<br />
'Usage (call, reference, etc) of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_name is<br />
'Name of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_type is<br />
'Type of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_line is<br />
'Database object line number of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_col is<br />
'Database object column number of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_text is<br />
'Text of the source of the call or reference';<br />
comment on column temp_ident_assocs.tgt_signature is<br />
'Target signature that is being called or referenced';<br />
comment on column temp_ident_assocs.par_signature is<br />
'Parent context signature of the source of the call or reference';<br />
comment on column temp_ident_assocs.ctx_signature is<br />
'Context signature of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_signature is<br />
'Signature of the source of the call or reference';<br />
comment on column temp_ident_assocs.src_usage_id is<br />
'Unique location and usage within a database object';</span><br />
<br />
Populate as much parent context as possible from the same source line (fast)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
update temp_ident_assocs dia<br />
set (par_usage, par_name, par_type, par_signature) = (<br />
select ctx.ctx_usage, ctx.ctx_name, ctx.ctx_type, ctx.ctx_signature<br />
from temp_ident_assocs ctx<br />
where ctx.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')<br />
and ctx.ctx_usage in ('DECLARATION','DEFINITION')<br />
and ctx.src_object_owner = dia.src_object_owner<br />
and ctx.src_object_type = dia.src_object_type<br />
and ctx.src_object_name = dia.src_object_name<br />
and ctx.src_line = dia.src_line<br />
and ctx.src_usage_id = (<br />
select max(mc.src_usage_id) from temp_ident_assocs mc<br />
where mc.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')<br />
and mc.ctx_usage in ('DECLARATION','DEFINITION')<br />
and mc.src_object_owner = dia.src_object_owner<br />
and mc.src_object_type = dia.src_object_type<br />
and mc.src_object_name = dia.src_object_name<br />
and mc.src_line = dia.src_line<br />
and mc.src_usage_id <= dia.src_usage_id ) );<br />
</span><br />
<br />
Populate all other parent context from previous source lines (slow)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
update temp_ident_assocs dia<br />
set (par_usage, par_name, par_type, par_signature) = (<br />
select ctx.ctx_usage, ctx.ctx_name, ctx.ctx_type, ctx.ctx_signature<br />
from temp_ident_assocs ctx<br />
where ctx.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')<br />
and ctx.ctx_usage in ('DECLARATION','DEFINITION')<br />
and ctx.src_object_owner = dia.src_object_owner<br />
and ctx.src_object_type = dia.src_object_type<br />
and ctx.src_object_name = dia.src_object_name<br />
and ctx.src_usage_id = (<br />
select max(mc.src_usage_id) from temp_ident_assocs mc<br />
where mc.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')<br />
and mc.ctx_usage in ('DECLARATION','DEFINITION')<br />
and mc.src_object_owner = dia.src_object_owner<br />
and mc.src_object_type = dia.src_object_type<br />
and mc.src_object_name = dia.src_object_name<br />
and mc.src_usage_id <= dia.src_usage_id ) )<br />
where par_signature is null;<br />
</span><br />
<br />
Add the missing context for declarations and definitions<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
update temp_ident_assocs<br />
set ctx_usage = src_usage<br />
,ctx_name = src_name<br />
,ctx_type = src_type<br />
,ctx_signature = src_signature<br />
,par_usage = src_usage<br />
,par_name = src_name<br />
,par_type = src_type<br />
,par_signature = src_signature<br />
where src_usage in ('DECLARATION','DEFINITION')<br />
and ctx_signature is null<br />
and tgt_signature = src_signature;<br />
</span><br />
<br />
This appears to be a bug in PLSCOPE because some context_usage_id's don't exist. So, we will fix that.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
update temp_ident_assocs dia<br />
set ctx_usage = par_usage<br />
,ctx_name = par_name<br />
,ctx_type = par_type<br />
,ctx_signature = par_signature<br />
where ctx_signature is null<br />
and par_signature is not null;<br />
</span><br />
<br />
Create the last index on temp_ident_assocs<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_ident_assocs$ix7</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> on temp_ident_assocs (par_signature, src_usage);</span><br />
<br />
<h3>
Call Tree Query</h3>
Done! Now to run a PL/SQL Call Tree Query. Pick a function or procedure in a package that calls other functions and/or procedures. Run the following SQL with ":OWNER" as the package owner, ":PKG_NAME" as the package name, and ":PROC_NAME" as the function or procedure name. (Updated SQL 04-Jun-2015).<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
with par as (<br />
select * from temp_ident_assocs<br />
where src_object_owner = upper(:OWNER)<br />
and src_object_name = upper(:PKG_NAME)<br />
and src_name = upper(:PROC_NAME)<br />
and src_usage = 'DECLARATION'<br />
), nodes as (<br />
select tgt_signature<br />
,par_signature<br />
,src_object_owner calling_owner<br />
,src_object_name || '.' ||<br />
par_name calling_name<br />
,par_type calling_type<br />
,tgt_object_owner called_owner<br />
,tgt_object_name || '.' ||<br />
tgt_name called_name<br />
,tgt_type called_type<br />
,src_line<br />
,src_text<br />
from temp_ident_assocs<br />
where src_usage = 'CALL'<br />
and tgt_type != 'CURSOR'<br />
and tgt_object_owner != 'SYS'<br />
)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">--select * from par;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">--select * from nodes;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">select calling_owner<br />
,LPAD('-| ', (Level-1)*3, '-|-') || calling_name calling_name<br />
,src_line<br />
,called_type<br />
,called_name<br />
,called_owner<br />
from nodes<br />
START WITH par_signature in (select src_signature from par)<br />
CONNECT BY NOCYCLE par_signature = PRIOR tgt_signature<br />
order siblings by src_line;<br />
</span><br />
<br />
<h3>
Reverse Call Tree Query</h3>
We can also run a reverse call tree query. Pick a function or procedure in a package that is called by other functions and/or procedures. Again, set the ":OWNER" as the package owner, ":PKG_NAME" as the package name, and ":PROC_NAME" as the function or procedure name. (Updated SQL 04-Jun-2015)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
with tgt as (<br />
select * from temp_ident_assocs<br />
where src_object_owner = upper(:OWNER)<br />
and src_object_name = upper(:PKG_NAME)<br />
and src_name = upper(:PROC_NAME)<br />
and src_usage = 'DECLARATION'<br />
), nodes as (<br />
select tgt_signature<br />
,par_signature<br />
,src_object_owner calling_owner<br />
,src_object_name || '.' ||<br />
par_name calling_name<br />
,par_type calling_type<br />
,tgt_object_owner called_owner<br />
,tgt_object_name || '.' ||<br />
tgt_name called_name<br />
,tgt_type called_type<br />
,src_line<br />
,src_text<br />
from temp_ident_assocs<br />
where src_usage = 'CALL'<br />
and tgt_type != 'CURSOR'<br />
and tgt_object_owner != 'SYS'<br />
)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">--select * from tgt;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">--select * from nodes;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">select rownum<br />
,called_owner<br />
,substr(SYS_CONNECT_BY_PATH(called_name, ' <- '),4) || ' <-'<br />
called_path<br />
,calling_name<br />
,calling_type<br />
,calling_owner<br />
,src_line<br />
,src_text<br />
from nodes<br />
START WITH tgt_signature in (select src_signature from tgt)<br />
CONNECT BY NOCYCLE tgt_signature = PRIOR par_signature<br />
order siblings by src_line;<br />
</span><br />
<br />
<h3>
BONUS: Procedure/Function Lines in a Package</h3>
<b>UPDATE (28-Mar-2016)</b>: Modified and renamed this table to use line/column position instead of line.<br />
<br />
Ever wondered how to determine which DBA_SOURCE lines belong to a procedure/function in a package? This table does that...<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create table temp_proc_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> as</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">select src_object_owner object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_object_name object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_object_type object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,par_name proc_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,par_type proc_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_signature proc_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_line beg_line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_line end_line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_line beg_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src_line end_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> from temp_ident_assocs</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where 0 = 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">DECLARE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> cursor main is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select src_object_owner, src_object_name, src_object_type,</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> par_name, par_type, par_signature, src_line, src_col</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> from temp_ident_assocs</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where src_usage in ('DECLARATION','DEFINITION')</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and src_object_type not in ('PACKAGE','TYPE','SYNONYM')</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> order by src_object_owner, src_object_name, src_object_type,</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> src_line, src_col;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> type main_nt_type is table of main%ROWTYPE;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> prev main%ROWTYPE;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> curr main%ROWTYPE;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> cnt number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> recs number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> function get_last_oline return number is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> oline number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select max(line) into oline from temp_dba_source</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where owner = prev.src_object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and name = prev.src_object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and type = prev.src_object_type;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> return oline;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end get_last_oline;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> function get_last_ocol (in_last_oline in number) return number is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ocol number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select length(text) into ocol from temp_dba_source</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where owner = prev.src_object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and name = prev.src_object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and type = prev.src_object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and line = in_last_oline;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> return ocol;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end get_last_ocol;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> procedure ins_rec (in_end_line in number, in_end_col in number) is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_beg_pos number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_pos number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_line number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_col number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> IF in_end_col = 1 THEN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_line := in_end_line - 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_col := get_last_ocol(lv_end_line);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ELSE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_line := in_end_line;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_col := in_end_col - 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> END IF;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_beg_pos := prev.src_line + prev.src_col/1000000;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> lv_end_pos := lv_end_line + lv_end_col/1000000;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> if lv_beg_pos < lv_end_pos then</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> insert into temp_proc_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,beg_line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,end_line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,beg_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,end_pos)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> values</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (prev.src_object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,prev.src_object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,prev.src_object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,prev.par_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,prev.par_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,prev.par_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,prev.src_line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,lv_end_line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,lv_beg_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,lv_end_pos);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> recs := recs + 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end if;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end ins_rec;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">BEGIN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> recs := 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> cnt := 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> open main;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> fetch main into curr;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> prev := curr;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> WHILE main%FOUND LOOP</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> IF prev.src_object_owner != curr.src_object_owner OR</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> prev.src_object_name != curr.src_object_name OR</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> prev.src_object_type != curr.src_object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> THEN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ins_rec(get_last_oline, get_last_ocol(get_last_oline)+1);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> prev := curr;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ELSIF prev.par_signature != curr.par_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> THEN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ins_rec(curr.src_line, curr.src_col);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> prev := curr;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> END IF;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> cnt := cnt + 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> fetch main into curr;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> END LOOP;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> if prev.src_object_name is not null then</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ins_rec(get_last_oline, get_last_ocol(get_last_oline)+1);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end if;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> close main;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> dbms_output.put_line('cnt = '||cnt||', recs = '||recs);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">END;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">/</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_proc_pos$ix1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> on temp_proc_pos(object_owner, object_name, object_type, beg_pos)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on table temp_proc_pos is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Static, indexed version of procedure and function locations within source code in the database.';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.object_owner is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object owner';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.object_type is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object type';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.object_name is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object name';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.proc_name is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Procedure or function name';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.proc_type is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Procedure or function type';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.proc_signature is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Procedure or function signature from ALL_IDENTIFIERS';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.beg_line is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Beginning line number in the source';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.end_line is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Ending line number in the source';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.beg_pos is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Beginning position in the source. Position is line number + (column position/1000000)';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_proc_pos.end_pos is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Ending position in the source. Position is line number + (column position/1000000)';</span><br />
<br />
<h3>
DOUBLE BONUS: Tables used by functions and procedures</h3>
<b>UPDATE (31-Mar-2016)</b>: Modified data in this table to use line/column position instead of line.<br />
<br />
How about matching database tables with functions and procedures? The following script creates a table that does, almost. It uses a simple string match, so it may return extra source lines that don't reference a database table. The source text is included to determine if it is not a table.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">
create table temp_referenced_tabs</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> as</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">select proc.object_owner object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.object_name object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.object_type object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.proc_name proc_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.proc_type proc_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.proc_signature proc_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.object_owner referenced_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.object_name referenced_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc.object_type referenced_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src.line line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src.line col</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,src.text text</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> from temp_proc_pos proc cross join temp_dba_source src</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where 1 = 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">DECLARE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> fnd_col number;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">BEGIN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> for procs in (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select * from temp_proc_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> order by object_owner, object_name, object_type, beg_pos)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> for deps in (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select * from temp_dba_dependencies dep</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where dep.owner = procs.object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and dep.name = procs.object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and dep.type = procs.object_type )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> for srcs in (</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> select * from temp_dba_source src</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> where src.owner = procs.object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and src.name = procs.object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and src.type = procs.object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and src.text is not null</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and src.line between trunc(procs.beg_pos)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and trunc(procs.end_pos) )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> loop</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> fnd_col :=</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> regexp_instr</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (srcs.text</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,'[[:space:],]'||deps.referenced_name||'[[:space:],]' || '|' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '[[:space:],]'||deps.referenced_name||'$' || '|' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '^'||deps.referenced_name||'[[:space:],]' || '|' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> '^'||deps.referenced_name||'$'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,1,1,0,'i');</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> if fnd_col > 0 AND</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (srcs.line + fnd_col/1000000) between procs.beg_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> and procs.end_pos</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> then</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> insert into temp_referenced_tabs</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,proc_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,referenced_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,referenced_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,referenced_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,col</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,text)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> values</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> (procs.object_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,procs.object_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,procs.object_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,procs.proc_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,procs.proc_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,procs.proc_signature</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,deps.referenced_owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,deps.referenced_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,deps.referenced_type</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,srcs.line</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,fnd_col</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> ,srcs.text);</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end if;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> commit;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> end loop;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">END;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">/</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_referenced_tabs$ix1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> on temp_referenced_tabs (object_name, object_type, object_owner)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">create index temp_referenced_tabs$ix2</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> on temp_referenced_tabs (referenced_name, referenced_type, referenced_owner)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> tablespace SYSAUX;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on table temp_referenced_tabs is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Static, indexed version of procedure and function references to table-like objects in the database.';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.object_owner is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object owner';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.object_type is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object type';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.object_name is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object name';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.proc_name is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Procedure or function name';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.proc_type is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Procedure or function type';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.proc_signature is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Procedure or function signature from ALL_IDENTIFIERS';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.referenced_owner is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object owner of the object being directly referenced';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.referenced_type is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object type of the object being directly referenced';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.referenced_name is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Database source object name of the object being directly referenced';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.line is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Line number in the source';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.col is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Column number in the source';</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">comment on column temp_referenced_tabs.text is</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> 'Text of the source';</span><br />
<br />
<h3>
Cleanup</h3>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop table temp_referenced_tabs;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop table temp_proc_pos;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop table temp_ident_assocs;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop table temp_dba_dependencies;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop table temp_dba_source;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop table temp_dba_identifiers;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">drop function temp_trigger_offset;</span><br />
<br />Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com4tag:blogger.com,1999:blog-2026467871915021067.post-25045665615605338222015-05-11T19:58:00.000-05:002015-05-11T19:58:09.624-05:00How to Delete a SQL*Developer Unit Test Suite Using PL/SQLIn my previous post "<a href="http://reldesgen.blogspot.com/2015/04/ci-with-sqldeveloper-jenkins-and-gnu.html">Integrating Jenkins and SQL*Developer's Unit Test</a>", I included a PL/SQL script in the "<a href="https://drive.google.com/file/d/0B4Qp7jjwAFDURlEwazRJU1E1U0U/view?usp=sharing">GNU_make_example.zip</a>" file that will delete an SQL*Developer Unit Test Suite. The script file is called "delete_utr.sql". The Makefile in that example uses this PL/SQL script to remove the Unit Test Suite "FIT_blog1" from the Unit Test Repository in the build database. That delete precedes the loading of an updated "FIT_blog1.xml" Unit Test Suite export file into the build database.<br />
<br />
Following is the full text from the "delete_utr.sql" script file developed for SQL*Developer 4.0.3:<br />
<br />
<br />
-- GNU_make_example delete_utr.sql<br />
-- http://reldesgen.com 2015<br />
<br />
set serveroutput on size unlimited<br />
<br />
DECLARE<br />
sname varchar2(100) := '&1.';<br />
utsid ut_suite.ut_sid%TYPE;<br />
TYPE utids_type is table of ut_test.ut_id%TYPE;<br />
utids utids_type;<br />
num number;<br />
-- Not deleting from ut_metadata<br />
-- Not deleting from ut_lookup_categories<br />
-- Not deleting from ut_lookup_datatypes<br />
-- Not deleting from ut_lookup_values<br />
-- Not deleting from ut_lib_startups<br />
-- Not deleting from ut_lib_dyn_queries<br />
-- Not deleting from ut_lib_validations<br />
-- Not deleting from ut_lib_teardowns<br />
BEGIN<br />
DBMS_OUTPUT.PUT_LINE('Deleting Suite ' || sname);<br />
select ut_sid into utsid<br />
from ut_suite where name = sname;<br />
DBMS_OUTPUT.PUT_LINE('Found UT_SID ' || utsid);<br />
select ut_id bulk collect into utids<br />
from ut_suite_items<br />
where ut_sid = utsid<br />
and ut_id is not null<br />
group by ut_id;<br />
num := SQL%ROWCOUNT;<br />
DBMS_OUTPUT.PUT_LINE('Found ' || num || ' Test UT_IDs');<br />
FOR i in 1 .. utids.count LOOP<br />
delete from ut_test_coverage_stats<br />
where uti_id in (select uti_id from ut_test_impl<br />
where ut_id = utids(i));<br />
num := SQL%ROWCOUNT;<br />
delete from ut_test_arguments where ut_id = utids(i);<br />
-- delete cascade on ut_test_impl_arguments<br />
-- delete cascade on ut_test_impl_arg_results<br />
num := num + SQL%ROWCOUNT;<br />
delete from ut_test where ut_id = utids(i);<br />
-- delete cascade on ut_test_impl<br />
-- delete cascade on ut_test_results<br />
-- delete cascade on ut_test_impl_results<br />
-- delete cascade on ut_validations<br />
-- delete cascade on ut_test_impl_val_results<br />
num := num + SQL%ROWCOUNT;<br />
DBMS_OUTPUT.PUT_LINE('Deleted ' || num ||<br />
' rows for Test UT_ID ' || utids(i));<br />
END LOOP;<br />
delete from ut_teardowns where ut_sid = utsid;<br />
num := SQL%ROWCOUNT;<br />
delete from ut_startups where ut_sid = utsid;<br />
num := num + SQL%ROWCOUNT;<br />
delete from ut_suite where ut_sid = utsid;<br />
-- delete cascade on ut_suite_items<br />
-- delete cascage on ut_suite_results<br />
-- delete cascage on ut_suite_item_results<br />
num := num + SQL%ROWCOUNT;<br />
DBMS_OUTPUT.PUT_LINE('Deleted ' || num ||<br />
' rows for Suite UT_SID ' || utsid);<br />
END;<br />
/<br />
<br />
<br />
Cheers!Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-42463728389560070532015-05-03T22:42:00.004-05:002015-05-05T04:53:48.538-05:00Creating a Lubuntu Build Server on AmazonIn previous posts, I showed how to use Oracle's SQL*Developer as part of a CI (Continuous Integration) server. Since I use AWS (Amazon Web Services) to host my development servers, I had to find a way to run SQL*Developer on AWS. One conflict I discovered was that SQL*Developer requires a GUI (Graphical User Interface) and I couldn't find an AWS server that had a GUI.<br />
<br />
After reviewing and testing many alternatives, I settled on Real VNC running on Ubuntu with LXDE. Real VNC has a free Windows viewer and a free Android viewer. It worked the first time I installed it and has worked flawlessly since. Because I am using an Amazon VPC with a VPN sever, I did not use the encryption capability of the VNC server.<br />
<br />
LXDE is a lightweight X-Windows desktop environment. It has a small foot print and doesn't spawn many processes. This was important for ease of installation and efficient execution on the Ubuntu server. LXDE is also conveniently bundled into an Ubuntu version called Lubuntu.<br />
<br />
<br />
<b><u>Setup</u></b><br />
<br />
1) Create an AWS instance using an "Ubuntu Server 14.04" AMI. The settings for this will vary, depending on your deployment environment in AWS. I found that a "t2.small" can run my entire build server, including VNC, SQL*Developer, Jenkins, and Oracle database. After creating it, you should be able to "ssh" to the new instance.<br />
<br />
2) Add Ubuntu Java Repository and get the latest updates<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">
sudo add-apt-repository ppa:webupd8team/java<br />
<br />
sudo apt-get update && sudo apt-get dist-upgrade<br />
</span>
(Select "install the package maintainer's version")<br />
<br />
3) Install packages for LXDE (lubuntu-core), VNC (vnc4server), Firefox (firefox), GUI editor (leafpad), and packages needed to install OracleXE (allien, libaio1, unixodbc)<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">
sudo apt-get install lubuntu-core vnc4server firefox leafpad alien libaio1 unixodbc<br />
</span>
<br />
4) To setup VNC, I used this tutorial from "HowtoForge"<br />
<br />
<a href="https://www.howtoforge.com/how-to-install-vnc-server-on-ubuntu-14.04">https://www.howtoforge.com/how-to-install-vnc-server-on-ubuntu-14.04</a><br />
<br />
5) I was able to setup an Oracle XE database using this blog from Mike Heeren<br />
<br />
<a href="http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/" style="text-decoration: none;">http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/</a><br />
<br />
6) Using a similar approach as Mike Heeren, I setup SQL*Developer. I started by using Firefox to download SQL*Developer into the "Downloads" directory. This implies you are successfully running a VNC client connection to the server on Amazon.<br />
<br />
7) Convert and install SQL*Developer<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">
sudo alien --scripts -d sqldeveloper-4.0.3.16.84-1.noarch.rpm<br />
<br />
sudo dpkg --install sqldeveloper_4.0.3.16.84-2_all.deb<br />
</span>
<br />
8) Run SQL*Developer for the first time<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">
sudo mkdir /home/.sqldeveloper/<br />
<br />
xhost +<br />
<br />
sudo /opt/sqldeveloper/sqldeveloper.sh<br />
</span>
(Java Path is /usr/lib/jvm/java-7-oracle)<br />
<br />
9) Then, to run SQL*Developer without "sudo":<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">
sudo chown ubuntu .sqldeveloper<br />
<br />
xhost -<br />
<br />
sqldeveloper<br />
</span>
<br />
10) At this point, SQL*Developer should run from the "Programming" menu in LXDE. (Click the CRT icon in the lower left to activate the menu.)<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-IIuBzLNkAsM/VUbPe3H7KnI/AAAAAAAARh4/J2dXpdkKJ-I/s1600/Lubuntu_Programming_Menu.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="339" src="http://1.bp.blogspot.com/-IIuBzLNkAsM/VUbPe3H7KnI/AAAAAAAARh4/J2dXpdkKJ-I/s1600/Lubuntu_Programming_Menu.PNG" width="640" /></a></div>
<br />
<br />
<br />
11) Because of a bug in the distribution, the "sdcli" script needs to be updated before SQL*Developer will run in command line mode:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">
vi /opt/sqldeveloper/sqldeveloper/bin/sdcli<br />
</span> (Change . "sqldeveloper" to . "/opt/sqldeveloper/sqldeveloper/bin/sqldeveloper")<br />
<br />
12) To setup Jenkins, I used this guide from Kohsuke Kawaquchi himself:<br />
<br />
<a href="https://wiki.jenkins-ci.org/display/JENKINS/Installing+Jenkins+on+Ubuntu" style="text-decoration: none;">https://wiki.jenkins-ci.org/display/JENKINS/Installing+Jenkins+on+Ubuntu</a><br />
<br />
<br />
Congratulations. You should have a complete Lubuntu build server running on AWS.<br />
<br />
<br />
<b><u>Resources:</u></b><br />
<br />
An example Ubuntu AMI:<br />
<a href="https://aws.amazon.com/marketplace/pp/B00JV9TBA6">https://aws.amazon.com/marketplace/pp/B00JV9TBA6</a><br />
<br />
More information on LXDE:<br />
<a href="http://lxde.org/">http://lxde.org/</a><br />
<br />
An example Amazon VPC scenario:<br />
<a href="http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/VPC_Scenario2.html">http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/VPC_Scenario2.html</a><br />
<br />
An example VPN AMI:<br />
<a href="https://aws.amazon.com/marketplace/pp/B00MI40CAE/ref=mkt_wir_openvpn_byol">https://aws.amazon.com/marketplace/pp/B00MI40CAE/ref=mkt_wir_openvpn_byol</a><br />
<br />Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-70114503667343199972015-04-18T13:57:00.000-05:002015-04-18T13:57:17.498-05:00Simultaneous CI on Windows and LinuxIn my previous post "<a href="http://reldesgen.blogspot.com/2015/04/ci-with-sqldeveloper-jenkins-and-gnu.html">CI with SQL*Developer, Jenkins, and GNU make</a>", I demonstrated a working build server using these items:<br />
<ul>
<li><a href="http://www.thatjeffsmith.com/archive/2011/11/introducing-the-sql-developer-shopping-cart/">SQL*Developer Cart</a></li>
<li><a href="http://www.thatjeffsmith.com/archive/2014/04/unit-testing-your-plsql-with-oracle-sql-developer/">SQL*Developer Unit Test</a></li>
<li><a href="http://www.thatjeffsmith.com/archive/2014/04/oracle-sql-developers-best-kept-secret-reports/">SQL*Developer Report</a> (<a href="http://help.catchsoftware.com/display/ET/JUnit+Format">JUnit output</a>)</li>
<li><a href="http://jenkins-ci.org/">Jenkins</a></li>
<li><a href="http://www.gnu.org/software/make/manual/make.html">GNU "makefile"</a></li>
</ul>
For my development environment, I use these additional items:<br />
<ul>
<li><a href="http://subversion.apache.org/">Subversion</a> for Source Code Management (SCM)</li>
<li>Windows (with <a href="http://www.cygwin.com/">Cygwin</a>) for development</li>
<li>Linux (L<a href="http://aws.amazon.com/marketplace/seller-profile/ref=dtl_pcp_sold_by?ie=UTF8&id=565feec9-3d43-413e-9760-c651546613f2">ubuntu</a>) on Amazon for a build server.</li>
</ul>
Because I use both Windows and Ubuntu, my builds must function on both platforms. Here are a few tips/tricks I found.<br />
<br />
<u>Use SQL*Plus Scripts</u><br />
<br />
SQL*Plus works the same on Windows and Linux. Maximize use of SQL*Plus scripts.<br />
<br />
<u>GNU Tools and POSIX API for Windows</u><br />
<br />
For the scripting that can't be done with SQL*Plus, install GNU Tools and POSIX API on Windows. I am using Cygwin (<a href="http://www.cygwin.com/">http://www.cygwin.com</a>). It is not identical to Linux, but is close. In my previous post I had 2 shell scripts that isolated these differences.<br />
<br />
<u>run_sdcli.sh</u><br />
<br />
I use the "run_sdcli.sh" script to run the <a href="http://www.thatjeffsmith.com/archive/2013/07/sql-developer-4-0-and-the-command-line-interface-cli/">SQL*Developer Command Line Interface</a>. Here is a portion of it:<br />
<blockquote class="tr_bq">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">
# Go to Oracle SQL*Developer binaries<br />
#cd "/opt/sqldeveloper/sqldeveloper/bin" # Linux
<br />
cd "/cygdrive/c/users/duane/sqldeveloper/sqldeveloper/bin" # Cygwin
</span></blockquote>
The SQL*Developer Command Line Interface must be run from its installed directory. This portion of the script resolves the problem that SQL*Developer is installed in different directories on different servers.<br />
<br />
Here is another portion of the "run_sdcli.sh" script:<br />
<blockquote class="tr_bq" style="-webkit-text-stroke-width: 0px; color: black; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">
# Run the SQL*Developer Command Line Interface<br />
#./sdcli "${@}" # Linux<br />
cmd /C sdcli "${@}" # Cygwin<br />
</span></blockquote>
The SQL*Developer Command Line Interface requires a Windows command shell to properly run in Windows. Since Cygwin is running a "bash" shell, the Windows command shell must be called in Windows. This script is not checked into SCM because it must be configured for each server.<br />
<br />
<u>get_cdir.sh</u><br />
<br />
The above script (run_sdcli.sh) runs the SQL*Developer Command Line from its installation directory. It passes all command line parameters to sdcli (using "${0}"). However, there is a problem. Since the script executes from a different directory, any parameter with a file name needs to include a path back to the current directory. That where this script (get_cdir.sh) helps. This script returns the current directory as a full path. However, it also makes an adjustment for Cygwin.<br />
<blockquote class="tr_bq" style="-webkit-text-stroke-width: 0px; color: black; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">
if pwd -P | grep -q '^/[Cc][Yy][Gg][Dd][Rr][Ii][Vv][Ee]/'<br />
then<br />
pwd -P | sed -e 's#^/[Cc][Yy][Gg][Dd][Rr][Ii][Vv][Ee]/##1' \<br /> -e 's#/#:/#1'<br />
else<br />
pwd -P<br />
fi
</span></blockquote>
The adjustment made for Cygwin is based on the idea that full paths to disk files in Cygwin start with "/cygdrive/". This script checks for that string at the beginning of the full path. If it finds the strings, it substitutes the correct drive letter at the front of the path. Remember, this is in preparation for running "sdcli". "sdcli" must have Windows paths, not Linux paths. This script can be checked into SCM because it works for both Cygwin and Linux.<br />
<br />
Everything else listed in these posts should run on Windows and Linux without problem. Cheers!<br />
<br />Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-46797938190039365922015-04-11T22:55:00.001-05:002015-04-17T05:43:03.144-05:00CI with SQL*Developer, Jenkins, and GNU makeIn a previous post (<a href="http://reldesgen.blogspot.com/2015/03/integrating-jenkins-and-sqldevelopers.html">Integrating Jenkins and SQL*Developer's Unit Test</a>), I showed how JUnit ouptut can be created from SQL*Developer and captured by Jenkins. In this post, I will show how to use GNU "make" as the build engine with Jenkins and SQL*Developer.<br />
<br />
(UPDATE 17-Apr-2015: Fixed formatting errors)<br />
<br />
<u>GNU make</u><br />
<br />
GNU "make" is included with the GNU software that is part of Linux. I found GNU "make" an easy to use and reliable build engine. I originally wrote my build scripts in "bash". Switching to GNU "make" greatly simplified the build scripting and provided much more functionality than my "bash" scripts. More information about GNU "make" can be found at <a href="http://www.gnu.org/software/make/manual/make.html">http://www.gnu.org/software/make/manual/make.html</a><br />
<br />
What does "make" do? It reads the "Makefile" to determine what recipes must be used to update targets that may have dependencies. Specifically, the "Makefile" in the ZIP file does this:<br />
<ol>
<li><i>Create/Update all the needed database objects and data in a database.</i> In this example, the database object are created from an SQL*Developer Cart created for this purpose.</li>
<li><i>Create/Update all the needed tests.</i> In this example, the tests are created from an SQL*Developer Unit Test Suite export created for this purpose.</li>
<li><i>Execute the Unit Test Suite and create a JUnit output for Jenkins.</i> In this example, the JUnit output is created from an SQL*Developer Report created for this purpose.</li>
<li><i>Only execute the necessary pieces when something changes.</i> In this example, dependency/target relationships are handled with the built-in dependency capability of "make" using the "Makefile" created for this purpose.</li>
</ol>
<u>Supporting Scripts</u><br />
<br />
All of the files/scripts used for this example are in this <a href="https://drive.google.com/file/d/0B4Qp7jjwAFDURlEwazRJU1E1U0U/view?usp=sharing">GNU_make_example.zip</a> file. The list of files in the ZIP file is described in the README.txt that is also in the ZIP.<br />
<br />
<u>Some things to consider:</u><br />
<br />
In order to accurately update database objects, the supporting scripts will delete database objects so they can be re-created. In particular, the "drop_all.sql" script will drop all objects in a schema. Since this is run on a newly created schema, it should not be a problem to remove all objects.<br />
<br />
Also, the "delete_utr.sql" script will remove all data associated with a Test Suite. This includes the results from previous runs of the Test Suite. On a build server, this would not be a problem, but may be unexpected on a workstation. Again, the installation procedure creates a new Unit Test Repository for this example, so it should not be a problem to remove all data.<br />
<br />
<u>Additional Prerequisites:</u><br />
<br />
<ul>
<li>GNU utilities like make, sed, grep (I am using Cygwin on Windows7)</li>
<li>Oracle database (I am using Oracle11g Express Edition)</li>
<li>SQL*Developer (I am using 4.0.3)</li>
<li>Jenkins (I am using "<span style="font-family: Courier New, Courier, monospace;">java -jar jenkins.war --httpPort=8081</span>" so it doesn't interfere with Oracle APEX on port 8080)</li>
</ul>
<br />
<u>Before Running Jenkins:</u><br />
<br />
It is important to get the build working before using it with Jenkins. For Cygwin on Windows7, the "make" command should be run in a "Command Prompt" window, not a "Cygwin Terminal". The "make" utility and accompanying scripts should properly isolate the differences between Windows and POSIX (Linux).<br />
<ol>
<li>Unzip the ZIP file into any directory</li>
<li>Review the README.txt file</li>
<li>Follow the "Installation" instructions</li>
<li>Follow the "Testing" instructions</li>
</ol>
<u> Running Jenkins:</u><br />
<br />
1) Open the local Jenkins website (My URL is HTTP://127.0.0.1:8081) and create a new job.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-arnBVIvPkX4/VSdOF-ueiGI/AAAAAAAARdQ/i82jdg4LofA/s1600/GNU_make_example01.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-arnBVIvPkX4/VSdOF-ueiGI/AAAAAAAARdQ/i82jdg4LofA/s1600/GNU_make_example01.png" /></a></div>
<br />
2) Select Freestyle and Enter a name.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-aoxy4m0Q9YQ/VSdOFy71jfI/AAAAAAAARco/jmMmZy4f05M/s1600/GNU_make_example02.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-aoxy4m0Q9YQ/VSdOFy71jfI/AAAAAAAARco/jmMmZy4f05M/s1600/GNU_make_example02.png" /></a></div>
<br />
3) We won't be using Source Code Management in this example<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-6g0F-5Nwb9s/VSdOF80T6FI/AAAAAAAARcs/-sZOQZvUDHo/s1600/GNU_make_example03.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-6g0F-5Nwb9s/VSdOF80T6FI/AAAAAAAARcs/-sZOQZvUDHo/s1600/GNU_make_example03.png" /></a></div>
<br />
4) Add a Build Step<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-73L0tDwvpl0/VSdOGTVNEGI/AAAAAAAARcw/mb_FBL5RF94/s1600/GNU_make_example04.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-73L0tDwvpl0/VSdOGTVNEGI/AAAAAAAARcw/mb_FBL5RF94/s1600/GNU_make_example04.png" /></a></div>
<br />
5) Select "Execute Windows batch command" (Note: I am using Cygwin on Windows7. For Linux, use "Execute shell")<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-KKhLwnlFoeo/VSdOGg3p_aI/AAAAAAAARc0/EsT4yhmgBsU/s1600/GNU_make_example05.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-KKhLwnlFoeo/VSdOGg3p_aI/AAAAAAAARc0/EsT4yhmgBsU/s1600/GNU_make_example05.png" /></a></div>
<br />
6) Enter "cd" to confirm where the blog1 workspace is located. (Note: I am using Cygwin on Windows7. For Linux, use "pwd -P")<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-6DHx91jxi6U/VSdOG8seaEI/AAAAAAAARc4/MXyO2TJe8H4/s1600/GNU_make_example06.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-6DHx91jxi6U/VSdOG8seaEI/AAAAAAAARc4/MXyO2TJe8H4/s1600/GNU_make_example06.png" /></a></div>
<br />
7) "Save" the project, click "Build Now", click the down arrow next to "#1", then click "Console Output"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-f9Ganj1jsyM/VSl-DoPY78I/AAAAAAAARfM/tkJgGLHY7-0/s1600/GNU_make_example07.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-f9Ganj1jsyM/VSl-DoPY78I/AAAAAAAARfM/tkJgGLHY7-0/s1600/GNU_make_example07.png" /></a></div>
<br />
8) The Console Output reveals the location of the job workspace (C:\Users\Duane\.jenkins\jobs\blog1\workspace)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-VM5A9zGpzr8/VSdSpZt0nQI/AAAAAAAAReM/z2px78Jj_28/s1600/GNU_make_example08.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-VM5A9zGpzr8/VSdSpZt0nQI/AAAAAAAAReM/z2px78Jj_28/s1600/GNU_make_example08.png" /></a></div>
<br />
9) Copy the working set of unzipped files into the blog1 workspace. Remove the "*.done" files (blog1_cart.done, import_utr.done, and runtest.done) to reset the build process. This step would normally be accomplished automatically by Source Code Management like Subversion. The "*.done" files would not be included in the SCM Repository.<br />
<br />
10) In the browser, click "Back to Project", click "Workspace" and confirm the files are in place. (All these files are not necessary, as this is an example listing.)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-1w6L5LrrpcI/VSl7gMG5OWI/AAAAAAAARfA/PSfKo3u9es4/s1600/GNU_make_example09.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-1w6L5LrrpcI/VSl7gMG5OWI/AAAAAAAARfA/PSfKo3u9es4/s1600/GNU_make_example09.png" /></a></div>
<br />
11) Click "Configure" and change "cd" to "make all".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-1lN-TxNflg8/VSe2e3rQhWI/AAAAAAAARes/as0k3Y4wAOI/s1600/GNU_make_example10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-1lN-TxNflg8/VSe2e3rQhWI/AAAAAAAARes/as0k3Y4wAOI/s1600/GNU_make_example10.png" /></a></div>
<br />
Note: When running in Linux, environment settings for Java and Oracle XE may be needed. I did not need these for Cygwin on Windows7.<br />
<div dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;">
<span style="font-family: 'Courier New'; font-size: 11px; line-height: 1.38; white-space: pre-wrap;">
export JAVA_HOME=/usr/lib/jvm/java-7-oracle
export PATH=$JAVA_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_SID=XE
</span></div>
<br />
12) Click "Add post-build action"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-wzJgUza9YVI/VSl-VFGJYRI/AAAAAAAARfU/R1aGTDuld9w/s1600/GNU_make_example11.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-wzJgUza9YVI/VSl-VFGJYRI/AAAAAAAARfU/R1aGTDuld9w/s1600/GNU_make_example11.png" /></a></div>
<br />
13) Select "Publish JUnit test result report"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-vKicFoTkx-A/VSl-zHirDSI/AAAAAAAARfc/Nqx6pSqNMS8/s1600/GNU_make_example12.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-vKicFoTkx-A/VSl-zHirDSI/AAAAAAAARfc/Nqx6pSqNMS8/s1600/GNU_make_example12.png" /></a></div>
<br />
14) Add "junit_report*.xml" for "Test report XMLs". This will properly capture the "junit_report_FIT_blog1.xml" output from our testing.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-6okRDiUS5kE/VSmBBqqq_YI/AAAAAAAARfo/heLEuBvbsQM/s1600/GNU_make_example13.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-6okRDiUS5kE/VSmBBqqq_YI/AAAAAAAARfo/heLEuBvbsQM/s1600/GNU_make_example13.png" /></a></div>
<br />
15) Save these changes. The, as in step 7, click "Build Now", then "Console Output". The test should end with "<span style="background-color: white; color: #333333; font-size: 13px; white-space: pre-wrap;">Recording test results</span>" and "<span style="background-color: white; color: #333333; font-size: 13px; white-space: pre-wrap;">Finished: FAILURE</span>". As indicated in the README.txt, there is an error included in the testing. So, the test will show as failed.<br />
<br />
16) To see if the test results were properly captured, click the down arrow next to build number like in Step 7. The menu should have an extra item "Test Result" that was not in Step 7. Click it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-yUMIuBBtpGo/VSmEM8JZz6I/AAAAAAAARf0/gL9Ryh5Vk-4/s1600/GNU_make_example14.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-yUMIuBBtpGo/VSmEM8JZz6I/AAAAAAAARf0/gL9Ryh5Vk-4/s1600/GNU_make_example14.png" /></a></div>
<br />
17) Test Results from that build are displayed:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-YwLz9fqgIrw/VSnWFn9t1-I/AAAAAAAARgY/XESUnDvi5x0/s1600/GNU_make_example15.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-YwLz9fqgIrw/VSnWFn9t1-I/AAAAAAAARgY/XESUnDvi5x0/s1600/GNU_make_example15.png" /></a></div>
<br />
Under "All Failed Tests", the "Test Name" that failed can be broken down to the following into the following components:<br />
<ul style="-webkit-text-stroke-width: 0px; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px;">
<li>PL/SQL Package being tested: EMP</li>
<li>PL/SQL Procedure begin tested: REORG</li>
<li>SQL*Developer Unit Test Name: Reorganization Test</li>
<li>Unit Test Implementation Name: Test Implementation 1</li>
<li>Implementation Sequence: 2_RUNTEST</li>
</ul>
Implementation Sequence "2_RUNTEST" is 2 of 4 Implementation Sequences:<br />
<ul>
<li>1_STARTUP</li>
<li>2_RUNTEST</li>
<li>3_VALIDATE01 - Where 01 is the Validation Sequence</li>
<li>4_TEARDOWN</li>
</ul>
<br />
NOTE: Under "All Tests", the package "SQL*Developer_Test_Suite" does not actual exist as a PL/SQL package. This package name is used to collect the "Test_Suite_STARTUP" and "Test_Suite_TEARDOWN" results that are run at the Test Suite level. The "Class" for this package is the name of the Test Suite. Otherwise, as a general rule, "Class" is the name of the procedure/function being tested.<br />
<br />
18) Click on the failed test "EMP.REORG.Reorganization Test:Test Implementation 1:2_RUNTEST"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-s8Ipd6fs5uY/VSnYb5LuKCI/AAAAAAAARgk/eNQTd2TUHsQ/s1600/GNU_make_example16.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-s8Ipd6fs5uY/VSnYb5LuKCI/AAAAAAAARgk/eNQTd2TUHsQ/s1600/GNU_make_example16.png" /></a></div>
<br />
The "Stacktrace" information is particularly useful here. The error indicates a foreign key constraint violation on "BLOG1.BOSS". In fact, the "I_BOSS" value of 22 is incorrect. To fix this test, change that value to any "EMPNO" value from the "EMPLOYEE" table (like 7654).<br />
<br />
<br />
Hopefully, this was a successful start to Continuous Integration with SQL*Developer, Jenkins, and GNU "make". Thank you for reading,<br />
<br />Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com1tag:blogger.com,1999:blog-2026467871915021067.post-81702714363986696492015-03-28T13:24:00.001-05:002015-04-13T22:16:55.965-05:00DDGS Presentations 1 thru 5<a href="http://dmstex.com/">DMSTEX Business Solutions LLC</a> provides Database Design and Generation Services (DDGS). These services include development of Database Schema, PL/SQL API, Test Data, and Maintenance Forms. The following playlist demonstrates some of the details of DDGS.<br />
<br />
<iframe allowfullscreen="" frameborder="0" height="344" src="https://www.youtube.com/embed/videoseries?list=PLm47oi3KkTrOmDENiQ0F9O0ESwpm0olv_" width="425"></iframe>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com0tag:blogger.com,1999:blog-2026467871915021067.post-44957884260032871752015-03-28T00:24:00.000-05:002015-10-08T09:26:04.589-05:00Integrating Jenkins and SQL*Developer's Unit TestThe following links are SQL*Developer reports that will produce JUnit formatted output. They are specifically designed and tested to work with Jenkins.<br />
<br />
<a href="https://drive.google.com/file/d/0B4Qp7jjwAFDUbm10RGhCZ0NnZlU/view?usp=sharing">junit_suite_report.xml</a> - Report for a single Test Suite (UPDATED 11-Apr-2015)<br />
<a href="https://drive.google.com/file/d/0B4Qp7jjwAFDUY19pSnVwWlRKeTQ/view?usp=sharing">junit_test_report.xml</a> - Report for a single Unit Test (UPDATED 11-Apr-2015)<br />
<br />
Download the above XML files and load them into SQL*Developer like this:<br />
<ol>
<li>View -> Reports</li>
<li>Right Click on "User Defined Reports"</li>
<li>Select "Open Report"</li>
<li>Select the XML file and Click "Open"</li>
</ol>
<span style="color: red;">These reports DO NOT create working HTML reports. More about how to use the reports below.</span><br />
<br />
You can execute tests and reports from the command line.<br />
<br />
<b><u>To Run a Unit Test</u></b><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">sdcli unittest -run -test -name "TEST_NAME" -repo "REPO_CONNECTION" -db "DB_CONNECTION"</span><br />
<br />
<b><u>Create JUnit Report for a Unit Test</u></b><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">sdcli reports generate -report junit_test_report -db "REPO_CONNECTION" -file "REPORT_FILENAME" -bind "test_name=TEST_NAME"</span><br />
<br />
("junit_test_report" will select that latest run of the Unit Test named in the "test_name" bind variable.)<br />
<br />
<b><u>Run a Test Suite</u></b><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">sdcli unittest -run -suite -name "SUITE_NAME" -repo "REPO_CONNECTION" -db "DB_CONNECTION"</span><br />
<br />
<b><u>Create JUnit Report for a Test Suite</u></b><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">sdcli reports generate -report junit_suite_report -db "REPO_CONNECTION" -file "REPORT_FILENAME" -bind "suite_name=SUITE_NAME"</span><br />
<br />
("junit_suite_report" will select that latest run of the Test Suite named in the "suite_name" bind variable.)<br />
<br />
TEST_NAME - Name of a unit test in SQL*Developer<br />
SUITE_NAME - Name of a test suite in SQL*Developer<br />
REPO_CONNECTION - SQL*Developer connection name for the unit test repository.<br />
DB_CONNECTION - SQL*Developer connection name that will run the unit test.<br />
REPORT_FILENAME - File name for output of the SQL*Developer report.<br />
<br />
More information about using the SQL*Developer command line is at<br />
<a href="http://www.thatjeffsmith.com/archive/2013/07/sql-developer-4-0-and-the-command-line-interface-cli/">http://www.thatjeffsmith.com/archive/2013/07/sql-developer-4-0-and-the-command-line-interface-cli/</a>.<br />
<br />
<b><u><span style="color: red;">IMPORTANT</span></u></b><br />
<span style="color: red;">These reports create an HTML file called "REPORT_FILENAME".html. The file must be stripped of the HTML tags and saved as an XML file. The following "sed" command will perform the conversion:</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">sed -e '1,/^<pre> Remove this line and above$/d' -e '/^Remove this line and below <\/pre>$/,$d' < REPORT_FILENAME.html > REPORT_FILENAME.xml</span><br />
<br />
After the conversion, the XML file should look like an XML file. Jenkins will show errors in the "build console" if something is wrong with the XML file.<br />
<br />
To tell Jenkins how to capture the JUnit XML output:<br />
<br />
<ol>
<li>Click on the Build</li>
<li>Click "Configure"</li>
<li>Scroll down to "Add Build Step"</li>
<li>Select "Publish JUnit test result report"</li>
<li>Enter the location of the XML files in "Test report XMLs"</li>
</ol>
<br />
This is an example of JUnit output from SQL*Developer captured by Jenkins from several Test Suites combined into one build.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-myIYWYztZ68/VRal0zu6wYI/AAAAAAAARa0/hdt2rBC83l8/s1600/Jenkins1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="444" src="http://4.bp.blogspot.com/-myIYWYztZ68/VRal0zu6wYI/AAAAAAAARa0/hdt2rBC83l8/s1600/Jenkins1.png" width="640" /></a></div>
<br />
This is an example of an error captured by Jenkins from SQL*Developer JUnit output.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-FLubEGl_cAc/VRalyTInMkI/AAAAAAAARao/hnYN-MBEtxc/s1600/Jenkins2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="329" src="http://1.bp.blogspot.com/-FLubEGl_cAc/VRalyTInMkI/AAAAAAAARao/hnYN-MBEtxc/s1600/Jenkins2.png" width="640" /></a></div>
<br />
More information about JUnit XML format for Jenkins is at<br />
<a href="http://nelsonwells.net/2012/09/how-jenkins-ci-parses-and-displays-junit-output/">http://nelsonwells.net/2012/09/how-jenkins-ci-parses-and-displays-junit-output/</a><br />
<br />
Additional JUnit XML format information is at<br />
<a href="http://help.catchsoftware.com/display/ET/JUnit+Format">http://help.catchsoftware.com/display/ET/JUnit+Format</a>Duane Dieterichhttp://www.blogger.com/profile/17412847678998751716noreply@blogger.com21