Hi,

I faces two ORA error while using SYS.DBMS_ADVANCED_REWRITE

this my program and error raised :

[code]
DECLARE
l_src LONG;
l_dest LONG;
BEGIN
l_src :=
‘select a.empno, a.ename, a.sal, sum(b.sal) ‘
|| ‘from emp a, emp b ‘
|| ‘WHERE a.sal > b.sal ‘
|| ‘OR ‘
|| ‘( a.sal= b.sal ‘
|| ‘and a.rowid >= b.rowid ) ‘
|| ‘group by a.empno, a.ename, a.rowid,a.sal ‘
|| ‘order by a.sal, a.rowid ‘;
l_dest :=
‘select empno,ename,sal, ‘
|| ‘sum(sal) over(order by sal,rowid) as new_sal ‘
|| ‘from emp ‘;
sys.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
name => ‘DEMO’,
source_stmt => l_src,
destination_stmt => l_dest,
VALIDATE => FALSE,
rewrite_mode => ‘TEXT_MATCH’);
END;[/code]

Error :

[code]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[/code]