Joe Fuda

A new topic has been added to the Unicode section on SQL Snippets. "Code Points in SQL or PL/SQL" examines various techniques for determining a character's Unicode code point. One technique involves creating a custom PL/SQL function called UNICODEPOINT(). When compared to other techniques UNICODEPOINT() proves to be faster, leaner, and more scalable. It also works on more Oracle versions than the other techniques.
...

A new section on Unicode has been added to SQL Snippets. Topics covered include

• a practical overview of Unicode
• Unicode support in Oracle
• working with Unicode data in UNIX and Windows XP operating systems
• viewing Unicode data in Excel 2007, Firefox, Internet Explorer, Notepad, PuTTY, SQL Developer, and SQL*Plus
• transferring Unicode data with WinSCP and ftp.

...

They say everyone gets at least 15 minutes of fame in their lifetime. Here's my total to-date.

1 minute (Middle School): my picture and some artwork appeared in The Toronto Star after I won their weekly cartoon contest for kids

30 seconds (High School): I was pictured in The Etobicoke Guardian performing a welding demonstration at a local shopping mall (this doesn't count for a full minute because a welding mask covered my face in the picture)

1 minute (University): I was pictured in The Toronto Star again, this time they caught me with my arm dyed purple, pants rolled up, and wearing a yellow hard hat as I waded through a fountain in front of Toronto City Hall during a University of Toronto Engineering hazing ritual

Yesterday a couple of minutes were added to that total when I was featured in Oracle's Innovation Showcase. As part of our 100-day countdown to Oracle OpenWorld Oracle is posting interviews with 100 of its top innovators. I was "Innovator of the Day" this past Friday, though I'm still listed there today too for some reason. I guess if you're lucky enough to be picked on a Friday then you become "Innovator of the Weekend" by default. You can find the full interview at this link, where it will reside even after my visage fades from the spotlight of the main showcase page.

So let's see, that leaves me with 10 minutes and 30 seconds of future fame left. I wonder what The Toronto Star will catch me doing next?

...

A new tutorial has been added to SQL Snippets exploring the GROUP BY clause and related extensions such as GROUPING SETS, ROLLUP, and CUBE. Group related functions such as GROUP_ID, GROUPING, and GROUPING_ID are also covered.
...

The "Columns to String: Comma Separated Values (CSV)" tutorial now includes three new sections, "How to Create a CSV File", "How to Escape Double Quotes, CR, and LF", and "How to Include a Header Line".
...

Originally introduced in 1992 as "Snapshots" in Oracle 7, Materialized Views are now used in ways far removed from their original raison d'être, replication. Database programmers use them for data warehousing, denormalization, and even validation. Despite their versatility though, materialized views remain a mystery to some programmers due to their complexity. The new SQL Snippets tutorial "Materialized Views" strips away the mystery with its simple test cases, step-by-step exploration of the basics, common programming pitfall alerts, and a useful utility called MY_MV_CAPABILITIES which analyzes and reports a materialized view's capabilities in a single step.
...

Introduced in Oracle 11g the REGEXP_COUNT function greatly simplifies counting the number of times a pattern appears inside a string. The following examples demonstrate how to use REGEXP_COUNT with some simple patterns. For versions prior to 11g alternative logic that provides similar functionality is also presented.
...

The SQL Snippets "Rows to Columns" tutorial has been updated to include solutions that use the new 11g PIVOT clause of the SELECT command.
...

The SQL Snippets "Nulls and Equality" tutorial has been updated with new solutions and a discussion about the undocumented SYS_OP_MAP_NONNULL function.
...

Well, it's been exactly one year since SQL Snippets first appeared on the web as a prototype site containing 33 pages viewed by a handful of visitors in its first month (a few close friends and the occasional searchbot). It now has over 250 pages, RSS feeds, site search, and HaloScan commenting. It gets thousands of visitors each month from around the globe (including the loyal searchbots, we've become steady friends this past year) and has been blogged about, StumbleUpon'd, and del.icio.us'd. (*ouch*) I'd like to say a big THANK-YOU to all of you who helped spread the word about SQL Snippets or provided feedback about the site in its first year. Your efforts are much appreciated.

The last year has seen a big change in my career as well. Some of you may have noticed the rate of new SQL Snippets topics started dwindling back in July. That's because I went back to work for Oracle that month and no longer have the luxury of working on this site full time. I'm doing pretty much the same job I had when I last worked there in 2006. In fact, I was even re-assigned some of the projects I worked on during my prior stint and they're exactly the way I left them. Some things never change, literally.

