/*
drop table test_t1 purge;
*/
set echo on feed on;

create table test_t1 as 
(
      select level id, 1 + level c2, 1 status, ' ' err   from dual
      connect by level <= 1000
      union all
      select 1000+level id, 1 + level c2, 2 status, ' ' err   from dual
      connect by level <= 500
      union all
      select 999999 id, 999999 c2, 1 status, 'ERROR' err   from dual

);

declare
  profile_name varchar2(30):='XT_TRANSLATE_PROFILE';
  PROFILE_DOES_NOT_EXIST exception;
  pragma EXCEPTION_INIT(PROFILE_DOES_NOT_EXIST, -24252);
BEGIN
  begin
       DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name);
  exception
      WHEN PROFILE_DOES_NOT_EXIST THEN NULL; /* ignore if non-existant */
  end;

  DBMS_SQL_TRANSLATOR.CREATE_PROFILE (profile_name);
END;
/

BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION (
    profile_name => 'XT_TRANSLATE_PROFILE',
    sql_text => q'[select count(*) from test_t1 where test_t1.status = :B and err <> ' ']',
    translated_text => 'select count(*) from test_t1 where 1=0 and status=:B',
    enable => TRUE);
END;
/
 
--select * from all_sql_translation_profiles;
var B number;
exec :B:=1;

ALTER SESSION SET SQL_TRANSLATION_PROFILE = XT_TRANSLATE_PROFILE;
alter session set events = '10601 trace name context forever, level 32';

select count(*) from test_t1 where status=1;
select count(*) from test_t1 where test_t1.status = :B;
select count(*) from test_t1 where test_t1.status = :B and err <> ' ';

SQL> BEGIN
  2  DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION (
  3      profile_name => 'XT_TRANSLATE_PROFILE',
  4      sql_text => q'[select count(*) from test_t1 where test_t1.status = :B and err <> ' ']',
  5      translated_text => 'select count(*) from test_t1 where 1=0 and status=:B',
  6      enable => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> var B number;
SQL> exec :B:=1;

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET SQL_TRANSLATION_PROFILE = XT_TRANSLATE_PROFILE;

Session altered.

SQL> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SQL> select count(*) from test_t1 where status=1;

  COUNT(*)
----------
      1001

SQL> select count(*) from test_t1 where test_t1.status = :B;

  COUNT(*)
----------
      1001

SQL> select count(*) from test_t1 where test_t1.status = :B and err <> ' ';

  COUNT(*)
----------
         0