Wednesday, March 30, 2011

It's the little things....

That can really mess up your day.

I'm using this little script:

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

if substr(upper(v_flush),1,2) = 'BP' then
dbms_output.put_line ('*** Buffer Pool Flushed ***') ;
end if;
if substr(upper(v_flush),1,2) = 'SP' then
dbms_output.put_line ('*** Shared pool flushed ***') ;
end if;
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.

1 comment:

  1. Stupid computers! Always doing what we tell them to do, not what we want them to do.