The Tom Kyte Blog

Big Data...

2012-02-02T15:01:00.006-05:00

I'll be doing a web seminar on Big Data on February 16th at 10am Pacific Time. Here is the info:

Big Data Essentials: What You Need to Know, February 16th, 10:00 am – 1:30 pm PT

Big data is big news these days. But you don’t base IT investment decisions on magazine headlines.

Join us for the Big Data Online Forum to learn the essentials of big data—from the technology underlying it to real-world use cases. Oracle’s Tom Kyte, Cloudera CEO Mike Olson, and other industry thought leaders will be on hand to explain how big data can deliver revolutionary insight and competitive advantage.

You’ll get answers to tough questions surrounding big data, including:

What business insight can big data uncover?
How do you manage big data?
How do you integrate big data into decision-making?

All about Security - SQL Injection

2012-02-02T14:33:00.003-05:00

I recently did a web seminar on Oracle Database Security (you can see a replay of it here). We had over 1,300 live attendees (glad I couldn't see you all - that would be scary) and the feedback was pretty good.

We also received a few questions, well, actually - a lot of questions. I'm going to try to tackle them here bit by bit. I'm going to start with my favorite topic - questions centered around SQL Injection. I'll center on the core concepts around SQL Injection in this article and then do a followup article regarding the Oracle Database Firewall - a tool useful for detecting and blocking SQL Injection attacks.

During the presentation - I talked about how insidious SQL Injection is - and how hard it can be to detect. In fact, I've written about this before, in this article. The interesting thing about that article on injecting is the very last part of it, the section on "selective system grants". If you read that small section you'll see a comment "Note: Revised contentto prevent SQL injection for this procedure submitted by Roy Jorgensen.". What that means is - the original article I submitted had a SQL Injection bug in it - right after I just spent pages going over SQL Injection! That wasn't too embarrassing was it (it was). But it does point out how easy it is for a SQL Injection bug to sneak into code - even when the coder knows full well what SQL Injection is and how it happens!

Anyway, during the web seminar I talked about a slide I use - with a full stored procedure on it - that contains a SQL Injection bug. I ask the audience, usually full of developers and DBAs to tell me how the code can be SQL Injected.. I tell them right out - this code can be injected and if I were to put it in my schema and grant you execute on it - you could use this to read pretty much any table I own.

I usually hear crickets at this point in time, no hands, no volunteers. Here is the slide:

Note that the input to this procedure is a binary Oracle date - it is fixed length, 7 bytes of data - the century, year, month, day, hour, minute and second. The input is not a string, the input cannot contain things like "or 1=1" - typical SQL Injection attack strings. It can only contain an Oracle date. So - the question is - how can I 'trick' this stored procedure into showing me anything I want to see in the schema that owns the procedure (thus bypassing any and all security the application tier might have put in place - there are no restrictions on what I can and cannot see now).

Before we get there - let's talk about the bit of code that will be problematic - that is line 10. As noted there is a double implicit conversion going on there. That line of code is really:

Where created = to_date( to_char( p_date ) );

There is an implicit to_char on the date field in order to concatenate it to the query string. Then, at runtime there is an implicit to_date on the string we concatenated in so we can compare it to a date. This is a very common thing I see in code all of the time (implicit conversions) - but it is pure evil. Not only will we discover it is the cause of a SQL Injection issue - but here it is a logic bomb as well.

First of all - by default - that to_date( to_char() ) conversion will have a side effect of effectively truncating the time component from the date field. That is evil. If you wanted to truncate the time off - please use TRUNC() on the date - it is much faster, more efficient, and expresses clearly that you intend to truncate the time component. To_date(to_char()) does none of that. Secondly - the conversion by default will also lose the century. If you were trying to look for things created during the war of 1812 - you would lose, you cannot search for 1812 - it would become 2012 (well, right now as I write this it would be 2012 - in 38 years it will become 2112 and you won't be able to search for 2012 anymore...).

Also consider that I said "by default". By default the NLS_DATE_FORMAT is DD-MON-RR (currrently, it has been different in the past!). What happens to this code when someone decides to change it? Your application might well start querying up entirely different data!

So, the implicit conversion by itself is bad - but the real issue is the SQL Injection flaw. If you just run this procedure, by default - it certainly looks OK:

ops$tkyte%ORA11GR2> exec inj( sysdate )

select *

from all_users

where created = '02-FEB-12'

PL/SQL procedure successfully completed.

that looks OK - seems pretty safe - until, until someone who has read the documentation comes along. They might run your code like this:

ops$tkyte%ORA11GR2> alter session set

2 nls_date_format = 'dd-mon-yyyy"'' or ''a'' = ''a"';

Session altered.

ops$tkyte%ORA11GR2> exec inj( sysdate )

select *

from all_users

where created = '02-feb-2012' or 'a' = 'a'

A.....

EBRAPP.....

EBRTBLS.....

UTIL.....

USER2.....

PL/SQL procedure successfully completed.

Now that is surprising, you might not even know you could do that in an NLS_DATE_FORMAT. It is really hard to protect against something you don't even know you can do - isn't it? I've had people look at that example and scoff at it - saying "so what, they were allowed to see that table". Ok, take it a step further, I'd like to know what tables you own - so I can start querying them. I'll just do this:

ops$tkyte%ORA11GR2> alter session set

2 nls_date_format = '"''union select tname,0,null from tab--"';

Session altered.

ops$tkyte%ORA11GR2> exec inj( null )

Select *

from all_users

where created = ''union select tname,0,null from tab--'

....

Now you can see where this is going... I find one SQL Injection bug in one procedure and I've unlocked the entire schema.

So, the question now comes up - how do I protect myself from this? What can I do to ensure I'm not subject to SQL Injection in this code?

There are two ways - the hard way and the easy way.

The hard way involves writing code to validate everything and having serious code reviews of any code that uses string concatenation to build their SQL statements - any code that takes a parameter as input and concatenates it to a SQL query must be read and reviewed by many people - many people who will be super critical of the code. In this case, the resulting code would have to be:

where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'')';

You need to have a coding standard that says:

You shall never use implicit conversions ever, as in never.
You shall always use an explicit date mask with dates, as in every single time, you will not rely on defaults (because defaults can inject you and because defaults can radically modify your logic unintentionally!)

And now you have to comb through all of your code looking for these bad practices (you should anyway - you have major logic bombs just waiting to explode in your code if you rely on default NLS settings and implicit conversions).

The easy way however is the way to go. The easy way is - just use bind variables! If you use bind variables, you cannot be SQL Injected - this is true for PL/SQL, for Java, for any and all languages. If you use bind variables you cannot be SQL Injected - period. It is that simple, really and truly. If the code was:

7 l_query := '

8 select *

9 from all_users

10 where created = :x';

11 open c for l_query USING P_DATE;

there is no way the end user can trick that SQL query into becoming anything other than what it is - in fact, for this example, the code should have been:

cursor c is select * from all_users where created = p_date;

begin

open c;

...

and nothing more - it shouldn't have even been using dynamic SQL. In Java/C#/C++/etc - you would be using dynamic SQL and you should be using bind variables. So, that answered all of these questions I received:

• where can I find an illustration of SQL injection?

• can u share the sql injection demo code

• Can you share that SQL injection slide?

• Can you show a code example of the SQL injection bug that nobody noticed during your presentations?

• Can you show us or point us to the site of the example of SQL injection bug?

• Is SQL injection all about binding, or is there more?

Another question was:

• should application layer deal with the SQL injection attacks prevention as that layer understands what the proper data access patterns look like rather then database?

My response to that is - the application layer should definitely be aware of SQL Injection and use secure coding practices which would include:

always use a bind variable unless you have an excellent technical reason not to - and then you must submit your code for review to at least five people who do not like you - they must be motivated to rip your code apart, critically review it, make fun of it - so they find the bugs.

However - we need to also employ defense in depth - for when the inevitable bug slips through. When I next write about this - I'll be going over the Oracle Database Firewall - a tool that can provide at least one more layer of defense.

The last question on this topic was:

• What is the dbms_assert PL/SQL package? How does it help prevent SQL injection? Should my organization be using it?

For that - I'll just forward you onto an excellent paper on this subject written by Bryn Llewellyn. You can find that paper here.

Oracle Database Security...

2012-01-06T16:44:00.002-05:00

Oracle Database Security Best Practices Webcast Series

I’ll be kicking off a best practices webcast series this February 1^st at 10 AM Pacific. Join me as we discuss the threats every IT Database and Security administrator needs to be aware of, as well as best practices for securing your databases. We’ll cover how best to guard against SQL injection attacks, encrypt sensitive data, enforce least privilege and separation of duties, database auditing and masking non-production data.

http://event.on24.com/r.htm?e=390461&s=1&k=5F30645E675CBF55C8BDED9F3D28AE69&partnerref=blog1_sec_dbsecmulti

Happy (belated) New Year!

2012-01-05T10:39:00.000-05:00

Another year, another list of 'resolutions'. Welcome to twenty-twelve :)

I really liked this one (the followup)

2011-11-09T14:12:00.001-05:00

I recently wrote about an interesting question (not Oracle related!). I found the question interesting because it reminds me of so many questions I actually receive about Oracle. They are vague, incomplete, ambiguous, confusing at times.

The question I referred you to fell squarely in that category. It was:

If you choose an answer to this question at random, what is the chance you will be correct?
a) 25%
b) 50%
c) 60%
d) 25%

They are missing the correct answer in the list! And adding the correct answer would change the answer - so that it might be incorrect as well. Meaning - there is more missing here than supplied. You don't stand a chance of answering the question at all.

I put forth that the answer is "e) none of the above" - making the right answer "20%".

Looking at the problem, if you assume the right answer is 25% - because you have a 1 in 4 chance of selecting any one of the items - the probability you will get the right answer randomly is 50%. So, it cannot be 25% - if it were - it would be 50% (confusing, yes).

Moreover, if you assume 50% is correct, then the right answer is 25% - because the chance of picking 50% is 1 in 4.

If you assume 60% is correct - well, I don't know what to say.

The question itself seems to somehow affect the answer - the act of answering the question changes the answer. Sort of like when you run a program in the debugger and it works perfectly - but when you run it standalone it crashes (that has never happened to me, nope, not a chance...).

So, my answer is "e) none of the above"

A unique opportunity redux...

2011-11-07T14:44:00.001-05:00

Method-R will be hosting Chris Date for a series seminars during the last week of January, 2012. The last time he did this was three years ago - so the occurrences of these seminars is few and far between.

If you have the time to go - and are curious about doing SQL "right" - I'd highly recommend it. It is not often you have the opportunity to sit in a class like this...

Isn't it the truth...

2011-11-07T09:01:00.001-05:00

Every now and then an XKDC comic strip comes along that is just so 'real world' that it makes you stop and laugh out loud.

Like this one did for me.

How many times have I seen a complex, convoluted, hardly (or not at all) working bit of software - software that does just one, maybe two things - but is capable of being the OS, the database, and every program ever written if necessary. Just in case.

I guess that is why I'm still a Unix fan after all of these years. A big collection of very very specific tools that can be used together to do pretty much anything.

Although - there are two sides to every coin I guess... Sometimes (not often) - simple can be wrong too. It depends.

I really liked this one...

2011-10-31T12:53:00.001-04:00

I answer lots of questions about Oracle. Many of them are ambiguous, unclear, not fully specified, hard to follow. It is like unraveling a puzzle sometimes (ok, most of the times...)

That is why I found this question to be really amusing - especially after reading the comments. Lots of disagreement as to the meaning of the question!

So, what do you think the answer is... I'll post what I think after I see some feedback. It is a very very interesting question.

And it also points to why I think the answer to all questions is mostly "Why" or "It depends". We usually need a lot more information to answer what might appear to be simple questions.

A little more on Oracle OpenWorld...

2011-10-12T10:47:00.000-04:00

I've already posted information on where to get my OpenWorld materials (see the comments for how to access the content for other sessions as well). Now I'd like to invite you to listen in for more information as well. I know both of the speakers - I've podcasted with Willie Hardie in the past and Mark Townsend is the guy I work for.

So, get the full recap on key Database product announcements from Oracle OpenWorld. And chat LIVE with Oracle Database VPs Willie Hardie and Mark Townsend. This exclusively online event is Oct 18/19 (two sessions). Register today: http://bit.ly/qtzxTO

Following Oracle OpenWorld last week, do you fully understand how new database product announcements impact your projects? Do you have questions about big data, new engineered systems, or clouds? Attend our OpenWorld recap and live chat with Oracle Database experts on October 18/19. For details and to register, visit http://bit.ly/qtzxTO

Calling all DBA's - we still need your input...

2011-10-11T16:13:00.000-04:00

And time is running out. A month ago we started taking input for Oracle University and what sort of courses they should be providing. The survey is nearing an end - and while we received a great response to day, they would like to get more input.

So, if you have the time and inclination - check out http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=517

Thanks!

Coming to Dublin...

2011-10-11T11:32:00.001-04:00

I'll be coming over to Dublin, Ireland at the beginning of November. On November 2nd I'll be holding a free seminar from 9:00 to 15:30 at the Oracle offices. Everyone is invited to attend. You can register by downloading this file and emailing the contact. It also includes all of the information for the day. I'll be doing two of my sessions from Oracle Open World this year (Five things you probably didn't know about SQL / PLSQL), a talk on database options and then an open question and answer session at the end.

Hope to see you there!

Back from Open World...

2011-10-07T11:17:00.004-04:00

I just returned home from Oracle Open World 2011. It was good to meet some new faces and meet up with some old ones.

I had five sessions this year - one of them was a panel session (no content to post) - and all of them had good audiences and good questions at the end. I've posted the materials I used here for download. All of the powerpoints plus the scripts I used to demonstrate what I was talking about are included. It should all be on the Open World site as well (only the powerpoints, no separate scripts) as they collected all of the presentations from every session as it was happening and uploaded them.

Again - thanks to everyone that attended one of my sessions, I hope you got something useful out of it...

DBAs - We would like your input...

2011-09-09T12:37:00.000-04:00

Calling all DBAs - Oracle University would like your inputs in order to better tailor their course offerings around what you actually need to do, rather than what they think you need to do.

They've put a bit of thought into what data they need - you can read about their process for building this survey here.

The survey itself is available on-line for about the next six or so weeks. You can participate in the survey here.

So, if you've ever wanted to influence what is taught to the next generation of DBA's - or influence what sort of courses would be available for you - please take the time to fill out the survey.

Thanks!

45 years ago...

2011-09-08T21:03:00.004-04:00

Space: the final frontier. These are the voyages of the starship Enterprise. Its five-year mission: to explore strange new worlds, to seek out new life and new civilizations, to boldly go where no man has gone before.

It was 45 years ago today that those words were broadcast. One of my favorite TV series (Enterprise was probably my personal favorite of all of the genres) of all times.

I enjoyed reading this list of eight gadgets from Star Trek that made it into 'real life'. I never realized that the StarTAC from Motorola was named after Star Trek - that is sort of neat. I remember many years back, Verizon came into Oracle in Virginia to try to sell our division on a new phone plan. My manager and a coworker were there and the Verizon person asked us what type of phone we used. My manager had an AT&T phone that was pretty fancy for the time - he could walk up to his computer and the blue tooth would unlock it automatically. My coworker had an early Palm phone - it could do data and web and all. Mine - it was a Star TAC - I flipped it out and said "I can make phone calls with it". The guy from Verizon said "yeah, well the Smithsonian just called and they want your phone" (I got a new phone shortly after... A Palm phone, I used it for years).

Anyway - the list of things that were "Star Trek worthy" but are now real is longer than eight - but that blog post is fun to read. But as I wrote a while ago - and as someone commented on that post - we still need transporters!

And the winners are...

2011-09-01T14:03:00.005-04:00

To the first 10 that signed up for the Real World Performance Day using the "referred by Tom's blog" moniker - the books and shirts are in the mail:

I just sent them out today - you should get them soon. Looking forward to meeting:

Venky from Illinois
Kevin from Toronto
Tom from Toronto
Heather from Toronto
Stephan from Maryland
Daniel from North Carolina
Aaron from Maryland
Joe from Massachusetts
Steve from Wisconsin
Ted from Pennsylvania

(I guess Canadians type fast - Toronto being the big winner!)

Oracle OpenWorld...

2011-08-29T08:11:00.003-04:00

I'll be presenting at Oracle OpenWorld again this year. These are the session's I have scheduled right now:

Session ID: 14124

Session Title: General Session: What's New and Improved and Coming in Oracle Application Development
Venue / Room: Marriott Marquis - Salon 9
Date and Time: 10/3/11, 11:00 - 12:00

Session ID: 14120

Session Title: Five Things You Didn't Know About SQL
Venue / Room: Moscone South - 103
Date and Time: 10/4/11, 17:00 - 18:00

Session ID: 14643

Session Title: SQL Tuning Expert Roundtable
Venue / Room: Moscone South - 103
Date and Time: 10/5/11, 11:45 - 12:45

Session ID: 14122

Session Title: Five Things You Didn't Know About PL/SQL
Venue / Room: Moscone South - 103
Date and Time: 10/6/11, 10:30 - 11:30

Session ID: 14123

Session Title: All About Oracle Database Security
Venue / Room: Moscone South - 103
Date and Time: 10/6/11, 13:30 - 14:30

Additionally - I have some limited availability for one on one customer meetings during the week if interested. In order to arrange those, I would ask that you get in touch with your current Oracle sales contact and have them get in touch with me. I might not be able to meet with everyone that requests a meeting - but we can try!

Real World followup...

2011-08-29T08:01:00.004-04:00

