8 Replies Latest reply: Sep 21, 2012 5:03 PM by jgarry RSS

    PL/SQL Maximize Reuse

    638720
      Hi All,

      Imagine I have a team of 20+ developers all writing PL/SQL extracting data for our internal customers who in turn use this to answer key Business questions (sound familiar?).

      The common data requests are packaged up and presented in a 'self serve' form to the customers via OBI/Answers.

      However, across the team we have a series of ADHOC SQL queries (which maybe asked for again in a couple of months) that are also required. You know the ones, where everyone panics as a graph drops by 7%. :)

      For these ADHOC requests, I want to store these some where, perhaps in a code repository? and therefore wondered if:
      a) You have experienced this problem and what you did to resolve it?
      b) Know of some 'principles' which would enable this code repository to be built
      c) Know of some magical software (or prebuilt PL/SQL functionality) that would help

      I look forward to your response.

      Edited by: DaveyB on Sep 20, 2012 8:39 PM
        • 1. Re: PL/SQL Maximize Reuse
          Mark Malakanov (user11181920)
          a) You have experienced this problem and what you did to resolve it?
          For frequently used SQLs or joins I create views.
          c) Know of some magical software (or prebuilt PL/SQL functionality) that would help
          prebuilt - views
          b) Know of some 'principles' which would enable this code repository to be built
          Clear naming convention and commenting of views
          • 2. Re: PL/SQL Maximize Reuse
            638720
            Hello,

            Firstly, thank you for your quick reply. The concept of using views seems very logical and would help in certain situations.

            The hyperthetical issue we face is that often we need to join 4 (badly optimised) or more large tables (some with in excess of a billion rows). In this situation a view wouldn't be suitable but rather a series of SQL scripts therefore a repository of 'some kind' is needed.

            Arguably we could discuss 'proper' database adiministration but in this hypothetical situation we are lonely developers who need to get a job done but will file a request to our DBAs to make the change (which may not happen due to reasons X, Y and Z).

            One colleague did propose that we could store all the SQL statement in a table and fire them from there? Unsure how this would work with multiple parameters. :)
            • 3. Re: PL/SQL Maximize Reuse
              rp0428
              >
              The hyperthetical issue we face is that often we need to join 4 (badly optimised) or more large tables (some with in excess of a billion rows). In this situation a view wouldn't be suitable but rather a series of SQL scripts therefore a repository of 'some kind' is needed.
              >
              Why would a view not be suitable for that situation? A view is just a stored query. Your reasoning doesn't sense. Do you really want a junior developer to manually join 4 or more large tables and then you have to deal with the mess if they join them wrong or wind up with cartesian joins because they don't join on the correct columns. Not saying views are the answer but don't rule them out when they can take care of some of the basics for you.

              Can't you just store the scripts/queries on a shared network drive? Then your developers can access them, modify to use the parameters they need and execute them.

              That is a common approach using GUIs like sql developer and Toad both of which can access scripts on network drives and execute them.
              >
              One colleague did propose that we could store all the SQL statement in a table and fire them from there? Unsure how this would work with multiple parameters.
              >
              The parameters are just one issue. How do you plan to deliver the result set to the client (either the developer or the business user). Those result sets with likely have different projections for the different queries.

              Just share the scripts. Either put them on a network drive to make them accesible or put them in your version control system where they can be checked out when needed.
              • 4. Re: PL/SQL Maximize Reuse
                riedelme
                DaveyB wrote:
                The hyperthetical issue we face is that often we need to join 4 (badly optimised) or more large tables (some with in excess of a billion rows). In this situation a view wouldn't be suitable but rather a series of SQL scripts therefore a repository of 'some kind' is needed.
                Sounds like a good answer.

                Another approach would be to centralize such queries in an application or reports. Apex or other web tools could provide functionality for such centralization

                Either way you will need to educate your developer base to what is available and how to use it. Good documentation (too often neglected!) is something you will need.
                One colleague did propose that we could store all the SQL statement in a table and fire them from there? Unsure how this would work with multiple parameters. :)
                This would be one approach, but this idea uses dynamic SQL when not stricly speaking necessary. Parameters can be implemented with bind variables in the SQL but this could be unwieldy and diffiucult to manage.

                Good luck!
                • 5. Re: PL/SQL Maximize Reuse
                  6363
                  DaveyB wrote:

                  In this situation a view wouldn't be suitable
                  Why?
                  One colleague did propose that we could store all the SQL statement in a table and fire them from there?
                  This is a really bad idea, examples of the mess it leads to are posted on these forums regularly.

                  {thread:id=2415253}
                  {message:id=10458877}

                  Tables are for data, views and stored procedures are for executable code.
                  • 6. Re: PL/SQL Maximize Reuse
                    sb92075
                    +1
                    • 7. Re: PL/SQL Maximize Reuse
                      Mark Malakanov (user11181920)
                      The hyperthetical issue we face is that often we need to join 4 (badly optimised) or more large tables (some with in excess of a billion rows). In this situation a view wouldn't be suitable but rather a series of SQL scripts therefore a repository of 'some kind' is needed.
                      The scripts that store intermediate results to (temp) tables?
                      You can replace this by using WITH queries.
                      And again create views based on these WITH queries.
                      • 8. Re: PL/SQL Maximize Reuse
                        jgarry
                        +1
                        Though the simile is breaking down: http://reviews.cnet.com/8301-13746_7-57436639-48/tata-commercializing-an-air-powered-car/
                        Even more bizarro, Nitrogen is used in tires to supposedly reduce rolling resistance (because more bigger molecules don't leak out as fast, keeping them properly inflated) and in aircraft to reduce fire risk, and liquid Nitrogen has been demonstrated as a fuel for heat exchange engines.