-- 1. Load AWR into SQL Tuning Set(STS)
-- Drop SQL Tuning Set (STS)  
BEGIN 
  DBMS_SQLTUNE.DROP_SQLSET( SQLSET_NAME => 'VG_STS01' );  
END;  
/
  
-- Create SQL Tuning Set (STS)  
BEGIN 
  DBMS_SQLTUNE.CREATE_SQLSET( SQLSET_NAME => 'VG_STS01', DESCRIPTION => 'SQL Tuning Set for loading plan from AWR into SQL Plan Baseline');  
END;  
/

-- Load AWR plan into STS
DECLARE
    REF_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
     OPEN REF_CUR FOR
     SELECT VALUE(P) 
     FROM TABLE( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY ( BEGIN_SNAP => 16184 
                                                         , END_SNAP => 16186 
                                                         , BASIC_FILTER => ' SQL_ID = ''6h6cvx26whvny'' AND PLAN_HASH_VALUE = 2212718001  ' 
                                                         , ATTRIBUTE_LIST => 'ALL' 
                                                          ) 
                ) p  ;  
     BEGIN
     DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME => 'VG_STS01'
                             , POPULATE_CURSOR => REF_CUR  
                             ) ;
     EXCEPTION
        WHEN OTHERS THEN 
        NULL;
     END;   
     DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME => 'VG_STS01'
                             , POPULATE_CURSOR => REF_CUR  
                             ) ;
     commit;                        
END;
/

/*

-- Load AWR plan into STS
BEGIN
     OPEN :REF_CUR FOR
     SELECT VALUE(P) 
     FROM TABLE( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY ( BEGIN_SNAP => 16184 
                                                         , END_SNAP => 16186 
                                                         , BASIC_FILTER => '  SQL_ID = ''6h6cvx26whvny'' AND PLAN_HASH_VALUE = 2212718001  ' 
                                                         , ATTRIBUTE_LIST => 'ALL' 
                                                          ) 
                ) p  ;  
END;
/

print :ref_cur;

BEGIN
DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME => 'VG_STS01'
                             , POPULATE_CURSOR => :REF_CUR  
                             ) ;
END;
/

*/