Things that caught my attention this week...

Things that caught my attention this week. Some possible “NSFW” words in some of the links – if that would offend, you’ve been warned.

My all time favorite programming language is not C but C is a very close 4th (SQL, PL/SQL, and PL/I beat it out). So, I saw this “California Coding” page and laughed out loud. Ah, the fun you could have with macros – hated them, never knew if I was calling a function or if something else was happening. When used properly – they (macros) were very cool. When abused, obfuscated code resulted… In a similar vein – here is a quick cartoon. Reminded me of Jr. High School. I had to write some silly sentence a couple of hundred times more than once. Wonder if they still use that as a punishment.

Couple of visual tricks passed by this week. The first is another chromatic illusion similar to one I wrote about before. The Spanish Castle was neat, but this one is even more “disturbing”. With the Castle – when you move your eyes, it goes back to black and white – this one endures longer. So, can you really believe what you see with your own eyes. Almost like we have a bug in our software… We are easily tricked (this one is pretty cool too)…

A tiny peek at my political leanings again. Jon Stewart rocks. In that link, he looks at video games and government control. I love his new rating system. The grand theft auto “visual” spoof was great. And Representative Pitts – well, watch it… I was astounded by the statements.

Lastly, this next link has many “NSFW” words in it, but is so so true.

You know how...

You know how many people make fun of the postal service at times. Well, I’m here to express my amazement at actually receiving a piece of correspondence. It was addressed as follows:



The only bit that is correct is “Mr. Thomas Kyte”. The street address used by the hotel was “1910 Orpale Way”. I work at “1910 Oracle Way”. The City – DC. Nope, DC is not a “place” really – Washington DC might be. But I don’t work there. I work in Reston VA. That number – 201190. Well, that is not even a zip code! They botched my real zip code of 20190.

So, a letter using a street that does not exist, in a non-existent city/state, with a zip code that isn’t a zip code….

Was delivered. In two weeks (or less – I don’t check my mail at work everyday, it came sometime between the 26th and 30th of June).

Kudos to the postal service. Not bad service for 39 cents – not only that but it was technically “international” mail. Canada Post had to first figure it out and then get it to the USPS.

It has been about...

It has been about one and a half years since I last bought a laptop. That is pretty long for me – the technology changes so fast and I use it so much that I typically got one per year. The last one I got, an HP Pavillion zd7000 has worked very nicely and still does – but, it is really large. Large and really heavy, really really heavy. So large that it just fits between the arms of the seat in a plane. So large it requires two fans to keep the heat down. So heavy that you cannot really use it on your lap – it is more of a desktop replacement. The battery life is a tad “short” as well – maybe 40-60 minutes these days.

So, I shopped around for a replacement and picked up a Sony Vaio TX790:



It has a pretty fast processor (1.3GHz Pentium M), battery life is 5 to 7 hours, the screen is small but has lots of pixels (1366x768), plenty of RAM (1.5 GB). At 2.76 lbs – versus the 9 lbs for the HP (closer to 12 lbs with the plug!) – this is truly lightweight. The screen looks amazing (I like small fonts to begin with and this thing is just crisp looking). I wanted to be able to read books (the Treo can do it, but the screen on that is a bit too small and getting it to read a PDF file reasonably is not something I could accomplish) as well as watch the occasional DVD on a plane – and this laptop will do that just fine.

I’ve got most of my software installed, have my VMWare images up and running – they seem to perform acceptably – haven’t yet decided if I’ll go 100% to the small laptop while on travel. I’ll probably pack the big one in my carry on the first couple of trips – to make sure I can do everything I need on the small one, with respectable performance.

I’ll be giving it a workout next week when I’m on vacation. It’ll be the only computer we are taking for the four of us – we’ll be sharing. That is a hint that asktom will be a little less updated than normal!

I was reading...

I was reading Eddie Awad’s blog entry on the “gender” of websites. I was curious about http://asktom.oracle.com/ and http://tkyte.blogspot.com/ of course.

I was surprised to find that asktom is ‘female oriented’, and young female oriented at that:




And that the blog is apparently interesting to male teenagers:



I doubt it – since there is no information on “how” they are making this determination, I have very little faith that it is even a tiny bit accurate.

Interesting, but I rank it up there with horoscopes. Fun to read, but not really meaningful.

Funny Unix Shell Commands...

Funny Unix Shell Commands.

$ got a light?
No match

Indeed… Not all of them work on all Unix/Linux variants, but still amusing.

I've enjoyed the "what the???" sites...

I’ve enjoyed the “what the??” sites – both the “Oracle” and “IT in general” one. Today – I’m going to post two of my own “what were they thinking”.

I received this function in the mail this morning. It is a neat one. Can you quickly tell what the purpose is? (I’ve renamed all variables, they were less meaningful before the change – did not want anyone grepping their code to see if it was one of their functions)

