PL/SQL Block with Cursor
611900Jul 21 2010 — edited Jul 23 2010I was trying to create a PL/SQL block where I am trying to call all the SQL Plans and then drop them one by one. Here is how I am doung -
SET SERVEROUT ON;
DECLARE
EXECSTR VARCHAR2(50);
v_plans_dropped pls_integer;
CURSOR C1 IS SELECT SQL_HANDLE FROM DBA_SQL_PLAN_BASELINES;
BEGIN
FOR I IN C1 LOOP
EXECSTR := ''''||I.SQL_HANDLE||'''';
v_plans_dropped:=dbms_spm.drop_sql_plan_baseline(sql_handle=>EXECSTR);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR while dropping SQL PLAN: ' || SQLCODE || ' : ' || SQLERRM);
END;
/
This is giving me error like - ERROR while dropping SQL PLAN: -38131 : ORA-38131: specified SQL handle
'SYS_SQL_1470897e7b72c982' does not exist
Whereas this SQL Plan is there in the baseline - SELECT sql_handle FROM dba_sql_plan_baselines;
SQL_HANDLE
--------------------------------------------------------------------------------
SYS_SQL_1470897e7b72c982
SYS_SQL_75ac336a8071cb06
SYS_SQL_269cdcd4862f8685
SYS_SQL_269cdcd4862f8685
SYS_SQL_0ffdb3bddbe422e2
SYS_SQL_14d81bbae4a7cc93
SYS_SQL_5c512b58e7795ba2
SYS_SQL_33515785e80b75d0
Above PL/SQL block should be same as like deleting it one by one -
DECLARE
v_plans_dropped pls_integer;
BEGIN
v_plans_dropped:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_1470897e7b72c982');
END;
/
If I run this then it will be sucessul but when I run under a PL/SQL block using cursor then it is giving me error ORA-38131.
Can you please tell me where I am going wrong here.
Thanks for your time!