troubleshooting Question

Parsing scripts, code reformatting, Oracle syntax

Avatar of mrjoltcola
mrjoltcolaFlag for United States of America asked on
Programming Languages-OtherOracle DatabaseSQL
3 Comments2 Solutions722 ViewsLast Modified:
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.
Walter Ritzel
Senior Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros