Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL Block with Cursor

611900Jul 21 2010 — edited Jul 23 2010
I 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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 20 2010
Added on Jul 21 2010
4 comments
1,480 views