@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Search SQL Text from SQLArea to find SQLId
*  Parameters : 1 - SQLText to search (Use % as wild card)
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  12-Jun-13  Vishal Gupta  Added force_matching_signature to output
*  08-Mar-12  Vishal Gupta  Created
*
*
*/

DEFINE sqltext="&&1"

SET long 100
SET longchunksize 100
SET pages 0

COLUMN sql_fulltext             HEADING "SQLText"  FORMAT a200 WRAP
COLUMN force_matching_signature             HEADING "FORCE_MATCHING_SIGNATURE"        FORMAT 999999999999999999999

-- Using a DISTINCT gives following error
-- ORA-00932: inconsistent datatypes: expected - got CLOB
SELECT inst_id, s.sql_id, force_matching_signature, s.sql_fulltext
FROM   gv$sqlarea   s 
WHERE upper(s.sql_text) like upper(q'[&&sqltext]')
AND s.sql_text NOT LIKE '%gv$sql%'
AND s.sql_text NOT LIKE '%dba_hist_sql%'
;

UNDEFINE sqltext

@@footer