13 Replies Latest reply: Feb 17, 2013 11:45 PM by Billy~Verreynne RSS

    Reg : Code validation in PL/SQL -

    ranit B
      Hi Experts,

      Need some ideas/pointers regarding some ' code check/syntax validation ' tool.

      Is there any way - to create a PL/SQL proc (say 'X') where the i/p will be any Table creation or Procedure script. X will check for the syntax validations and any other coding loop-holes.
      Actually we have a lot of overhead of manual checking the code for some coding best practices. So just want to automate them in a proc X.
      May be writing Regexs or any other way... some checks like -
      +1]+ CREATE or REPLACE PROCEDURE <schema_name>.<proc_name>
      Usually developers over here avoid the schema name.

      +2]+ Proper Exception Blocks with no WHEN OTHERS THEN NULL;

      Is this actually feasible or I'm just over-thinking?

      (I've access to both 10g and 11g databases.)

      Please suggest some ideas. (as of now, I'm trying for a windows Batch script which will check for the validation for scripts stored in a folder as +.txt+ )
      Help much appreciated.

      Ranit B.
        • 1. Re: Reg : Code validation in PL/SQL -
          sb92075
          ranit B wrote:
          Hi Experts,

          Need some ideas/pointers regarding some ' code check/syntax validation ' tool.

          Is there any way - to create a PL/SQL proc (say 'X') where the i/p will be any Table creation or Procedure script. X will check for the syntax validations and any other coding loop-holes.
          Actually we have a lot of overhead of manual checking the code for some coding best practices. So just want to automate them in a proc X.
          May be writing Regexs or any other way... some checks like -
          +1]+ CREATE or REPLACE PROCEDURE <schema_name>.<proc_name>
          Usually developers over here avoid the schema name.

          +2]+ Proper Exception Blocks with no WHEN OTHERS THEN NULL;

          Is this actually feasible or I'm just over-thinking?

          (I've access to both 10g and 11g databases.)

          Please suggest some ideas. (as of now, I'm trying for a windows Batch script which will check for the validation for scripts stored in a folder as +.txt+ )
          Help much appreciated.

          Ranit B.
          some, many, most Professional software development groups actually do physical code reviews prior to releasing code into Production.
          • 2. Re: Reg : Code validation in PL/SQL -
            ranit B
            some, many, most Professional software development groups actually do physical code reviews prior to releasing code into Production.
            Thanks SB, but isn't there any way we can automate the manual/physical work... And yes over here also, after development and before moving to production the code is checked thoroughly.
            But, I just want to reduce the effort for developers who do the first round of check.

            Please give me some suggestions, you and all Experts/Gurus have years of exp... Might have seen any tool or any such work.
            • 3. Re: Reg : Code validation in PL/SQL -
              Etbin
              Start with http://www.stevenfeuerstein.com/
              Sorry, I don't have other links at hand. You'll find several at the bottomof the main page.
              He is the PL/SQL evangelist - I have seen lots of scripts to generate table maintenance, error handling and other procedures/functions. He is the author of Code Tester http://unittest.inside.quest.com/index.jspa (comes with Quest Software TOAD I think) Test Driven Development approach could be very useful for you: I used the principle in "punched card era" where after every code change of some important program the deck of test data cards was fed to the new version enabling it to punch out the results and the new deck was then compared to the deck of (usually mannully computed) official results, Any differences had to be resolved before the
              "production use"
              now there's other folks business to do the testing ;)

              Regards

              Etbin
              • 4. Re: Reg : Code validation in PL/SQL -
                Sayan Malakshinov.
                Just useful link in addition to Etbin answer: http://www.toadworld.com/SF/standards

                Best regards,
                Malakshinov Sayan
                http://orasql.org
                • 5. Re: Reg : Code validation in PL/SQL -
                  ranit B
                  Thanks a lot Etbin and Xtender.

                  But this tutorial and the Code Tester seems like testing the business logic of code with various 'data inputs'.
                  What I need is to check for if 'best practices' are implemented in code or not.

                  Hope I explained my concern clearly, please let me know.
                  • 6. Re: Reg : Code validation in PL/SQL -
                    Sayan Malakshinov.
                    ranit B wrote:
                    But this tutorial and the Code Tester seems like testing the business logic of code with various 'data inputs'.
                    In the above link described PL/Scope, read about it.
                    ranit B wrote:
                    WHEN OTHERS THEN NULL;
                    In 11.2 exists special warning for this: PLW-06009

                    Regards,
                    Sayan Malakshinov
                    http://orasql.org
                    • 7. Re: Reg : Code validation in PL/SQL -
                      ranit B
                      Can you please get me the link? I dint find any called ' PL/Scope '
                      But this tutorial and the Code Tester seems like testing the business logic of code with various 'data inputs'.
                      In the above link described PL/Scope, read about it.
                      Btw, i guess you are not getting my requirement. I don't need to check the 'business logic' just the code for proper naming-conventions and other best practices followed or not.
                      I'm not seeing any topic related to this check for automation.
                      • 8. Re: Reg : Code validation in PL/SQL -
                        Sayan Malakshinov.
                        Part "Identify Violations of Best Practices" in the http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165471.html

                        Regards,
                        Sayan Malakshinov
                        http://orasql.org
                        • 9. Re: Reg : Code validation in PL/SQL -
                          Etbin
                          after development and before moving to production the code is checked thoroughly.
                          You mean to be compliant to some kind of standards and/or best practices ?
                          I'm not very comfortable with such things because once adopted they might stop enhancements, progress, evolution, you name it ...
                          The authors are usually high placed and so proud about they require the rules to be followed blindly, introducing disaster after disaster after disaster ...
                          Around here we still didn't get rid of loops (procedural thinking way predominates over thinking sets), keeping old rows in the same table (instead of having history tables/partitions) is still considered the best option, several EAV approaches are heavily pushed forward, ...
                          and our system (in spite being an Exadata) is slow and slower ... and "damn, something has to be done to make it run faster, for God's sake" can be heard all around.
                          The irreversible seems to have been reached already. :(

                          In any case, the best strategy seems to proceed case by case using the [url http://en.wikipedia.org/wiki/Big_Stick_ideology]Big Stick ideology
                          Billy's famous lead pipe might do even better ;)

                          Regards

                          Etbin
                          • 10. Re: Reg : Code validation in PL/SQL -
                            ranit B
                            The words were really wonderful, Etbin. Thanks!

                            But, naming conventions g_ , v_ and <schema>.<table_name/proc_name/object_name> can (mostly) never change.. right?
                            So just trying to come up with something like this.

                            Starting with a simple tool and gradually increase complexity.

                            Or, do you have any other tool in mind which can reduce daily effort?
                            Any tool you find really useful and worth developing - to ease daily efforts

                            Fyi - We have a tool which on input a proc/table gives all other objects linked to it... even indexes, triggers, etc. Something new like this ;)
                            • 11. Re: Reg : Code validation in PL/SQL -
                              EdStevens
                              ranit B wrote:
                              The words were really wonderful, Etbin. Thanks!

                              But, naming conventions g_ , v_ and <schema>.<table_name/proc_name/object_name> can (mostly) never change.. right?
                              So just trying to come up with something like this.

                              Starting with a simple tool and gradually increase complexity.

                              Or, do you have any other tool in mind which can reduce daily effort?
                              Any tool you find really useful and worth developing - to ease daily efforts

                              Fyi - We have a tool which on input a proc/table gives all other objects linked to it... even indexes, triggers, etc. Something new like this ;)
                              When I was a "developer" ( we called the postion "Programmer/Analyst") we spent a lot of effort on naming standards. And given that the standards were published and known by every programmer in the shop, catching violations was easy to do during the code reviews even though that wasn't the focus or intended purpose of the code review.

                              So, if you are doing code reviews for business logic, and you have a published standards manual to cover naming conventions, etc., then standards adherence will be a trivial addition to a 'business logic' code review. I think you are chasing a solution in search of a problem. I know it's hard for the younger generation to accept, but sometimes automation is simply not the solution. Or at least not the optimal solution.

                              IN fact, this is where a little human psychology comes into play. If you have published standards, you won't even have to make 'standards adherence' an agenda item on a 'business logic' code review. Developers will be quick to call each other out on it, and the fear of that will help drive adherence in the first place. It's a factor that is not understood by either computers or humans who believe computers are the solution to everything.

                              That said, there are tools out there that will help spot some issues. SQL Navigator is one that comes to mind.


                              Edited by: EdStevens on Feb 17, 2013 6:19 PM

                              Edited by: EdStevens on Feb 17, 2013 6:22 PM
                              • 12. Re: Reg : Code validation in PL/SQL -
                                Etbin
                                But, naming conventions g_ , v_ and <schema>.<table_name/proc_name/object_name> can (mostly) never change.. right?
                                Agreed, but applying Big Stick if not lead pipe is much more effective ;)
                                a solidarity (not reporting findings) may develop between checkers (assuming programmers checking each other code without making corrections as it would require reading the documentation (usually not available at the moment as it would if ever be written once things get done and never consulted by anyone since then) or just the requirements needing further explanation of things already explained to the first writer introducing additional delays - I know no one wants some other's finger being pointed at, but ther's a danger of no care being taken writing the first version as it would be checked anyway transferring the burden on checkers)
                                Some of checks you mention might be easy to implement:
                                procedure/function parameters must begin with p_ (looking at SQLs you know where those came from)
                                declarations must begin with some predefined prefix followed by an underline character followed by the name ...
                                some others not quite:
                                after <tt>from </tt> there must be <schema>.<table_name> and if ther's a comma (but it might be on the next line - if the standard allows it) a table alias must follow and the same applies until <tt>where</tt> is reached if there are only plain tables (but there can be subqueries too containing other subqueries - so recursion is to be applied - at least you're certain it will stop)
                                Is it worth the effort compared to the threatening Big Stick ?
                                If you're the one supposed to write all those checks you'd be better off applying for the Big Stick carrier's consulant if not for the one (her/him)self.
                                It will take a big effort for sure but (according to Murphy's Law combined with 80/20 rule) just a small percentage of inconsistencies might be found not effectively reducing the checking (at least by ill intentioned opinions) further enhancement requiring additinal efforts, or there will be indeed just a small number of inconsistencies (again by ill intentioned opinions most probably not owing to your software) but the manual checking must not be reduced substantially yet ...
                                and it might make you feel dumb ...
                                To me it seems feasible (but I'm not in your shoes) doing it and and gradually increase complexity as you say only to get into the Big Stick carrier's shadow or after obtainig her/his power. It's something (you are/might be the man) your superiors must be aware of too.
                                I wish it's all wrong, but as always - we never know for certain ...

                                Regards

                                Etbin
                                • 13. Re: Reg : Code validation in PL/SQL -
                                  Billy~Verreynne
                                  ranit B wrote:

                                  But, naming conventions g_ , v_ and <schema>.<table_name/proc_name/object_name> can (mostly) never change.. right?
                                  So just trying to come up with something like this.
                                  Bad idea IMO.

                                  Any standard that relies on scope as a prefix to naming an object is inherently flawed. As scope of an object changes. Often enough.

                                  There are numerous instances where one changes scope of an object - making a variable, a parameter, or a parameter a variable, moving a private global into the public interface section, etc.

                                  Not to mention that the scope prefix approach is based on Hungarian notation that today is considered a failure in a strong type language. By programmers that are considered world leaders in their fields of expertise.
                                  Starting with a simple tool and gradually increase complexity.
                                  Standards do not mean proper engineered code. So I would not be too overly concerned with standards. In many years of programming (and defining and implementing code standards), I have learned that what works the best is that which is the most logical and natural for a programmer to follow, and requires minimal effort to implement.

                                  This means, for example, not having standards that define how an IF condition needs to look like. Once had a chief programmer (job title) told me that we were not allowed to have more than one condition as using AND or OR is not allowed. The reason he gave is that we (lessor programmers) do not understand Boolean logic. I ignored him, as ignored his insulting standards.

                                  So the last thing one wants to have, are standards that are a pain to adhere to, and difficult to remember and implement.

                                  Instead I believe that before telling programmers how to dot their i's and cross their t's, best practises be enforced to ensure robust code, structured code, reusable code, and maintainable code.

                                  And that no s/w tool can evaluate. Which means code review is important. So that developers understand what works, what does not, and most importantly, the why behind it. After which sensible standards will come naturally to these programmers. Instead of the standard saying no +"exception when OTHERS then+", these programmers will not need such a standard - as they will understand how exceptions need to be treated and clearly understand what are dangerous exception handlers and will avoid that in their code.

                                  Standards are a very poor substitution for knowledge and experience.