Skip to content

select from column-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here ;) 

create table t(description varchar2(12) primary key, 
  numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION NUMBERS
PRIME 2,3,5,7
ODD 1,3,5,7,9

Now I want to unpivot numbers in rows


select description,(column_value).getnumberval()  
from t,xmltable(numbers)

DESCRIPTION (COLUMN_VALUE).GETNUMBERVAL()
PRIME 2
PRIME 3
PRIME 5
PRIME 7
ODD 1
ODD 3
ODD 5
ODD 7
ODD 9

It is that simple :)

Works also with strings :


select (column_value).getstringval() 
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c
Posted 03 July 2009 † Laurent Schneider § Uncategorized ‡ Comments Off °

Mac OSX Software I like

Sometimes I get the question which Mac OSX software I use. So here's a list of programs I've installed. Some I use often others less often. If you use other nice software, feel free to add!

Standard Mac Software:
  • Address Book
  • AppleScript
  • Automator
  • Calculator
  • Dashboard
  • Dictionary
  • Expose
  • Font Book
  • Image Capture
  • TextEdit
  • Unison
  • Utilities
  • Spaces
  • iCal
  • iSync
  • Stickies
  • Server - because we also have Mac OSX Server

Office:
  • iWork
  • iLife

Internet Applications:
  • Adium - chat
  • iChat - chat
  • Colloquy - irc (not used that much)
  • Cyberduck - ftp client
  • DynDNS Updater - link a hostname to an ip
  • Dropbox - online storage
  • Firefox with different plugins (YSlow, LiveHTTPHeaders, Firebug, ColorPicker, ...)
  • Safari - standard browser on OSX
  • GoToMeeting - to do remote support and meetings
  • SSH Tunnel Manager - ssh
  • Meerkat - ssh
  • Cisco VPNClient - vpn
  • Viscosity - vpn
  • Skype
  • Mail
  • Twitterrific - twitter reader
  • Reader Notifier - get rss updates
  • Yuuguu - connect to others
  • Transmission - download files

Graphical Software:
  • Adobe Acrobat 8 Professional
  • Adobe Dreamweaver CS3
  • Adobe Flash CS3
  • Adobe Illustrator CS3
  • Adobe InDesign CS3
  • Adobe Photoshop CS3
  • PDFpenPro - edit pdf
  • Paintbrush
  • OmniGraffle Professional 5 - make schemes
  • iWeb - make websites

Games:
  • Chess
  • Quinn - tetris like
  • MasterMind - dashboard plugin

