@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Execution plan from cursor cache
*  Parameters : 1 - SQL_ID
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  18-Mar-12  Vishal Gupta  Intial version
*
*
*/



DEFINE sql_id="&&1"
DEFINE child_number="&&2"



COLUMN  _sql_id         NEW_VALUE  sql_id             NOPRINT
COLUMN  _child_number   NEW_VALUE  child_number       NOPRINT

SELECT DECODE('&&sql_id','','%','&&sql_id')   "_sql_id"
     , DECODE('&&child_number','','NULL','&&child_number')   "_child_number"
FROM DUAL
;



SET long 4000 
SET longchunksize 4000 
SET pagesize 0
 
SELECT * 
FROM table(DBMS_XPLAN.display_cursor( sql_id          => '&&sql_id'
                                    , cursor_child_no => &&child_number
                                    , format          => 'ADVANCED ALLSTATS LAST'
                                  )
          )
;

UNDEFINE sql_id
UNDEFINE child_number


@@footer