Using execute immediate to generate SQL containing bound variables

A SQL, fixed its execution plan via SPM, can be passed DBMS_SPM. Load_plans_from_cursor_cache implementation. This feature can also be used to fix the execution plan by adding hint without modifying the original SQL.
DB version:oracle 11.2.0.4
Os:centos 6.6
For example:
Original SQL Walk Index:
SELECT * from SCOTT. TB_SPM WHERE object_id=10;
Want to go through the hint by adding a full table scan:
SELECT/*+full (TB_SPM) */* from SCOTT. TB_SPM WHERE object_id=10;
In the V$sql query out, the original SQL SQL_ID=064QCDMGT6THW, plus hint SQL sql_id=ahdtbgvsd3bht,plan_hash_value=970476072.
Do the following:
DECLARE
CNT number;
V_sql CLOB;
BEGIN
--Get the original statement SQL text
SELECT Sql_fulltext into V_sql from v$sql WHERE sql_id = ' 064qcdmgt6thw ' and rownum=1;
--use hint SQL sql_id and plan_hash_value to fix the SQL of the original statement
CNT: = dbms_spm. Load_plans_from_cursor_cache (sql_id = ' Ahdtbgvsd3bht ', Plan_hash_value = 970476072, sql_text = V_SQL);
END;
This will fix the hint execution plan on the original statement. Executes the original statement, v$sql the Plan_hash_value column and the Sql_plan_baseline column to confirm whether it is fixed.
The test found that some SQL with bound variables, sql_id and plan_hash_value with constant SQL cannot be fixed, you can try to use execute immediate to generate SQL that contains bound variables.
For example:
DECLARE
V_sql VARCHAR2 (2881064151);
BEGIN
V_sql: = ' SELECT/*+full (TB_SPM) */* from SCOTT. TB_SPM WHERE object_id=:1 ';
EXECUTE IMMEDIATE V_sql
USING 10;
END;
Or
var v number;
EXEC:V: =10
SELECT/*+full (TB_SPM) */* from SCOTT. TB_SPM WHERE object_id=:v;
Oracle adds hint to SQL statements via SQL profile for reference: http://www.linuxidc.com/Linux/2015-05/116974.htm

See Oracle Feature page for more information on Oracle HTTP://WWW.CODESEC.NET/TOPICNEWS.ASPX?TID=12

Using execute immediate to generate SQL containing bound variables