@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display all SQL Profiles
*  Parameters : 
*          
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  16-May-12  Vishal Gupta  Created
*/

COLUMN  column1  FORMAT a120

UNDEFINE sql_profile_name
DEFINE sql_profile_name="&&1"


DEFINE rpad_length=15

PROMPT *****************************************************************
PROMPT *  Profile Details
PROMPT *****************************************************************

set heading off
SELECT /*+ ORDERED */ 
       RPAD('Name',&&rpad_length)          || ' : ' || so.name  || chr(10) ||
       RPAD('Description',&&rpad_length)   || ' : ' || sp.description  || chr(10) ||
       RPAD('Version',&&rpad_length)       || ' : ' || ad.version  || chr(10) ||
       RPAD('Category',&&rpad_length)      || ' : ' || sp.category || chr(10) ||
       RPAD('Force Matching',&&rpad_length)|| ' : ' || sp.force_matching  || chr(10) ||
       RPAD('Type',&&rpad_length)          || ' : ' || sp.type  || chr(10) ||
       RPAD('Status',&&rpad_length)        || ' : ' || sp.status  || chr(10) ||
       RPAD('Signature',&&rpad_length)     || ' : ' || so.signature  || chr(10) ||
       RPAD('Creator',&&rpad_length)       || ' : ' || ad.creator  || chr(10) ||
       RPAD('Creation Time',&&rpad_length) || ' : ' || to_char(sp.created,'DD-MON-YY HH24:MI:SS')   || chr(10) ||
       RPAD('Last Modified',&&rpad_length) || ' : ' || to_char(sp.last_modified,'DD-MON-YY HH24:MI:SS')   || chr(10) ||
       RPAD('Last Executed',&&rpad_length) || ' : ' || to_char(so.last_executed,'DD-MON-YY HH24:MI:SS')   || chr(10) ||
       RPAD('Last Verified',&&rpad_length) || ' : ' || to_char(ad.last_verified,'DD-MON-YY HH24:MI:SS')   || chr(10) ||
       RPAD('SQL Text',&&rpad_length)      || ' : ' ||  chr(10) ||
       sp.sql_text  || chr(10) ||
       ' ' column1
  FROM dba_sql_profiles sp
       JOIN sys.sqlobj$ so ON so.signature = sp.signature AND so.category  = sp.category AND so.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan  */ 
       JOIN sys.sqlobj$auxdata ad ON so.signature = ad.signature AND so.category  = ad.category  AND ad.obj_type = 1  /* 1 = SQLProfile, 2=SQL Plan  */ 
       JOIN sys.sql$text st ON so.signature = st.signature
       JOIN sys.sql$ sq ON so.signature = sq.signature 
WHERE 1=1
 AND upper(sp.name) = upper('&&sql_profile_name')
ORDER BY sp.last_modified DESC  
;  

set heading on

@@footer