We help IT Professionals succeed at work.
Get Started

Parsing scripts, code reformatting, Oracle syntax

Last Modified: 2013-11-18
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
Senior Software Engineer
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE