57 lines
1.2 KiB
SQL
57 lines
1.2 KiB
SQL
@@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
|