Oracle Coding Standards Feature Implementation

Question

Okay, I have reached a sort of an impasse.

In my open source project, a .NET-based Oracle database browser, I've implemented a bunch of refactoring tools. So far, so good. The one feature I was really hoping to implement was a big "Global Reformat" that would make the code (scripts, functions, procedures, packages, views, etc.) standards compliant. (I've always been saddened by the lack of decent SQL refactoring tools, and wanted to do something about it.)

Unfortunatey, I am discovering, much to my chagrin, that there doesn't seem to be any one widely-used or even "generally accepted" standard for PL-SQL. That kind of puts a crimp on my implementation plans.

My search has been fairly exhaustive. I've found lots of conflicting documents, threads and articles and the opinions are fairly diverse. (Comma placement, of all things, seems to generate quite a bit of debate.)

So I'm faced with a couple of options:

• Add a feature that lets the user customize the standard and then reformat the code according to that standard.

—OR—

• Add a feature that lets the user customize the standard and simply generate a violations list like StyleCop does, leaving the SQL untouched.

In my mind, the first option saves the end-users a lot of work, but runs the risk of modifying SQL in potentially unwanted ways. The second option runs the risk of generating lots of warnings and doing no work whatsoever. (It'd just be generally annoying.)

In either scenario, I still have no standard to go by. What I'd need to know from you guys is kind of poll-ish, but kind of not. If you were going to use a tool of this nature, what parts of your SQL code would you want it to warn you about or fix?

Again, I'm just at a loss due to a lack of a cohesive standard. And given that there isn't anything out there that's officially published by Oracle, I think this is something the community could weigh in on. Also, given the way that voting works on SO, the votes would help to establish the popularity of a given "refactoring."

P.S. The engine parses SQL into an expression tree so it can robustly analyze the SQL and reformat it. There should be quite a bit that we can do to correct the format of the SQL. But I am thinking that for the first release of the thing, layout is the primary concern. Though it is worth noting that the thing already has refactorings for converting keywords to upper case, and identifiers to lower case.

Answer 1

TOAD has a "pretty printer" and uses a ton of options to give the user some say in what is done. (But it has gotten so complicated that I still can't manage to get the results I would like.)

For me, some options look downward horrible, but it seems that some people like them. A sensible default should be okay for 80% of the time, but as this is an issue of religious wars, I'm sure that you can spend a totally unreasonable amount of time for pretty small results. I'd suggest to code some things to handle the 10-year-old sp you mentioned, and to include something like a <pre> tag that the pretty printer leaves alone.

Answer 2

If all you're doing is rearranging whitespace to make the code look consistently clean, then there's no risk of changing SQL results.

However, as an Oracle/PLSQL developer for the past 8 years, I can almost guarantee I wouldn't use your tool no matter how many options you give it. Bulk reformatting of code sounds great in principle, but then you've totally destroyed its diffability in version control between revisions prior to and after the reformat.

Answer 3

PL/SQL is an Ada derivative, however Ada's style guide is almost as gut-twisting disgusting as the one most "old-school" DB-people prefer. (The one where you have to think their caps lock got stuck pretty bad)

Stick with what you already know from .Net, which means sensible identifiers, without encrypting/compressing half the database into 30 chars.
You could use a dictionary and split camel-cased or underscored identifier parts and check if they are real words. Kinda like what FxCop does. Could be bit annoying, though. Since the average Oracle database has the most atrocious and inconsistent naming guidelines that were obsolete even 30 years ago. So, I don't think you'll reach the goal of getting clean identifiers everywhere in your projects (or your user's)

Since PL/SQL is case insensitive and columns are preferred over equally named local vars, you'll have to make even more tradeoffs. You can take parts of the style guide of other pascal derivatives (Ada is based on Modula, which is based on Pascal), like Delphi which feel a bit closer to home for PL/SQL (I use a mixture of .Net & Delphi). Especially the "aPrefix" for parameters can be a life saver, because you won't collide with column names that way:

subtype TName is SomeTable.Name%type;
subtype TId   is SomeTable.Id%type;

function Test(aName in TName) return TId is
  result TId;
begin
  SELECT t.Id
  INTO   result
  FROM   SomeTable t
  WHERE  t.Name = aName;

  return result;
exception
  when No_Data_Found then
    return null;
end;

Without the prefix, oracle would always pick the column "Name" and not the parameter "Name". (Which is pretty annoying, since columns can be qualified with an alias...)

I configured my PL/SQL Devloper to make all keywords in lowercase, however, I made the ones that are used in plain SQL to be uppercased (SELECT,WHERE, etc) As a result, SQLs are sticking out of the code, but not all my code has to be brutalized by all-upper keywords. (They are highlighted anyways, so what's with the all-upper fetish? ;-) )

When your tool is capable of identifying plain SQLs and give some visual clue, then even the SQL keywords wouldn't need to have a different casing.

btw, I'd love to take a look at it. Can you post an url, or is still "under cover"? Cheers, Robert

Answer 4

I like the "standard" Of Tom Kyte (in his books). That means everything in lowercase. Most easy for the eyes.