SQL patches are used to add hints to SQL statements that you cannot modify directly.
This is how you normally create the patch:
declare c clob; begin select sql_text into c from dba_hist_sqltext where sql_id='&&1' and rownum=1; sys.dbms_sqldiag_internal.i_create_patch (sql_text => c, hint_text => '&&2', name => '&&PATCHNAME'); end; /
This works for most cases. However, since the patch is bound to the SQL by the SQL_ID, it might become ineffective once literals are used. Literals will create different SQL_IDs for each combination of values, since altering a value will alter the SQL text itself, creating a child cursor in the process.
This is when you want to use “forced matching”, which will calculate the SQL_ID by implicitly treating the literals as bound variables, thus creating the same SQL_ID for any combination of literals.
DECLARE sql_text clob ; hints varchar2(1000) :='&&2'; description varchar2(100):='created on ' || &&CURDAT; name varchar2(100) :='&&PATCHNAME'; output varchar2(100); sqlpro_attr SYS.SQLPROF_ATTR; BEGIN dbms_output.enable(null); select sql_text into sql_text from dba_hist_sqltext where sql_id='&&1' and rownum=1; sqlpro_attr := SYS.SQLPROF_ATTR(hints); output := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE( SQL_TEXT => sql_text, PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(sqlpro_attr), NAME => name, DESCRIPTION => description, CATEGORY => 'DEFAULT', CREATOR => 'SYS', VALIDATE => TRUE, TYPE => 'PATCH', FORCE_MATCH => TRUE, IS_PATCH => TRUE); dbms_output.put_line(output); END; /