Avatar of mrjoltcola
Flag for United States of America

asked on 

Parsing scripts, code reformatting, Oracle syntax

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.
Programming Languages-OtherOracle DatabaseSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon