@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display SQL Text from AWR repository
*  Parameters : 1 - SQL_ID
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  22-Oct-15  Vishal Gupta  Added FORCE_MATCHING_SIGNATURE in the output heading
*  08-Mar-12  Vishal Gupta  Initial version
*
*
*/

UNDEFINE sqlid
UNDEFINE force_matching_signature


DEFINE sqlid="&&1"
DEFINE force_matching_signature=""

SET long 4000000 
SET longchunksize 400000 
SET pages 0
set lines 300




COLUMN  _force_matching_signature          NEW_VALUE  force_matching_signature            NOPRINT

select  to_char(ss2.force_matching_signature) "_force_matching_signature" 
  from  v$database d
      , dba_hist_sqlstat ss2 
  where d.dbid = ss2.dbid
    AND ss2.sql_id = '&&sqlid' 
    and rownum = 1
;

Prompt   ********************************************************
Prompt   *  SQL Text for SQLID = &&sqlid 
Prompt   *  Force Matching Signature = &&force_matching_signature
Prompt   ********************************************************

COLUMN sql_text             HEADING "SQLText"  FORMAT a150 WRAP
 
SELECT s.sql_text
FROM   dba_hist_sqltext   s 
     , v$database d 
WHERE d.dbid = s.dbid 
  AND s.sql_id = '&&sqlid'
;


@@footer