/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display SQL Profile hints
*  Parameters : 1 - SQL Profile Name
*          
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  23-Mar-15  Vishal Gupta  Created
*
*/


UNDEFINE SQLPROFILENAME
DEFINE SQLPROFILENAME="&&1" 

COLUMN outline_hints   HEADING "SQL Profile Hints"   FORMAT a150


SELECT extractvalue(value(d), '/hint') AS outline_hints  
FROM xmltable('/outline_data/hint' passing
          (SELECT xmltype(sd.comp_data) as xmlval 
             FROM dba_sql_profiles sp
             JOIN sys.sqlobj$ so ON so.signature = sp.signature AND so.category  = sp.category AND so.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan  */ 
             JOIN sys.sqlobj$data sd ON sd.signature = so.signature AND sd.category  = so.category  AND sd.obj_type = 1  /* 1 = SQLProfile, 2=SQL Plan  */
             --JOIN sys.sqlobj$auxdata ad ON ad.signature = so.signature AND ad.category  = so.category  AND ad.obj_type = 1  /* 1 = SQLProfile, 2=SQL Plan  */
            WHERE 1=1
              AND sp.name = '&&SQLPROFILENAME'
     )
   ) d
;