title: How to use Oracle DBMS_ADVANCED_REWRITE with bind variable?
body: <p>We need to implement a query rewrite with a bind variable because we don't have the option of modifying the web application source code. Example:</p>
  
  <pre><code>BEGIN
       SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
       name             =&gt; 'test_rewrite2',
       source_stmt      =&gt; 'select COUNT(*) from ViewX where columnA = :1',
       destination_stmt =&gt; 'select COUNT(*) from ViewY where columnA = :1',
       validate         =&gt; FALSE,
       rewrite_mode     =&gt; 'recursive');    
  END;
  </code></pre>
  
  <p>The above command will result in error because there is a bind variable:</p>
  
  <pre><code>30353. 00000 -  "expression not supported for query rewrite"
  *Cause:    The SELECT clause referenced UID, USER, ROWNUM, SYSDATE,
             CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable,
             correlation variable, a set result, a  trigger return variable, a
             parallel table queue column, collection iterator, a non-deterministic
             date format token RR, etc.
  *Action:   Remove the offending expression or disable the REWRITE option on
             the materialized view.
  </code></pre>
  
  <p>I am reading <a href="https://www.linkedin.com/groups/Tune-Query-without-Changing-Code-2154434.S.5795940598020059137" rel="nofollow">here</a> that there is a work around but I just cannot find the document anywhere online. </p>
  
  <p>Could you please tell me what the work around is? </p>