Tom,
I was trying to tune one of the sql written in Ansi syntax without toughing using dbms_advanced_rewrite API.
but ended up with the below error, Can you help me what i am missing here?
rajesh@ORA10G> declare
2 l_src long;
3 l_dest long;
4 begin
5 l_src :='select a.empno, a.ename, a.sal, sum(b.sal) '||
6 'from emp a, emp b '||
7 'WHERE a.sal > b.sal '||
8 'OR '||
9 '( a.sal= b.sal '||
10 'and a.rowid >= b.rowid ) '||
11 'group by a.empno, a.ename, a.rowid,a.sal '||
12 'order by a.sal, a.rowid ' ;
13 l_dest :='select empno,ename,sal, '||
14 'sum(sal) over(order by sal,rowid) as new_sal '||
15 'from emp ';
16 sys.dbms_advanced_rewrite.declare_rewrite_equivalence
17 (name =>'DEMO',
18 source_stmt =>l_src,
19 destination_stmt =>l_dest,
20 validate =>false,
21 rewrite_mode =>'TEXT_MATCH');
22 end;
23 /
declare
*
ERROR at line 1:
ORA-30389: the source statement is not compatible with the destination statement
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 16
rajesh@ORA10G>
it is a limitation of dbms_advanced_rewrite, it will generate this query:
ops$tkyte%ORA11GR2> ( select a.empno, a.ename, a.sal, sum(b.sal) new_sal
2 from emp a, emp b
3 WHERE a.sal > b.sal OR ( a.sal= b.sal and a.rowid >= b.rowid )
4 group by a.empno, a.ename, a.rowid,a.sal
5 order by a.sal, a.rowid )
6 UNION ALL
7 ( select empno,ename,sal, sum(sal) over(order by sal,rowid) as new_sal
8 from emp )
9
ops$tkyte%ORA11GR2> /
order by a.sal, a.rowid )
*
ERROR at line 5:
ORA-00907: missing right parenthesis
to test for equivalence. The order by is unfortunately not supported with this feature (dbms_advanced_rewrite). It was logged as a documentation bug and error message bug (documentation needs to reflect this limitation and error message should be more appropriate)
Bug 18360269 : ERROR ORA-30389 AND ORA-00907 RUNNING DBMS_ADVANCED_REWRITE WITH ORDER BY