CREATE OR REPLACE
FUNCTION convert_date( the_date_string IN CHAR)
RETURN DATE
AS
   date_to_return DATE;
   yyyy_num       NUMBER(4);
   mm_num         NUMBER(2);
   mm_char        CHAR(2);
   dd_num         NUMBER(2);
BEGIN
   IF the_date_string IS NULL
   THEN
      date_to_return := NULL;
      RETURN date_to_return;
   ELSE
      yyyy_num :=
      TO_NUMBER(SUBSTR( the_date_string,1,4));
      mm_num   :=
      TO_NUMBER(SUBSTR( the_date_string,5,2));
      dd_num   :=
      TO_NUMBER(SUBSTR( the_date_string,7,2));
      IF mm_num > 9
      THEN
         mm_char := mm_num;
      ELSE
         mm_char := '0' || mm_num;
      END IF;
      date_to_return :=
         TO_DATE(yyyy_num || mm_char||
                dd_num,'YYYYMMDD') ;
      RETURN date_to_return;
   END IF;
END;
/

Yes indeed – the functional equivalent of:

To_date( the_date_string, ‘YYYYMMDD’ );

Interesting. The other one was a multi-page (many pages) query. The correlation names used in this query?

• Aaaaaa
  
• Bbbbbb
  
• Cccccc
  
• Aaaa
  
• Bbbb
  
• Cccc
  
• Dddd
  
• Aa (many times!! For many different tables in inline views)
  
• Bb (same as aa!)
  
• Cc

I told the person that sent it to me that I felt I was working on the human genome project with DNA sequences, not a SQL query. Best yet was this part in the query that looked like this:

…
from
( select aa.c1, aa.c2,
aa.min_this, aa.max_that,
bb.cnt_distinct
from (select c1, c2,
      min(this) min_this,
      max(that) max_that
 from t
where c3 between 'X' and 'Y'
  and c4 between 'A' and 'B'
group by c1, c2 ) aa,
(select c1, c2,
      count(distinct something) cnt_distinct
 from t
where c3 between 'X' and 'Y'
  and c4 between 'A' and 'B'
group by c1, c2 ) bb
where aa.c1 = bb.c1
and aa.c2 = bb.c2
) aaaa
…

Now, does anyone else see an obvious performance enhancing technique they might apply to this? Maybe we just move the count(distinct something) up into the AA inline view, lose BB altogether and remove the join…

Not only that, but that inline view – referenced multiple times (block copied) – so it was performance drag times 3. Remove the join – turn into a “with” subquery (execute it once and reuse it) and, well, things are better.

I'll never understand airline pricing...

I’ll never understand airline pricing. Just finished booking two trips. One – to California. I said when and where I wanted to go, picked United and the price was almost $1,000. Looking through the options – I saw I could go on USAIR for half as much. Looking closer – the USAIR flights are operated by United. It was the same flight – it’ll be a United plane. Only I’m paying 50% less because I bought the ticket through USAIR instead of United.

The other trip was a one way. One way to Dallas - $709. If I wanted to return, the round trip would be $427. Interesting.

Easily amused...

Easily amused… That describes me sometimes. Take a look at these funny shop names. I was particularly fond of “Brief Moments”, “The Frying Scotsman” and “Junk and Disorderly”. All very nice play words.

And a blast from the past. When programming was ‘cool’. I remembered that Atari print ad as I read it. “Little did they know I had Microsoft Basic II, the powerful programming language that uses simple English commands”. The entire ad is pretty funny.

And if you need a quick laugh and have the bandwidth to view it – this rocketboom from last week made me chuckle (the Bill Gates “interview” part).

Why do people email...

Why… Why do people email (unsolicited) really big things to people they do not know? I frequently get screen shots – of character mode sqlplus sessions. Yeah, that’s the ticket – let’s email a 5mb screen shot of our character mode sqlplus session. I get code (really long pieces of code). I get executables. I get dmp files. I get trace files. Alert logs. You name it. Compression – what is that? Cut and paste of simple text – what is that?

Today – a 1.8mb statspack report. The text of the email said “Maybe not the right way to approach for help. But please excuse. … Really sorry for the long post”. They know they are doing something outside the normal bounds of netiquette, but do it anyway. To top it off – the statspack was inline, not an attachment. Not even a chance it wouldn’t be downloaded.

Never mind that it might slam me big time on the road. Heck, when I was using a satellite connection – that simple email would have been more than 5% of my quota for the day!

To top it off – the statspack report was for 15 minutes (that is good), but unfortunately, it was 15 minutes when no one was logged on, when no activity was taking place in the database. Yes – transactions per second? 0.00. Executes per second? 0.47 (meaning, every two or three seconds on average, a statement was executed). User calls per second? 0.03 – 0.03! Each node reports – 1 CPU second used (total of 3 CPU seconds). The only SQL in the report? Yep – the SQL executed by the statspack snaps.

I have no idea why anyone would send such an email. None.

Guess it is sort of like a person at a party that meets a doctor and proceeds to take off a shoe to show them a foot problem and ask for advice.

This internet thing...

