I think you are not getting the SQL to match. Here's my example
I changed your program to be as per my initial example:
sqlStmt = "SELECT COUNT(*) FROM EMP WHERE DEPTNO = ?";
Notice - *no* trailing space in the *java* code.
And here my database initialisation
SQL> conn demo/demo
Connected.
SQL> exec DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name => 'DEMO_PROFILE'); exception when others then null;
PL/SQL procedure successfully completed.
SQL> exec dbms_sql_translator.create_profile('DEMO_PROFILE');
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
3 profile_name => 'DEMO_PROFILE',
4 sql_text => 'SELECT COUNT(*) FROM EMP WHERE DEPTNO = :1 ', -- 1 trailing space
5 translated_text => 'SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :1');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select * from user_sql_translation_profiles
2 @pr
==============================
PROFILE_NAME : DEMO_PROFILE
TRANSLATOR :
FOREIGN_SQL_SYNTAX : TRUE
TRANSLATE_NEW_SQL : TRUE
RAISE_TRANSLATION_ERROR : FALSE
LOG_TRANSLATION_ERROR : FALSE
TRACE_TRANSLATION : FALSE
PL/SQL procedure successfully completed.
SQL> select * from user_sql_translations
2 @pr
==============================
PROFILE_NAME : DEMO_PROFILE
SQL_TEXT : SELECT COUNT(*) FROM EMP WHERE DEPTNO = :1
TRANSLATED_TEXT : SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :1
SQL_ID : dcfu2rmf00qd0
HASH_VALUE : 3691010464
ENABLED : TRUE
REGISTRATION_TIME : 30-AUG-16 10.36.08.033000 AM
CLIENT_INFO :
MODULE :
ACTION :
PARSING_USER_ID :
PARSING_SCHEMA_ID :
COMMENTS :
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
Notice - *1* trailing space in the *profile setup* code.
Now I compiled/ran the java program and got the expected 3 rows
C:\temp>java MySqlBindVariableTest
SQL Statement:
SELECT COUNT(*) FROM EMP WHERE DEPTNO = ?
3
O.K
SQL> select sql_id, sql_text from v$sql
2 where sql_text like '%WHERE DEPTNO%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------
dcfu2rmf00qd0 SELECT COUNT(*) FROM EMP WHERE DEPTNO = :1
8k84p2jb9b1v2 select sql_id, sql_text from v$sql where sql_text like '%WHERE D
EPTNO%'
Important note: You can see that the SQL_ID found in v$sql is a *match* for the one that I have created the translation profile for.
So now I edited the java program, and commented back in:
sqlStmt="alter session set sql_translation_profile =DEMO_PROFILE";
prepStmt = conn.prepareStatement(sqlStmt);
rs = prepStmt.executeQuery();
sqlStmt="alter session set events = '10601 trace name context forever, level 32'";
prepStmt = conn.prepareStatement(sqlStmt);
rs = prepStmt.executeQuery();
and here's the results (shared pool was flushed again)
C:\temp>java MySqlBindVariableTest
SQL Statement:
SELECT COUNT(*) FROM EMP WHERE DEPTNO = ?
0
O.K
SQL> select sql_id, sql_text from v$sql
2 where sql_text like '%WHERE DEPTNO%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------
96k8ryxhram8q SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :1
8k84p2jb9b1v2 select sql_id, sql_text from v$sql where sql_text like '%WHERE D
EPTNO%'
You can see the translation was done, and we (correctly) got no rows.