I have just returned from Scotland and Germany where we did two more 'Real World' days. The turn out was great and the audiences were fantastic - lots of really good questions.

If you would like to hear about the session from an attendee - listen to this podcast by Peter Tran, Senior Performance Lead with PROS Revenue Management as he shares his thoughts and experiences from the IOUG’s Real World Performance Day.

We are planning quite a few dates in the US in the near future - see http://ioug.org/realworldperformance for a list of locations.

Register by Monday, August 29 (5pm Pacific) and add a co-worker or friend as your guest for free!

On step three of the registration process, enter your guest under the "BOGO" promotion!

I am happy to say we have our 10 winners (and more), I'll be getting in touch with them soon to deliver the book and T-shirt.

Something free and interesting sounding...

2011-08-06T09:21:00.002-04:00

Tanel Poder is doing another 'secret' free webinar. I've known Tanel for quite a few years now and he is a pretty smart guy. The topics he is presenting on this time center around scanning of data - and all of them look very interesting. Some are Exadata related but most of them would be useful for anyone.

See this page for details on the seminar topics and the 'secret' link to sign up (for free!). If you miss the 'live' session - he is going to record it for posterity.

A Day of Real World Performance Raffle...

2011-08-02T14:24:00.005-04:00

If you haven't heard - I'm participating in a series of seminars entitled "Real World Performance". It is a rather unique forum - with three simultaneous presenters and three screens. I find it more interesting than a seminar delivered by a single person - all day long. We haven't had anyone fall asleep yet - there is enough moving around and back and forth on the stage to keep you engaged all day long.

To get things jump started, I'm running a raffle of sorts. I'm going to give away the coolest t-shirt you'll ever own:

and a signed copy of

All you have to do is to sign up for the Real World Performance Day and select "Tom Kyte's Blog" from the "How did you hear about this event" element on the registration form. I'll be sending out a t-shirt and signed book to the first ten people that sign up this way. I'll be sending them out at the end of August. Apologies in advance to Germany, Australia and Scotland Real World participants - they are using a different registration system and I won't be able to track your sign-ups.

Here's a list of some podcasts we've done on the RWP tour - please listen to
get an idea of what takes place:

Tom Kyte, Oracle performance specialist and Host of
"AskTom.Oracle.Com" tells us what's in store at The IOUG's Real
World Performance Day.

Andy Flower, President of the Independent Oracle Users Group
(IOUG), discusses plans for COLLABORATE 11 and what folks can
expect when the Real World Performance Tour rolls to their town.

Hope to see you at one of them - we'll be doing more locations next year so if you don't see a city near you - they might still be on the drawing board.

Security Webinar...

2011-07-27T18:03:00.003-04:00

Tomorrow - July 28th at 10:00 PDT (13:00 East Coast US Time) - I'll be delivering a free Webinar on database security. If you are interested - you can register using this link: http://event.on24.com/r.htm?e=328712&s=1&k=D11C69EADAB1A25981D9E53966EE5D15&partnerref=blog1_sec_dbsecmulti

Hope to virtually see you there!

An update and a day in Dayton...

2011-07-12T20:18:00.003-04:00

I wrote previously about a neat Chrome add-in for AWR viewing - and it has recently been updated. You can read about that here...

Additionally - I've been a little tardy in updating asktom with a list of events I'll be doing (I'll get that filled in next week - it includes San Francisco USA, Sydney Australia, Edinburgh Scotland, Frankfurt Germany, Milwaukee WI, Chicago IL and Toronto in the next month and a half..). But more immediately is Dayton Ohio this thursday. You can read about that here...

Do check out that chrome plugin if you use chrome and view the HTML AWR reports, it really does make them better.

Coming soon to Dublin, London and Helsinki...

2011-05-05T17:06:00.002-04:00

I'll be doing some visits to Europe coming up soon.

On May 16th, I'll be doing a half day session in Dublin, Ireland. The information for this visit can be found here.

On May 17th, I'll be doing a day of "Real World Performance". You can read the writeup for that in Computer World.

And lastly, on May 19th, I'll be participating in the EMEA Harmony Conference in Helsinki, Finland. Chris Date will be there presenting as well! Should be interesting.

Two Things...

2011-04-28T16:11:00.002-04:00

The first is an "AWR Formatter" written by a friend of mine, Tyler Muth. It's pretty cool - works as a Chrome plugin - and it makes an AWR report a little more 'friendly' to use. It creates hot links for many of the wait events (so you know what they mean) and it summarizes up a lot of stuff - making the AWR report a lot more "interactive". Check it out and give him feedback on it if you have time.

The second is a book recommendation. If you've been looking for an excuse to learn APEX (application express) and wanted a book to work through it - here is one for you. I know most of the authors personally and can attest for their technical knowledge of APEX. I also really appreciate what they are doing with the royalties. See that link for what they've done. I knew both of the people they reference there.

A Marathon...

2011-03-25T10:17:00.003-04:00

If you don't see me updating asktom too much in the next two weeks - it'll be because:

I'll be starting the week in Brussels, Belgium for the OBUG on Tuesday March 29th
I'm in Paris, France in the middle of the week - Wednesday March 30th
And finishing in Madrid, Spain on the 31st of March

then I'm off to

Rome, Italy for Monday April 4th
Milan, Italy for Tuesday April 5th
flying up to Oslo, Norway for a 'geek cruise' on Thursday through Saturday

And then you'd think I'd be heading home - but not quite - I have a quick stop in Orlando Florida on Monday April 11th for IOUG Collaborate and then I go home :)

I can relate...

2011-03-21T19:13:00.002-04:00

It is not often a simple picture and caption will make me laugh out loud - but this did. I feel like that some days... Most days now I guess :)

Something you don't always see..

2011-03-16T08:38:00.002-04:00

Something worth money for free. Check it out - space is limited and will probably go pretty fast. Tanel knows his stuff.

Another Day, Another....

2011-03-14T10:20:00.002-04:00

Another day, another day of Real World Performance. We are bringing it East. The first four events where held in Los Angeles, San Diego, Seattle and Phoenix. The next are are in

Houston Texas, Wednesday April 20th
Atlanta Georgia, Friday April 22nd

Click on the link above to get more information and see some feedback from the first four events. It is a day long event - with early bird registration breaks if you register before March 18th! It'll be $75 off if you register early. That is just $100 for IOUG members and $150 for non-members. Given that you get a delicious box lunch as part of the deal - that's a pretty good price.

This is being done in conjunction with the IOUG - they are organizing the logistics, we are doing the talking. After March 18th, it'll be $175USD for IOUG members and $225USD for non-members.

Very Nice...

2011-03-11T11:26:00.001-05:00

Recently I wrote about a Chrome plugin that allowed you to block sites from being returned in google search results.

They have gone a step better - they've made that functionality available to anyone logged into google. With any browser! Very nice.

Interesting Read Followup…

2011-02-18T16:27:00.001-05:00

Two days ago I wrote about an interesting security fiasco and said I’d follow up on it in a day or two – so here we are.

Just to recap – I wrote previously:

As a quick test - see if you can

determine how the following bit of code can be attacked
what might be the outcome of this attack - what might be compromised on your server if this code were attacked, what could they do with it?
how to best protect against that attack
how else - short of the "best" - would you protect against the attack


create or replace procedure inj( p_date in date )
as
l_rec   all_users%rowtype;
c       sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' ||p_date ||'''';

dbms_output.put_line( l_query );
open c for l_query;

for i in 1 .. 5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line( l_rec.username || '.....' );
end loop;
close c;
end;

In the comments to that blog entry we did get the answers bit by bit, I’ll summarize them here.

For #1 and #2, determine how the following bit of code can be attacked and what the outcome might be, we saw in the comments that all it took was the ability to set your NLS_DATE_FORMAT. A capability anyone with CREATE SESSION has. For example, if we set the NLS_DATE_FORMAT and run the procedure:

ops$tkyte%ORA11GR2> alter session set

2 nls_date_format = '"''union select tname,0,null from tab--"';

Session altered.

ops$tkyte%ORA11GR2>

ops$tkyte%ORA11GR2> exec inj(sysdate)

select *

from all_users

where created = ''union select tname,0,null from

tab--'

BIG_AUDIT_TABLE.....

BIG_TABLE.....

USER_PW.....

C1.....

C2.....

PL/SQL procedure successfully completed.

As you can see – instead of reading ALL_USERS, we are now reading TAB – and seeing what tables might exist there. Now, I cannot query USER_PW – that is owned by the owner of the procedure and I have no select on it – but – we’ll be able to read it anyway since this stored procedure can be used to query any table in the schema that owns it! But first, we’ll need to see the column names – we don’t know those yet.

ops$tkyte%ORA11GR2> alter session set

2 nls_date_format = '"''union select tname||cname,0,null from col--"';

Session altered.

ops$tkyte%ORA11GR2> exec inj(sysdate)

select *

from all_users

where created = ''union select tname||cname,0,null

from col—'

USER_PWPW...

USER_PWUNAME...

I think you are getting the picture – using a bit of trickery – we can query up many things in that schema – discovering what we need as we go along.

If I have CREATE PROCEDURE (say a disgruntled developer, or a developer that just likes to be able to do stuff on the sly…) – I can go much further. Consider this:

ops$tkyte%ORA11GR2> grant execute on inj to scott;

Grant succeeded.

ops$tkyte%ORA11GR2> connect scott/tiger

Connected.

scott%ORA11GR2> create or replace function chgpw return varchar2

2 AUTHID CURRENT_USER

3 as

4          pragma autonomous_transaction;

5 begin

6          execute immediate 'alter user ops$tkyte identified by barfoo';

7          return 'got you';

8 end;

9 /

Function created.

scott%ORA11GR2> grant execute on chgpw to public;

Grant succeeded.

scott%ORA11GR2>

scott%ORA11GR2>

scott%ORA11GR2> connect ops$tkyte/barfoo

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

scott%ORA11GR2> connect scott/tiger

Connected.

scott%ORA11GR2> alter session set

2 nls_date_format = '"''union select scott.chgpw,0,null from dual--"';

Session altered.

scott%ORA11GR2> exec ops$tkyte.inj(sysdate)

select *

from all_users

where created = ''union select scott.chgpw,0,null from

dual--'

got you.....

PL/SQL procedure successfully completed.

scott%ORA11GR2> connect ops$tkyte/barfoo

Connected.

Anyone with create procedure and create session can kidnap that account. This is NOT a bug in Oracle, this is not a bug in the database – this is everything working as expected. This is a bug in your developed code.

Now, what about #3 and #4 – how can we protect against that attack? The easiest answer is always “use bind variables”. If you use bind variables in your SQL, your SQL cannot be injected. It is that simple.

If you use bind variables, your SQL cannot be injected.

SQL can only be injected when you concatenate inputs from the outside into your SQL. If you do not concatenate these inputs, they cannot become part of your SQL.

Now, what if you couldn’t use bind variables (I’m hard pressed to come up with a case whereby that is actually true!) or didn’t want to use bind variables (as might be true in a data mart/data warehouse). What then? In this case – you would never rely on an implicit conversion. This bit of code:

where created = ‘’’ || p_date || ‘’’’;

relies on an implicit conversion from a string to a date. Implicit conversion are evil incarnate – if you see them in your code or others code – fix it right away. Many bad things can happen – you’ve seen one above. One possible fix would be to use this code:

where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'' )';

That would make SQL Injection for that date input impossible – the ALTER SESSION SET NLS_DATE_FORMAT would have no impact on us anymore.

In other cases where the input is a string – we could use the DBMS_ASSERT package.

But always remember this:

If you use bind variables, your SQL cannot be injected.

It is really that simple!!!

For Vienna and Tallinn...

2011-02-18T08:09:00.002-05:00

If you are near Vienna or Tallinn - you have an opportunity to attend an excellent two day seminar hosted by Oracle University - but delivered by Richard Foote. If you really want to know all about indexing in Oracle - this seminar will deliver.

I know Richard really knows his stuff - and I've seen him present many times before. This will be a seriously good event. The linked to page has linked to the registration pages.

An Interesting Read...

2011-02-16T07:53:00.006-05:00

I found this article on the 'security' firm HBGary to be very interesting.

They were brought down by 'Anonymous' recently, in a completely embarrassing fashion (for a security firm, for sure).

What I found most interesting was how it all started and how easy it was....

It all started with a simple SQL Injection attack on their custom Content Management System (CMS). By finding a small exploit in that application, they gained access to the usernames and hashed passwords. Since the hashed passwords were neither salted, nor exceptionally well hashed - simple rainbow tables were used to figure out the (simple) passwords of a few power users. Once they had the passwords to the CMS - they then discovered some power users (at a security firm) liked to reuse their passwords over and over on many systems. That fact in turn gave them access to their email - where they discovered yet more passwords (sent in emails) - including the root password to a powerful internal machine. Given that information - and improper ssh configuration - they were able to "socially engineer" (over email) remote access to this machine - to which they now had root... And that was the end of that.

An internet security firm - brought down - by not following the most *basic* of security principals.

And all because of - SQL Injection... If you don't use bind variables - you are susceptible to it. If you accept input from an end user and concatenate it into your SQL, you are subject to SQL Injection. If you use bind variables - if you do not dynamically construct your SQL at runtime - you are not subject to it. It is that simple.

I say this a lot:

"it is much harder to write code that doesn't use binds than it is to write code that uses binds".

To which I get a lot of confused stares - for all of the developers "know" that if they use binds - they have to write MORE code - not less. And MORE = harder -right?

Wrong, 100% wrong. If you do not use binds, you have to write more code than if you use them. The code you have to write is the code to ensure with 100% degree of certainty that your inputs from the end user are valid, are safe, will not subject you to SQL Injection. And that is non-trivial. The real kicker is - after you write that code - you better submit it for review to at least five people that do not like you (that last bit is important). They have to be SUPER critical of the code and subject it to rigorous review.

As a quick test - see if you can

determine how the following bit of code can be attacked
what might be the outcome of this attack - what might be compromised on your server if this code were attacked, what could they do with it?
how to best protect against that attack
how else - short of the "best" - would you protect against the attack


create or replace procedure inj( p_date in date )
as
l_rec   all_users%rowtype;
c       sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' ||p_date ||'''';

dbms_output.put_line( l_query );
open c for l_query;

for i in 1 .. 5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line( l_rec.username || '.....' );
end loop;
close c;
end;

I'll post my answers to 1-4 tomorrow or the next day.

Think about it - how many of the developers you work with would even know that bit of code was easily attacked? Not many in my experience (I wouldn't have seen it right off until just a few years ago by the way - no magic here...)

Searching on Google just got a lot safer...

2011-02-15T07:13:00.003-05:00

If you use Chrome anyway. If you haven't tried Chrome - give it a whirl, I was a diehard Firefox user until I tried out Chrome.

There is an extension that can block sites on Google Search. It is called the "Personal Blocklist". Within seconds of installing it - the annoying sites that use whatever technique they can to get to the top of a Google Search were blocked (by me, you just click on "block " in the search result and they disappear!)

You can opt to show the blocked results at anytime - so if you wanted to see them - they are easily there, but hidden.

Very cool - very very cool. There was at least one or two sites I blocked immediately - including the annoying ones that pretend to show answers but then want you to log in to read them (sometimes with a paywall).

But what is even worse...

2011-02-14T09:38:00.000-05:00

Is that they didn't use any bind variables :(

Deferred Segment Creation...

2011-02-14T09:09:00.003-05:00

Something I learned new this morning...

Oracle 11g introduced the new feature "deferred segment creation". In a nutshell - it is a new (default!) feature whereby when you create a new table - no segment is created, no initial extent is allocated, no storage is reserved.

The design goal was to prevent hundreds or thousands of segments being created by a 3rd party application that only uses 100 of the tables it creates. Many 3rd party applications create every possible table they might use - only to use 100 of them given the feature set you use.

So, the upside of this feature is that you save space, you don't clutter up your data dictionary. Sounds all good - but could there be downsides? Anytime the default way the database operates changes - there could be downsides.

One 'obvious' side effect of deferred segment creation could be seen in an installation script that installs all of its tables, indexes, etc and then counts USER_SEGMENTS rows to ensure everything was installed. The count would not be ZERO instead of however many segments were created.

Another one, one that I just became aware of this morning, is that you can now create a table in a tablespace which you have NO QUOTA on. In the past - you would expect this as the logical outcome of not having any quota on a tablespace:


ops$tkyte%ORA10GR2> create user a identified by a default tablespace users;
User created.

ops$tkyte%ORA10GR2> grant create session, create table to a;
Grant succeeded.

ops$tkyte%ORA10GR2> connect a/a
Connected.

a%ORA10GR2> create table t ( x int primary key );
create table t ( x int primary key )
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

However in 11g - you'll find this behavior instead:


ops$tkyte%ORA11GR2> create user a identified by a default tablespace users;
User created.

ops$tkyte%ORA11GR2> grant create session, create table to a;
Grant succeeded.

ops$tkyte%ORA11GR2> connect a/a
Connected.

a%ORA11GR2> create table t ( x int primary key );
Table created.

I just created a table in a tablespace I have no quota on - successfully. Or did I?


a%ORA11GR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Apparently - not really. The table created - but the segments cannot be created. The ORA-1950 is not an error you would be expecting on an INSERT - you might expect "unable to extend", but not an ORA-1950 in general.

You should note that you can change this default behavior - that is turn off deferred segment creation either

at the database level via your init/spfile
at the session level via "alter session set deferred_segment_creation = false;"
statement by statement via "create table t ( x int ) segment creation immediate;"

if you like. Things change over time...

Getting Started...

2011-02-12T17:27:00.003-05:00

An oft asked question is: "how do I get started". As in getting started in IT, in programming, in database development - whatever.

You can check out some answers to that question from various opinion holders - including people such as Jonathan Lewis, Arup Nanda, myself and others in this months NoCOUG Journal.

This months journal is "unique" and somewhat special to me. See if you can guess why. I'll make it interesting - if you can post a comment here stating why this months journal is special (to me) - I'll send you an autographed copy of "Expert Oracle Database Architecture - 2nd edition". You'll need to include some method of contacting you (suggest a disposable email address if you don't want to post your real one).