This internet thing might catch on… The “P” key on my Treo stopped working correctly. It would not click up or down anymore and the slightest touch would cause a “p” to be typed. P is very close to “O”, “L” and backspace on the Treo. So, typing was ‘hard’.

I sat down a while ago and started taking the Treo apart – only to discover, it really didn’t want to (come apart). I unscrewed all of the screws but no go.

Today, I was more determined. So, I googled “taking a Treo 650 apart”. First hit – got lucky. The Treo 650 “Tips, Tricks and Hacks” blog had an article “how to disassemble your Treo 650”. Photos – step by steps. It was great. Got the thing apart.

But, couldn’t see anything obvious to fix the “P” key. So, I put it back together (and it still worked). So I thought about getting a replacement keypad..

Googled “Treo 650 replacement keypad” and the second hit was someone wanting the same thing… because…. Their P key was broken (seems to happen a lot – the P key). Read the thread – and they said:

I took out the internal keypad, and since my P is near the edge, I just took a very small flat head to peel up the adhesive top, stick the flat head in the P spot, and poked the metal spring inside UP. now it's working fine.

Ok, one more try, get out that special tool (that all computer geeks have, the “special” screwdriver), take out the 7 screws on the Treo case, the 2 screws on the keypad, use tweezers to pull up the adhesive top – fix the little silver dot/button, put it all together and success. I have a functioning P key again.

I remember exactly where I was when that stupid key broke. On flight UA-917 from IAD to SEA to speak at the PSOUG (Puget Sound Oracle User Group). That was April 13th, it has been driving me nuts since. And now it is fixed. And the web is the reason.

Can you remember life before google, yahoo, amazon, (asktom for me at the very least, semi-life changing), and the rest? Can you imagine life without them again…

What did I decide on...

What did I decide on… I had an idea of what I wanted to do at OOW (Oracle Open World) and Joel Garry came close to it. I settled on “Database Worst Practices”. Why do yet another “Best Practices” presentation – lots of those out there. My abstract is:

A lot of time is spent by IT professionals studying 'best practices' in our industry. I would like to turn that around and take a look at some 'worst practices', the top things you can do to ensure failure, delays and poor performance in your database application implementations. Instead of focusing on things to do in your database applications we’ll look at things you should never do. Based on my experiences with customers, we’ll investigate the top ten things done wrong over and over again and how to avoid making the same mistakes yourself.

I’ve already got a good start on it, no shortage of examples. So so so many examples. Here is an interesting “point example”. Bet you didn’t know we have a year 2038 problem looming and it is already starting to affect us!

Busy Times...

Last week there were customer visits, visits to corporate, and user groups. The last user group was the Calgary Canada User Group where they had a social afterwards:



Next to me on the right is Mark Perry, the President of the local user group in Calgary. This is the second time I presented up there (part of my "territory" - I cover North America) and it's been a well organized event each time. Well attended too!

This week - in Las Vegas. Off to a good start, I picked up some money at the blackjack table yesterday. That is always a good thing. Won't be posting too much this week but I do have a quick question...

I'd like to hear what you might like to hear about at Oracle Open World. I have 50 minutes - what would make an impact on you? I have my ideas - but always willing to get some more. Remember - 50 minutes, you cannot get too geeky/deep down in that short of a time frame. But I don't want "light and fluffy" either. Something in the middle...

By the way - I'm really enjoying the comments on the "one minute" blog - the intriguing idea below... I will be summarizing them and talking about what I took away from it all.

Saw this and was intrigued...

Saw this and was intrigued. What do you think? Good idea or bad (would something similar about Oracle make sense?)

Me, I’m on the fence – serious things take serious time, but – is everything serious?

The Simpsons...

One of my kids favorite shows is the “Simpsons”. I’ve never been too fond of it, but maybe I’ll have to change that opinion. Turns out the Simpsons may be subliminally trying to teach our kids higher math!

I’ve remarked more than once that it appears that “marketing” and “technology” seem to have some touch points – based on a blog by Seth Godin. Well, apparently, Seth reads technology based blogs as well and sees some parallels. I liked that Joel on Software entry – neat read.

Anyway, off to Vegas for the next week starting tomorrow. As you know “what happens in Vegas stays in Vegas” – so it might be quiet… Then again, I like to take pictures and talk (write) so who knows…

This made me laugh...

This made me laugh. Have you ever sent a text message to someone in the same room? (guilty). How about in your own house? (guilty). I use them all of the time for sub-conversations during conference calls too.

Never go anywhere without my Treo. Always able send or receive. Hard to remember what it was like without instant messaging/SMS.

I can only imagine how this transaction happened...

A while ago, I mentioned a flight I took from Frankfurt to Tel-Aviv. That flight was on Feb 3rd, 2006 (a while ago). United wouldn't credit me the miles - told me that according to their records, I never took that flight (still, I couldn't figure out how I was able to get from Frankfurt - where I must have been since they flew me there - to Tel-Aviv, where again, I must have gotten since they flew me back!).

