{"id":837,"date":"2014-01-05T16:27:05","date_gmt":"2014-01-05T15:27:05","guid":{"rendered":"http:\/\/qualilogy.com\/en\/?p=837"},"modified":"2014-01-05T17:05:58","modified_gmt":"2014-01-05T16:05:58","slug":"plsql-analysis-with-sonarqube-blockers","status":"publish","type":"post","link":"http:\/\/qualilogy.com\/en\/plsql-analysis-with-sonarqube-blockers\/","title":{"rendered":"PL\/SQL analysis with SonarQube &#8211; Blockers"},"content":{"rendered":"<p><a href=\"http:\/\/500px.com\/Vicken\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-1504\" alt=\"PLSQL_BlockersCritical\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/PLSQL_BlockersCritical.jpg\" width=\"400\" height=\"266\" \/><\/a>In the previous post, we have seen how to create <a href=\"http:\/\/qualilogy.com\/en\/plsql-analysis-sonarqube-plsql-quality-profile\/\" target=\"_blank\">our own PL\/SQL Quality Profile<\/a> activating all the 132 existing rules in the SonarQube default profile. Now, we can relaunch the analysis initiated earlier.<\/p>\n<p>This way, I will be able to work with all the rules existing in the PL\/SQL profile and select the ones that I want to use in order to create a PL\/SQL dashboard for my demo environment.<!--more--><\/p>\n<h2>Blockers<\/h2>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/Blockers.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1511\" alt=\"Blockers\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/Blockers.jpg\" width=\"669\" height=\"167\" \/><\/a><\/p>\n<p>At first, we can detect 16 violations of the rule \u2018Use IS NULL and IS NOT NULL instead of direct NULL comparisons\u2019. What is this about?<\/p>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleNULL.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-1513\" alt=\"RuleNULL\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleNULL.jpg\" width=\"428\" height=\"47\" \/><\/a>A drill-down from the list of defects and SonarQube allows us to access the code presenting this bad practice.<\/p>\n<p>We can see that the programmer performs a test on the variable &#8216;v_periodo&#8217; in order to verify if this one is an empty string, <span id=\"result_box\" lang=\"en\"><span class=\"hps\">because it has been<\/span> <span class=\"hps\">initialized<\/span> <span class=\"hps\">in this way<\/span><span>,<\/span> <span class=\"hps\">or<\/span> <span class=\"hps\">because this <\/span><span class=\"hps\">value<\/span> has been <span class=\"hps\">assigned<\/span> <span class=\"hps\">to<\/span> it <span class=\"hps\">somewhere in the<\/span> <span class=\"hps\">algorithm<\/span><span>.<\/span> <span class=\"hps\">For example:<\/span><\/span><\/p>\n<pre>DECLARE\r\n   name v_periodo varchar(10) := ''<\/pre>\n<p>The problem is that:<\/p>\n<ul>\n<li>Oracle considers an empty string as a NULL value. So as a result of the previous treatment, this variable does not contain an empty string, but a NULL value.<\/li>\n<li>Oracle makes the difference between an empty string and a NULL value. So the test:<\/li>\n<\/ul>\n<pre>IF v_periodo = ''<\/pre>\n<p>returns <strong>false<\/strong> as v_periodo <strong>is not<\/strong> an empty string, but a NULL value. The correct statement is:<\/p>\n<pre>IF v_periodo IS NULL<\/pre>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">This is a<\/span> <span class=\"hps\">good example of<\/span> a <span class=\"hps\">&#8216;bad<\/span> <span class=\"hps\">practice&#8217;<\/span> <span class=\"hps\">that<\/span> <span class=\"hps\">will most<\/span> <span class=\"hps\">certainly<\/span> cause <span class=\"hps\">a<\/span> <span class=\"hps\">failure<\/span> <span class=\"hps\">in the application,<\/span> <span class=\"hps\">since the condition<\/span> <span class=\"hps\">tested<\/span> <span class=\"hps\">(empty string<\/span><span>) will never be<\/span> <span class=\"hps\">possible,<\/span> <span class=\"hps\">and therefore<\/span> <span class=\"hps\">the corresponding business logic<\/span> <span class=\"hps\">will never be<\/span> <span class=\"hps\">executed.<\/span> <\/span><\/p>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">And of course,<\/span> <span class=\"hps\">w<\/span><span class=\"hps\">e discover that this<\/span> <span class=\"hps\">defect<\/span> has been <span class=\"hps\">duplicated<\/span> <span class=\"hps\">a few lines below<\/span><span>:<\/span><\/span><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleNULL21.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1519\" alt=\"RuleNULL2\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleNULL21.jpg\" width=\"404\" height=\"33\" \/><\/a><\/p>\n<p>I think there must be somewhere a computer theorem or some variant of Murphy&#8217;s Law which states that the number of Copy-Paste of an incorrect instruction is proportional to its gravity. The more serious, the more reproduced.<\/p>\n<p>The other two &#8216;Blocker&#8217; violations are quite clear and we just have to take a look at the SonarQube documentation to understand what is wrong and what are the consequences:<\/p>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleTrigger1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1521\" alt=\"RuleTrigger1\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleTrigger1.jpg\" width=\"582\" height=\"81\" \/><\/a><\/p>\n<p>A COMMIT or a ROLLBACK statement in a trigger lead to an ORA-04902 error. It is necessary to use a PRAGMA (compiler directive) to declare an autonomous transaction in order to realize this. However, I would say that I tend to question any DML (Data Manipulation Language) instruction within a trigger.<\/p>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleBlocker3.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1522\" alt=\"RuleBlocker3\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2013\/12\/RuleBlocker3.jpg\" width=\"535\" height=\"108\" \/><\/a><\/p>\n<p>A variable has been declared twice, leading to an Oracle PLS-03371 error. I especially appreciate that SonarQube indicates the 2 lines where this instruction is duplicated because it is one of the most difficult to investigate, especially in a program like this that has tens of thousands of line of code. This is common in older PL\/SQL &#8216;Legacy&#8217; applications.<\/p>\n<p>What can we say about these 3 Blockers?<\/p>\n<p>First, that their severity is completely justified:<\/p>\n<ul>\n<li>In the first case, a &#8216;malpractice&#8217; programming leads to a logical error and a very possible bug.<\/li>\n<li>And the following two cases are bugs that will stop the application, which is not really what the users expect and that you want them to see.<\/li>\n<\/ul>\n<p>These are therefore three rules for which no violation will be allowed: zero tolerance, these defects must be corrected immediately.<\/p>\n<p>Second observation: these rules are known, judging by the low number of cases encountered. Nevertheless, it is difficult if not impossible to avoid that:<\/p>\n<ul>\n<li>A new member in the team, possibly not very experienced, does not not known the programming best practice. I bet this is the case of the first rule (use exclusively NULL \/ NOT NULL) and that the same programmer has used an incorrect syntax and then Copy\/Pasted it everywhere.<\/li>\n<li>Even when everyone knows all the rules, the lack of attention is always possible. With just 1 or 2 defects for the next two rules, this is again probably what did happen.<\/li>\n<\/ul>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">It is<\/span> <span class=\"hps\">easy to forget to<\/span> <span class=\"hps\">declare a<\/span> <span class=\"hps\">transaction<\/span> <span class=\"hps\">or<\/span> that <span class=\"hps\">a variable has<\/span> <span class=\"hps\">already been declared<\/span> <span class=\"hps\">a few tens<\/span> <span class=\"hps atn\">(<\/span><span>or even hundreds<\/span><span>) of lines<\/span> <span class=\"hps\">above.<\/span> <span class=\"hps\">Fortunately, the<\/span> S<span class=\"hps\">onarQube<\/span> PL\/SQL plugin <span class=\"hps\">allows us to verify<\/span> <span class=\"hps\">and correct<\/span> quickly and easily <span class=\"hps\">these defects<\/span><span>.<\/span> <span class=\"hps\">I especially appreciate<\/span> <span class=\"hps\">to be able to go<\/span> <span class=\"hps\">down to the<\/span> <span class=\"hps\">line of code<\/span> <span class=\"hps\">where the<\/span> <span class=\"hps\">violation is to be found,<\/span> <span class=\"hps\">validate<\/span> <span class=\"hps\">it and<\/span> <span class=\"hps\">decide<\/span> <span class=\"hps\">for immediate<\/span> <span class=\"hps\">remediation.<\/span><br \/>\n<\/span><\/p>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">In the next post<\/span> <span class=\"hps\">we will discuss the<\/span> <span class=\"hps atn\">&#8216;<\/span><span>Critical&#8217;<\/span><span>.<\/span> <span class=\"hps\">Again, Happy New Year and have a good week.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the previous post, we have seen how to create our own PL\/SQL Quality Profile activating all the 132 existing rules in the SonarQube default profile. Now, we can relaunch the analysis initiated earlier. This way, I will be able to work with all the rules existing in the PL\/SQL profile and select the ones [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-837","post","type-post","status-publish","format-standard","hentry","category-sonarqube-plsql"],"_links":{"self":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts\/837"}],"collection":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/comments?post=837"}],"version-history":[{"count":5,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts\/837\/revisions"}],"predecessor-version":[{"id":840,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts\/837\/revisions\/840"}],"wp:attachment":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/media?parent=837"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/categories?post=837"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/tags?post=837"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}