4 Replies Latest reply: Jun 27, 2012 9:30 AM by flavioc RSS

    checking text query validity without actually running the query: ideas?

    flavioc
      Hi,
      I would like to implement a sort of function that does a preliminary check on the validity of the search string and ideally this function should work with both CTXCAT and CONTEXT indexes (and grammars).
      Initially I thought that CTXQUERY.EXPLAIN function could do this for me, but, unless I am missing something, it only works with CONTEXT indexes and it doesn't accept a string containing a text query rewrite using CTXCAT grammar (it throws DRG-11119: operation is not supported by this index type).

      Plan B would be to create an empty table with a CONTEXT index and then run the query either with the CONTEXT grammar or the CTXCAT grammar via query rewrite and if the query runs, returning no rows, presumably fast enough for this purpose, then the query string is assumed to be valid.
      What I don't like much about plan B is the need for an additional table plus the "dummy" context indexes just to perform the parsing of the text query, but so far I didn't come up with more brilliant ideas.

      Any thoughts?
      Am I missing something that does the magic without hassle?

      Thanks
      Flavio
        • 1. Re: checking text query validity without actually running the query: ideas?
          Herald ten Dam
          Hi,

          in the documentation it is mentioned for explain: "CTX_QUERY.EXPLAIN does not support the use of query templates" and that is the only way you can use CTXCAT grammar for a contains. So if you want to use EXPLAIN then you need an CTXCAT index to test.

          Herald ten Dam
          http://htendam.wordpress.com
          • 2. Re: checking text query validity without actually running the query: ideas?
            flavioc
            Hi Harald,
            I had noticed this, that's why I was leaning towards my custom method involving a dummy table.
            I made a test with the dummy table method and it looks like I can successfully check the syntax of both CONTEXT and CTXCAT grammars (via query template), I just found out that I need to insert at least one record, otherwise the text query is not even executed, the optimizer is too smart....

            So, unless there is some other method that doesn't require a dummy table, this seems the way to go for me.

            Thanks
            Flavio
            • 3. Re: checking text query validity without actually running the query: ideas?
              Roger Ford-Oracle
              I think it makes sense, but can you explain why you want to do this? What's the advantage of running a "pre check" rather than submitting the query and catching the error if it occurs?

              Perhaps not really connected, but I'm currently working on a query parser which will take queries in end-user "Google-style" syntax, and convert them into valid Oracle Text queries. Would that help you at all?
              • 4. Re: checking text query validity without actually running the query: ideas?
                flavioc
                Under normal circumstances I wouldn't do this, as you say I'd just run the query and catch the error, but I need to do this inside a report refresh performed via AJAX (using Oracle Apex) and the catch is that when such refresh fails owing to a syntax error, the partial refresh mechanism of APEX becomes broken, so even if you amend the query and submit a new valid string, it won't show up any more.
                That's why I need to be sure that the string is valid before running the query, hence the syntax checking function.

                The google-style parser sounds interesting, may be I can try to implement it in a future release of my app!

                Thanks
                Flavio