How to escape ' in dynamic sql
Dear Experts ,
I am trying to escape single quote(') in dynamic sql but could not find a way , I used escape character / and two single quotes '' but it did not work .
The following is dynamic SQL I am try to generate :
SQL> select 'exec dbms_stats.gather_index_stats(ownname=>\'SYSADM\', indname=>\''||iname||'\', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' from test_missing_index;
select 'exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>''||iname||'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' from test_missing_index
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
The desired out put should be :
exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>'test_index', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
Any help will be highly appreciated