An amazing 5 months later, it suddenly "just appeared". I can imagine my transaction being stuck in a little error log somewhere and someone finally asking "what is this file" and pushing it through. Sort of like having a letter fall behind a cabinet and not getting sent for a long long time.

That takes me to 51 segments for the year (still they are missing one however from LGA to IAD) and over 70k miles. By the end of the week, I'll have added 8 more segments, yippee.

A kidnapped blog!!

A kidnapped blog! Doug Burns blog on blogspot has been kidnapped. Fortunately, he got himself up and running again here (and is using one of my favorite words in his URL). A spammer is camping out on his old blog, which caused not only the disappearance of Doug’s content but also the spam to hit the feeds that subscribed to his blog.

Made me wonder if this was entirely isolated – but it is not. It has happened before.

This got me thinking, I have no backup of my blog entries. And a quick google around turns up that there is no “export” from blogspot. The only workaround is to create a template and publish on the home page every post and all of the comments. I tried that but at 300+ posting with 5,000+ comments – blogger “broke”. No joy.

If the blog got kidnapped – I would lose all of that. Makes you think. It took a bit of work to put it together. But I always just used either the blogger edit screens or the word plugin to post with. I have a couple dozen blog entries on disk – but they are mostly incomplete/pre-final versions as I tend to refine the words/format after posting.

As the blogger above whose blog was kidnapped pointed out “you get what you pay for, blogger is free”. So, my plan is to investigate “real” blog sites (typepad maybe)? And start pdf-ing my blog entries, the contents + comments page – in order to have a backup.

So, couple of questions. Anyone know of any blogspot utilities that can connect and retrieve each post to disk locally? Better yet, one that will save the comments and post.

And recommendations for a hosted blogging service? I don’t want to run the infrastructure, but I don’t mind paying for it to be done for me. Especially if that hosted service has a process to automate the export from blogspot.com to itself that would be great. I’m looking for simplicity – I don’t want 1,001 cool features. The more simple the better.

Quick Update:

I found "blogCollector [Lite]" - free to test with. It downloaded my entire blog and made a nice pdf book for me in a couple of seconds. I can get on the plane tonight feeling confident that my blog entries will not just "disappear". It might take work to put them all back but it could be done.

And, for anyone that is interested, you can use this tool to point at various blogs/RSS feeds and make a pdf book out of them in seconds. Pretty cool.

Also, blogger is now making me use word verification to publish posts. My guess - someone wrote an automatic "let's try some username/passwords and see if we cannot modify blogs". With the word verification - that is not possible (not feasible would be a better way to put it I guess). But it does break the posting tools! I had to save as draft and then log into blogspot to publish this content.

Progress, that is what it is all about :)

This is very cool...

This is very cool.  Very neat – did not believe it at first.  

It appears to have been a success...

It appears to have been a success. Yesterday, I tried a “Viewlet Cam”. In less then 24 hours, it has had 1,000 views. I got some really good feedback on both asktom under the original question and on the blog posting.

Just to address some of them, I’ll copy bits of them here:

I can't wait till you do more of them. I think it would be very helpful if you put a link to the presentation area on the home page.

What I’ll do is set up a page similar to this one linked to from my “tidbits” page on asktom. I’ll keep it up to date over time as I add more of them so there will be one place to go.

I think you have to be very selective or you will be recording presentations for the rest of your life.

If this is going to be used as a supplement to your "written" answer, I like it.

I agree, this will not be a replacement for the written answer. I think Howard J Rogers summed it up nicely:

On the other hand "What is a hash join" is a rather more 'conceptual' issue than 'How do you install Linux?'. One involves ideas and concepts, the other involves click-this-do-that. The ideas issues may well require more vocalised (self)-debate and explanation than the plain didactic 'howto'.

That is what this format would be reserved for – it is not appropriate for all (not even most) questions. That and it takes longer to produce than a quick text answer. I think maybe one or two a week at most.

In email I received this comment:

I felt it would be better for the commute then online - does that software allow for video-podcast style applications?

In response to that, I’ve made the online only viewlet available as a self contained .exe file as well. Not exactly a podcast, but should work for most people.

Additionally, I found that I can use both “low quality graphics” as well as “low quality sound” – this reduced the viewlet image size from 13mb to a more reasonable 1.8mb. I was a bit worried about the additional bandwidth these things could end up consuming, but the 1-2mb size for about 15 minutes of content seems reasonable.

In short, a success. Thanks for the feedback – all of it. Sorry about the one or two people who had a problem with it (yes, you actually need speakers or a headset to hear it), but since it is a value add as opposed to “the main delivery mechanism”, I’m not overly concerned.

Something new...

Something new.  I wanted to deliver some content on asktom in a more “interactive” (one way interactive – me to you) way for a while now.  I asked around and found that likely the best approach is “Viewlet Cam”.

A friend/coworker of mine - Steve Muench – gave it high praise and pointed me to some examples of his own use of the software.  So I downloaded it yesterday, read the documentation (gasp! It did make it easier to use) and played with it. It was very easy to use – especially if you lay out what you want in powerpoint and then record the presentation.  I’ll be playing with just using my Linux desktop and SQLPlus as well.

