I'm trying to do the simplest possible translation for a canned application from which I cannot change the generated SQL.I want to translate this exact statement:
select owner,table_name from all_tables where table_name='FRED';
to
select owner,table_name from dba_tables where table_name='FRED';
So I do this:
dbms_sql_translator.create_profile('DDPRE');
dbms_sql_translator.register_sql_translation('DDPRE',
q'[select owner,table_name from all_tables where table_name='FRED']',
q'[select owner,table_name from dba_tables where table_name='FRED']');
And it works great. BUT I want that same exact statement with table_name='BARNEY' or 'WILMA' to get translated as well -- trying to use bind variables from the examples in the Oracle Docs or setting CURSOR_SHARING=FORCE doesn't work. I want to avoid writing a custom translator, even if it's just translating Oracle SQL to Oracle SQL with a small PL/SQL package -- I would hope that there's an easy way to do this somewhat straightforward translation. THANKS!