14 Replies Latest reply: Nov 3, 2013 9:41 AM by Stew Ashton RSS

    [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"

    Stew Ashton

      Oracle Database 12c introduces the MATCH_RECOGNIZE clause, which uses regular expressions in the PATTERN sub-clause.

       

      Regular expressions use the question mark ("?") to indicate a "reluctant qualifier".

       

      SQL Developer sees the question mark and says "Missing IN or OUT parameter at index:: 1".

       

      How can I tell SQL Developer that this question mark is part of the syntax and not a bind variable?

        • 1. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
          Stew Ashton

          I asked this question in the JDBC forum and got this suggestion:

           

          Statement s = conn.createStatement();

            s.setEscapeProcessing(false);

            ResultSet rs = s.executeQuery(sqlWithReluctantQualifier);

           

          Is there a way I can get SQL Developer to do this? I don't see what good JDBC escape processing does within SQL Developer anyway, since we have to name our bind variables anyway.

          • 2. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
            rp0428

            StewAshton wrote:

             

            I asked this question in the JDBC forum and got this suggestion:

             

            Statement s = conn.createStatement();

              s.setEscapeProcessing(false);

              ResultSet rs = s.executeQuery(sqlWithReluctantQualifier);

             

            Is there a way I can get SQL Developer to do this? I don't see what good JDBC escape processing does within SQL Developer anyway, since we have to name our bind variables anyway.

            You don't see 'what good JDBC escape processing does within SQL Developer anyway' because people can only help you based on the information you provide in your thread:

             

            1. you posted your question in the JDBC forum: so readers will assume your question is related to JDBC statements

            2. you never provided ANY code or other information that shows EXACTLY just WHAT you are doing and HOW you are doing it.

             

            Java JDBC code can use bind variables but those can be DIFFERENT than the bind variables that sql developer uses in trying to emulate sql*plus.

             

            Post ALL of the information needed to help you:

             

            1. Oracle DB 4 digit version and name

             

            2. name and version of the JDBC driver you are using

             

            3. full version of sql developer you are using

             

            4. an EXACT copy of any code or statements that you are trying to execute. If you are using a lengthy or complex statement then post the SIMPLEST statement that reproduces the problem.

             

            5. explain EACH of the steps you are taking to execute the code/statement

             

            6. post EXACT copies of the response you get and tell us what window you get that response in.

            • 3. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
              Stew Ashton

              To all:  anyone interested in this problem can consult the JCBC thread here: Cannot use question mark (?) in Database 12c MATCH_RECOGNIZE clause

               

              Basically, the developer who handles question marks in the JDBC driver says it's a JDBC driver issue.

               

              To rp0428: here are the answers to your questions.

               

              1. Oracle DB 4 digit version and name -- as stated above, 12c, the only available patchset. I could add Enterprise Edition.

               

              2. name and version of the JDBC driver you are using -- as implied above, the one bundled with SQL Developer 4.0 EA2

               

              3. full version of sql developer you are using -- as stated above, SQL Developer 4.0 EA2

               

              4. an EXACT copy of any code or statements that you are trying to execute. If you are using a lengthy or complex statement then post the SIMPLEST statement that reproduces the problem.

               

              SELECT 'Hello' FROM dual

              match_recognize(

                pattern (A*?)

                define A AS 1=1

              );

               

              5. explain EACH of the steps you are taking to execute the code/statement

               

              Write the code in the worksheet, then choose "Run statement"

               

              6. post EXACT copies of the response you get and tell us what window you get that response in.

               

              In the "Query result" window: "Missing IN or OUT parameter at index:: 1"

              • 4. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                Gary Graham-Oracle

                Hi Stew,

                 

                As far as I am aware, SQL Developer does its own parsing and always tells the driver "setEscapeProcessing(false)" regardless of the statement's Java subclass, so the developer on the JDBC team is off the hook for this one.  I, however, am not too surprised -- that's why we have EA releases.

                 

                Bug logged against the SQL Developer Worksheet component:

                Bug 17591803 - PARSER INTERPRETS ? IN 12C MATCH_RECOGNIZE PATTERN AS BIND VAR


                Regards,

                Gary

                SQL Developer Team

                • 5. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                  rp0428

                  Make sure you read Doug's reply in the JDBC thread on this:

                  Given that I wrote the code in the drivers that detects '?' I can confidently say that the error OP describes is caused by the driver. MATCH_RECOGNIZE syntax includes a '?' token. The only way to get a '?' token through the driver is to call setEscapeProcessing(false). The driver will correctly handle a '?' in a literal or a comment, no problem. But the driver treats all '?' tokens as parameter markers unless escape processing is disabled. So a '?' token in a MATCH_RECOGNIZE clause will cause the driver to throw exactly the error OP describes.

                  • 6. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                    Gary Graham-Oracle

                    Thanks, I already did.  Of course the JDBC driver does require a fix for the case of setEscapeProcessing(true), but that is not relevant to SQL Developer at this time since we always seem to use setEscapeProcessing(false).  The JDBC driver's ability to do escape processing can be a great help to Java programmers trying to deal with database PL/SQL syntax in certain areas (bind variable, date/time/timestamp formats, outer join syntax, row limits), but not so much to SQL Developer users pounding out SQL and PL/SQL in a worksheet.

                     

                    Our parser currently must not have a rule that prevents replacing the Java-style '?' variable placeholder with a SQL-style bind variable if it is in the context of a regex pattern within a match_recognize clause. That's what needs to be fixed.

                     

                    -Gary-

                    • 7. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                      Stew Ashton

                      Hi Gary,

                       

                      Thanks very much for logging the bug and for the explanation. Trying the EA version was definitely worthwhile!

                       

                      Best regards, Stew

                      • 8. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                        rp0428
                        Our parser currently must not have a rule that prevents replacing the Java-style '?' variable placeholder with a SQL-style bind variable if it is in the context of a regex pattern within a match_recognize clause.

                        Not surprising - you guys are good but keeping totally in-sync with both the Oracle and the JDeveloper teams would be a pretty tall order!

                         

                        The MATCH_RECOGNIZE clause was just introduced with the new Oracle 12C database, along with many other new features and new syntax constructs to support those features.

                         

                        The syntax for that clause includes a number of complex sub-clauses and can include regular expressions.

                         

                        Chapter 18 (SQL for Pattern Matching) of the Data Warehousing Guide contains an extensive discussion of the syntax and provides examples:

                        http://docs.oracle.com/cd/E16655_01/server.121/e17749/pattern.htm

                         

                        The clause will, in many cases be used instead of the more traditional WHERE clause and the WHERE clause, for PL/SQL can contain 'bind variables'. The 12C PL/SQL doc currently makes no mention of MATCH_RECOGNIZE so it isn't clear if that clause can include bind variables.

                         

                        Tests I have done trying to use bind variables with that clause in PL/SQL and with dynamic sql (EXECUTE IMMEDIATE) have not been successful and I haven't been able to get an answer from Oracle as to whether bind variables are supported for that clause.

                        • 9. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                          dsurber

                          I had no idea SQL Developer duplicated the SQL rewrite that the driver does. Oh, well. If it makes you happy.

                           

                          MATCH_RECOGNIZE is going to be a problem until the JDBC Expert Group comes up with a revision to escape parts of the SQL. As soon as the EG agrees on something the Oracle JDBC team will add it to our drivers. We are not going to do something nonstandard as that always causes problems down the road. Until then, setEscapeProcessing(false) is the only solution for JDBC. Sigh.

                           

                          Douglas

                          • 10. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                            Gary Graham-Oracle

                            Hi Douglas,

                            I had no idea SQL Developer duplicated the SQL rewrite that the driver does. Oh, well. If it makes you happy.

                            I doubt anyone goes out of the way to write extra code, but sometimes it has advantages.  SQL Developer has dependencies on many other groups within Oracle, and the team is always appreciative to get a code drop that adds new features or fixes some bug that blocks us.  In the case of JDBC, staying with setEscapeProcessing(false) means we do our own bind variables.  At least it won't be SQL Developer that logs a bug against JDBC and sets it as the base bug for Bug 17591803!

                             

                            Hope the JDBC Expert Group agrees soon on those escaping rules.

                             

                            Regards,

                            Gary

                            • 11. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                              Gary Graham-Oracle

                              Update:  SQL Developer will provide a partial fix for 4.0 EA3.  If the query has no bind variables, escape processing will be switched off and the JDBC driver will pass through the match_recognize pattern.  Otherwise we will have to wait for the JDBC team to provide a standards-based fix.

                               

                              -Gary-

                              • 12. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                                Stew Ashton

                                Thanks much for the update, Gary.

                                 

                                Best regards, Stew

                                • 13. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                                  Gary Graham-Oracle

                                  Update:  the partial fix for 4.0 EA3 has been dropped for 4.0 production (not sure if it might still slip-out with EA3) since QA caught a regression in some other test cases.  Will have to wait on a fix from the JDBC team. 

                                  • 14. Re: [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"
                                    Stew Ashton

                                    Gary, thanks for the update. Too bad it's not easy. I would appreciate further updates when a fix becomes available.

                                     

                                    Best regards, Stew