7 Replies Latest reply on Dec 19, 2018 1:28 PM by Gaz in Oz

    Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?

    user2885917

      The code below is compiled and executed in Oracle Database 18c (18.3), but the parser cannot parse it.

      I guess this is somehow related to the difference between a CASE statement (END CASE) and an CASE expression (END). The latter implies the use of END only.

      In any case, the PL/SQL compiler resolves this collision adequately, but the parser does not. Is this some kind of problem with parsing?

       

      select case

               when c.char_length = 0 then

                c.data_length

               else

                c.char_length

             end case

        from dba_tab_columns c

      where c.table_name = 'V_$PARAMETER'

         and c.column_name = 'VALUE'

       

      parser_syntax_error_2.png

        • 1. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?

          As previously requested please submit suspected bugs directly to Oracle via your MOS account.

           

          The forums are NOT for reporting bugs.

           

          I also suggest you read the Oracle documentation BEFORE posting to learn the supported synxtax for SQL and PL/SQL

           

          In SQL you need to use SQL syntax and functionality. The CASE EXPRESSION is SQL syntax. The CASE STATEMENT is PL/SQL functionality.

           

          The documentation shows these clearly.

           

          A SIMPLE web search for 'oracle 18c case expression' returns the links to BOTH docs on the very first page of results

          https://docs.oracle.com/database/121/SQLRF/expressions004.htm#SQLRF20037

          CASE Expressions

          CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. The syntax is:

          Description of case_expression.gif follows

          https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/CASE-statement.html#GUID-F4251A23-0284-4990-A156-00A…

          13.8 CASE Statement

          The CASE statement chooses from a sequence of conditions and runs a corresponding statement.

          . . .

          Syntax

           

          simple_case_statement ::=

          searched_case_statement ::=

          Description of searched_case_statement.eps follows

          • 2. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?
            user2885917

            I also suggest you understand the attached code BEFORE posting.

            But I will explain - in the given example in the "END CASE" string, the "CASE" substring is a valid column alias, not a keyword.

            Or the documentation states, that the column alias cannot have a name "CASE"?

            Anyway, this is a valid CASE expression code for both SQL and PL/SQL compilers.

            • 3. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?

              I also suggest you understand the attached code BEFORE posting.

              I do understand.

               

              You need to understand that the forums are NOT for reporting suspected bugs.

               

              You need to report them directly to Oracle via your MOS account. Otherwise the proper people at Oracle, and other members of the Oracle community will never see them.

              But I will explain - in the given example in the "END CASE" string, the "CASE" substring is a valid column alias, not a keyword.

              It is a RESERVED WORD - as the documentation CLEARLY shows:

              https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-reserved-words-keywords.html#GUID-9BAA3A99-41B…

              C

               

              CASE, CHECK, CLUSTERS, CLUSTER, COLAUTH, COLUMNS, COMPRESS, CONNECT, CRASH, CREATE, CURSOR

              If you use keywords, reserved words or keywords for your own names then the risk is yours and you should have no expectation at all that Oracle should change their products to support you doing that. The docs clearly warn you NOT to use them - do so at your own risk..

               

              Anyway, this is a valid CASE expression code for both SQL and PL/SQL compilers.

              No it is NOT - it is a CASE STATEMENT - SQL does NOT support pl/sql case statements. It only supports case expressions as the doc syntax diagram shows.

               

              You seem to be making up your own rules - Oracle defines the rules that you need to follow. If you don't follow them you can expect nothing but problems.

              • 4. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?
                user2885917

                I repeat that this is a valid code that is successfully parsed and executed (as seen in the screenshot) without errors, both in the SQL and in the PL/SQL. I did not find in the documentation any restrictions on the use of keywords for column aliases - maybe you will give a link to the documentation?

                • 5. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?
                  Gaz in Oz

                  I repeat that this is a valid code that is successfully parsed and executed (as seen in the screenshot) without errors, both in the SQL and in the PL/SQL

                  You have posted a SQL statement using a SQL CASE expression. There is no PL/SQL

                  parser involved in running just SQL. Even if you imedded that SQL statement in

                  an anonymous PL/SQL block, the PL/SQL parser would NOT parse the SQL statement,

                  it would pass the parsing to the SQL engine. This is called a context switch.

                   

                  The SQL parser has seen you have ended the CASE expression with a column

                  alias, as bourn out by your screenshot clearly showing the column heading "CASE".

                  Change that to something else and watch the heading change to what ever you type

                  after "end ".

                  For brevity the SQL statement could have been written with the optional AS alias prefix, explicitly showing the second case statement is an alias, and SQL parser chose to accept it as an alias:

                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  select case
                    2            when c.char_length = 0 then
                    3              c.data_length
                    4            else
                    5              c.char_length
                    6        end AS case
                    7  from  dba_tab_columns c
                    8  where  c.table_name = 'V_$PARAMETER'
                    9* and    c.column_name = 'VALUE'
                   10  /
                  
                        CASE
                  ----------
                        4000
                  
                  1 row selected.
                  
                  SQL>
                  
                  
                  • 6. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?
                    thatJeffSmith-Oracle

                    Since it's valid and runs, the parser should not be throwing up a warning.

                     

                    So it's a bug.

                    1 person found this helpful
                    • 7. Re: Parser can not parse the correct code:{ select case ... end case from ... } Problem with parsing?
                      Gaz in Oz

                      I repeat that this is a valid code that is successfully parsed and executed (as seen in the screenshot) without errors, both in the SQL and in the PL/SQL

                      You have posted a SQL statement using a SQL CASE expression. There is no PL/SQL

                      parser involved in running just SQL. Even if you imedded that SQL statement in

                      an anonymous PL/SQL block, the PL/SQL parser would NOT parse the SQL statement,

                      it would pass the parsing to the SQL engine. This is called a context switch.

                       

                      The SQL parser has seen you have ended the CASE expression with a column alias, as borne out by your screenshot, clearly showing the column heading "CASE" in the query output.

                      Change that to something else and watch the heading change to what ever you type, as long s it is allowed...

                      after "end ".

                      The SQL statement could have been written with the optional AS alias prefix, explicitly showing the second case word is an alias:

                      SQL> ed
                      Wrote file afiedt.buf
                      
                        1  select case
                        2            when c.char_length = 0 then
                        3              c.data_length
                        4            else
                        5              c.char_length
                        6        end AS case
                        7  from  dba_tab_columns c
                        8  where  c.table_name = 'V_$PARAMETER'
                        9* and    c.column_name = 'VALUE'
                       10  /
                      
                            CASE
                      ----------
                            4000
                      
                      1 row selected.
                      
                      SQL>