Anyway, last night I opened the queues up on asktom and hoped to get a question that would be nicely answered using this technique.

The third question in my queue this morning fit nicely (guess I did not need to take 30 of them, oh well, gives me something to do over lunch…).  It was about hash joins and how they work.

You hear/see it here.  Give me feedback on whether you like the approach.  If it works out I’ll get a real microphone and buy the software.  Might be nice to publish bits and pieces of the seminar I do from time to time in 10/15 minute “sound bites” or to answer a question that would be hard to answer just using written text.

Interesting APEX application...

Interesting APEX application.  Dimitri Gielis sent me an email telling me about his World Cup application.  Apparently, in certain parts of the world, this soccer stuff is popular.  Ok, ok – I know, “football” is another name for it.  

Anyway, this looks like an interesting application of APEX and shows some of the power behind the tool.  On his blog, Dimitri discusses the genesis of the application.  If you’ve wanted to see what an APEX application can look like – this is a pretty good example.  I’ve linked not to the home page of the application, but rather to the about page – which gives a pretty good overview of what it is and how it got started.

Feel free to give Dimitri feedback on his blog!

On a side note, I’ve added a new site to the end of my “blogroll”.  Dan Hurley sent me a link to The Daily WTF.  I knew of the existence of this forum, just had never read it.  After reading the initial link, I had to read more (and more).  So, I’ve subscribed to the feed.  Looks good for a laugh (or a cry) in the morning.

Another puzzle...

Another puzzle.  Fruit is easy.  How about this.  No trick there – you can cut them out if you want…

Very cool...

Very cool. A bubble “cube”.

And here is a puzzle for you – my Aunt wrote me (using this thing called “pen and paper” and delivered by some guy in a strange truck – go figure) that I had solved this “puzzle” many years ago. She has a picture hanging in her kitchen:



Anyway, she said I figured out what the fruits represented a long time ago and she wanted to know the answer again.

Each fruit is a single digit (and unique of course, it would not make sense for the pear to be the same digit as the peach). I gave it to my kids and didn’t really look at it.

Until today. Took a minute – but I got the answer (I have the time stamped email to prove it)… Can you?

Update:
If you want to figure it out on your own - don't peek at the comments!

What is up with this...

What is up with this stuff. I suppose it is people trying to trick out google or other search engines.

I find my stuff from asktom screen scraped and included in fictitious “blogs” all of the time. Some of them I sort of “get” what they are doing. They scrape my data (including ‘asktom’ and other keywords) and then create a link to their own stuff. Making it look to search engines like the information is tightly related. Examples:

• http://www.sf2host.com/top search engine ranking tool/202/


• http://www.sites-blog.com/barbados basic facts/124/


• http://www.storage-blog.com/easy cd creator basic download/187/

Others I don’t get at all. They scrape some of the data – but link back to the source? For example

• http://convertdecimal.com/?p=38

An entire blog about decimal conversion? It doesn’t make sense – if you go to their home page and look at their blogroll – they apparently have a lot of “conversion” blogs. I didn’t understand the purpose of that site at all – it links to nothing but the sources so I don’t see how it would benefit a SEO company (search engine optimizer).

Then there is the last category – they scrape the data – but link back to nothing at all. It is like hitting the end of the internet. Stop – go no further, for example:

• http://www.23ltd.com/reverse cell phone number lookup/2006/05/reverse osmosis reverse cell phone.html

Again, I just don’t “get it” – maybe someone out there can enlighten me as to what the point of the last two are.

I understand (and sort of despise) the first category. It is cheating, not entirely honest – trying to always game the search engines. It is disingenuous on two levels – first, the SEO’s claim to be really successful, but I sort of have my doubts. So it could be dishonest to the customer of the SEO. Second – if it does work – it is gaming them system, a system we use and rely on. Makes the stuff we find perhaps be of lesser quality than it should be.

Updated:
I figured out what is up with the last one that didn't seem to link anywhere. It didn't link anywhere when I was using Firefox with adblock! I tend to check my posts using IE after the fact and when I used IE it become obvious what the reverse phonebook "blog" was doing. People search for reverse - so they blog heavily about reverse and reference lots of other stuff in their text (to broaden the hits I they receive I suppose).

Varying in lists...

Varying in lists. This is such a frequently asked question on asktom that I’ve decided just to write up the various ways you can do this.

First the problem statement: You have a string, it looks like

• 1, 2, 3, 4
• ‘A’, ‘B’, ‘C’

Or something similar. You would like to retrieve all rows from some table/query such that some column is in that string. That is, you would like to execute:

• select * from t where x in (1,2,3,4)
• select * from t where y in (‘A’,’B’,’C’)

You would like to use bind variables (because you’ve heard through the grapevine that bind variables are “good”). However, when you try:

SQL> variable txt varchar2(25)
SQL> exec :txt := ' ''SYS'', ''SYSTEM'' '
PL/SQL procedure successfully completed.

SQL> print txt

TXT
--------------------------------
'SYS', 'SYSTEM'

SQL> select *
  2    from all_users
  3   where username in (:txt);
no rows selected

SQL> select *
  2    from all_users
  3   where username in ('SYS','SYSTEM');

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYS                 0 30-JUN-05
SYSTEM              5 30-JUN-05

It does not seem to work at all. When you “bind” the inlist – no data, when you hard code it – data is found. The reason – well, that should be clear, the example above that used the bind variable in this case is equivalent to this query with literals:

SQL> select *
 2    from all_users
 3   where username in ( ' ''SYS'', ''SYSTEM'' ' );
no rows selected

There is a single string, a single value in that in list. What we need to do is turn that into a “set”. Here are some approaches you can take.

If you have a finite number of items in the in-list
By all means just bind the individual elements. That is, in the above example, suppose we let the user pick up to 10 items in a pick list. I would strongly encourage the query you use to be:

Select * from all_users where username in ( :bv1, :bv2, :bv3, … :bv10 );

And you would NOT use a single string, you would bind 10 inputs to this query (binding NULLs for any bind variable they did not set a value for). This works well for small lists – it would be quite tedious obviously for dozens or hundreds of items.

If you are in Oracle 8i
We can use a function that returns a collection. We’ll make it so that we can “query a string” – use the string as if it were a table itself. The concept goes like this. We’ll need a collection type (I’ll just use a table of varchar2(4000)), and a PLSQL function that will parse a delimited string and return it as a collection:

SQL> create or replace type str2tblType as table of varchar2(4000)
2  /
Type created.

SQL> create or replace
2  function str2tbl
3  ( p_str in varchar2,
4    p_delim in varchar2 default ',' )
5  return str2tblType
6  as
7     l_str long default p_str || p_delim;
8     l_n   number;
9     l_data str2tblType := str2tblType();
10  begin
11      loop
12          l_n := instr( l_str, p_delim );
13          exit when (nvl(l_n,0) = 0);
14          l_data.extend;
15          l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
16          l_str := substr( l_str, l_n+1 );
17      end loop;
18      return l_data;
19  end;
20  /
Function created.

SQL> column column_value format a10
SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

SQL> select *
2    from TABLE( cast( str2tbl(:txt) as str2TblType ) )
3  /

COLUMN_VAL
----------
SYS
SYSTEM

SQL> select *
2    from all_users
3   where username in
4  (select *
5     from TABLE( cast( str2tbl(:txt) as str2TblType ) )
6  )
7  /

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYS                 0 30-JUN-05
SYSTEM              5 30-JUN-05

If you are in 9iR2 and above
Then we can skip the function all together and just use DUAL to generate rows and parse the string. Consider:

SQL> select level l
2    from dual
3  connect by level <= 5;

    L
----------
    1
    2
    3
    4
    5

So, we can use DUAL to generate rows and then using substr/instr – effectively parse the bind variable and return the i'th element from it. For example:

SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

SQL> with data
2  as
3  (
4  select
5    trim( substr (txt,
6          instr (txt, ',', 1, level  ) + 1,
7          instr (txt, ',', 1, level+1)
8             - instr (txt, ',', 1, level) -1 ) )
9      as token
10    from (select ','||:txt||',' txt
11            from dual)
12  connect by level <=
13     length(:txt)-length(replace(:txt,',',''))+1
14  )
15  select * from data;

TOKEN
----------------------------------
SYS
SYSTEM

Once we have that accomplished – the rest is easy:

SQL> with data
2  as
3  (
4  select
5    trim( substr (txt,
6          instr (txt, ',', 1, level  ) + 1,
7          instr (txt, ',', 1, level+1)
8             - instr (txt, ',', 1, level) -1 ) )
9      as token
10    from (select ','||:txt||',' txt
11            from dual)
12  connect by level <=
13     length(:txt)-length(replace(:txt,',',''))+1
14  )
15  select *
16    from all_users
17   where username in (select * from data);

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYSTEM              5 30-JUN-05
SYS                 0 30-JUN-05

Now, some people look at that “with data” bit and say “that is too much, too hard to code that every time”. We can use a VIEW to hide the complexity here – and use a stored procedure as our way to “bind to the view” (this is sort of a parameterized view in effect). It would look like this:

SQL> create or replace context my_ctx using my_ctx_procedure
2  /
Context created.

SQL> create or replace
2  procedure my_ctx_procedure
3  ( p_str in varchar2 )
4  as
5  begin
6          dbms_session.set_context
7          ( 'my_ctx', 'txt', p_str );
8  end;
9  /
Procedure created.

SQL> create or replace view IN_LIST
2  as
3  select
4    trim( substr (txt,
5          instr (txt, ',', 1, level  ) + 1,
6          instr (txt, ',', 1, level+1)
7             - instr (txt, ',', 1, level) -1 ) )
8      as token
9    from (select ','||sys_context('my_ctx','txt')||',' txt
10            from dual)
11  connect by level <=
12     length(sys_context('my_ctx','txt'))
13       -length(replace(sys_context('my_ctx','txt'),',',''))+1
14  /
View created.