Entertainment:
  • DVD Player
  • DivX Converter
  • DivX Player
  • EyeTV - makes it able to watch tv on my mac
  • Flip4Mac - be able to play wmv
  • GarageBand - audio
  • Front Row - menu to all entertainment
  • iDVD
  • iMovie
  • iPhoto
  • iTunes
  • ScreenFlow - record video
  • Photo Booth
  • VLC - play video
  • QuickTime Broadcaster
  • QuickTime Player
  • VisualHub - convert video (not updated anymore)
  • Spotify - play any song (only works when I'm in the UK)
  • BookSmart - online creation of you photo albums
  • WorldRadio widget - listen to radio as dashboard app

Local/External storage:
  • ExpanDrive - Mount drives even through ftp etc
  • Macfusion - extend to other filesystems
  • SuperDuper! - backup your Mac

Phone:
  • Missing Sync for Windows Mobile - I've an HTC and not an iPhone, so need extra software to sync agenda, contacts etc
  • TomTom HOME - on my phone I have also GPS and navigation software

Oracle related:
  • Oracle SQL DataModeler - SQL Modeler
  • Power*Architect - SQL Modeler
  • SQLEditor - SQL Modeler
  • SQLDeveloper - my day to day tool to talk to the Oracle database
  • Versions - SubVersion client
  • VMware Fusion - to get access to my remote machines

Project Management:
  • Merlin - allows to estimate the work and breakdown in pieces
  • Mindjet MindManager - read MindMaps
  • OmniFocus - to do list which is able to sync as well

Text Editor:
  • SubEthaEdit - allows to work on a text with more persons, real time editing
  • TextMate - favorite text editor

OSX Tools:
  • OmniDazzle - mouse focus and effects
  • Quicksilver - lunch commands fast
  • StuffIt Expander - zip like
  • 1Password - store passwords in a secure way
  • Wallet - store passwords in a secure way
  • World Clock Deluxe - to keep an eye when my clients get awake
  • iBank - trying that, MS Money type app
  • Caffeine - let your Mac never go in sleep mode
  • Time Machine - automated backups
  • iStat Pro widget - shows a lot of information about your Mac (ip, temperature etc)
  • AccuWeather widget
  • SysTran translation widget
  • Currency converter widget

For the people who want to work faster, here's a good list of shortcuts for OSX.
Posted 03 July 2009 † Dimitri Gielis § Uncategorized ‡ Comments Off °

SQL Modeler for Mac OSX (Part 2)

Louis-Guillaume Carrier-Bédard commented on my previous post about SQL Modeler for Mac OSX, that I had to check out a tool called Power*Architect.

So I thought to give it a go. The steps to get it running:
  • Go to the SQL Power website and download Power*Architect
  • As most OSX applications you install by dragging the application in your Applications folder
  • So now I wanted to get an ERD of some tables, so you run Power*Architect and select from which Data Source you want to capture.
  • I had to download the Oracle 10g JDBC driver first (apparently 11g is not yet foreseen in Power*Architect)
  • In the User Preferences you can tell where the driver can be found
  • Next I dragged-dropped from the interface the tables I wanted to the right hand side, but the tables where not nicely ordered
  • Luckily there is a button "Automatic Layout" which I pushed and that gave me the below result


As far as I've tested Power*Architect, it looks very promising. It's definitely a tool I will try a bit more in the future.
Posted 03 July 2009 † Dimitri Gielis § Uncategorized ‡ Comments Off °

Oracle Database File System (DBFS) on Exadata Storage Server. Hidden Content?

A colleague of mine in Oracle’s Real-World Performance Group just pointed out to me that the link (on my Papers, Webcasts, etc page) to the archived webcast of Part IV in my Oracle Exadata Storage Server Technical Deep Dive Series was stale. Actually, the problem turns out that I mistakenly set the file to expire [...]
Posted 02 July 2009 † kevinclosson § Uncategorized ‡ Comments Off °

Fundamentals of Software Performance Quick Reference Card

I just posted "Fundamentals of Software Performance Quick Reference Card" at the Method R company website:
This two-page quick reference card written by Cary Millsap sums up computer software performance the Method R way. The first page lists definitions of the terms you need to know: efficiency, knee, load, response time, and so on. The second page lists ten principles that are vital to your ability to think clearly about software performance. This document contains meaningful insight in a format that's compact enough to hang on your wall.
It's free, and there's no sign-up required. I hope you will enjoy it.
Posted 02 July 2009 † Cary Millsap § Uncategorized ‡ Comments Off °

SQL Modeler for Mac OSX

On the first of July Oracle's SQL Developer Data Modeler got a production release. You have the choice between a full version and a Viewer only version.

I installed the full version on my Mac to see how it is like. These are the steps I followed to get it working on my MacBook Pro:
  1. Download the zip file from OTN
  2. Unzip the file in my Applications folder
  3. You'll see a folder called "datamodeler"
  4. I ran a Terminal session to call this command: sh datamodeler.sh
  5. The first time SQL Developer Data Modeler asks you to enter the full pathname of a J2SE installation. For me the path is:
    /System/Library/Frameworks/JavaVM.framework/Versions/1.6/Home
  6. Next SQL Developer Data Modeler opens and you are good to go
  7. I tried to generate a relational and logical datamodel and it worked great (see below screenshot of the DG Tournament schema


As expected Oracle didn't make the tool for free. I hoped differently, but apparently Larry decided against it. The latest Oracle pricelist shows under the Tools section the price of SQL Developer Data Modeler. The first year you pay USD 3,000 + USD 660 (support and upgrade), from the second year onwards it's 660, but that gives you the right to install all updates. I definitely think the product can justify the price, but maybe not for everybody or in every project as you might not want to use all the features.

One of the most important parts for me is viewing the ERD in a graphical way. I tend to use the ERD to explain clients how we see the application. Or if we come in when the application already exist, to quickly know what is going on behind the scenes. Till now I used SQLEditor of MalcolmHardie Solutions. It allows me to connect to a database and get the ERD for a schema or certain tables. It can do a lot more like adding tables, columns etc and you can see the sql statements in different formats. Below is a screenshot of the same schema as the picture before.


Most of the time I have to rearrange the tables so it's more logic and more understandable. Personally I think Oracle SQL Developer Data Modeler looks a bit better and it has more features and I had to not rearrange that much as with SQLEditor. But then I guess that explains the difference in price ;-) SQLEditor costs $79.

I still have a lot to learn of the functionalities of Oracle SQL Developer Data Modeler, but what I saw at the conferences and what I could do so far, I definitely think it's worth for you to give it a try.
Posted 02 July 2009 † Dimitri Gielis § Uncategorized ‡ Comments Off °

Happiness is more subject in your subject line.

If a certain lack of variety has weighed on the format of your day-to-day, feed-to-email deliveries, things are looking up at last. Recent advances in dollar-sign technology have brought some strange and fascinating new capabilities to the Email Branding section of FeedBurner's Email Subscriptions feature. Read on, ostensibly for the many useful pictures and descriptions, but really for the danger and excitement only a new checkbox can bring.

First, sign in to your Google Account on FeedBurner and then click your feed's title, then Publicize > Email Subscriptions > Email Branding.



Always want to feature the title of the latest post in your subject line? Just put ${latestItemTitle} in the Email Subject/Title textbox:



Do you often have more than one post per day? You can help your readers uncover exactly how many new missives you've got planned for them in each update. Check the "Change Subject…" box and reveal a secondary subject line to use when 2 or more feed posts are delivered in a single email.

Remember, good subject lines command attention in crowded inboxes.



Behold! The mythical "almost empty" inbox. But in this case, the most recent post's subject line, thanks to ${latestItemTitle}, is right in this FeedBurner-delivered email, shining through.



Have fun with this new feature, but please note that ${pithyRetort}, ${iambicPentameter}, and ${heartfeltApology} are not yet supported.

Posted by Paul Darga and Matt Shobe, FeedBurner Team
Posted 02 July 2009 † Matt Shobe § Uncategorized ‡ Comments Off °

Using the iPhone for Gaming?

I never expected my iPhone would be a gaming device.

Earlier this week, as I did my civic duty, I found myself bored to tears in a jury room, waiting to be called. It was actually a bit funny. The day started with a video about jury service, including interviews where jurors said they were excited when their names were called for a trial.

Sitting in that jury room with nothing to do, I began to understand the excitement.

Flight Control, 64 is my best, but I'm still new

I only had my phone with me. Note to self, next time prepare like you’re going on a plane: laptop, power, movies, headphones.

I managed to kill an hour playing Sudoku, but after that, I got restless. The jury room had wi-fi, a nice perk, and I remembered Paul mentioning Flight Control to me as awesome and totally addictive. I’d read that in a couple reviews, too.

So I decided to buy it, and happy day, it’s on sale right now.This blog isn’t an iPhone app review blog, and I’ve stopped talking about apps (other than the Oracle People app) because that’s not what we do. I only chatter about apps I enjoy.

I enjoy Flight Control.

The game is simple. Land the planes on the corresponding colored runways by tracing a path with your finger and don’t crash them. Easy peasy, until you have a bunch of planes, flying at different speeds all over the place.

It’s a phenomenal game for the iPhone platform, easy to learn and easy to play. It’s also easy to succeed right away, but it gets increasingly difficult, an attribute of all good games.

For me, this is the addictive part. I was able to play the game immediately, with success, and as it got harder, I kept playing, bolstered by that initial success. Plus, I was bored to tears with nothing else to do.

I enjoy the design aspects of the game. I wonder how that design meeting went. Was the idea universally praised, or did it require a few meetings and a prototype to get the green light?

A while back, Paul mentioned that his iPhone was more gaming console than anything else now, and I was skeptical. I’ve changed my mind. There are some very imaginative games out there that use the iPhone in creative ways, e.g. with the latest version of Flight Control, you can play with another person simultaneously and divert planes to their airports. Can’t wait to try that.

Plus, you never know when you’ll be stuck with nothing to do.

What do you think? Any iPhone games to recommend? Find the comments.

Update: I finally acted on recommendations from Rick and Paul that Fieldrunners was a great game. Aside from being one of Time’s top ten video games of 2008, the game is produced by Portland company, Subatomic Studios.  It has not disappointed; I’ve been playing it for the past hour or so, great fun.

Posted 02 July 2009 † Jake § Uncategorized ‡ Comments Off °

2 Anniversaries today…

Today marks 2 anniversaries for me. It’s my 40th birthday, and it’s exactly 1 year since I gave up eating chocolate.

They say life begins at 40. I’m staying in all day today because I don’t to be out in case I miss it… :)

Cheers

Tim…

Posted 02 July 2009 † Tim... § Uncategorized ‡ Comments Off °

Real-Time SQL Monitoring in SQL Developer

If you haven't seen 11g's Real Time SQL Monitoring feature, you need to. It's one of the most useful Oracle performance troubleshooting tools I've seen since I started working with Oracle too long ago. I was first aware of it via Greg Rahn's blog post.

To date I've used it via DB Control for demos and it's sweet, but one of the problems with any demo based on DB/Grid Control is that it's use is likely to be limited to those who have DBA access. Yes, you can set up view access to GC so that people can monitor performance of targets that they have sufficient account privileges on, but a lot of sites won't want the overhead of setting that up for developers and app support teams.

So I was intrigued when I noticed Tom Kyte mention that Oracle have been working on making more of these tools available to developers. After a quick email, a look at his slides and some further help from Sue Harper, I was able to try out RTSM in SQL Developer 1.5.4

Once you have selected Tools->Monitor SQL



you'll see a grid table of records. This will include all monitored statements, including this example parallel query that I'm running just now.




If I right-click any of the statements and select 'Show SQL Details' I'll see the Real Time SQL Monitoring screen, which is deeply cool.



One criticism, though. On my dinky laptop screen, the execution plan steps don't display properly as that column's too narrow. I can resize it




but then it just annoyingly sets it back to it's original width every time the screen refreshes. Hopefully that's something that can be fixed, for those of us with dinky monitors ;-) Even so, it saved me during my last course teach because 11g DB Control started to play up on my laptop, so I was able to fall back on the SQL Developer option. It's definitely worth a look.

Thanks to Sue and Tom for their help ...
Posted 02 July 2009 † Doug Burns § Uncategorized ‡ Comments Off °