Monday, 20 April 2015

Using an Oracle PL/SQL block in pre SQL

Using an Oracle PL/SQL block in pre SQL

Question :

Is it possible to use an Oracle anonymous block in pre SQL? I would prefer that over the overhead of having to create a stored procedure since the action being taken only takes place in one location.

 

Solution:

 

Should be possible - just put a backslash before each semi-colon. 
e.g.:
 

declare
 
cursor bitmaps is
 
select index_name
 
from user_indexes
 
where index_type = 'BITMAP'
 
and table_name in ('X', 'Y')\;
 
exec_immed varchar(100)\;
 
begin
 
FOR bitmaps_rec in bitmaps
 
LOOP
 
execute immediate 'alter index '||bitmaps_rec.index_name||' unusable'\;
 
END LOOP\;
 
end\;
 

No comments:

Post a Comment