Fear not though, I still plan on adding new content when I can. The content management system I built for this site is effectively complete now so, unlike the past 12 months, I won't need to spend too much time on the mechanics of the site and can focus mostly on content going forwards. In fact, I even managed to put together a quick page on a new 11g feature this weekend. Check it out at SQL Snippets: Columns to Rows - UNPIVOT (11g).

Time for some cake ...

...

SQL Snippets "Columns to Rows" section has been expanded to include a topic on using Oracle 11g's new UNPIVOT clause, which makes all prior techniques for transforming columns into rows now obsolete.
...

A new tutorial has been added to SQL Snippets which demonstrates how aggregate functions deal with null values. Techniques for generating results that ignore nulls and results that include nulls are highlighted.
...

The SQL Snippets "Integer Series Generator" section has been expanded to include new topics covering techniques for generating multiple integer series in a single query. Unlike previous topics which only demonstrated queries that created a single integer series, like 1,2,3,4, the new topics show how a query can generate a series like 1,2 for one row and 1,2,3 for another row in the same query.
...

A new, hierarchical technique for converting strings to rows has been added to the SQL Techniques Tutorials - String to Rows tutorial in SQL Snippets.
...

This tutorial has been updated to include a discussion of how hierarchical queries that do not have a CONNECT BY condition with PRIOR behave. A sample query that uses "PRIOR DBMS_RANDOM.VALUE IS NOT NULL" to make the CONNECT BY LEVEL technique conform to documented requirements has also been added.
...

This topic was inspired by Tom Kyte's So, in your opinion ... blog post about a new SQL feature Oracle is considering (described at Pattern matching in sequences of rows).

I'll admit I've never tackled this kind of pattern matching before and I didn't understand the entire paper. It's a pretty dense read. From what I can tell though, using the new feature would be a lot like applying regular expressions to rows of values. This got me thinking. Instead of adding a whole new feature for this, why not simply convert the rows into strings and then use existing regular expression support to do the pattern matching?

Even if the feature described in the paper does something more sophisticated than this, tackling the requirement with existing functionality using simple string aggregation logic and regular expressions sounded like a fun challenge. Here's my stab at a solution.

...

This new SQL Snippets tutorial explores different approaches for implementing varying IN lists, including ones that use LIKE, MODEL, and integer series generator logic. The solutions will be presented as SQL commands, but the logic can be easily implemented in PL/SQL functions if required.
...

Every once in a while my non-programmer friends give me feedback about my site like "the blog posts are good, but when it comes to all that SQL stuff, well, it's a little over my head ... SQL sure seems to rely on that SELECT command a lot though."

So, I thought it might be good to take a break from SQL tutorials and spend time writing about something a broader audience will find useful. I looked through my archives and found some notes I made a few years back when I rewired the telephone cabling in my home and installed some LAN jacks. At the time I was surprised how difficult it was to get simple, straightforward answers to questions like what kind of cable to use or what kind of distribution device to buy. Even learning the proper names for connectors and figuring out which coloured wires go together proved more complex than I expected.

The amount and quality of information on the subject has gotten better over the years, but I still haven't found a source that presents all the home wiring information I need in a practical, systematic way. You'll find my stab at sorting it all out starting at Residential Telecommunications.

Be forewarned though, if you thought SQL was complicated wait until you learn about the 11 different ways to wire a telephone jack! :-)

...

Here's something cool. A new Google Maps feature called "Street View" is available for some streets. With it you can "virtually" walk down a road seeing actual street level photographs as you go. You can pan the image 360 degrees or zoom in and out as you please.

Here's a Google Maps Street View image of Marine World Parkway in front of Oracle's HQ buildings in Redwood Shored, CA. Click on the E or W arrows to travel along the road or click and hold the image then drag your mouse pointer to the right to see the fountain and more buildings.

...

Two new techniques for converting numbers into words have been added to SQL Snippets. Unlike the ubiquitous Julian based solution (which uses the J and JSP format models) the new SQL Snippets techniques use the fractional second format models FFn and FFSP. Using fractional second models produces solutions that, unlike the Julian solution, can handle the numbers 0, 1,721,058 to 1,721,423, and those greater than 5,373,484 without errors or elaborate workarounds.
...