The NoCOUG Journal team is of course not invited to this little contest as they already know the answer :)

UPDATED: Congratulations to Dave K. for correctly pointing out that my son Alan took the picture that is the cover of the journal this month!

About Database Security...

2011-02-08T15:26:00.003-05:00

I'll be doing a webcast on Thursday Feb. 24th at 9:30am (PST) regarding "How Secure is your Enterprise Data?". It'll be just about 30 minutes long.

I won't be the only speaker - I'm on second at 9:30am (PST). Anyone can "attend" (since it is virtual after all).

Hope to "see" you there...

A short podcast...

2011-02-04T06:34:00.002-05:00

We've just posted a short podcast with more information about the Real World Performance days I'm participating in. We are starting on the west coast of the US and will be presenting across the country over the next few months - eventually ending up in the UK.

For more information or to sign up - goto http://www.ioug.org/ and select Events -> A day of Real World Performance from the menu.

Hope to see you there!

Looks right to me...

2011-01-27T17:03:00.001-05:00

I found this amusing... Not too far from the truth regarding how many resumes are put together ;)

A Day of Real World Performance...

2011-01-26T17:54:00.004-05:00

Coming soon to San Diego, Los Angeles, Seattle and Phoenix - A Day of Real World Performance. I and two of my cohorts will be presenting day long seminars (all three of us, at the same time, on the same stage) in those four cities.

This is being done in conjunction with the IOUG - they are organizing the logistics, we are doing the talking. It'll be $175USD for IOUG members and $225USD for non-members. You get lunch with this so it is definitely worth it :)

For information - goto http://ioug.org/ and click on the Events menu and select "a day of real world performance"

Hope to see you there!

A couple of road trips coming up in the US...

2011-01-19T15:31:00.002-05:00

I did quite a bit of international travel last year - not that I ignored North America, but I definitely visited more user groups and conferences and customer events across the ocean than near to home.

This year is starting off different. I'll be in Cincinnati, Vancouver, Seattle and Denver next week (see http://asktom.oracle.com for the links to those events).

Right after that in February - I'll be in San Diego, Los Angeles, Seattle and Phoenix for an interesting IOUG set of events. The URLs for those will be posted really soon - but mark the calendar for the week of Feb 21st for those. The format is rather different from the norm - there will be three of us presenting simultaneously. The cast will consist of Andrew Holdsworth (he runs our real world performance group), Graham Wood - the father of ASH and AWR in many respects and myself. We don't always agree (which makes it more interesting) on the nitty gritty details - but as you'll see we are ready and willing to talk it out. Once we've done these first four - we'll have more in the making in the other parts of the US - working the west coast first but will be doing the entire country (that is the plan). I'll keep you updated on that.

In March - I hit Dallas for the annual Hotsos Symposium. This is a conference I always try to make (only missed one of them!) and the list of speakers and topics this year looks awesome. It is definitely something to check out - the training day to be given by Karen Morton is sure to be a really good add on as well if you can stay the full four days. I've found the training days to be something well worth considering attending. To get a full day from someone rather than just session after session with different speakers puts things in a different perspective. In a day someone can deliver content that just isn't possible in a single session environment.

The end of an era...

2010-12-16T22:31:00.002-05:00

Before google - there was altavista. And now altavista, originally of DEC, is finally shutting down. I remember it well - the origins of babelfish and the best search engine of its time.

Things have certainly changed in the last 15 years or so. I can still remember getting to the "end" of Yahoo - back when it was small enough to do that. It was before yahoo got this big - back when it was a directory and not a "portal" (before "portal" was part of our common nomenclature). Back when the Yahoo random link was the "stumbleupon" of its time.

Clicking through that old set of yahoo pages can be interesting - the early ads are still there - "devour the web in almost half the time" - "Rockwell k56flex is here". I remember my first 56kbs modem - it was amazing in its time :) And it was a Rockwell.

Well said...

2010-12-16T09:48:00.001-05:00

I have nothing to add to this...

Well said.

A tiny challenge...

2010-12-10T15:46:00.001-05:00

Check out this asktom post and see if you can answer "why that probably won't ora-1555".

A follow up...

2010-12-03T22:52:00.002-05:00

A follow up to my last post. I think this guy just hit the nail on the head. I've read some of his stuff in the past. He breaks down and explains security issues pretty darn good. I think he got this idea (Washington Monument) right too.

Stuff happens. There are crazy people out there. There is a fine line between being safe and being paranoid. Maybe not even that fine. We now know that if a hijacking takes place, you don't sit in your seat - you rush the people trying to do it and pummel them. The cockpit doors being reinforced is the greatest security measure ever - and more than enough. The rest of it all - just intrusive and sometimes just embarrassing.

You have to draw a line between reasonable security and "we'll reactively respond to every new tactic as it comes along".

My big fear - they start doing this (the 'security' thing) for monuments, buses, subways, trains, state parks, restaurants, movies, etc.

I've flown over 200,000 miles this year - that is about 10 working weeks in the air - that involves a lot of airports. I know how intrusive this stuff can be. I would hate to see the "security stuff" impose itself on my other 40 some weeks (and everyone else's time).

I don't know why, but I'm reminded of one of my favorite movies. Brazil.

I might have to move...

2010-11-17T17:11:00.006-05:00

Nothing about technology or the database here. If you aren't interested in my thoughts about things outside of that - stop reading, go away.

I lived next to New Jersey for much of my life (1972-1987)..

I lived in New Jersey for more than a year (commuted there for a year, lived there for a year - 1992-1993).

I might have to move back. Because of this (NJ is not the only one, Minnesota is looking into it as well). Bravo I say. I fly *a lot* and I love flying *out*of country - because I know all of my flights that week (after the first one) - until the last one back home - will be "nice" ones.

Because:

I don't have to take off my shoes.
I don't have to empty my entire backpack for someone.
I don't have to be naked (in a picture for someone, somewhere).
I don't have to be felt up all over.
I don't have to be insulted in many ways and forms.

Until I come home that is - leaving and coming back - that is the problem these days. Didn't used to be that way, but it is now. It is easier for me to go from one foreign country to another than it is for me to go from state to state in the US. Seriously. I'd rather go to another country and fly from place to place than in the US - is it that much of a hassle. I feel the need to get to an airport with at least 2 hours, if not 3 to be safe, "just in case something new and special" will happen to me that time.

It isn't about safety anymore - it's about "let us react to the latest thing that happened and pretend that makes everyone feel better about themselves"

I've flown in/out of Israel more than a few times. They have it right. I've never stood in their lines (and they have lots of traffic) for more than 30 minutes. And I've never been subjected to what I am in the US. Just saying. They seem to be pretty darn secure. I've seen every layer of their security and experienced it. Pretty innocuous - yet daunting. But definitely not embarrassing or intrusive.

I'm #2....

2010-10-06T15:51:00.002-04:00

Read this article

I am so #2 - they could have started and finished with #2 in my opinion.

The rest of the points are not bad - but #2 hit the nail on the head!

In my "what are we still doing wrong" talk - I talk about #2 for a long time - the 'business' telling the technologist HOW to do something - not telling them WHAT they need to do. We know how to do things, they know what they need to do - the opposite is not necessarily true...

Mr Trace...

2010-08-09T11:56:00.003-04:00

This looks interesting http://carymillsap.blogspot.com/2010/08/mister-trace.html.

I always like software that comes with a free trial too - I always hesitate to try something out that costs a couple of dollars, even if the amount is small - because I might not like it and then what.

So, no risk really - if you try it - and have an opinion about it - feel free to post your comments either way...

I guess I should qualify that "post your comments". How about - post your informed, backed up with some points, comments :)

Another SQL*Plus thing I learned...

2010-06-24T16:22:00.004-04:00

In the past, when you exit SQL*Plus, it would always commit - there was an implicit "commit work" issued for you right before it would disconnect your session.

It has always been that way. It doesn't have to be that way anymore as of 11g Release 2.

There is a new exitcommit setting - it defaults to ON which is the way it has always worked. But now you can set it to OFF which implies that a rollback will be issued instead of commit.

Things change over time... If you rely on SQL*Plus committing upon exit - if you have a script that relies on that, bear in mind it is no longer "a fact", it is highly probable that SQL*Plus will commit when you exit - but not a sure thing like it used to be. Yet another new thing I learned while updating the book Expert Oracle Database Architecture. I was just reminded of it today while I was going over the proofs of the chapters...

See http://download.oracle.com/docs/cd/E11882_01/server.112/e10823/ch_twelve040.htm#BABEGEGC for details on exitcommit.

I learned I don't think I like this...

2010-06-21T21:07:00.006-04:00

I was reading around, stumbled on an article from Dr Dobb's online (I have a long history with Dr Dobb's - had it not been for them - you wouldn't be reading this!). The article described a 'feature' of Windows 7 that I have mixed feelings on. The same sort of mixed (well, not so mixed, I lean far to one side on the use of this feature) feelings I have for cursor_sharing being set to anything other than EXACT.

Here is the article.

Ok, so why don't I like it? It seems to be a way to 'self correct' a program. It "seems" like a "good thing".

I don't like it because it won't help get the problem fixed (same with cursor_sharing :( ). In fact, it will promote *more* code being developed that suffers from heap overwrites. It lets bad developers develop bad code even faster and distribute it - thinking they are seriously good developers. That is, it leads to delusion and the bad coders getting more senior without learning from their mistakes.

In short, it instills a false sense of "hey, I'm pretty good" in developers that probably shouldn't have that sense.

It could definitely lead to some really strange issues - think about it, a program that used to crash - stops crashing - for a while - then crashes more (as the overwrite occasionally gets bigger than normal, requiring more "pad" bytes). And who knows how allowing a memory overwrite to propagate into other bits of the code will affect it. I prefer code that works right - not code that sometimes seems to work.

I'm reminded of a code review I did some 20 years ago. I asked the developer "why do you have this massive static array defined - we don't seem to use it". The answer "if you take it out, the program crashes, the compiler must have a bug". The look on my face - I wish I had a picture - it would have been priceless.

I'm not a fan of this "let's try to fix it for you and let you pretend you know what you're doing" approach to software. We rely on software way too much.

Oh well, just a 30 second thought - I just read the article and felt the need to gripe... Things like this scare me.

I have to say - I wrote something similar myself some many years ago . It was a C library I called xalloc. It replaced the malloc, calloc, realloc, free, etc functions of C. It worked by allocating (or freeing) the requested memory and adding a few bytes. It would set some bytes at the FRONT and the END of the allocated memory, set some bytes to represent the source code file and line number that allocated the memory, and return a pointer to the memory to be used by the program. Every time you called any of the xalloc functions - it would inspect the allocated memory (all of it) and CRASH the program if any of the magic bytes in front/at the end of the memory block had been changed. When the program exited - it would report on all allocated memory that wasn't freed. You could turn off the checking with an environment variable if you wanted, but it was always ready to be "on". I made everyone that worked in my team use it - it saved us countless hours (and it found the bug in the code of the person that needed to allocate that big array in a few seconds)...

My approach differed from Windows 7 in that I would prefer a program to crash and burn immediately rather than live for another second if it made such a grievous error. I'd still rather the program die than continue today...

What I learned about deadlines...

2010-06-09T10:25:00.002-04:00

I learned that I am not the only one :) Seth's blog is one of the ones I read every time. They are short, to the point and almost always meaningful to me. Deadlines are the greatest motivator for me - if I do not have a deadline for something, I can pretty much guarantee you I will not finish it. I set my own little deadlines for things just to get finished. Whenever someone asks me to do something for them - write a foreword, make a recommendation, whatever - I typically say "sure and what is the drop dead date". If they know me, they'll give me a date before the true 'drop dead' just to have it in a timely fashion (because the odds they see it before then are slim to none).

Speaking of deadlines, I just finished the 2nd edition of Expert Oracle Database Architecture. Right now, this minute. Just have to dot I's and cross T's now - a few final copy edits and it'll be done. This will be the blurb on the back of the book (which you can expect to see soon)

Expert Oracle Database Architecture

Dear Reader,
I have a simple philosophy when it comes to the Oracle database: you can treat it as a black box and just stick data into it, or you can understand how it works and exploit it fully. If you choose the former, you will, at best, waste money and miss the potential of your IT environment. At worst, you will create nonscalable and incorrectly implemented applications—ones that damage your data integrity and, in short, give incorrect information. If you choose to understand exactly how the Oracle database platform should be used, then you will find that there are few information management problems that you cannot solve quickly and elegantly.

Expert Oracle Database Architecture is a book that explores and defines the Oracle database. In this book I’ve selected what I consider to be the most important Oracle architecture features, and I teach them in a proof-by-example manner, explaining not only what each feature is, but also how it works, how to implement software using it, and the common pitfalls associated with it. In this second edition, I’ve added new material reflecting the way that Oracle Database 11g Release 2 works, updated stories about implementation pitfalls, and new capabilities in the current release of the database. The number of changes between the first and second editions of this book might surprise you. Many times as I was updating the material – I myself was surprised to discover changes in the way Oracle worked that I was not yet aware of. In addition to updating the material to reflect the manner in which Oracle Database 11g Release 2 works – I’ve added an entirely new chapter on data encryption. Oracle Database 10g Release 2 added a key new capability – transparent column encryption – and Oracle Database 11g Release 1 introduced transparent tablespace encryption. This new chapter takes a look at the implementation details of these two key features as well as manual approaches to data encryption.

This book is a reflection of what I do every day. The material within covers topics and questions that I see people continually struggling with, and I cover these issues from a perspective of "When I use this, I do it this way." This book is the culmination of many years’ experience using the Oracle database, in myriad situations. Ultimately, its goal is to help DBAs and developers work together to build correct, high-performance, and scalable Oracle applications.

Thanks and enjoy!

What I learned about "Security"

2010-05-21T03:14:00.001-04:00

I travel a lot. Because of that, I use 'hotspots' all over the place. I am connecting through Germany right now and had to sign up for a T-Mobile hotspot. They require you set up an account - to buy a 60 minute pass (I don't really like that, I don't want an account but they make you).

So, I set up my account - username, password - credit card information, etc. Get logged in and immediately receive an email. I've received this email before (because I always have to set up a new account since I can never remember what my 'old' account was) . It was the standard "welcome to T-Mobile" sort of email, but it always contains this (I've written to them before - that is like sending email to a bit bucket, no response, no action). Here is the email (xxxxx represents information I:

From - Fri May 21 09:05:34 2010
X-Account-Key: account5
X-UIDL: AHxxafafdafda
X-Mozilla-Status: 0001
X-Mozilla-Status2: 00000000
X-Mozilla-Keys:                                                                                
X-Apparently-To: xxxxxx@yahoo.com via 206.190.49.114; Fri, 21 May 2010 00:04:39 -0700
Received-SPF: none (mta1056.mail.mud.yahoo.com: domain of noreply-wlan@t-mobile.net does not designate permitted sender hosts)
X-Originating-IP: [193.254.174.32]
Authentication-Results: mta1056.mail.mud.yahoo.com  from=t-mobile.net; domainkeys=neutral (no sig);  from=t-mobile.net; dkim=neutral (no sig)
Received: from 127.0.0.1  (EHLO wlansmtp.t-mobile.net) (193.254.174.32)
 by mta1056.mail.mud.yahoo.com with SMTP; Fri, 21 May 2010 00:04:39 -0700
Received: from kxsnsrg2 (kxsnsrg1 [172.28.76.134])
 by wlansmtp.t-mobile.net (Postfix) with ESMTP id 37BDD6716
 for ; Fri, 21 May 2010 09:04:37 +0200 (CEST)
Date: Fri, 21 May 2010 09:04:37 +0200
From: noreply-wlan@t-mobile.net
Message-Id: <1274425477.9165@kxsnsrg2>
To: xxxxxxx@yahoo.com
Subject: T-Mobile welcomes you to your new HotSpot Pass Account

T-Mobile welcomes you to your new HotSpot Pass Account.  The password for your
new account is XXXXXXXXX

Yes, that is right, they emailed my password - over unencrypted email, for no apparently good reason at all. Why??? Why would they do this??? What is the point? What is the reason?

Why am I posting this? Well, maybe they'll read or hear about it this way and change it. I found this funny - this is their FAQ:

https://hotspot.t-mobile.net/TMD/en_GB/web/security/index.html#1

Is the HotSpot registration (log in) secure?

Yes, because the access details are transmitted in code to the T-Home / T-Mobile HotSpots. The code that is used is SSL. The software for this is integrated into the browser. If this is not the case, you can update your browser. The relevant downloads are available from the browser provider.
By using our HotSpot Manager, which automatically logs onto T-Home / T-Mobile HotSpots, you can be assured that the registration details are only transmitted to a confidential hot spot web portal.

Well, that is not quite true is it. You can also be assured that your password will be transmitted to everyone on the planet in clear text via good old email.

In the year 2010, you would think we'd know better.

They shouldn't be STORING my password let alone EMAILING IT to me. Sigh....

Now I've got some passwords to change, ugh....

A SQLNet thing I probably forgot about...

2010-05-10T12:12:00.003-04:00

My most recent "ah-hah", or "oh yeah" moment came reading Jonathan Lewis's blog. A very neat "SQL Net compression" detail.

It is one of those things that I cannot remember if it was something I knew but forgot - or just never knew.

His example makes the point nicely, you have to appreciate those little test scripts for that. You can clearly see what a difference an order by might make on a the size of a result set set across the network.

Yet another reason to look at bulk processing - the more reasons the better...

Not something new...

2010-05-05T17:27:00.004-04:00

But something I see people learning over and over and over again.

I was reminded of a recently asked asktom question - regarding an "intermittent" 'Oracle' bug in a java application ('Oracle' used for sarcasm on my part).

I was reading the current Steven Feuerstein's Blog entry (read that link before going on). It was exactly the same problem found by the java developers on asktom - which they were certain would be an 'Oracle bug' (hint: it wasn't, it was clearly and demonstrably in their code).

Funny, I see the pattern so often that I saw the bug in both bits of code almost immediately. It jumped out and hit my in the face.

SPOILER ALERT: don't read past here if you want to test your ability to find the bug, read Steven's article first.

Maybe I'll put in a request for the to_date function to be overloaded to accept a date as input and just return that date as output.

edit: added after *thinking* about what I just said...

That of course would never work. People are expecting the date format to be applied to the string, so just returning the date could of course NOT be the right thing to do. I guess the overload would have to turn:

to_date( DATE, 'fmt' )

into

to_date( to_char(date,'fmt'), 'fmt' )

instead of

to_date( to_char(date, IMPLICIT_FORMAT ), 'fmt' )

as it does now, but it would be a HUGE change to existing code that 'relies' the way it currently works...

The to_date function takes a string as input and since the date can be converted to a string - it is. I've seen MANY people use:

to_date( date )

to "truncate" a date (horrible idea - not only slow, but RISKY) - it would break their code (not that it isn't already broken) so it would probably be questionable...

Important enough to point out to a wide audience though - beware implicit conversions and watch out for to_date( of a date )!

A decade ago...

2010-05-03T07:25:00.002-04:00

It was ten years ago that asktom was "born" (and five years and a month ago this blog was)...

The first question asked was about Oracle 7.3 on a Sun 5.5.1 machine - in early 2000 (right after we got by that year 2000 thing). Funny, the last time it was updated was.... just a little more than 3 months ago - it is still alive...

Man oh man - have things changed since. I stand at about 12,000 published Q&A's (about 40,000 in total - not all get published for various reasons...) I have 10 new ones in the queue (will get to them soon - have lots of travel planned for today and the week in fact)...

I've learned a lot in the last ten years - things have changed considerably.

Remember what a "big" server looked like in 2000? Remember what they cost? Think about how now you can buy stuff off of the shelf on your credit card that blows them away.

In 1997 - we were doing terabyte test to scales - a terabyte was such a big deal then that we would put out a press release. Now we have a database machine with 5tb of flash cache just to buffer part of the database.... A terabyte is nothing - my son would be disappointed in any laptop with less than that for storage...

It is only because things keep changing so fast, so much - that this stuff stays interesting. Imagine if it were still like 1993 (when I joined Oracle) 17 years later. It would be pretty boring...

Anyway, thanks for all of the great questions - looking forward to more...

Something new I learned about estimated cardinalities...

2010-04-28T10:26:00.008-04:00

I've used pipelined functions and other table functions many times in the past. One of the drawbacks with using them is the fact that the optimizer has no clue what the estimated cardinality will be.

Another thing that has bothered me - is the question "why doesn't the optimizer learn from its mistakes". If it estimated a certain step in a given plan would return 5,000 rows - and it discovers through experience "it only returns 5 rows" - why doesn't it 'learn' from that.

Well, 11g is addressing both of these. I was aware of the 2nd issue being addressed - features like adaptive cursor sharing are all about that.

But I stumbled on a new feature (first available in 11.1 releases) that started affecting table functions (in 11.2) - that feature is called 'cardinality feedback'.

Normally, when you have a pipelined function - the estimated cardinality is computed based on your blocksize - the default number of rows that will come from it are based on your database block size. I have an 8k block size so....


ops$tkyte%ORA11GR2> create or replace type str2tblType
                  as table of varchar2(30);
2  /

Type created.

ops$tkyte%ORA11GR2> create or replace
2  function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
3  return str2tblType
4  PIPELINED
5  as
6      l_str      long default p_str || p_delim;
7      l_n        number;
8  begin
9      loop
10          l_n := instr( l_str, p_delim );
11          exit when (nvl(l_n,0) = 0);
12          pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
13          l_str := substr( l_str, l_n+1 );
14      end loop;
15      return;
16  end;
17  /

Function created.

ops$tkyte%ORA11GR2> column plan_table_output format a80 truncate
ops$tkyte%ORA11GR2> variable in_list varchar2(255)
ops$tkyte%ORA11GR2> exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select *
2    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2407808827

--------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    29   (0
--------------------------------------------------------------------------------

.
The optimizer guesses 8168 rows. It all probability - the real number of rows is not anywhere near 8168. If we use this estimated row count in a bigger query - we'll probably end up with the wrong plan (I like to say - wrong card=wrong plan, right card=right plan - where card is cardinality).

Now, if I run the query and ask Oracle "what plan did you use", we get to see 'reality' - not an explain plan (explain plans are many times not representative of reality!) and reality said:


ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> select *
2    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cu030hs8vrjjn, child number 1
-------------------------------------
select *   from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t

Plan hash value: 2407808827

--------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    29 (100
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    29   (0
--------------------------------------------------------------------------------


13 rows selected.

Which is not any different from explain plan at this point. Reality says "I came up with a plan based on an estimated cardinality of 8168 rows"... However, the database actually *ran* the query this time - and the database has learned from its mistake:


ops$tkyte%ORA11GR2> select *
2    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cu030hs8vrjjn, child number 2
-------------------------------------
select *   from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t

Plan hash value: 2407808827

--------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    29 (100
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |     6 |    12 |    29   (0
--------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement


17 rows selected.

.
Apparently, it hard parsed that query - it did not reuse the plan. We know that it hard parsed since it just created child #2 - and we can see the plan is different - the row count is much lower.

Now, in this example, onl y the estimated row counts changed - the actual plan is the same (the query is after all very simple). Will this affect real world queries?

Yes, it will :)

ops$tkyte%ORA11GR2> create table data
2  as
3  select *
4    from all_objects;

Table created.

ops$tkyte%ORA11GR2> create index data_idx on data(object_name);

Index created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'DATA' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> with T as
2  ( select distinct * from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0 )
3  select * from data, t where data.object_name = t.column_value
4  /
....
10 rows selected.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  bv5ar1b8sft67, child number 0
-------------------------------------
with T as ( select distinct * from TABLE(cast( str2tbl( :in_list ) as
str2tblType) ) t where rownum > 0 ) select * from data, t where
data.object_name = t.column_value

Plan hash value: 892089582

--------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |       |       |   319 (100)|          |
|*  1 |  HASH JOIN                             |         | 13730 |  1528K|   319   (1)| 00:00:04 |
|   2 |   VIEW                                 |         |  8168 |   135K|    30   (4)| 00:00:01 |
|   3 |    HASH UNIQUE                         |         |  8168 | 16336 |    30   (4)| 00:00:01 |
|   4 |     COUNT                              |         |       |       |            |          |
|*  5 |      FILTER                            |         |       |       |            |          |
|   6 |       COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    29   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL                    | DATA    | 72236 |  6842K|   288   (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")
 5 - filter(ROWNUM>0)


27 rows selected.

.
Ugh, one of those (tongue in cheek) nasty full scans and hash joins (they are good - when they are appropriate, they aren't appropriate here...)

as opposed to

ops$tkyte%ORA11GR2> with T as
2  ( select distinct * from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0 )
3  select * from data, t where data.object_name = t.column_value
4  /
...
10 rows selected.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  bv5ar1b8sft67, child number 1
-------------------------------------
with T as ( select distinct * from TABLE(cast( str2tbl( :in_list ) as
str2tblType) ) t where rownum > 0 ) select * from data, t where
data.object_name = t.column_value

Plan hash value: 3947981921

----------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |       |       |    48 (100)|          |
|   1 |  NESTED LOOPS                           |          |       |       |            |          |
|   2 |   NESTED LOOPS                          |          |    10 |  1140 |    48   (3)| 00:00:01 |
|   3 |    VIEW                                 |          |     6 |   102 |    30   (4)| 00:00:01 |
|   4 |     HASH UNIQUE                         |          |     6 |    12 |    30   (4)| 00:00:01 |
|   5 |      COUNT                              |          |       |       |            |          |
|*  6 |       FILTER                            |          |       |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH| STR2TBL  |     6 |    12 |    29   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | DATA_IDX |     2 |       |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID           | DATA     |     2 |   194 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - filter(ROWNUM>0)
 8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
 - cardinality feedback used for this statement


33 rows selected.

Nice - it learned from the errors of its ways...

Note: this affects more than just table functions - read some of the links found on google to see other interesting examples.

Very nice.

That old restart problem again...

2010-04-27T16:33:00.004-04:00

Not something entirely learned new today - but rather a revisited "something I learned" coupled with an "I didn't necessarily expect it in this case".

It is the old "statement restart" and "evil triggers" issue. I had an asktom question asking why a row seemed to be getting deleted twice. You should read that link, I'll be referring to it here.

I immediately knew what the issue was (I was pretty sure). Before I even read the test case - I had already pointed them to three articles I wrote on the subject a while ago...

But when I looked at their test case and modified it slightly to be smaller and easier to read - I was frankly surprised at what was happening - but ok with the behavior.

It takes a series of bad things to happen for this issue the poster was seeing to have happen, you have to

a) use a trigger (evil)
b) do something non-transactional in that trigger - for example modify a package global variable
c) do slow by slow processing (if they did a big mass operation - they could still see a restart, but it would be less likely perhaps)

Here is a snippet of the code again:


ops$tkyte%ORA11GR2> create or replace PACKAGE pacepack
2  as
3      type array is table of number index by varchar2(40);
4      g_data array;
5      g_cnt  number;
6  end pacepack;
7  /

Package created.

That package just has some global variables (evil global variables - evil evil - they didn't use globals in their API, they had getter/setter functions - but they behaved like globals as well). We'll create a row trigger to save the rowids of the rows our trigger has processed - even if Oracle rolls back our firing statement - modifications we've made in our trigger to the globals won't rollback.

Now, we'll code the trigger:


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table delete_data
2  as
3  select owner, object_name
4  from dba_objects
5  where rownum  < 53001;

Table created.




ops$tkyte%ORA11GR2> create or replace trigger delete_data_bt
2  before delete or update on delete_data
3  for each row
4  begin
5      if ( pacepack.g_data.exists(rowidtochar(:old.rowid)) )
6      then
7          dbms_output.put_line( 'doing "' || :old.rowid ||
                   '" again was called ' || pacepack.g_cnt );
8      else
9          pacepack.g_data(rowidtochar(:old.rowid)) := 1;
10      end if;
11      pacepack.g_cnt := pacepack.g_cnt + 1;
12  end;
13  /

Trigger created.

The trigger checks to see if the PLSQL global table variable has the rowid already entered in it - if so, it says "I already did this one", else it sets a flag showing it processed that one for the first time. We also increment a count (you know, to count the rows we've deleted or updated).

Now we process the data:


ops$tkyte%ORA11GR2> declare
2      CURSOR L_DELETE_CSR IS
3      SELECT *
4        FROM delete_data
5         for update ;
6      l_cnt number := 0;
7  BEGIN
8       pacepack.g_data.delete;
9       pacepack.g_cnt := 0;
10      for l_delete_row in l_delete_csr
11      loop
12            update delete_data
13             set owner = lower(owner)
14           where current of L_delete_CSR;
15          l_cnt := l_cnt + 1;
16      end loop;
17      dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt ||
                     ' local count = ' || l_cnt );
18  END;
19  /
doing "AAAYMdAAEAABFrnABH" again was called 8827
doing "AAAYMdAAEAABFwiAAx" again was called 20140
doing "AAAYMdAAEAABFyeACk" again was called 31405
doing "AAAYMdAAEAABFzaABE" again was called 42670
trigger count = 53004 local count = 53000

PL/SQL procedure successfully completed.

As you can see - our trigger was fired 53,004 times - for 53,000 rows. Our counts do not match and we can see the four updates that were restarted.

Why they were restarted - not really relevant - the fact is - they CAN be restarted, we can show that they can be restarted, it is documented that they can be. Any statement can be restarted - we do a savepoint before executing the statement and if we deem necessary (which we did obviously), we rollback to that savepoint and do it over.

So, expect the restart - and stop using triggers. If you do use triggers - be very very very careful to make sure they are not subject to issues with restarts (eg: NO autonomous transactions - NO modifications of global variables - NO API calls that have side effects that don't roll back (setter functions for example)!

Did you know...

2010-04-24T13:47:00.002-04:00

It is Saturday (ok, you probably knew that) so I'll do something non-Oracle. Did you know - carrots aren't really supposed to be orange? They should be purple. And they are if you grow a few generations of the Orange ones (they revert back to their natural state).

So, much like the yellow, easy to peel bananas we enjoy - Orange carrots are a product of "us".

I wonder what Bugs Bunny would have to say about that...

A new thing about sql*plus

2010-04-23T15:24:00.002-04:00

What I learned today was - a new feature in the venerable old tool SQL*Plus.

I was asked about trapping the SP2 errors in SQL*Plus, those you get when you have a bad SQL*Plus command - like this:

ops$tkyte%ORA11GR2> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

In that case - neither of the SQL*Plus error handling bits:

OSERROR
SQLERROR

will work for you - it is not an OS error like "unable to open spool file", it is not a SQL error - selct is not SQL, it never got to the SQL layer. that SP2 error is uncatchable. And I wrote as much.

Things change - my answer is dead on correct, for version 10gR2 and before (hey, the question was from someone using 10gR2 - so technically, I was correct ;) ). It is not technically true in 11g and above.

SQL*Plus in 11g added an "error logging" facility. A session may issue:

SQL> set errorlogging on

and have any SQL, OS or SP2 errors logged into a logging table, similar to DML error logging. Additionally - you can have your errors tagged with an identifier, making it easy to find your error records. So, you can now check (using SQL) at various points in time to see if you've hit an error - or your program that runs sqlplus and runs a script can check to see if any errors occurred in your session easily.

thanks to Enrique Aviles for pointing it out and thanks to Arup Nanda for writing it up (ctl-f for SQL*Plus Error Logging on that page).

Note that you need the 11g SQL*Plus, not just an 11g database with an old sqlplus connected to it! This is a feature of SQL*Plus.

On the flipside though, this means it is available for older database releases! You can connect to 9i with 11g SQL*plus and use this:


[tkyte@dellpe ~]$ sqlplus scott/tiger@ora9ir2

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 15:36:51 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

scott%ORA9IR2> set errorlogging on
scott%ORA9IR2> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
scott%ORA9IR2> select timestamp, username, script, statement, message
 2  from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
-------------------------------------------------------------------------------
SCRIPT
-------------------------------------------------------------------------------
STATEMENT
-------------------------------------------------------------------------------
MESSAGE
-------------------------------------------------------------------------------
23-APR-10 03.37.02.000000 PM
SCOTT

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

Something I recently unlearned...

2010-04-21T10:39:00.006-04:00

This is how many of the things I learn everyday come into being. They are actually things I have to "unlearn" because what used to be true has changed over time.

Once upon a time ago - I remember the day I learned this, it was during a benchmark in 1993, I learned that UNINDEXED foreign keys had some locking implications. Specifically if you:

update the parent primary key (which does happen, some 'frameworks' update every column even if the value did not change)
delete from parent

Then you should probably index the foreign key in the child table - else there will be a full table lock placed on the child table - for the duration of the transaction.

Then Oracle 9i was released and I had to relearn the rule. The rule in 9i was as above still - just modified as to the duration of the lock (many people think the restriction actually went away - but it did not, it was changed). In 9i and above, if you update the parent or delete from the parent with an unindexed foreign key - the child table is still locked - just for the duration of the update or delete! The lock is released after the statement processed - not when you commit. This was "better", but the lock still exists.

Sometime during 9i - I learned yet another modification to the rule above. The rule in 9i now has to include:

if you merge into the parent table

in addition to update and delete. As I was getting ready to add that to the 2nd Edition of Expert Oracle Database Architecture - I learned something new, the rule has changed again. The MERGE doesn't always lock the table anymore in 11g Release 1 and above - so we are back to just update and delete (sort of!).

Here is the small test case you can use to verify - the set up is:

ops$tkyte%ORA9IR2> create table p ( x int primary key, y int );
Table created.

ops$tkyte%ORA9IR2> insert into p values ( 1, null );
1 row created.

ops$tkyte%ORA9IR2> insert into p values ( 2, null );
1 row created.

ops$tkyte%ORA9IR2> create table c ( x references p );
Table created.

ops$tkyte%ORA9IR2> create or replace
procedure modify_p( p_what in varchar2 )
2  as
3      pragma autonomous_transaction;
4      deadlock exception;
5      pragma exception_init( deadlock, -60 );
6  begin
7      if ( p_what = 'DELETE' ) then delete from p where x = 2;
8      elsif ( p_what = 'UPDATE' ) then update p set x = 2 where x = 2;
9      elsif ( p_what = 'MERGE' ) then
10          merge into p using (select 2 x, 42 y from dual) d
11          on (p.x=d.x)
12          when matched then update set y = d.y
13          when not matched then insert(x,y) values (d.x,d.y);
14      end if;
15      rollback;
16      dbms_output.put_line( p_what || ': successful...' );
17  exception
18      when deadlock then
19          dbms_output.put_line( p_what ||
         ': we deadlocked, we needed full table lock');
20          rollback;
21  end;
22  /
Procedure created.

So, a parent table with two rows - 1 and 2. An empty child table with an unindexed foreign key. A stored procedure that runs as an autonomous transaction - so it cannot share the locks of the parent transaction, if the parent transaction has anything locked - the autonomous_transaction will NOT be able to also lock it. The autonomous transaction attempts to either

delete row X=2 from parent
update row X=2 in parent
merge into row x=2 in parent using when matched then update, when not matched then insert

and if it deadlocks - prints out a message telling us that and rolls back. If successful, prints out a message telling us that and likewise rolls back.

To test, we just insert into the child table a record that points to row x=1 in parent (we'll never touch that row in the parent table) and then try the three DML opertions:

ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'DELETE' );
DELETE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'UPDATE' );
UPDATE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'MERGE' );
MERGE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

There you go, you can see it deadlocked on all three - they all needed to lock the child table before doing their work.

If you run that in 10gr1 and 10gr2 - you'll see the same results - all three lock. However, starting in 11g Release 1 - you'll see this:

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'DELETE' );
DELETE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'UPDATE' );
UPDATE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'MERGE' );
MERGE: successful...
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

That merge no longer locks the child table. Curious as to this change - I wanted to see if it was official or not and found bug 5970280 - from which I learned that it was officially changed and that some of you running 10.2 might see a different result for this test case (the fix was backported and is available for 10.2).

The fix is more complex than appears (aren't they always?) It is not just "turn off lock for MERGE", it is "analyze the merge and

if the merge just inserts - treat as an insert
if merge does an update or update and insert (and we are NOT updating the primary key!) treat as an update to non-primary key columns
if merge does an update or update and insert (and we are updating the primary key) lock child table
if merge includes a delete - treat as a delete and lock child table

So, if your merge was:


10          merge into p using (select 2 x, 42 y from dual) d
11          on (p.y=d.x)
12          when matched then update set x = d.x
13          when not matched then insert(x,y) values (d.x,d.y);

then you would see:


ops$tkyte%ORA11GR1> exec modify_p( 'MERGE' );
MERGE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

or if your merge included a possible delete branch, you would see the same.

So, I guess the rule in 11gR1 and above is, if you

update parent primary key
delete from parent
use a merge that does either of the above

and you have an unindexed foreign key - you should expect a full table lock on the child table. If you index that foreign key - no untoward locking will take place.

Things change over time :)

What will happen if...

2010-04-20T13:23:00.005-04:00

So, something else I learned recently...

Say you have a table T:

ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.

and you update a row in that table:

ops$tkyte%ORA10GR2> update t set x = x+1;
1 row updated.

and using an evil autonomous transaction you try to lock that row (in the same session, but a new transaction - one that cannot 'see' the effects of the parent transaction)

ops$tkyte%ORA10GR2> declare
2          pragma autonomous_transaction;
3          l_rec  t%rowtype;
4  begin
5          select * into l_rec from t for update wait 5;
6          commit;
7  end;
8  /
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5

So far, it all seems normal. You asked to wait for 5 seconds, you did - and you time out. But, what happens if you wait longer?

ops$tkyte%ORA10GR2> declare
2          pragma autonomous_transaction;
3          l_rec  t%rowtype;
4  begin
5          select * into l_rec from t for update wait 6;
6          commit;
7  end;
8  /
declare
*
ERROR at line 1:
??????????????????????????????????

What error are you expecting that time - hint, it is not ORA-30006 it is

ops$tkyte%ORA10GR2> declare
2          pragma autonomous_transaction;
3          l_rec  t%rowtype;
4  begin
5          select * into l_rec from t for update wait 6;
6          commit;
7  end;
8  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5

apparently the self deadlock code kicks in before the timeout happens and the deadlock detection code doesn't see the "we will eventually time out"

And yes, this applies to multi-session cases as well - run this script to see that:

drop table t;
set echo on
create table t ( x int );
insert into t values ( 1 );
insert into t values ( 2 );
commit;
select * from t where x = 1 for update;
set echo off
prompt in another session issue:
prompt select * from t where x = 2 for update;;
pause
prompt in another session issue:
prompt select * from t where x = 1 for update wait 10;;
set echo on
select * from t where x = 2 for update;

It will deadlock one of the sessions - without waiting for the "wait" timeout.

So, deadlock detection trumps a waiting period. If the waiting period expires before the deadlock routines kick in - you get the ora-30006, if the deadlock routines kick in before the timeout - you get ora-60.

NO_DATA_NEEDED - something I learned recently

2010-04-19T11:06:00.003-04:00

I'll be writing about this in Oracle Magazine shortly as part of the asktom column - but thought I'd mention it here too.

Recently on asktom.oracle.com, I was asked a question about the pre-defined exception NO_DATA_NEEDED. At first I thought that is was a typo – they really meant NO_DATA_FOUND – since I hadn’t heard of or read about that exception. But in looking a little deeper, I discovered what it was.

If you ever write a pipelined function - there is a good chance you need to be aware of it. Don't go searching for it in the documentation (it will be there in the next dot release - but it isn't there yet), you won't find it. Don't google for it - you won't find much about it yet. But it is something we've needed, probably knew we needed, just never thought about it.

What if you have a pipelined function that does something like:

a) open file
b) read line - pipe row
c) when no more data, close file and return

It works perfectly - if you read all records from the file. However, if you call it from a query such as:

select * from table(pipelined_function( '/tmp/foo.dat' )) where rownum = 1;

What happens - what happens if there was zero records in /tmp/foo.dat to read? one record? More than one record?

Well, "it depends". In all likelihood - if there was one or more records - you would leave a file handle open - and if you called this function over and over, you would leak an open file each time and eventually run out of file handles. So, you would have an error that would "sometimes happen" and "sometimes not happen". In other words - one of those strange non-reproducible bugs that only happens when it rains on a Tuesday after midnight but before 8am.

Enter NO_DATA_NEEDED - an exception that doesn't behave like any other exception. An exception that is raised - but does not cause failure. An exception that can be caught, but if it isn't - everything is still OK.

Say you have code like this:


create or replace function
foo( inputs ... )
return some_type
PIPELINED
as
  /* declaration */
begin
  /* initialization */

  /* process a loop */
      pipe row(i);
  end loop;

  /* clean up */
  return;
end;
/

The clean up code would execute and do the right thing if you exit the loop - but not so if you just stopped calling this function. The NO_DATA_NEEDED exception is there for just such a case. Here is a concrete example:


SQL> create or replace function
 2  generate_data( n in number )
 3  return sys.odciNumberList
 4  PIPELINED
 5  as
 6  begin
 7      dbms_output.put_line
 8      ( '===>>> INITIALIZE' );
 9      for i in 1..generate_data.n
10      loop
11          dbms_output.put_line
12          ( '===>>> PROCESS' );
13          pipe row(i);
14      end loop;
15      dbms_output.put_line
16      ( '===>>> CLEAN UP' );
17      return;
18  end;
19  /

Function created.

That is a pretty straightforward PL/SQL pipelined function – if we run it to completion – we would see this output:


SQL> select *
 2    from table(generate_data(2));

COLUMN_VALUE
------------
          1
          2

===>>> INITIALIZE
===>>> PROCESS
===>>> PROCESS
===>>> CLEAN UP
SQL>

Which is what we expect – but what if we don’t fetch two rows from that function, what if we only fetch one?


SQL> select *
 2    from table(generate_data(2))
 3   where rownum = 1;

COLUMN_VALUE
------------
          1

===>>> INITIALIZE
===>>> PROCESS
SQL>

As you can see – we did the initialize and one process bit of our code, but the rest – it was just skipped over, because the invoking SQL statement did not need it. We didn’t see any error (we would expect an unhandled exception to raise an error!), it just appears to have worked.

There was however, an exception raised – an exception that does not have to be caught. It will be ignored entirely if it is not caught. It differs from every other exception in that regard – we would expect an unhandled exception to propagate to the client and appear as “an error”. Lets see what happens with out code if we implement this error handler:


SQL> create or replace function
 2  generate_data( n in number )
 3  return sys.odciNumberList
 4  PIPELINED
 5  as
 6  begin
 7      dbms_output.put_line
 8      ( '===>>> INITIALIZE' );
 9      for i in 1..generate_data.n
10      loop
11          dbms_output.put_line
12          ( '===>>> PROCESS' );
13          pipe row(i);
14      end loop;
15      dbms_output.put_line
16      ( '===>>> CLEAN UP' );
17      return;
18  exception
19      when no_data_needed
20      then
21          dbms_output.put_line
22          ( '***>>> CLEAN UP' );
23          return;
24  end;
25  /

Function created.

On line 19 we catch the predefined exception NO_DATA_NEEDED and on line 21 announce that we are cleaning up (releasing any resources that need be released). Now when we run this pipelined function without exhausting it we see:


SQL> select *
 2    from table(generate_data(2))
 3   where rownum = 1;

COLUMN_VALUE
------------
          1

===>>> INITIALIZE
===>>> PROCESS
***>>> CLEAN UP
SQL>

As you can see – our special cleanup code (we used ***>>> to announce it) was executed and we could clean up any resources we allocated.

What I learned today :)

2010-04-17T10:19:00.003-04:00

That I grew my beard for a reason.

I am apparently more trustworthy looking simply because of it.

And here I thought I was doing it to save money on razors. Good side effects are always nice.

Never Imagined...

2010-04-16T11:11:00.003-04:00

I never imagined that I would have to cancel a seminar, because of....

A volcano.

I was supposed to be in Hungary for a two day seminar Monday and Tuesday - which I had to cancel since the airlines told me the earliest I would be able to leave for Europe would be .... Tuesday night!

I also had to cancel a planned in person appearance at the Bulgaria Oracle User Group - we are going to try to make it a 'virtual' appearance for some technical sessions if possible. That'll take place next Friday the 23rd.

So, to anyone that was signed up for the seminar in Budapest - I apologize, but there is no way to get there from here right now. We'll be looking for a reschedule date now...

Evaluating an expression, like a calculator...

2010-04-16T10:47:00.004-04:00

Today, I learned a new 11g Release 1 and above 'trick' that I wasn't aware of. This is pretty cool.

A frequently asked question in the past has been:

I have a string, with some calculation in it - like "1+2/3". I would like to evaluate that string and get the result. How do I do that.

Historically - the answer has been "dynamic SQL, but please be careful to not flood the shared pool with tons of literal SQL and be really careful about SQL Injection!"

Now, I learned a new way. It came from this post, thanks to the frequent asktom poster "Sokrates"


ops$tkyte%ORA11GR1> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

ops$tkyte%ORA11GR1> variable x varchar2(40)
ops$tkyte%ORA11GR1> exec :x := '55+42*123/3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select xmlquery(replace( :x, '/', ' div ' ) 
                            returning content ) .getNumberVal()
2    from dual
3  /

XMLQUERY(REPLACE(:X,'/','DIV')RETURNINGCONTENT).GETNUMBERVAL()
--------------------------------------------------------------
                                                      1777

It works for very very simple expressions - XQuery arithmetic expressions are very simple. They support +, -, *, div, idiv (integer division), and mod


ops$tkyte%ORA11GR1> exec :x := '(55+42-124) idiv 3 div 2 mod 5*2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> /

XMLQUERY(REPLACE(:X,'/','DIV')RETURNINGCONTENT).GETNUMBERVAL()
--------------------------------------------------------------
                                                         -9

See http://www.xquery.com/tutorials/guided-tour/xquery-operators.html for a guided tour of what you can do.

to_char(dt,'yyyy') is almost as good as trunc(dt,'y')...

2010-04-15T11:27:00.004-04:00

I'm in the middle of updating Expert Oracle Database Architecture Edition 1 to Edition 2. It'll cover up through Oracle Database 11g Release 2. While doing it, I've been discovering that "things change over time". Well, Ok, that is not a new discovery - I've said that before...

If you are interested in the new edition, you can read the unedited, probably incorrect, updates - you can buy the "alpha book" in pdf, which will entitle you to a full pdf of the final copy. See http://apress.com/book/view/1430229462 for details.

Anyway, while updating the book - I was re-running all of my 'test' scripts - the scripts I use to demonstrate points - and of course since things change - the results of some of the scripts have changed as well. Things like private redo strands affected my measurements in the Redo/Undo chapter (rendering some of the old examples useless...). And other small changes pop up and make themselves apparent.

For example, to_char(dt,fmt) has changed, for the better. It is faster...

In the first edition, I wrote:

For example, they will use
Where to_char(date_column,'yyyy') = '2005'
instead of
Where trunc(date_column,'y') = to_date('01-jan-2005','dd-mon-yyyy')
The latter is a far more performant and less resource-intensive approach.

Note: in the following - you would not use literals in the query, you would use bind variables to represent the dates! Just an example...

While I would still *prefer* you to use trunc(date_column'y') - it says so much more than the to_char does semantically - it cannot be for the reason I wrote. In the first edition - I showed by executing a query against a copy of ALL_OBJECTS that it was more efficient:


select count(*)
from
t where to_char(created,'yyyy') = '2005'
                                                                                   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.01       0.05          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.41       0.59          0        372          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.42       0.64          0        372          0           4
                         
select count(*)
from
t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy')
                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.04       0.16          0        372          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.04       0.16          0        372          0           4

but when I re-ran that in 11g Release two - I discovered:


select count(*)
from
t where to_char(created,'yyyy') = '2005'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.09       0.10          0        364          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.09       0.10          0        364          0           4

select count(*)
from
t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.07       0.07          0        364          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.07       0.07          0        364          0           4

*note - I used the same number of rows in 11g as 10g in the above

Well, that isn't too different anymore, is it? They made it a bit more efficient than it used to be in the past - the to_char and trunc are now about the same. In fact that change happened with 10g Release 2! (edition 1 of Expert Oracle Database Architecture was up through 10g Release 1)

Fortunately though, my ultimate conclusion - that:


select count(*)
from
t where created between to_date('01-jan-2005','dd-mon-yyyy') and
to_date('01-jan-2006', 'dd-mon-yyyy')-1/24/60/60


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0        364          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.00          0        364          0           4

is the right way to code that query still stands - that is from 11g Release 2 - it is significantly faster than to_char or trunc. And, it'll be infinitely more "index friendly" in general that either of to_char or trunc.

So, things change - the tuning advice of yesterday might well be meaningless or - at least less meaningful - today...

Glad I have my test scripts :) It would be much harder to update the book without them - I wouldn't have a big red flag staring at me in the face without them.

That is how today's myths get started - most of the myths surrounding the database have their origins in some nugget of truth from the past. But unless you have some method of seeing when things change - it'll be hard to figure out what's changed.

Been a while - new theme coming...

2010-04-14T16:59:00.002-04:00

Been a while since I've written here. I've decided to start posting again from time to time, but I'm going to follow a theme for a while.

The theme will be "I didn't know that..." - or "What I learned new today..."

I am constantly amazed as what I don't know - given that I've been using Oracle for 23 years now - you might think that would be a small set of things. It isn't, it is quite large.

Here is one thing I learned new today. It is about SQL*Plus. A pretty simple tool, been using it for a long long long time. Been using it since it was an extra cost option to the database!

Anyway, did you know that you could put @file into a SQL command?


ops$tkyte%ORA10GR2> !cat test.sql
select * from dual;

ops$tkyte%ORA10GR2> explain plan for
 2  @test

Explained.

ops$tkyte%ORA10GR2> l
 1  explain plan for
 2* select * from dual
ops$tkyte%ORA10GR2>

Neat - I did not know that.

I have more, I'll let them leak out day by day (or so)...

A fairly unique opportunity...

2009-12-07T08:08:00.002-05:00

CJ Date will be giving a seminar in Dallas Texas next month. I wish I could get to it myself - but I'm already fully committed that week.

Maybe we can get the answer to using NULLs. Or at least some more input :)

Comparative Window Functions...

2009-11-15T08:42:00.003-05:00

I've been known as a huge fan of Analytic functions (as evidenced by the Rock and Roll linkability!)

And - they could be getting better in the near future. Read this document for a proposal to allow analytics to access the current row value to be compared against any other row value in a defined window.

I've already supplied them with my feedback (which started with "this is an awesome idea") - and you can too - by posting it here. They'll be checking back to see what you say.

Also, this is being proposed as well:

Another window function extension, not contained in the attached proposal, is the notion of VALUE based windows. Currently, we have ROW based (or physical) and RANGE based (logical) windows. RANGE window has limitation in that there can only be one sort key in window ORDER BY. On the other hand, ROW based window is agnostic to column value and can be non-deterministic.

The new VALUE based window allows one to include all rows with "n" values before or after the current row's value. For example, VALUE 2 PRECEDING and 3 FOLLOWING would include all rows with 2 values that are prior to current row's value and all rows with 3 values that come after the current row's value in sort order.

ticker txndate volume
orcl 1 10
orcl 2 10 <--------------------------- start of window for (orcl,6,12)
orcl 2 11
orcl 2 11
orcl 3 11
orcl 6 12 <=== assume this is current row
orcl 7 12
orcl 11 11
orcl 11 12
orcl 11 12
orcl 13 11 <------------------------- end of window for (orcl,6,12)

Similar RANGE window would have rows [orcl,6,12] through [orcl,7,12]. Similar
ROW window would include rows [orcl,3,1] through [orcl,11,11].

The VALUE based window would find usefulness when there are gaps in the dataset. For example, a query like "find the intra-day maximum for a stock in the past three trading days". Today, to do this one has to aggregate on trading date and then compute the moving max (in the past 3 days).

VALUE based window can have multiple keys in ORDER BY.

Thanks in advance for any feedback or ideas you might have on this.

Use NULL for unknown data...

2009-10-22T08:11:00.004-04:00

Hah, it goes back much further than I thought... Snopes.com pointed out this morning that the use of a 'bad default value' dates back to at least 1979...

See http://tkyte.blogspot.com/2009/10/back-from-oracle-openworld.html for the original reference.

Back from Oracle OpenWorld

2009-10-21T10:07:00.002-04:00

I am back and have been taking a set of new questions on asktom. Last week was a busy one out in California and I'm finally getting caught up on emails and questions (100% on the former, still working on the latter)

Anyway, I saw an article and it made me laugh - and sort of cry at the same time. It has to do with the use of default values. A lot of developers/DBAs have a very certain fear (that is the best word I can think of to describe their attitude) of NULL - the 'unknown' value. So, instead of using NULL for an effective_end_date field (for records that we don't know the end date for, they don't have one) or using NULL for values they do not know the value of - they use some 'fake' value. This fake value is assumed to be a value that could never possibly be used.

But....

Things change over time.

I'm sure when the developers implemented this system - using XXXXXXX for a license plate value that was unknown seemed 'reasonable'. I mean - who would ever ask for a vanity plate with seven X's on them?

Talk to the guy with almost $20k in fines that aren't his to see if he might know someone that might want a vanity plate with XXXXXXX on it :)

Do not fear NULL.

Understand it, but don't fear it.

http://tkyte.blogspot.com/2006/01/something-about-nothing.html
http://tkyte.blogspot.com/2006/01/mull-about-null.html

Oracle OpenWorld first presentation

2009-10-11T13:55:00.002-04:00

My first presentation of Oracle OpenWorld is done - it was the one I was most nervous about. It was a keynote, the opening session for the Oracle Develop conference track. As a keynote - I try to make it fun and informative but not a brain dump of all things technical.

The room filled up - which surprised me, it was 9am on a Sunday morning...

And the presentation seemed to work - I ran about 3 minutes over (need to tighten it up a tiny bit) - but it went well. Everyone laughed when they were supposed to, and didn't when they weren't. Even got a bit of spontaneous applause every now and then :)

For those that missed it, or those not at the conference that want to see it - there will be a replay tomorrow (Monday, October 12th) at 11am pacific time - see this link for details.

ID#: S312577
Title: Keynote: Oracle Develop "What Are We Still Doing Wrong"
Track: Oracle Develop: Database
Date: 11-OCT-09
Time: 09:00 - 10:00
Venue: Hilton Hotel
Room: Grand Ballroom B

Off to Oracle OpenWorld

2009-10-09T16:31:00.004-04:00

I've been a bit busy with things recently - getting ready for Oracle OpenWorld in particular. I haven't been able to pay as much attention to asktom as I normally do - that'll change the week after OpenWorld (I'll be taking questions again that week - until then, probably not)

ID#: S312577
Title: Keynote: Oracle Develop "What Are We Still Doing Wrong"
Track: Oracle Develop: Database
Date: 11-OCT-09
Time: 09:00 - 10:00
Venue: Hilton Hotel
Room: Grand Ballroom B

ID#: S311235
Title: All About Metadata: Why Telling the Database About Your Schema Matters
Track: Oracle Develop: Database
Date: 12-OCT-09
Time: 11:30 - 12:30
Venue: Hilton Hotel
Room: Imperial Ballroom B

ID#: S311322
Title: DBA 2.0: Battle of the DBAs Revisited
Track: Database
Date: 12-OCT-09
Time: 17:30 - 18:30
Venue: Moscone South
Room: Room 103

ID#: S311234
Title: The top 10 - No, 11 - New Features of Oracle Database 11g Release 2
Track: Database
Date: 13-OCT-09
Time: 17:30 - 18:30
Venue: Moscone South
Room: Room 103

ID#: S311236
Title: Efficient PL/SQL: Why and How to Use PL/SQL to Its Greatest Effect
Track: Database
Date: 14-OCT-09
Time: 17:00 - 18:00
Venue: Moscone South
Room: Room 103

I'll be in the OTN lounge on Wednesday from 12:45pm till 1:30pm with hte "Heavy Hitters" program. Anyone is free to attend that session.

Also, I'll be in the database demo grounds in Moscone West - in the middle of the demo area. It'll say "database overview" in big letters on top and "Oracle Database 11g Release 2" underneath. I'll be manning a station there on Tuesday from 10:30am till 11:30 am and on Wednesday from 9am till 10:15am

Looking forward to OpenWorld - both the beginning and the end :) It'll be nice to get back home after a week at a conference.

http://asktom.oracle.com/tkyte/flat

2009-10-08T14:56:00.005-04:00

How Can I unload data to a flat file

Many times we are asked

"Does anyone know an easy way of dumping the data from an Oracle table into a delimited(comma, tab etc) ascii file?"
"Does anyone know an easy way to unload data in a format for sqlldr to reload later?"

Well here is a way to do it into Excel or any spreadsheet that understands the industry standard SYLK file format.

Here is a pro*c program that does it to a flat file very fast.

and here is a PLSQL routine that uses utl_file to do the same

A reader (Andy Rivenes) offers this more "robust" plsql implementation based on the original code.

And lastly, SQLPlus can do this quite easily but it is a pain to have to write a script/table. What I've done is setup scripts for UNIX and NT that allow you from the command line to execute things like:

$ sqlldr_exp scott/tiger dept
LOAD DATA
INFILE *
INTO TABLE dept
REPLACE
FIELDS TERMINATED BY '|'
(
deptno
,dname
,loc
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|RESTON
40|OPERATIONS|BOSTON

As you can see, this script unloaded the scott.dept table into a format that sqlldr can easily reload. All you would need to do is execute:

$ sqlldr_exp scott/tiger dept > dept.ctl

to create a control file that can be moved somewhere else and reloaded back into a dept table very quickly.

I also use a slight modification of this script called "flat". Flat does the same thing as sqlldr_exp does mostly except that it dumps the data into a tab delimited format without the sqlldr extras at the top. This makes the extract usable in spreadsheets and such.

In both cases some things you need to be aware of are with regards to this script:

There is an absolute limit of 2000 bytes in 7.x and 4000 bytes in 8.x per line/row for unloaded data. The total size of the unloaded data is unlimited -- the maximum size of an individual row of data is what is limited.
It makes no attempt to unload dates with the century or time component -- you must change your default NLS_DATE_FORMAT if this is a problem.
Beware of data with pipes or tabs in it!
Beware of data with newlines as well...
The NT scripts (.cmd files) need modifications if your command line sqlplus is not called SQLPLUS (eg: its plus33 or something similar)
On NT, you need to set your SQLPATH environment variable and put these files into that directory OR you need to run flat and sqlldr_exp from those directories so sqlplus can find the corresponding flat.sql and sqlldr_exp.sql files.

download that script here

http://asktom.oracle.com/tkyte/who_called_me

2009-10-08T14:52:00.002-04:00

How Can I find out who called me or what my name is

Many times we are asked "in a procedure/function, can I find out who called me" or "can I dynamically figure out the name of the procedure or package that is currently executing".

You can find it in the call stack returned by dbms_utility.format_call_stack. I wrote a small routine called who_called_me that returns this sort of information (it doesn't tell you who you are, it lets you know who called you). If you wrap who_called_me with a function who_am_i, you'll get what you need. If you create the who_called_me/who_am_i routines, you'll be able to:


SQL> create or replace procedure demo
2  as
3  begin
4     dbms_output.put_line( who_am_i );
5  end;
6  /

Procedure created.

SQL> exec demo;
TKYTE.DEMO

In current releases of the database, this code has been incorporated into the OWA_UTIL package - you probably already have it in your database. If not, you can use this really old version that might need a tweak or two to work in your database release:


create or replace procedure who_called_me( owner      out varchar2,
                        name       out varchar2,
                        lineno     out number,
                        caller_t   out varchar2 )
as
   call_stack  varchar2(4096) default dbms_utility.format_call_stack;
   n           number;
   found_stack BOOLEAN default FALSE;
   line        varchar2(255);
   cnt         number := 0;
begin
--
   loop
       n := instr( call_stack, chr(10) );
       exit when ( cnt = 3 or n is NULL or n = 0 );
--
       line := substr( call_stack, 1, n-1 );
       call_stack := substr( call_stack, n+1 );
--
       if ( NOT found_stack ) then
           if ( line like '%handle%number%name%' ) then
               found_stack := TRUE;
           end if;
       else
           cnt := cnt + 1;
           -- cnt = 1 is ME
           -- cnt = 2 is MY Caller
           -- cnt = 3 is Their Caller
           if ( cnt = 3 ) then
               lineno := to_number(substr( line, 13, 6 ));
               line   := substr( line, 21 );
               if ( line like 'pr%' ) then
                   n := length( 'procedure ' );
               elsif ( line like 'fun%' ) then
                   n := length( 'function ' );
               elsif ( line like 'package body%' ) then
                   n := length( 'package body ' );
               elsif ( line like 'pack%' ) then
                   n := length( 'package ' );
               elsif ( line like 'anonymous%' ) then
                   n := length( 'anonymous block ' );
               else
                   n := null;
               end if;
               if ( n is not null ) then
                  caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
               else
                  caller_t := 'TRIGGER';
               end if;

               line := substr( line, nvl(n,1) );
               n := instr( line, '.' );
               owner := ltrim(rtrim(substr( line, 1, n-1 )));
               name  := ltrim(rtrim(substr( line, n+1 )));
           end if;
       end if;
   end loop;
end;
/

create or replace function who_am_i return varchar2
is
   l_owner        varchar2(30);
   l_name      varchar2(30);
   l_lineno    number;
   l_type      varchar2(30);
begin
  who_called_me( l_owner, l_name, l_lineno, l_type );
  return l_owner || '.' || l_name;
end;
/

http://asktom.oracle.com/tkyte/unindex

2009-10-08T14:48:00.002-04:00

Unindexed Foreign Keys

Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.

The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.

Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.

So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:

SQL> @unindex

STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES          AI_APP_CODE
ok   EMP                            DEPTNO               DEPTNO

The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.

http://asktom.oracle.com/tkyte/hexdec

2009-10-08T14:35:00.003-04:00

Base Conversion Routines

This set of routines is useful to convert between various 'bases' in Oracle. Once you install these functions, you will be able to perform operations such as:


SQL> select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual
2  /

BIN             HEX             OCT
--------------- --------------- ---------------
1111011         7B              173

SQL>
SQL> select to_dec( '1111011', 2 ) base2, to_dec( '7B' ) base16,
2         to_dec('173',8) base8
3    from dual
4  /

BASE2     BASE16      BASE8
---------- ---------- ----------
  123        123        123

Of course, with current releases - you would never user TO_HEX since TO_CHAR already does this (faster). Also TO_NUMBER would be used to convert HEX to decimal these days as well


ops$tkyte%ORA10GR2> select to_char( '1234', 'XXXX' ) from dual;

TO_CH
-----
4D2

Here is the original code:


create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
 l_str varchar2(255) default NULL;
 l_num number default p_dec;
 l_hex varchar2(16) default '0123456789ABCDEF';
begin
 if ( p_dec is null or p_base is null )
 then
  return null;
 end if;
 if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
  raise PROGRAM_ERROR;
 end if;
 loop
  l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
  l_num := trunc( l_num/p_base );
  exit when ( l_num = 0 );
 end loop;
 return l_str;
end to_base;
/


create or replace function to_dec
( p_str in varchar2,
 p_from_base in number default 16 ) return number
is
 l_num   number default 0;
 l_hex   varchar2(16) default '0123456789ABCDEF';
begin
 if ( p_str is null or p_from_base is null )
 then
  return null;
 end if;
 for i in 1 .. length(p_str) loop
  l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
 end loop;
 return l_num;
end to_dec;
/
show errors

create or replace function to_hex( p_dec in number ) return varchar2
is
begin
 return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
 return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
 return to_base( p_dec, 8 );
end to_oct;
/

http://asktom.oracle.com/tkyte/update_cascade

2009-10-08T14:27:00.003-04:00

UPDATE CASCADE PACKAGE

Generates needed package and triggers to support update cascade
in Oracle without removing or infringing on DECLARITIVE RI.

This package supports:

Tables with multi-part primary keys (primary key(a,c,b))
Update cascade to many child tables from one parent
Self-referencing integrity such as that found in the SCOTT.EMP table
(mgr->empno)
Is completely application transparent. The application does not
know it is happening
Versions 7.0 and above of the database.
Tuned and optimized to fully avoid full table scans on all tables
(complete with utility to show you un-indexed foreign keys in a schema,
Cascading an update to un-indexed foreign keys can be bad).

This solution has the following restrictions:

All foreign keys to the parent table must point to the primary key
constraint of the parent table. They cannot point to a unique
constraint on the parent table, they must point to the primary key.
No other unique constraints/indexes may be in place on the parent
table other then the primary key constraint.
Updates to primary keys that do not generate 'new' primary keys
are not currently supported. For example, take the standard DEPT
table. The update statement "update dept set deptno = deptno+10"

will
not work whereas the update "update dept set deptno = deptno+1"
will.
The first update will change 10->20, 20->30 and so on. Problem is
that 10->20 is not generating a 'new' primary key. On the other hand,
deptno=deptno+1 does not have this problem since 10->11, 20->21 and
so on.

NOTE: an update that affects a single row will never suffer from this
problem.
the owner of the parent table must also be the owner of the child
tables.
the owner of the parent table must run the following package in
their schema. This package must be installed for each user that wants
to generate update cascade support. It may be dropped after the cascade
support has been generated.
the owner of the parent table must have been granted
create procedure and create trigger. these
priveleges may not be inherited from a role.

Installing, Using, and seeing how the demo works

This package consists of four SQL scripts

uc.sql
demobld.sql
unindex.sql
generate.sql

UC.SQL

UC.SQL should be run by any user wanting to implement update cascade. It will
create

A package spec for update_cascade
A package body for update_cascade

Once this package is installed, you are able to implement update cascade on any
table via the pl/sql call:


PROCEDURE update_cascade.on_table
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
P_TABLE_NAME                   VARCHAR2                IN
P_PRESERVE_ROWID               BOOLEAN                 IN     DEFAULT
P_USE_DBMS_OUTPUT              BOOLEAN                 IN     DEFAULT

Input Name	Default	Usage
p-table-name	NO DEFAULT	is the name of the parent table
p-preserve-rowid	TRUE	affects the generation of the code used to implement the generated packages. If set to TRUE (the default) the rowid of the updated parent row will not change due to the update. If set to FALSE, the rowid of the updated row WILL change BUT the code executes in about 66% of the time.
p-use-dbms-output	FALSE	defaults to FALSE which means the update_cascade package will execute (create) the packages/triggers. If set to true, the generated code will be printed using dbms_output.put_line (make sure to set serveroutput on size 100000 before using TRUE if you want to see the code).

For example....


SQL> REM will do the default cascade support on
SQL> REM the dept table
SQL> REM ROWIDS will be preserved (unchanged).
SQL> REM Package will be created in the database
SQL> exec update_cascade.on_table( 'dept' )

SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table
SQL> REM (the rowids will change).  This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one
SQL> exec update_cascade.on_table( 'dept', false )

SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table
SQL> REM (the rowids will change).  This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one.  Also, the packages will be written
SQL> REM to the SCREEN, not into the database.
SQL> exec update_cascade.on_table( 'dept', false, true )

The above would generate and compile all of
the packages/triggers need to support
cascading updates on the dept table to any other table.
You would run this any time you

changed the primary key definition of the dept table
added a child table to the schema
(eg: executed a create table and that table points to dept)
removed a child table from schema

NOTE: The user executing update_cascade must have been granted

CREATE TRIGGER
CREATE PROCEDURE

directly to themselves. They cannot just have the privilege via a role.

The other mode of calling update_cascade.on_table is as follows:


SQL> set feedback off
SQL> spool tmp.sql
SQL> exec update_cascade.on_table( p_table_name => 'dept', p_use_dbms_output =>TRUE )
SQL> spool off

The above would generate and print (as opposed to compile) all of the
packages/triggers/views needed to support cascading update on the dept table
to any other table. You would use this mode to

Avoid having to grant CREATE VIEW, CREATE TRIGGER, CREATE PROCEDURE
directly to the person (they can get these priveleges via a role like DBA).
To inspect the generated code to understand what it does.
To generate a package that can be used to install update cascade support
at another site without giving them the update_cascade package itself.

The other mode of calling update_cascade.on_table is as follows:


SQL> exec update_cascade.on_table( p_table_name => 'dept', p_preserve_rowid =>FALSE )

The above would generate faster versions of the udpate
cascade packages. They run in 2/3 of the time of the default version but
the rowid's of the updated parent records will change. This makes this
version less desirable with FORMS. If you use forms heavily, use the
default mode so that rowids are preserved.
The triggers to cascade can
get away with a lot less work in this mode. The
triggers fire half the time they would in the default mode and an update that
would normally fire and affect 2x the number of rows is not needed. The
generated package code is streamlined as well (less actuall code is
generated, procedural code not just updates).

DEMOBLD.SQL

This script creates one user and three tables in that users schema. The user is
called UCDEMO.
WARNING: This script does a "drop user ucdemo cascade". It is
run as SYSTEM. Please review it before running it. Modify if you want.

Once the script creates the user it will create six tables and populate them.
The tables are:

DEPT with a primary key
EMP with a primar key, DECLARATIVE RI to DEPT, DECLARATIVE RI to EMP
PROJECTS with a primary key, DECLARATIVE RI to EMP
T1 with a three part primary key
T2 with a three part primary key and a three part foreign key to T1
T3 with a three part primary key and a three part foreign key to T2

To begin the demo, you might issue:


SQL> update dept set deptno=deptno+1;
update dept set deptno=deptno+1
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005184) violated - child record
found

Then to see the update cascade in action, you would:


SQL> @uc


Table created.

Table altered.

Package created.

Package body created.

SQL> exec update_cascade.on_table('dept');

PL/SQL procedure successfully completed.

SQL>

SQL> select * from dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
 10 ACCOUNTING     NEW YORK
 20 RESEARCH       DALLAS
 30 SALES          CHICAGO
 40 OPERATIONS     BOSTON

SQL> select empno, deptno from emp where deptno = 10;

EMPNO     DEPTNO
---------- ----------
7839         10
7782         10
7934         10

SQL> update dept set deptno = deptno+1;


4 rows updated.

SQL> select * from dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
 11 ACCOUNTING     NEW YORK
 21 RESEARCH       DALLAS
 31 SALES          CHICAGO
 41 OPERATIONS     BOSTON

SQL> select empno, deptno from emp where deptno in ( 10, 11 );

EMPNO     DEPTNO
---------- ----------
7839         11

7782         11
7934         11

SQL>

As you can see, after the package is installed,
the updates cascade successfully to
the child tables. The effect of this is even more
dramatic when you do it to the
emp table. The MGR column of the EMP table points to
the EMPNO column of the EMP
table. In addition, the EMPNO column of the PROJECTS table points to the EMPNO
column of the EMP table. The following scenario is a good demo of more complex
integrity:



SQL> select empno, mgr from emp;

EMPNO        MGR
---------- ----------
7839
7698       7839
7782       7839
7566       7839
7499       7698
7521       7698
7654       7698
7902       7566
7369       7902
7788       7566
7844       7698
7876       7788
7900       7698
7934       7782

14 rows selected.

SQL> update emp set empno = 8000 where empno = 7698;

update emp set empno = 8000 where empno = 7698
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record
found


SQL> exec update_cascade.on_table('emp')

PL/SQL procedure successfully completed.

SQL> update emp set empno = 8000 where empno = 7698;

1 row updated.

SQL> select empno, mgr from emp;

EMPNO        MGR
---------- ----------
7839
7782       7839
7566       7839
7499       8000
7521       8000

7654       8000
7902       7566
7369       7902
7788       7566
7844       8000
7876       7788
7900       8000
7934       7782
8000       7839

14 rows selected.

UNINDEX.SQL

The lack of an index on a foreign key will adversely impact the performance
of a cascading update. For example, the emp table is created via:


CREATE TABLE EMP
(EMPNO NUMBER(4) primary key,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) references emp,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) references dept);

