@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Hints in a SQL Statement
*  Parameters : 1 - SQLID
*               2 - Child Number
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  01-May-12  Vishal Gupta  First Draft
*
*/


set pages 50

DEFINE sql_id="&&1"
DEFINE child_no="&&2"


SELECT extractvalue(value(d), '/hint') AS outline_hints
  FROM  xmltable('/*/outline_data/hint'
                 passing ( SELECT xmltype(other_xml) AS xmlval
                             FROM v$sql_plan
                            WHERE sql_id LIKE NVL('&sql_id',sql_id)
                              AND child_number = &child_no
                              AND other_xml IS NOT NULL
                          )
                 ) d
;

UNDEFINE sql_id
UNDEFINE child_no                 

@@footer