13 Replies Latest reply: Jan 13, 2011 6:14 AM by Toon Koppelaars RSS

    Full Oracle SQL grammar

    161617
      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
          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
            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
              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
                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
                  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
                    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
                      Billy~Verreynne
                      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
                        Toon Koppelaars
                        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
                          Billy~Verreynne
                          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
                            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
                              "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
                                Hadricon
                                +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
                                  Toon Koppelaars
                                  Please wear a name tag that has "user6397963" printed on it, so we know who you are.

                                  ;-)