ORACLE-BASE Blog Aggregator

Not long ago - I wrote about some frustrations with the state of software ‘development’.  This morning I read an article written not too long ago "They Write the Right Stuff".  I liked it a lot.  Maybe not practical or reasonable for every piece of software (but then again, why not?) - but definitely sound techniques and processes for everyone.

The sections are

• The product is only as good as the plan for the product.  Ah, they are talking specifications, communication, documentation…
• The best teamwork is a healthy rivalry.  Indeed!  I’ve said before the best was to test your ability to recover in a DBA team would be to set up two teams - one is responsible for damaging a test database in any way they want to.  The other team is responsible for recovering from that catastrophe.  Next week - switch sides.  Not only fun but very enlightening (when I poll audiences, less than 5%, way less, have done a recovery in the last six months - could it be they might not be able to today if needed?).
• The database is the software base.  Now, they did not mean the database is the center of the application itself (I would say something like that) but rather the history, change control, reason for all change is.  The history, the legacy of the code is as important as the plan for the product.  You need to understand why things were done the way they were - in order to safely change it later.
• Don’t just fix the mistakes - fix whatever permitted the mistake in the first place.  My favorite one!  How many times will I have to hear in my life "we have to do X, but you cannot tell us to use method Y to do it - Y cannot be done".  I get that all of the time - tell me how to make it go faster, but don’t look at or mention touching the application.  The mistakes are typically to be found there - in the application (typically means 99.99999999999999999% give or take a small amount).

As an aside, anyone that knows me, knows my mantra - written many times:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL Statement, then do it in PL/SQL (as little PL/SQL as possible!).
• If you cannot do it in PL/SQL, try a Java Stored Procedure.  The number of times this is necessary is extremely rare today with Oracle9i and above.
• If you cannot do it in Java, do it in a C external procedure.  This is most frequently the approach when raw speed, or the use of a 3rd party API written in C is needed.
• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

Therefore - I just loved this Oracle-WTF.  And you know what - it pairs up with the "write stuff" article nicely.  I’ll bet you that that original stored procedure was not planned (no specs), peer reviewed (no health rivalry), change managed as it was tweaked over time, and until now - never "fixed".  Can you imagine how long it took to reverse engineer that into a single SQL statement (I’d guess a minimum of an hour - and likely more).  An hour well spent, but I know personally the frustration of that person for that hour - cursing every developer that touched the code before them.

And I cannot tell you how much I hate code like this:

    BEGIN        v_temp_runs := 0;        SELECT COUNT(*)        INTO   v_temp_runs        FROM   dual        WHERE  EXISTS               ( SELECT *                 FROM   temp_runs );    END;

    IF v_temp_runs > 0 THEN

Why count something and do something else if there was something to be counted?  JUST DO IT, if there is nothing there - SO WHAT?

At least the original code did not end in "when others then null;" - there is that.

I use a couple of venues to generate new material, new talking material every year.  Hotsos symposium has always been one, Oracle University Seminars have been another.  Oracle OpenWorld is definitely one as well.

Well, believe it or not, it is time for me to submit my abstract for Oracle OpenWorld already - even though it is not until September.  So, it is time to come up with yet another topic.  I’ve been known to do a “top ten” style presentation for a while at OpenWorld - the last few years have been top tens on 9i Release 2, 10g Releases 1 and 2 and most recently - 11g Release 1.  Intermixed with that was a database ‘worst practices’ presentation as well.

So, now - I’m looking for some fresh ideas.  A topic that can be well covered in an hour and would have broad general appeal.  In reality - if I get a couple of good ideas, I’ll probably generate the material for all of them but just pick one (maybe two) for presentation at OpenWorld. 

So, I invite you to submit an idea - doesn’t have to be fully fleshed out - just a concept, something you would find useful at a venue such as OpenWorld (or a presentation to a user group - as this is where this material will be used time and time again…)

Thanks in advance for any ideas!

But, we all know that is the true cause of the problem… The lack of use of bind variables - making special characters in names do nasty things like: (this is a quote)

… apostrophe is often mistaken for a piece of computer code, corrupting the system. …

Ah, yes, the nasty apostrophe, capable of corrupting entire systems.

It is sloppy programming (they got that right).  Lack of binds is a co-winner of first place "bad things to do" right along side "when others then null".

I still wish….

• when others would be removed from plsql
• triggers would be deprecated and removed
• autonomous transactions would start just raising errors
• literals in sql would raise an error

All of those bullets are hyperbole, but they make a point.  When I see "when others" in code, it is more often used wrong (in the order of 1000 to 1).  When I see a trigger developed to "enforce integrity" - it is almost always wrong.  The way people use autonomous transactions - almost always WRONG.  Literals in SQL - usually wrong (but not always, but here I would err on the side of caution - outlaw literals and let bind peeking put them back in later…)

I know, I’ve said this before.

You know, I’ll say it again.

Because I see the same four mistakes every day.  Every. Single. Day.  Especially the "when others" and lack of binds.  Followed by triggers.  Fortunately, most programmers never read to the autonomous transaction chapter…

because when you own it, you own the world.  You can literally do anything you want (given you have that privilege of course)…

Check this out.  Neat eh.

Control the command line and nothing will ever control you.  You have to understand something before you can do that stuff.

Besides, ASCII art is universal… (ctl-f for "A standard confirm")

ops$tkyte%ORA10GR2> CREATE TABLE hash_example  2  ( hash_key_column   date,  3    data              varchar2(20)  4  )  5  PARTITION BY HASH (hash_key_column)  6  ( partition part_1 ,  7    partition part_2 ,  8    partition part_3 ,  9    partition part_4 10  ) 11  /

Table created.

ops$tkyte%ORA10GR2> insert into hash_example  2  select sysdate+rownum, 'x'  3    from all_objects  4   where rownum <= 10000;

10000 rows created.

ops$tkyte%ORA10GR2>ops$tkyte%ORA10GR2> select pname, cnt,  2         substr( rpad('*',30*round( cnt/max(cnt)over(),2),'*'),1,30) hg  3    from  4  (  5  select uo.subobject_name pname, count(*) cnt  6    from user_objects uo, hash_example he  7   where uo.data_object_id = dbms_rowid.rowid_object(he.rowid)  8   group by uo.subobject_name  9  ) 10  order by pname 11  /

PNAME                                 CNT HG------------------------------ ---------- ------------------------------PART_1                               2398 ***************************PART_2                               2466 ****************************PART_3                               2563 ******************************PART_4                               2573 ******************************

I give a presentation on "worst practices" from time to time

One of the many sections in there regards source code control/configuration management (remember, this is a worst practice talk, not a best practice - so the slides are meant to be ironic, the opposite of what is true)

In there I talk about how many people don’t seem to consider their "database schema" (including code for goodness sake!) to be something that you put under source code control.  Nope, you just let everyone in there and let them make changes - in the database directly. 

They consider database code to be less than code - not worthy of being under any sort of change management.  That is why I liked this blog entry I read this morning.

They got it right - dead on.

Think about it…