The MGR column and the DEPTNO column are not indexed by default. So, for
example, if one were to issue:


SQL> update emp set empno = empno+1;

This would eventually issue:


update emp set mgr = SOME_NEW_VALUE
where mgr = SOME_OLD_VALUE;

Since the MGR field is not indexed, this update would do a full scan. It
would do this full scan once for each row in EMP that was updated.

Unindex.sql will generate output such as:


SQL> @unindex.sql

STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
**** EMP                            MGR
ok   EMP                            DEPTNO               DEPTNO
ok   PROJECTS                       EMPNO                EMPNO, PROJ_NO
ok   T2                             A                    A, B
ok   T3                             A, B                 A, B, C

SQL>

The four **** indicate that MGR is a foreign key in the EMP table that is
not indexed. It should be (also see chapter 6 in the server application
developers guide for other reasons why un-indexed foreign keys are bad).

GENERATE.SQL

This is a simple script that will generate all of the needed
update_cascade.on_table( 'table_name' ) statements that need to be executed
for all parent tables in a schema. For example, in the demo account it
would create a spool file that contains:


SQL> @src/update_cascade/generate

prompt Update Cascade on table: DEPT
execute update_cascade.on_table( 'DEPT' )

prompt Update Cascade on table: EMP
execute update_cascade.on_table( 'EMP' )

prompt Update Cascade on table: T1
execute update_cascade.on_table( 'T1' )

prompt Update Cascade on table: T2
execute update_cascade.on_table( 'T2' )

HOW it works:

When you update the primary key of a parent table, you might want to
cascade the update to the children. This is hard to do for many
reasons and can be problematic. This package works around the lack of
an update cascade option.

This package uses three triggers to perform it magic.

A before update trigger; used to reset some package variables
A before update, for each row trigger; used to capture the before
and after images of the primary keys in pl/sql tables.
It also 'undoes' the update to the primary key.
An After update trigger that does the following steps:
- 'Clones' the parent records with their new primary key, eg:
  insert into parent select NEW_KEY, other_cols
  from parent where CURRENT_KEY = ( SELECT OLD_KEY
  FROM DUAL)
  
  for example, given "update dept set deptno=deptno+1", this would
  insert the values of 11, 21, 31, 41 into the dept table. 11 would
  have the values in the rest of the columns that 10 had. 21 would
  look like 20 and so on.
- If p_preserve_rowids = TRUE, then the primary keys of the row that
  was cloned and the clone would be flip flopped. For example, if you
  issue: update dept set deptno = 11 where deptno = 10 we would make 10
  become the new value 11 and 11 become the old value 10.
- Re-Parents the child records in all subordinate tables.
  Performs the equivalent of:
  update child set fkey = ( select new_key
  from DUAL )
  where fkey = ( select old_key from DUAL )
- It then removes the 'cloned' parent records or the record with the
  old primary key value.

A look at the code

The follow annotated code is the generated packages and triggers you
would create by generating support for the dept table.

The following package spec is what would be generated for the
typical 'DEPT' table
found in the scott schema (when declaritive RI is used). See the annotations in
the spec for a description of what each entry means and how it is used. The
annotations are not found in the generated code, the generated code is not
commented.

This generated code preserves rowids. The code that preserves rowids will
be in bold. This code would not be present in the generated package if
rowid preservation was disabled.



SQL> exec update_cascade.on_table('dept',true,true);

The following is a typical package specification generated for a table.
The package spec name is always u || TABLE_NAME || p. The package name is
in mixed case (to prevent collisions with other user objects).


 create or replace package "uDEPTp"
 as
 --

Rowcnt is used to collect the number of rows processed by a given update
statement. It is reset in the uDEPTp.reset routine in a before update
trigger. The 'inTrigger' variable is used to prevent recursive firing of
triggers when p_preserve_rowid = TRUE;


     rowCnt    number default 0;
     inTrigger boolean default FALSE;

 --

For each element in the primary key, a table type will be declared and then
an array of that type will be declared to 1.) hold the before image, 2.) the
after image, and 3.) an empty array used to zero out the previous two
arrays.


     type C1_type is table of "DEPT"."DEPTNO"%type index by binary_integer;
 --
     empty_C1 C1_type;
     old_C1   C1_type;
     new_C1   C1_type;
 --
 --

Reset is the routine fired by the BEFORE UPDATE trigger that resets the
rowcnt variable and empties out the arrays from the previous invocation.


     procedure reset;
 --

Do cascade is the work horse routine. It performs the actual cascade when
fired from an AFTER UPDATE trigger.


     procedure do_cascade;
 --

Add Entry simply increments the rowcnt and collects the before/after images
of the primary keys. It also 'undoes' the update to the primary key by
accessing the :new and :old variables.


     procedure add_entry
     (
          p_old_C1 in "DEPT"."DEPTNO"%type
         ,p_new_C1 in out "DEPT"."DEPTNO"%type
      );
 --
 end "uDEPTp";
/

This is the package body generated. It implements the above specification


 create or replace package body "uDEPTp"
 as
 --
     procedure reset
     is
     begin
 --

This line is present in all routines when p_preserve_rowids = TRUE. It
prevents recursive firing of the triggers.


         if ( inTrigger ) then return; end if;
 --
         rowCnt := 0;
         old_C1 := empty_C1;
         new_C1 := empty_C1;
     end reset;
 --
     procedure add_entry
     (
          p_old_C1 in "DEPT"."DEPTNO"%type
         ,p_new_C1 in out "DEPT"."DEPTNO"%type
      )
     is
     begin
 --
         if ( inTrigger ) then return; end if;

 --

This code saves the before and after images in pl/sql tables and 'undoes'
the primary key update by setting the new columns back to the old columns.


         if (
              p_old_C1 <> p_new_C1
          ) then
         rowCnt := rowCnt + 1;
         old_C1( rowCnt ) := p_old_C1;
         new_C1( rowCnt ) := p_new_C1;
         p_new_C1 := p_old_C1;
         end if;
     end add_entry;
 --
     procedure do_cascade
     is
     begin
 --
         if ( inTrigger ) then return; end if;
         inTrigger := TRUE;
 --

For every row that was updated we will perform the clone, cascade and
delete....


         for i in 1 .. rowCnt loop

This insert clones the parent row, duping the old values with the new
primary key.


             insert into DEPT (
              "DEPTNO"
             ,"DNAME","LOC") select
              new_C1(i)
             ,"DNAME","LOC"
             from "DEPT" a
             where ( "DEPTNO" ) =
                   ( select  old_C1(i)
                       from dual );
 --

This code is generated only when p_preserve_rowids=true and will flip-flop
the old and new primary keys, hence preserving the rowid of the original
parent.


             update "DEPT" set
             (  "DEPTNO" ) =
             ( select
                  decode( "DEPTNO", old_c1(i), new_c1(i), old_c1(i) )
               from dual )
             where (  "DEPTNO" ) =
                   ( select  new_C1(i)
                       from dual )
                OR (  "DEPTNO" ) =
                   ( select  old_C1(i)
                       from dual );
 --

Do a cascade update to all children tables.


             update "EMP" set
             (  "DEPTNO" ) =
             ( select   new_C1(i)
                 from dual )
             where (  "DEPTNO" ) =
                   ( select   old_C1(i)
                     from dual );
 --

Removing the old primary key value.


             delete from "DEPT"
              where (  "DEPTNO" ) =
                    ( select  old_C1(i)
                        from dual);
         end loop;
 --
         inTrigger := FALSE;
         reset;
    exception
        when others then
           inTrigger := FALSE;
           reset;
           raise;
     end do_cascade;
 --
 end "uDEPTp";
/

Lastly, we have the three triggers placed on
the parent table to effect the update
cascade. The first trigger simply 'resets' the package variables above.


create or replace trigger "uc$DEPT_bu"
before update of
"DEPTNO"
on "DEPT"
begin "uc$DEPT_pkg".reset; end;

The next trigger, the for each row trigger,
simply calls add_entry for each changed
row.


create or replace trigger "uc$DEPT_bufer"
before update of
"DEPTNO"
on "DEPT"
for each row
begin
"uc$DEPT_pkg".add_entry(
:old."DEPTNO"
,:new."DEPTNO"
);
end;

The last trigger, calls do_cascade to effect the change


create or replace trigger "uc$DEPT_au"
after update of
"DEPTNO"
on "DEPT"
begin "uc$DEPT_pkg".do_cascade; end;

Download

Download all files in a tar format

http://asktom.oracle.com/tkyte/

2009-10-08T14:21:00.014-04:00

This page will be published and republished over the next couple of days and into the future. It will contain pointers to the material that was previously hosted on http://asktom.oracle.com/tkyte. I'll be reformatting those pages to fit into blogspot here and host the data supporting the pages in my "files" tab on asktom. After that is all done, I'll be doing an update to the questions and answers on asktom to map to the new URLS for everything.

So - beware - you will see a flurry of activity on my blog for a bit. It won't be anything new, but it might be something you didn't see before...

Selected Utilities/Frequently Asked Questions

Advert of sorts: Canada, UKOUG, Chile, Peru, Poland...

2009-09-20T18:29:00.004-04:00

For all events between now and the end of the year, my speaking schedule is published on asktom.oracle.com. This post hi-lights the "international" (for me, if you live there - it is local of course...) trips I'll be doing. You can see all of the US only trips on asktom as well - and there are quite a few (Atlanta, Baton Rouge, Reston VA, Baltimore, San Francisco, Saint Louis, Richmond VA, Birmingham AL - to name a few).

I'm frequently asked "how often am I on the road" - the question behind the question being "how do you get to keep up with the database and stay hands on". I travel exactly 50% of the time - one week on, one week off. I'm getting ready for a week "on" right now. I spend the other 50% working locally (mostly at home) with the database and customers - preparing new material, getting caught up on questions on asktom and the like. From time to time - since they will not move events like Oracle OpenWorld to accommodate my schedule - I travel two weeks and stay off the road for two weeks. One of those "two weeks - no travel" is coming up and I'm very much looking forward to that.

Anyway - to the schedule and links to what's happening...

I'll be in Canada quite a few times this year - three times this week (Quebec, Toronto and Montreal). I'll be back in Canada - a little further west and a lot further north - in November for the ICE conference in Edmonton. I get back to Toronto (for the local user group) again in November as well.

I'll be hitting Chile and Peru in South America in November - again looking forward to that as I've never been down there at all. The user group events are something I really enjoy (more than a seminar - those are very very tiring to deliver. Physically tiring) and getting out to a place I've never been is always interesting.

After that - I'm off to the UKOUG. I've presented there many times now and always look forward to the event in Birmingham. A good time to catch up with a lot of people and much like IOUG in the US - it is a highly technical conference. Every single session is delivered by people using the Oracle software, and mostly delivered by people that use the Oracle software every single day. A lot of hands on knowledge is transferred. Always looking forward to that conference.

My last trip out of the US in 2009 will be to Warsaw, Poland right after the UKOUG. I'll be delivering a two day seminar there. I won't have much time to look around unfortunately as I fly in late Tuesday night from the UK and leave early Friday morning for home. I'll have to have a nice dinner or two out though to get a feel for the city.

Into the first half of 2010 - I'll be hitting places such as Croatia, Tokyo, Moscow, Hungary, two cities in South Africa, the Baltics and Turkey... More details on those to follow...

I'm not a DBA anymore...

2009-09-18T10:14:00.005-04:00

After nine years and nine months of running the database that hosts asktom, I've retired.

Not from answering questions, but rather from being the DBA and semi-SA for the machine that was asktom.oracle.com.

Asktom.oracle.com is now synonymous with APEX.oracle.com - Joel Kallman facilitated the move (here is the story on that). So, I'm now running in a database with thousands of other applications. And - it seems a bit faster than my old hardware (which was held together with some glue and duct-tape - the drives were failing left and right...).

For some stories on APEX and the ability to handle a pretty large load - I suggest you glance at:

But now, for the first time in years, I won't be afraid to check asktom when I land and get off of the plane. If something goes wrong - the data center guys will fix it :)

And yes, the intermittent "some IE users get an error on asktom" issue was found and fixed.

Every day I learn something new...

2009-09-15T10:02:00.002-04:00

Two things

One, here is something new I learned today. I wasn't aware of that, that is a nice "stealth" feature :)

The other thing - I have a podcast on 11g Release 2 "things" posted on oracle.com if you are interested....

Memories...

2009-08-07T06:59:00.005-04:00

Wow, I stumbled on this - and I so remember it.

I remember my first Pascal (turbo of course), that really got me started programming at home, in my spare time. That was the beginning of the end.

And when I got Turbo C, that was it. I was hooked. I cannot count the number of times I had to reboot my computer learning C - as I was constantly overwriting memory in the beginning - but it was worth it. How many times did I have to flip flop the floppies "Insert Library Disk 1", "Insert Library Disk 2" - because I had no hard drive...

A blast from the past.

This morning, as I was crawling under my desk to get to the USB hub to plug in yet another device, I was thinking "remember back in the day when every device you bought came with an 'expansion card' and you had to crack the case to install hardware - this is too easy".

