Error while pinning pl/sql block
Hello,
I am getting an error while pinning a pl/sql block using dbms_shared_pool
Code:
SQL> l
1 select sql_text, address, hash_value from v$sqlarea
2* order by first_load_time desc
SQL>
SQL_TEXT ADDRESS HASH_VALUE
--------------------------------------------------------------------------------------
declare ss varchar2(100); cursor c1 is select object_name 036FD9C0 2702567367
from all_objects where object_type like 'PROC%'; begin open c1;
loop fetch c1 into ss; exit when c1%notfound; end loop;
close c1; end;
SQL> exec dbms_shared_pool.keep('036FD9C0,2702567367');
BEGIN dbms_shared_pool.keep('036FD9C0,2702567367'); END;
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: at line 1
Where I am going wrong? Is it possibe to load sql statement which is not in a pl/sql block or not in a funcion or procedure using the statement address and hash value? I get the same error if I try to pin a statement. I am able to pin procedure or function.
Please advice
Thanks
Sameer