PROCEDURE I_CREATE_PATCH(
         SQL_TEXT      IN CLOB,
         HINT_TEXT     IN VARCHAR2,
         NAME          IN VARCHAR2 := NULL,
         DESCRIPTION   IN VARCHAR2 := NULL,
         CATEGORY      IN VARCHAR2 := 'DEFAULT',
                   VALIDATE      IN BOOLEAN  := TRUE)
  IS
    RET_NAME  VARCHAR2(30);
    HS        SYS.SQLPROF_ATTR;
  BEGIN
    
    COMMIT;

    
    DBMS_SMB.CHECK_SMB_PRIV;

    
    HS := SYS.SQLPROF_ATTR(HINT_TEXT);

    RET_NAME := DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
      SQL_TEXT => SQL_TEXT,
      PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(HS),
      NAME => NAME,
      DESCRIPTION => DESCRIPTION,
      CATEGORY => CATEGORY,
      CREATOR => SYS_CONTEXT('USERENV', 'SESSION_USER'),
      VALIDATE => VALIDATE,
      TYPE => 'PATCH',
      IS_PATCH => TRUE);
  END;

select /*SQL Patch Test*/ dummy from dual;

SELECT * FROM TABLE(dbms_xplan.display_cursor());
declare
   p_hint_set        SYS.SQLPROF_ATTR;
   p_sql_id          varchar2(13)       := '&sql_id';
   p_name            varchar2(128)      := 'SQL_PATCH_TEST1';
   p_desc            varchar2(500)      := 'SQL_PATCH_TEST1 description';
   p_category        varchar2(128)      := 'DEFAULT';
   p_validate        boolean            := true;
   RET_NAME          VARCHAR2(30);
begin
   p_hint_set:=SYS.SQLPROF_ATTR(
      q'[opt_param('optimizer_index_cost_adj' 50)]',
      q'[opt_param('optimizer_index_caching' 10)]'
   );
   for s in (select sql_fulltext
             from v$sqlarea
             where sql_id = p_sql_id) 
   loop
       RET_NAME := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
         SQL_TEXT    => s.sql_fulltext,
         PROFILE_XML => SYS.DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(p_hint_set),
         NAME        => p_name,
         DESCRIPTION => p_desc,
         CATEGORY    => p_category,
         CREATOR     => SYS_CONTEXT('USERENV', 'SESSION_USER'),
         VALIDATE    => p_validate,
         TYPE        => 'PATCH',
         IS_PATCH    => TRUE
       );
   end loop;
end;
/
select /*SQL Patch Test*/ dummy from dual;

SELECT * FROM TABLE(dbms_xplan.display_cursor('','','advanced'));

exec sys.dbms_sqldiag.drop_sql_patch('SQL_PATCH_TEST1');

SQL> @tests/patch/1

D
-
X


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  2kj0tst4rj96f, child number 0
-------------------------------------
select /*SQL Patch Test*/ dummy from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.

Enter value for sql_id: 2kj0tst4rj96f

PL/SQL procedure successfully completed.


D
-
X


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  2kj0tst4rj96f, child number 0
-------------------------------------
select /*SQL Patch Test*/ dummy from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_index_cost_adj' 50)
      OPT_PARAM('optimizer_index_caching' 10)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DUMMY"[VARCHAR2,1]

Note
-----
   - SQL patch "SQL_PATCH_TEST1" used for this statement


43 rows selected.


PL/SQL procedure successfully completed.

  FUNCTION CLOB_TO_SQLPROF_ATTR(CL CLOB) RETURN SQLPROF_ATTR
  IS
    SQLPROF_V SQLPROF_ATTR;
    OFFSET    INTEGER;
    BUF_500   VARCHAR2(500 CHAR);
    BUFLEN    INTEGER;
  BEGIN
    IF CL IS NULL THEN
      RETURN NULL;
    END IF;

    SQLPROF_V := SQLPROF_ATTR();
    OFFSET := 1;

    LOOP
      BUF_500 := DBMS_LOB.SUBSTR(CL, 500, OFFSET);
      BUFLEN := LENGTH(BUF_500);

      IF (BUFLEN > 0) THEN
        SQLPROF_V.EXTEND(1);
        SQLPROF_V(SQLPROF_V.COUNT) := BUF_500;
      END IF;

      
      EXIT WHEN (BUFLEN < 500 OR BUFLEN IS NULL);

      
      OFFSET := OFFSET + BUFLEN;
    END LOOP;

    RETURN SQLPROF_V;

  END CLOB_TO_SQLPROF_ATTR;
...
  FUNCTION I_CREATE_PATCH(
    SQL_TEXT      IN CLOB,
    HINT_TEXT     IN CLOB,
    CREATOR       IN VARCHAR2,
    NAME          IN VARCHAR2 := NULL,
    DESCRIPTION   IN VARCHAR2 := NULL,
    CATEGORY      IN VARCHAR2 := 'DEFAULT',
    VALIDATE      IN BOOLEAN  := TRUE)
  RETURN VARCHAR2
  IS
    RET_NAME  DBMS_ID;
    HS        SYS.SQLPROF_ATTR;
  BEGIN
    
    COMMIT;

    
    DBMS_SMB.CHECK_SMB_PRIV;

    
    HS := CLOB_TO_SQLPROF_ATTR(HINT_TEXT);

    RET_NAME := DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
      SQL_TEXT => SQL_TEXT,
      PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(HS),
      NAME => NAME,
      DESCRIPTION => DESCRIPTION,
      CATEGORY => CATEGORY,
      CREATOR  => CREATOR,
      VALIDATE => VALIDATE,
      TYPE => 'PATCH',
      IS_PATCH => TRUE);

    RETURN RET_NAME;
  END I_CREATE_PATCH;

  
  
  
  FUNCTION I_CREATE_PATCH(
    SQL_ID        IN VARCHAR2,
    HINT_TEXT     IN CLOB,
    CREATOR       IN VARCHAR2,
    NAME          IN VARCHAR2 := NULL,
    DESCRIPTION   IN VARCHAR2 := NULL,
    CATEGORY      IN VARCHAR2 := 'DEFAULT',
    VALIDATE      IN BOOLEAN  := TRUE)
  RETURN VARCHAR2
  IS
    SQL_TEXT   CLOB;
    GETSQLTXTQ VARCHAR2(150);
    RET_NAME   DBMS_ID;
  BEGIN
    
    COMMIT;

    
    DBMS_SMB.CHECK_SMB_PRIV;

    
    BEGIN
    IF SQL_ID IS NOT NULL THEN 
      GETSQLTXTQ := 'select sql_fulltext from sys.v$sql ' || -- почему бы просто не взять из v$sqlarea
                     'where upper(sql_id) = upper(:sqlid) and '|| -- и нафига тут upper?
                     '      rownum = 1';
      EXECUTE IMMEDIATE GETSQLTXTQ INTO SQL_TEXT USING SQL_ID;
   ...

SQL> col xx for a100
SQL> select
  2     dbms_smb_internal.VARR_TO_HINTS_XML(
  3        dbms_sqldiag_internal.CLOB_TO_SQLPROF_ATTR(
  4           to_clob(rpad(' ',490,' '))||q'[leading(a b c) ]'
  5        )
  6     ) xx
  7  from dual;

XX
----------------------------------------------------------------------------------------------------
<outline_data><hint><![CDATA[




                   leading(a ]]></hint><hint><![CDATA[b c) ]]></hint></outline_data>