My first hard disk - partition it into 32mb or less partitions (DOS didn't do more than 32mb on a disk back then...), install hardware, reinstall hardware, load drivers (by hand...), have at a really really slow disk..

My first computer CD device - hardware to install first, then lots of device drivers (by hand, edit that config.sys)...

My first scanner, ditto

Modem... the same.

and so on. It is very much easier these days, but you lose a bit of knowledge with that ease of use. Maybe that is why I had to opportunity to write this...

It is true...

2009-08-06T06:43:00.002-04:00

I get to see a lot of "in house" applications - those applications developed internally for and by a company itself.

The screens on these applications many times have more fields on them than the mind can fathom. Fields and buttons galore.

Just like this cartoon demonstrates...

It is so true - I like simple user interfaces (yes, I'm a metalink classic fan too...)

Sometimes...

2009-08-04T13:59:00.003-04:00

Every now and then, you read something that makes you go "huh".

I was answering some questions on asktom today and had one about a CSV (comma separated values) file. Thought I would point the person to the 'specification' for that file format and the first searching I did turned this up (from a forum)

Perhaps I misunderstand the question because I'm not sure what
"format" and "escape" characters are.

However, to my knowledge, CSV files are nothing more than ascii text
files, which means the font is courier and 12 pt.

The emphasis is mine - a CSV file is just a file where the text is in 12pt courier! That is so simple.

Sometimes, you read something and it just makes you laugh out loud...

Collaborate09 thoughts...

2009-05-28T08:40:00.001-04:00

I've been meaning to write up my thoughts from this years IOUG Collaborate event. Ian Abramson, the president of the IOUG, has a nice series of write ups on the event itself - starting with day 1 through day 3 and then a wrap-up post. He describes it as a three day event - but to me it is more of a four day thing. For the last couple of years, I've done a university day at Collaborate - so once again I spent all day Sunday May 3rd in a conference center talking about Oracle for eight hours. This year I talked about Encryption, all of the Flashback technologies, Read & Write Consistency (a really nice way to introduce the flashback stuff actually, sort of a pre-requisite) and Database Reorganizations (when to, when not to and when you have to - how to).

I also had a one hour "regular" session on Monday where I presented on "What's new in Oracle Application Development" where I touched on general database enhancements, ODP.Net, PHP/Ruby/Python, Java/JDBC, SQL Developer and APEX advances. This is the session where I got most of the questions/comments this year.

I think I might have scared away some of the DBA crowd with my session title, they see "Application Development" and stay away - but I know for sure at least one DBA was in the audience. He came up to me afterwards and commented on how glad he was to have attended - for a pretty simple reason. In each of the tools I discussed (mostly .NET, SQL Developer and APEX) - I pointed out how they easily tied the application into AWR (automatic workload repository) and ASH (active session history) facilities and how they could be used as an interface to the tuning/diagnostic packs. He is sure the developers he supports are unaware of this tie in and he himself certainly was - but his first course of action upon getting back to the office would be to expose them to it. The problem he has had in the past is that many people consider tuning "a DBA task" - when in fact - it is really a developer's thing to do. Since most developers don't have access to, or don't want access to Enterprise Manager - they assumed the advanced tuning features were not available to them. Not any more - all of the development tools now have tie in's to AWR/ASH and various features of the tuning/diagnostic packs.

One of the areas of large interest is the new (in beta) SQL Developer data modeling features. A lot of people (self included) still find the good old ERD (entity relationship diagram) a good way to envision/model your schema. SQL Developer now has an engine to do just that - with the ability to spit out various different physical models from the same logical model - either in support of different databases (yes, it does not just do Oracle), or in support of different environments (simple storage characteristic changes).

One thing I find interesting/good about an event like Collaborate is the ability to learn something new. Even after using Oracle for going on 22 years, I still learn something new often (almost every day). Regardless of your skill level, there is the opportunity to expand your knowledge of the stuff you use. For example - Jonathan Lewis (a pretty smart guy when it comes to Oracle knowledge) posted about some things he discovered for the first time. I strongly encourage everyone to attend at least one event if possible every year - to network, to learn and..... to present. IOUG Collaborate is a good venue for doing that - UKOUG is another - and the call for papers for the UKOUG event in November is currently happening.... Take a chance - go for it. It'll probably go much better than my first presentation :)

An interview question...

2009-05-13T21:16:00.001-04:00

A couple of days ago I read an article "One of the toughest job-interview questions ever". I was reminded of it by this posting...

I found the original post interesting - mostly because I liked the answer the technical writer gave. A bit of background - someone interviewing for a technical writing position is asked what is clearly a "hard core, heads down, write code programmer question". The question seemed entirely inappropriate for the position - but - the answer given was great (I thought)

The answer consisted of lots of questions - in effect - a lot of push back. Define this, specify that, clarify this - need more information.

I can relate.

What surprised me was that a lot of the feedback was negative. A lot of people said "would never hire you", "you missed the point".

All of the time I was reading though, I was nodding my head saying "yeah, what about that". I would have hired him on the spot. Critical thinking, push back, give me the details, tell me what you are really trying to do.

The programmer that rolls over and just answers the question - without enough information to actually answer the question - should send the interviewer running away. But that is apparently what a lot of interviewers are looking for.

I've been known to have three to four very simple interview questions for "Oracle people". They are designed to test the simple to the sublime. They are:

I have a table:

create table t ( ....., month number, ..... );

Month is always a number between 1 and 12.

I ask three questions about this table:

1) how many rows are in the table

2) how many rows BY MONTH are in the table (i want to know how many rows for month one, month two
and so on)

3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)

The fourth question is more of a "do something for me" - and that is "go to the white board, draw a picture of Oracle and tell me how it works".

As the link says - a surprising number of people *struggle* (seriously) with the first question. The second - gets *most* (seriously) of the rest. The third question freaks them out mostly. Especially the parenthetical part. The fourth question - sends people running out of the room.

That is why I liked the article I originally read - the author was poking around, developing derived requirements, fleshing it out, figuring out what really needed to be done, not rolling over and saying "you got it, I'll be right on it, we'll do that straight away". Developers (DBA's, whatever) that don't push back, that don't dig into the question, that don't try to convey "this is more complex than you think, we need to go a bit into this to figure out what you really need" - well, I don't have any patience for them. They do not belong (in our profession).

Will that person (the interview-e) annoy you? Sure, from time to time (I'm sure that every now and then - someone is annoyed by me, probably).

Will you ultimately be really happy they were there? Absolutely.

Will the person that rolls over annoy you? Absolutely - every time - most of the time probably. Especially after they really mess you up the first time they are so "flexible". Will you ultimately be even a little happy they were there? I doubt it.

I've said many times - there are only TWO answers to all technical questions. They are:

WHY (why do you want to do that)
IT DEPENDS (it really does, and it requires digging around, poking, probing to figure out what it depends on...)

poke, probe, ask, discuss, dive deep, play stupid (it works, really) - but get the information...

A unique opportunity...

2009-04-01T19:54:00.001-04:00

Learn SQL from one of the original SQL'ers. Chris Date is doing some a seminar in conjunction with Cary Millsap's company Method-R. Unfortunately I cannot make it that week (I'd be there if I could) - but it is a rather unique and uncommon offering. I've often said that if you want to be able to "tune" SQL, you need to understand what is happening underneath the covers, what is available. You don't want a 10 step checklist, you want knowledge. That is what this is all about.

This article by Cary sums up the seminar as well as why it will be good (in his humble opinion)... Give it a look see...

Happy Birthday...

2009-03-13T10:30:00.001-04:00

To both the World Wide Web, age 20 years this day and Linux v1.0, age 15 years this day. Two creations that have changed a lot of things. Sort of neat that they happened on the same day, albeit five years apart...

Very good advice...

2009-03-09T12:27:00.001-04:00

I've been reading Seth Godin's blog for many years. He is a 'marketing' person, with a lot of good old fashioned common sense. I agree with most of what he writes - and he just did a longish post (for Seth Godin it was long). It was on Slack.

His two points in these unique times - if you find yourself unexpectedly with more free time than you had anticipated you should consider:

a) Continuing your education, learn something new. As you go to interview and look around, people will ask you what you've been doing with your time. If you can arrive at an interview with "I've been learning X in my free time" and be really excited about it - be able to converse about it, that'll be a really positive thing.

b) Participate - join the forums - become known. I've said that myself many times in the past.

So think about that if you find you have more time on your hands than you anticipated having... Not bad ideas. Even if you don't have a sudden abundance of free time - maybe find the time to do these two things..

Missing. The. Point....

2009-02-09T20:26:00.001-05:00

I probably buy 80 to 90% of my non-grocery items online. Furniture, pictures, gifts, TV's, books, kitchen stuff - whatever I can - all online. I hate the "store" experience. Before I go into an actual physical store I usually know exactly what I want - buy it and leave. It took me about 5 minutes to buy shoes this weekend :)

I buy online for the convenience - and the experience is fairly similar regardless where you shop. You typically have to create "that account" (even if you never intend to shop there again..) and you get that form to opt in or out of mailing. They almost always default to "opt in" and I invariably set it to "opt out"

I just bought some shelves while sitting here in King of Prussia, PA (I live in VA, another benefit of shopping online, just do it when/where-ever you want)... I received two emails. Email 1 - my receipt (great). Email 2, well, it was in response to me opting out:

While registering as a shopper with xxxxxx.com, you chose not to receive our promotional Email. This is being sent to confirm that yyyy@yahoo.com will not receive Email from xxxxxx.com.
The decision to receive Email is personal and can be influenced for a variety of reasons. In an attempt to better understand and respond to our customers, we would appreciate it if you would answer a short survey on this topic.

That just strikes me as "missing the point" :)

Can you imagine what my survey comment field might have contained.... The survey did contain

We value your feedback and encourage you to give us candid answers. Are there any comments you would like to make to xxxx? (Note: Response is limited to 250 characters)

250 characters. I shall have to choose my words carefully... I should have it written in Kanji to see if they support multi-byte and truly support 250 characters. Or if it is really 250 bytes.

A SQL Joke

2009-02-05T21:59:00.001-05:00

I don't think I've seen one before. An actual "joke" about SQL

A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

It is about as funny as most of my jokes. All the more funny to me since I just (as in a couple of minutes ago) wrote:

Joins are what RDBMS's do for a living.

Anyway - you can hit this link to see if it gets any comments...

A couple of links and an advert...

2009-02-05T14:03:00.001-05:00

Instrumentation

Cary Millsap has recently pointed to a couple of postings and quoted some people on the fine art of instrumentation. He quoted me (and got it pretty much 100% - dead on) a couple of times as well. I'll add a quote/story to his list of quotes...

When Oracle 10g first was released - I was out and about talking about new features. One of the big ones was the ASH/AWR/ADDM set of functionality. One time, while presenting it an audience member raised their hand and innocently asked:

What is the overhead of this, what performance impact will this have on my system

I paused for a second, thought about it, and said:

Probably at least negative 10 percent or less (meaning more - like negative 1000%, more negative)

The audience now paused and we sort of looked at each other and then I explained. The addition of this instrumentation/repository will allow your database to perform better than it currently is - any 'overhead' of the additional instrumentation is more than offset by the gain in performance.

Instrumentation, using bind variables correctly, not swallowing exceptions (when others, not followed by RAISE), avoiding triggers (and side effects) and designing your system to perform in the first place - it is a really short list I have but everything on it has profound impact.

It's a small world

As it turns out - both Cary and I were in Utrecht, the Netherlands recently. He was there one week, I was there the next (just missed each other). We were both there to deliver seminars and he pointed out that someone who had been to both posted a critique. It was neat to see how the two sessions seemed to have complimented each other.

And since that is sort of a review of my seminar - that brings me to the

Advert

You can see my scheduled on http://asktom.oracle.com/ - there is a calendar there showing my public events. I would like to point out that I'll be doing two day seminars in:

Prague, Czech Republic - March 10th-11th
Athens, Greece - April 7th-8th
Rome, Italy - May 18th-19th

Links to information regarding pricing, location and so on are on the the site. Also, any and all user groups I'm attending for the next 2-3 months is posted there as well. Hope to see you at one (or more) of the events.

Ok, I feel old...

2009-01-16T23:54:00.001-05:00

My kids couldn't/can't do it to me (make me feel old).

Physical exertion doesn't do it to me.

Nothing really did that - until I read this. Not the blog entry - but the comments that said things like:

That being said, that thing is NEAT. I have no idea what it is though. Something to do with old punch card computers?

Nice piece of computer history, though I have no clue what it is. =P

I had an IBM flowcharting template once (more than once), and not as a hand me down. I had the little paper pouch it went into as well. Ouch.

If this Oracle gig doesn't work out...

2009-01-09T11:09:00.001-05:00

Maybe I can become a photographer :) One of my pictures from a recent trip to Sydney Australia was chosen for inclusion in a travel guide.

I don't think that picture is one of my "best", but it was of historic building they wanted to include in the guide. I wish they would have picked a better shot - I like this one myself:

And this one would have been pretty cool - if I had centered it just a little more to the left:

but it was nice to be chosen...

Happy New Year...

2009-01-08T08:51:00.001-05:00

Happy New Year to everyone...

Dr. Dobb's Journal...

2009-01-07T18:15:00.001-05:00

Wow, a bit of my past is going away... Funny enough - I referred to that magazine just today in a talk.

Without Dr. Dobb's Journal - there might not be an asktom.oracle.com site.

Why not? What is the link? Dr. Dobb's is just a programmers journal after all (it and the C Users Journal were two subscriptions that changed my life, I learned a lot from them both). Well, the link is that without Dr. Dobb's I might not have learned Oracle - or learned it much later than I did - or learned it differently, less completely.

I wrote about Dr. Dobb's in the foreword to a book once and I'll reproduce it here:

In 1987 I was just graduating from college and starting my career as a software developer. I started as a PL/I programmer on IBM mainframes using two databases – SQL/DS on VM/CMS and DB2 on MVS. I became familiar with SQL, but was limited as to what I could do on these production environments.

One day while reading a magazine, Dr. Dobbs Journal, I noticed an advertisement for a relational database that ran on DOS – simple PC’s. It was a product named “Oracle”. I clipped out the coupon – filled it in and ordered this relational database for $99. About 2 weeks later – a dozen or so 5-1/4” floppy disks showed up in my mailbox and I had Oracle version 5.1.5c and all of the development tools I needed to start playing, learning and exploring with. I was hooked.

That was then, this is now – and now, you have the ability to do in 10 minutes, for free what took me weeks and $99 ($166 in 2006 dollars!) in 1987 accomplish. With the introduction of Oracle Database Express Edition – you can download, develop, deploy and distribute your applications for free.

Dr. Dobb's Journal
C Users Journal
Borland's Turbo Pascal and Turbo C
Oracle version 5.1.5c

Things that changed everything for me...

Slowly disappearing - well, except for Oracle of course - and C. I haven't read Dr. Dobb's in a while, I still write a bit of C here and there, I definitely use Oracle everyday.

This should be fun to watch...

2009-01-07T11:06:00.001-05:00

On reddit - you can post a 'question' to the community. Someone just posted "What is the worse design decision that you have made ?" My favorite comment on that thread so far is this one:

It started with an thought not unlike the following:
"Nah, flat-file should be fine."
It ended in tears. And a double digit load average.

Been there, seen that happen. My worst design decision I personally made was when I "invented" (see below for why I say "invented") the "funky data model" - better known as an EAV (entity-attribute-value). You know, the extensible model where all you need is four tables:

objects
attributes
object_attributes (objects is 1:M with object_attributes)
links (links objects to objects, an association table)

Man, I could store *anything* in there. And it was very secure - because trying to retrieve anything was really hard. Not only really hard, but really slow.

But the prototype/demo was awesome. It never worked in real life though.

I used the term "invent" above. Of course I didn't "invent" the EAV - it has existed as a concept for a long long time. But, I see it get re-invented in relational database applications over and over and over again. And every developer thinks for a short period of time "hey, this is so cool - I wonder why no one else has thought of this - they must not have thought of it or everyone would be doing it - I'll be famous".

And then they learn why not everyone is doing it :)

All about joins...

2009-01-02T10:19:00.001-05:00

I've pointed to an excellent youtube database posting by Stephane Faroult in the past - about worst practices in the entry "The word pathetic never sounded so good..."

He has published another short video (six minutes) where he stuck me between Descartes and Kipling during the story telling. As he wrote me, I could have been in worse company!

As before, I enjoy his style of delivery. And to see my dance floor analogy animated was sort of amusing for me.

Happy New Year all!

Hey, they stole my line!

2008-12-11T19:44:00.001-05:00

I've been known to say from time to time "my car won't start, why not?" (followed many times by a "I've given you as much information as you gave me regarding your problem so now we are even")

Well, these guys stole my story!!! http://notalwaysright.com/diagnostics-through-osmosis/1379

Very funny blog most of the time - I can feel their pain.

Doing it wrong...

2008-12-11T19:06:00.001-05:00

I hate queries of the following form:

select count(*) from <anything else here>

The reason? The code typically looks something like this around the count(*):

select count(*) into l_cnt from .....;
if ( l_cnt > 0 )
then
    process_the_data;
end if;

I've always wondered why that code isn't just:

process_the_data;

Why bother counting - and then processing if that count was greater than zero. Why not just process_the_data - that routine already knows how to stop when it runs out of data - just let it run out of data naturally on row zero if there is no data.

Many people don't stop to consider that

The count can change between the select count(*) and the process_the_data call - there might be nothing by the time you get into the process_some_data

The count can change while you are running the process_some_data call itself - you cannot use the count as "this is how many times to iterate" (I've seen it done - it fails spectacularly when there are less rows than you counted, it fails silently when there are suddenly more and you never get to them, it also sometimes works by accident).

I've seen code like:

select count(*) into :cnt from t;
allocate array of :cnt elements
open C for select * from t;
for i in 1 .. :cnt
loop
   fetch c into array(i);
end loop;
close c;

You can just imagine the damage that could do in a language like C for example - interesting results when there are less than :cnt rows to get, segmentation fault - core dumped (we HOPE - we hope it crashes) if there are more than :cnt rows to get.

Anyway, this isn't a post about "don't count and then process" (well, ok, it is in part) - this is a post about an interesting snippet of code a friend sent me. They are on site doing some "tuning". I've modified the variables and such to disguise it - but the "logic" is intact:

FUNCTION count_em_up
( p_input1 in number, 
  p_input2 in varchar2 
)
return number
IS
   CURSOR C 
   IS
   SELECT actual_columns
     FROM some_table
    WHERE a_column = p_input1 
      AND another_column = p_input2;

   l_the_cnt    number default 0;
BEGIN
   FOR rec IN C 
   LOOP
      l_the_cnt := l_the_cnt+1;
   END LOOP;
   RETURN l_the_cnt;
EXCEPTION
   WHEN OTHERS THEN
        RETURN NULL;
END;

That hurts me in so many ways.

The dreaded "when others <no error raised here>"

A loop to COUNT THE ROWS RETRIEVED BY A QUERY!!!

Because I did not believe it: A loop to COUNT!!! (had to be said twice)

A function to count rows - probably used in higher level code like this "if count_em_up(x,y) > 0 then process_some_data; end if;"

Well, at least there is the very real probability of tuning this particular application - there is probably lots and lots of low hanging fruit out there like this!