This discussion is archived
13 Replies Latest reply: Jan 13, 2011 4:14 AM by ToonKoppelaars RSS

Full Oracle SQL grammar

161617 Newbie
Currently Being Moderated
Hi!

Is there a full or even "official" Oracle grammar available in eg. YACC/Bison or BNF or ANTLR or ... format? I know that reference includes BNF snippets, but it would be pain to collect them together.

If this is too much to ask, then does anybody know whether those BNF snippets in reference add up to full grammar?

best greetings,
Aivar
  • 1. Re: Full Oracle SQL grammar
    6363 Guru
    Currently Being Moderated
    You mean this?

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2065706
  • 2. Re: Full Oracle SQL grammar
    161617 Newbie
    Currently Being Moderated
    You mean this?
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2065706
    Not really. I'd like to have something that I can mechanically convert to Bison/Yacc format.
    I need to create my own SQL parser.

    Edited by: aivarannamaa on Dec 9, 2010 9:11 PM
  • 3. Re: Full Oracle SQL grammar
    635471 Expert
    Currently Being Moderated
    aivarannamaa wrote:
    I need to create my own SQL parser.
    You'd never do it for the complete syntax, even for a select statement. It's incredibly complex, and gets more complex wiith every version.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#SQLRF01702

    Perhaps there's an alternative approach we can suggest -- what problem are you trying to solve here?
  • 4. Re: Full Oracle SQL grammar
    161617 Newbie
    Currently Being Moderated
    You'd never do it for the complete syntax, even for a select statement. It's incredibly complex, and gets more complex wiith every version.
    I kind of suspected that :)
    Perhaps there's an alternative approach we can suggest -- what problem are you trying to solve here?
    I'm building a static program analysis tool to find SQL errors from Java code. For simple cases I'm able to use parsing services from running Oracle testserver, but for some cases (eg. when programmer used loops for constructing SQL string and also for content-assist) I need to use special "abstract parsing" techniques and for this I need Bison's parsing tables.

    If you're interested then there's more info here:
    http://www.springerlink.com/content/cx22j11001706143/
  • 5. Re: Full Oracle SQL grammar
    635471 Expert
    Currently Being Moderated
    aivarannamaa wrote:
    I'm building a static program analysis tool to find SQL errors from Java code. For simple cases I'm able to use parsing services from running Oracle testserver, but for some cases (eg. when programmer used loops for constructing SQL string and also for content-assist) I need to use special "abstract parsing" techniques and for this I need Bison's parsing tables.
    Hmmm, I'd stick with submitting the code to an Oracle server if there's any way of doing so. There's more to determining the validity of a SQL statement than a syntax check because of issues like user privileges, validity of object names, and more esoteric stuff like not attempting to update a non-key preserved view.

    You might get away with syntax checking on simple cases but then you'll find yourself in an architecture where you've prevented yourself from constructing more efficient SQL (eg by using a subquery factoring clause, or CONNECT BY clause, or analytic function) because your parser does not support it.
  • 6. Re: Full Oracle SQL grammar
    161617 Newbie
    Currently Being Moderated
    Yea, I guess I'm gonna use some simplified grammar then but provide some backup ways for programmer to get rid of false-alarms.

    Thanks for the tips!
  • 7. Re: Full Oracle SQL grammar
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    aivarannamaa wrote:
    Yea, I guess I'm gonna use some simplified grammar then but provide some backup ways for programmer to get rid of false-alarms.
    Make sure to check for the 2 most common problems in Java code, calling Oracle:

    1) Use bind variables. In my book this is a non-negotiable.

    2) Close reference cursors. Usually happens when calling PL/SQL code that creates the cursor and passes a reference pointer for the SQL cursor to the client. The scope of this cursor is thus global. And unlike a normal PL/SQL cursor, does not go out of scope when the PL/SQL code block terminates (out-of-scope cursors are automatically released). So if the client does not explicitly close that ref cursor, it will remain (and consume server resources) until the end of the session. I've lost count how many times I have seen Java developers using ref cursors and not closing it when done (as if the idea of destructors and resource protection are foreign concepts when dealing with database resources).

     
    Personally, I think you endeavor is a waste of time. Java developers are mostly clueless on how to use a database correctly. They are dangerously (and even willfully) ignorant of database technology and architecture. The correct approach is to simply move all database processing (especially all SQL) into the database as stored procedures and present that as an API for the Java coder to use. This removes the need for any SQL in Java - and the ability of the Java developer to royally muck up the database server due to ignorance. The coder simply calls an API via anonymous PL/SQL blocks, using bind variables.
  • 8. Re: Full Oracle SQL grammar
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Billy  Verreynne  wrote:

    Personally, I think you endeavor is a waste of time. Java developers are mostly clueless on how to use a database correctly. They are dangerously (and even willfully) ignorant of database technology and architecture. The correct approach is to simply move all database processing (especially all SQL) into the database as stored procedures and present that as an API for the Java coder to use. This removes the need for any SQL in Java - and the ability of the Java developer to royally muck up the database server due to ignorance. The coder simply calls an API via anonymous PL/SQL blocks, using bind variables.
    +1

    Do note however:

    This does imply that you will need to hire a good database developer to be part of your project team...
  • 9. Re: Full Oracle SQL grammar
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Toon Koppelaars wrote:
    Do note however:

    This does imply that you will need to hire a good database developer to be part of your project team...
    Yep. And that will be cheaper in the long run than dumping this development on Java coders that simply do not grok database development. Or using that train wreck called Hibernate.
  • 10. Re: Full Oracle SQL grammar
    Boneist Guru
    Currently Being Moderated
    Toon Koppelaars wrote:
    +1

    Do note however:

    This does imply that you will need to hire a good database developer to be part of your project team...
    +1 (and I mean ideally, you'd need at least 2 good database developers, unless you're only working on one project at a time.)

    (Not that I've been overworked recently, nor am I the only database developer on my team, working on multiple, critical, projects all due at the same time. Nope, that wouldn't be me. It's a wonder I've any hair left!)

    </bitter> *{;-)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 11. Re: Full Oracle SQL grammar
    666127 Newbie
    Currently Being Moderated
    "Or using that train wreck called Hibernate."

    Thank God, i am not alone. I understand that Hibernate is becoming a wider threat to humanity?
  • 12. Re: Full Oracle SQL grammar
    830876 Newbie
    Currently Being Moderated
    +1 to Toon / Jan-Marten :)

    Guess Billy (btw +10 for you) has just qualified to enter the Miracle OpenWorld 2011, he should definitely come to Denmark next April.

    See you all in Billund ;)
  • 13. Re: Full Oracle SQL grammar
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Please wear a name tag that has "user6397963" printed on it, so we know who you are.

    ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points