SQL> exec my_ctx_procedure( :txt )
PL/SQL procedure successfully completed.

SQL> select *
2    from all_users
3   where username in
4   (select * from IN_LIST);

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYSTEM              5 30-JUN-05
SYS                 0 30-JUN-05

Now, you “bind” your queries in-list by calling MY_CTX_PROCEDURE and the view does the rest.

Technology is moving so fast...

So very very fast. This is beginning to get “out of the world” amazing. A robot hand controlled by thought alone. Sure, kinks to work out and all but the concept is there entirely. Think of the uses - both good and bad. Good – amputees for example. Bad – people wanting to “enhance themselves”, or even using it as a weapon (tell it to the hand – the hand goes to jail).

Anyway, I thought that was both neat and frightening at the same time. Reminded me a bit of the Brian Herbert (son of Frank) Dune series. The technology to create cymeks could exist in the not entirely distant future.

Staying in the technology zone – I saw this new Dell model – it looks pretty cool and compact (I’m into “clean”, “compact”). I liked the concept – a fold up desktop (a little mobile). Single unit (less cables, hate cables, have way too many cables). Integrated keyboard – which has pro’s and con’s. On one hand – the keyboard fits the machine like a glove. On the other hand, what if you don’t like the keyboard. I’m picky about my keyboard (since I spend so much quality time with it). One thing I didn’t like about the keyboard was the location (and actually the use of) the touch pad. First, I’m not a fan of touch pads and second, I’m a lefty. If you are going to put a touch pad on the keyboard – it better be under my thumbs – else I want it on the other side! When I use my laptop for more than a couple of minutes – I have to stick my USB travel mouse in, the touch pad drives me nuts quickly.

Actually – why don’t we see any desktop keyboards with the eraser mouse? I’ve looked high and low for one – if I could stop having to reach for that stupid mouse all of the time, I could save lots of time.

Anyone know of regular desktop keyboards that have the feature of a laptop?

It would be nice to have both the eraser mouse and buttons on the keyboard as well as a regular mouse.

Pointing to this blog again – I was reminded of my own “I wish I had done that” story. That blog links to Bessemer Venture Partners and a page of theirs entitled “anti-portfolio”. You can read my “wish I did that” story here. Missed opportunities…

And some funny stories here about “corporate lessons”. Reminds me of ROT (rules of thumb) for some reason.. I liked lessons 4 and 5 best.

Insightful...

Insightful. A drunken blog rant (that is the name of the blog). Stumbled upon it. Agree with much of it. Rational read – that is what I’ll call it.

I liked it. Insights into programming languages and the early days at Amazon. The description by the author of this entry:

A very drunken blog rant that I evidently never published;
I ran across it in my drafts. Tours (and bashes) C, C++, Lisp, Perl, Ruby, Python.

A metablog...

A meta-blog. My list of blog entries that was on the right hand side of the main page was getting

• Long
• Out of date

I hated having to update the template in order to refresh the list – hence, I’ll put them here with a little detail on each and will maintain this page instead of updating a static list on the home page. It’ll be a little more flexible and will allow anyone to comment on the list as well.

This list is in no particular order. Repeat:

this is not in any particular order, I opened them all in tabs
and just clicked around.

Not everything is about Oracle (surprise surprise, there are some other things outside of Oracle – believe it, or not!)

• Consumerist.com - funny real life stories about people interacting with the companies they love. Also - great deals in their "woot" section sometimes.

• Gizmodo.com I dig gadgets and this is a collection of gadgets and other stuff. Funny and many times irreverent reviews of technology. From the truly cool to the truly stupid sometimes.
  

• Creating Passionate Users Kathy Sierra’s blog (well, mostly by Kathy). Even though she does java stuff, her blog isn’t about that. It is about, well, creating passionate users. She has a really good message. If you’ve read my blog – you know I’ve linked to hers more than once.
  

• Jonathan Lewis – “not a blog” blog That is right, it is not a blog technically, but hey – it works. And the articles are good. Just wish it had an RSS feed! UPDATE: see this
  comment about how to get an RSS feed for this "not a blog"
  

• Seth Grodin’s Blog What do marketing and technology have in common? Apparently a lot. I have found parallel after parallel here. I like it.
  

• The Phantom Nitpicker I’ve been nitpicked – have you? Picky describes this blog. If you like someone dissecting some English (all in good fun of course) – this is for you.
  

• Smart Stuff Some really cool smart stuff. Need I say more?
  

• Butterflies and Wheels Alternative news feed, a little different than say, oh Fox news for example.
  

• Snopes.com – debunking Urban myths True, sometimes they also declare some as “truths”. After doing the research though!
  

