It's the little things....

That can really mess up your day.

I'm using this little script:

declare
v_flush varchar2(3) := '&1';
err_mesg varchar2(250);

begin
if substr(upper(v_flush),1,2) = 'BP' then
sys.hotsos_pkg.flush_bp;
dbms_output.put_line ('*** Buffer Pool Flushed ***') ;
end if;
if substr(upper(v_flush),1,2) = 'SP' then
sys.hotsos_pkg.flush_sp;
dbms_output.put_line ('*** Shared pool flushed ***') ;
end if;
exception
when others then
err_mesg := SQLERRM;
dbms_output.put_line ('****** Error! '||err_mesg) ;
end ;
/

And it's not working, as in, it's not flushing the pools. I get no errors. For the life of me I can't figure it out. I put in several DBMS_OUTPUT.PUT_LINE commands to print out what is going on as it runs. Eventually I notice that the variable v_flush is set to literally &1 while this thing runs. WHAT?

It turned out that DEFINE had some how gotten turned OFF in the session. How I'm not exactly sure, but now this script file has:

set define on

at the top of it.