@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display SQL Text from SQLArea for a FORCE_MATCHING_SIGNATURE
*  Parameters : 1 - FORCE_MATCHING_SIGNATURE
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  22-Oct-15  Vishal Gupta  Initial version
*
*
*/

UNDEFINE sqlid
UNDEFINE force_matching_signature

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

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




COLUMN  _sqlid          NEW_VALUE  sqlid            NOPRINT

select s.sql_id "_sqlid" 
  from gv$sqlarea s  
WHERE  s.force_matching_signature = &&force_matching_signature
and rownum = 1
;


Prompt   ********************************************************
Prompt   *  SQL Text for SQLID = &&sqlid 
Prompt   *  Force Matching Signature = &&force_matching_signature
Prompt   ********************************************************
 
COLUMN sql_text             HEADING "SQLText"  FORMAT a200 WRAP

SELECT s.sql_fulltext sql_text
FROM   gv$sqlarea   s  
WHERE  s.force_matching_signature = &&force_matching_signature
and rownum = 1
;


@@footer