• The Dilbert Blog Which has nothing to do with Dilbert at all. This is a blog by Scott Adams, the creator of Dilbert. He has some interesting opinions. I like the way he’ll throw out a blog, in a manner similar to tossing out a hand grenade, just to see what’ll happen. Controversial could describe it.
  

• FeuerThoughts Hah, you probably thought “about time, an Oracle blog again”. Nope, not too much about Oracle – but rather Steven Feuerstein writing what he wants, sort of like I do on my blog.

• Peter K's blog Musings of a guy who has been using Oracle since version 3. Sometimes (mostly) about Oracle - occasionally about other stuff outside Oracle.
  

• We do not use blogs Mogens Nørgaard and his view on things. He has a strange view – might be a defective eyeglass prescription, then again, it just might be Mogens.
  

• The Oracle Sponge David Aldridge talks mostly about data warehousing techniques and other things both Oracle and non-Oracle related. He needs to learn how to spell – but does all right most of the time.
  

• Niall Litchfield’s Blog Niall is active in the Oracle community and even though he mostly uses Windows… There is from time to time something interesting.
  

• Oracle Newbies Blog Not sure how many years of experience you can have and still want people to believe you are a newbie but Lisa can probably get away with it for another year or two (mostly due to the hair color I think, that has something to do with it).
  

• Mark Rittman’s Oracle Weblog One of the first Oracle blogs I ever saw way back when. Set the standard for the rest of us. Mostly about Business Intelligence and Oracle.
  

• Kottke.org Usually something interesting comes across there every day. Nothing about Oracle, just stuff.
  

• ITtoolbox Can get quite noisy and the material is very much hit or miss (you get the good with the bad and the really really bad). But there are articles worth reading there, it does take some “weeding out” though to find them.
  

• Dizwell Blog Howard J Rogers occasionally has an opinion about something. And he’ll share it. Sometimes about Oracle – sometimes not. Always well written and many times funny.
  

• Pete Finnigan – Oracle Security Almost always about Oracle, almost always about Security and Oracle.
  

• Pete-S Random Notes Typically about Warehousing, many times about Oracle. Not always, but mostly database related.
  

• Scott Spendolini’s Blog Scott, formerly of Oracle – now of “independent”, writes about APEX (the tool formerly known as HTMLDB) and other stuff, like turkeys.
  

• The Oracle-Base Blog Penned by Tim Hall – many times about Oracle, sometimes just a rant. But rants are good, aren’t they?
  

• The Intuitive Life Business Blog This is not about Oracle, just about “internet stuff” in general. Usually an interesting perspective on things.
  

• Sue’s Blog Sue Harper is the product manager for SQL Developer and an all around good person. She is passionate about the technologies she works with.
  

• So What A blog by Jeff Hunter (not the Jeff Hunter, just Jeff). When he doesn’t write about MySQL… Then he writes about Oracle.
  

• Oracle Musings By Dominic Delmolino – a recent entry to blogging. A long time Oracle user, developer, DBA and now DBA manager.
  

• Oracleblog By Robert Vollman in Canada. Random writings about things Oracle – mostly developer related (which is a good thing)
  

• Oracle WTF Good for a laugh – WTF, the only appropriate “IM speak”. Neat snippets of really bad ideas.
  

• Oracle Stuff I should have Known! By Andy Campbell – good stuff here. Technical, well written, thought out.
  

• The Eric S. Emrick Blog Hey, he stole my idea for a blog title! See the description of the Oracle Stuff above – applies equally for this one – technical, well written, thought out…
  

• Laurent Schneider OCM Laurent is an Oracle Certified Master and writes about Oracle events, technologies and random bits and bytes.
  

• Eddie Awad’s Blog Eddie writes about tips and tricks using Oracle and provides a couple of firefox plugins that you might find useful if you use Oracle a lot.
  

• Doug’s Oracle Blog Doug Burns (don’t be scared by the plushie animals he sometimes has on his home page, I don’t think it is really a problem) is a DBA and “teacher” in Scotland. One of his specialties is parallel query but he writes about a variety of Oracle topics.
  

• Chronicles of Roderick Roderick Manalac is a long time Oracle support technician – one of the best. He doesn’t write about Oracle too much here – mostly about, well, TV shows he watches! But every now and then something Oracle creeps in.
  

• Boing Boing Interesting stuff, nothing Oracle really, just interesting stuff.

• Dooce Hey, she is really funny from time to time. Very much not what you might expect I would peek at – but she is really funny (not every day, but frequently enough to warrant watching). Off color language from time to time, not too much. Anyone with a quote like this "I’ll admit that a martini requires a bit of dedication in the sense that the first time I ever drank one I got the distinct feeling that I was swallowing hairspray. After a few the taste started to dull into that of lighter fluid, but now, now that I’ve been drinking them for several years, I can’t wait for that first antiseptic sip." is definitely worth reading.

• The Daily WTF - very funny. All about IT, all about doing IT as wrong as you can do it. The genesis for the Oracle WTF above, covers more than just Oracle however.
  

Wow, over 30 of them. Good thing not everyone writes every day…