Parsing scripts, code reformatting, Oracle syntax

mrjoltcola used Ask the Experts™
One of the features I've been working on for a while for my Oracle tool is multi-statement script support. Otherwise, I'll never migrate completely away from Toad for my daily tasks.

So my thoughts after studying the problem, and implementing a semi-working first pass, is that creating a fully compliant Oracle SQL / PL/SQL parser, though within my reach eventually, is not practical in the short haul. The other downside is as Oracle gets new syntax, I have to update the parser. I've experience with this in Toad and PLSQL Developer over the years with the exact issue.

But until I do nearly fully parse Oracle dialect, I cannot provide a worthy code beautifier.

So the way I see it I take this approach:
1) Create a simple script parser that breaks the script into individual statements
   Example would be: SimpleOracleParser.Split(text), returns a list of statements, directive, etc. and I iteratively pass those to Oracle.
  The advantage here is I don't have to fully parse the statement, just tokenize it enough to recognize the end of it (semicolon, / on empty line, etc.) I don't have to parse PL/SQL either to skip embedded semicolons vs the end of the whole thing, if I just look for statements that start with "BEGIN, DECLARE, or any CREATE OR REPLACE of a PLSQL procedure or function, then scan for the first / on its own line.)

2) Create or license a more powerful, full Oracle parser for code-formatter and refactoring support. This may be a one-year timeframe, unless of course I started making money on the tool enough to assign a programmer to it besides myself. But there is no shortcut to decent code formatter / refactoring without this.

So I'm just interested in thoughts and opinions on this approach, and perhaps how often you guys use the same features of Toad, SQL Developer, etc. and which features you deem critical.

Keep in mind the tool is envisioned as a low-dollar budget tool, probably $50-100.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Engineer
My 2 cents here: implement the option 1, release as donation-ware, use the donations received to implement option 2.
Devinder Singh VirdiLead Oracle DBA Team
Consider the execution plan as well. Static execution plan may need newer version of your code and will increase sale.
Top Expert 2009


Thanks guys.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial