@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display SQL Binds from AWR
*  Version    : 10.2 and above
*  Parameters : 1 - SQL_ID          
*               2 - Number of Days  (Default 7 days) 
*               2 - Whereclause
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  27-Aug-15  Vishal Gupta  Created
*  
*
*/



/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE sql_id
UNDEFINE days
UNDEFINE whereclause
UNDEFINE force_matching_signature
UNDEFINE use_force_matching_signature

DEFINE sql_id="&&1"
DEFINE days="&&2"
DEFINE whereclause="&&3"
DEFINE force_matching_signature=""

COLUMN  _days                           NEW_VALUE  days                           NOPRINT
COLUMN  _force_matching_signature       NEW_VALUE  force_matching_signature       NOPRINT

set term off
SELECT DECODE('&&days','','7','&&days')                         "_days"
     , DECODE('&&sql_id','%','%','')                            "_force_matching_signature"
FROM DUAL
;

set term on


COLUMN  _force_matching_signature          NEW_VALUE  force_matching_signature            NOPRINT

set term off
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 = '&&sql_id' and rownum = 1;

select CASE WHEN '&&force_matching_signature' IS NULL THEN  '0'  
            WHEN '&&force_matching_signature' = '' THEN  '0'   
            ELSE '&&force_matching_signature' 
        END "_force_matching_signature" 
  from dual
;

set term on

/************************************/

Prompt 
Prompt **************************************************************
Prompt * AWR SQL Bind Values 
Prompt *  (Force Matching Signature = '&&force_matching_signature')
PROMPT * 
PROMPT * Input Parameters 
PROMPT *    - SQL Id           = '&&sql_id'
PROMPT *    - Days             = '&&days'
PROMPT *    - WhereClause      = '&&whereclause'
Prompt **************************************************************

COLUMN seperator                       HEADING "!|!|!|!"             FORMAT a1
COLUMN end_interval_time               HEADING "Snap Time"           FORMAT a15
COLUMN instance_number                 HEADING "I#"                  FORMAT 99
COLUMN SQL_ID                          HEADING "SQLId"               FORMAT a13
COLUMN plan_hash_value                 HEADING "Plan|Hash|Value"     FORMAT 999999999999  
COLUMN bind_values                     HEADING "Bind Values"         FORMAT a100
COLUMN bind_datatype                   HEADING "Bind DataType"       FORMAT a80

SELECT TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')      end_interval_time
     , ss.instance_number 
     , ss.sql_id
     , ss.plan_hash_value
     , (select listagg(NVL(name,':B' || position) 
                  ||'[' 
                  || datatype_string || NVL2(scale
                                            ,'(' || scale || NVL2(precision,','|| precision,'') || ')' 
                                            ,'' 
                                            ) 
                  || ']'
                  ||'=' 
                  || CASE 
                     WHEN datatype_string <> 'TIMESTAMP' THEN NVL(value_string,'NULL') 
                     ELSE TO_CHAR(ANYDATA.ACCESStimestamp(VALUE_ANYDATA))
                     END
                    ,','
               ) WITHIN GROUP (order by position)   
        from table(dbms_sqltune.extract_binds(ss.BIND_DATA)) 
        )  bind_values
/*        
       , (select listagg(NVL(name,':B' || position) || '=' 
                           || datatype_string || NVL2(scale,'(' || scale || NVL2(precision,','|| precision, '') || ')' ,'' )
                         ,','
                        ) WITHIN GROUP (order by position)   
          from table(dbms_sqltune.extract_binds(ss.BIND_DATA)) 
          )  bind_datatype
*/
  from v$database d
  JOIN dba_hist_snapshot s ON s.dbid = d.dbid
  JOIN dba_hist_sqlstat ss ON ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id
                           AND ss.executions_delta > 0
  where s.end_interval_time >  SYSDATE - &&days 
    AND ss.sql_id LIKE CASE ss.force_matching_signature 
                            WHEN 0 THEN '&&sql_id'
                            ELSE '%'
                        END
    AND ss.force_matching_signature = '&&force_matching_signature'
    &&whereclause
ORDER BY s.end_interval_time
      , ss.instance_number
;


@@footer