Optimizer

How can I change the optimizer_mode with in a PL/SQL procedure?

It's untested, but this might work ...

Code:

---

begin
  execute immediate 'alter session set optimizer_mode=''first_rows''';
end;
/

---

slimdave, execute immediate worked nicely.

I was wondering if there was any oracle supplied package to do the same.

nope, not as far as i know, but you could always create your own procedure.