Dynstatic SQL...

Here's Connor again...

Note that the following code has been "anonymised" to protect the guilty.

---

procedure P is
begin
   ...
   ...
   execute immediate 'drop  table T';
   execute immediate 'create table T as select * from ......';
   ...
   ...
   ...
   for i in ( select * from T ) loop
        ...
        ...
   end loop;
end;

Ah, a mix of dynamic and static references... Now how precisely did that compile? Nope, I'm not sure either.

If in doubt, test, test and test again

Thanks to Rob Baillie for the following example...

---

I was glancing through some legacy code today, and came across this.

It's funny how barnacles can accumulate when code changes over time.

            if r2.status_id = 3 then
                 v_gp := r2.rate;
            elsif r2.status_id in (11, 12) then
                if r2.type_id = 3 then
                    v_gp := r2.rate;
                else
                    v_gp := r2.rate;
                end if;
            else
                v_gp := r2.rate;
            end if;

Skip over the record being called r2 and work out what it actually does...

Oracle World Goes Sensible shock

You may be wondering WTF happened to all the WTFs, and so are we. It seems the Oracle world has been going through a rather depressing sensible season recently, in which nobody posts Pro*Ada code and wonders why it won't run at the SQL*Plus prompt, or suggests adding "AND 1=1" to any query to make it go faster. Even Mr Feuerstein seems to have deserted us.

Perhaps Mike Ault's foray into international economics cheered some of us up. He proposed an ingenious regulatory system requiring (I think) US grain, medicine and other key exporters to adjust their prices in response to international oil market fluctuations, on the grounds that certain oil producers are not taking America seriously enough. Now that'll teach Johnny Foreigner a lesson. "It is time for America to get tough", he adds. Oh dear.

On a more conventional note, a Mr Sahil Malik complained at great length on cdos about how hard it was to install 9i Personal Edition on his PC. At one point he fumed:

Larry Ellison IMHO has only one business idea - "Defeat bill gates and trap every programmer in matrix like pods powering oracle databases". WHAT THE HECK !! Time he matured up a bit.

I agree. Larry Ellison, if you're listening, you need to mature up a bit and forget the whole Matrix pod idea. We all know that ends badly.

Over on the OTN SQL Developer Forum, we were intrigued when one frustrated poster asked:

I cannot find clustered option for the indexes or PKs that I create in SQL Developer.

Where is the CLUSTERED check box in user interface?

Where indeed? Helpfully, Sharon from the SQL Developer team promised to get one added ASAP:

I have added an enhancement request to get this added to the interface in a future release.

We look forward to seeing what that does.