Twitter -> Google Reader

I was playing with google seeing what keywords would return my blog, and I saw Chris Muir mention my presentation on his Twitter feed.

Now I have chosen not to fall into the Twitter trap, but there are some feeds I wouldn't mind following, Chris being one of them (Doctor Karl being another!)

For those uninitiated into RSS aggregators such as Google Reader, you can subscribe to these Twitterers & read what they have to say without needing to sign up to Twitter.


Kinda like reading all their mail, I guess... I can't tell you if there is some sort of privacy option within Twitter that controls this, perhaps someone with an account can?

I recently subscribed to one that had what I thought was a sensational idea - As part of the 40th anniversary, NASA arranged a Twitter feed for the moon landing, so you could read what was going on as if it was happening real time today. It gave a wonderful sense of time and progression for those of us not around when it happened.

I think it's nifty.

UNION ALL - Performance & Accuracy

Time for my first on topic Oracle titbit - UNION ALL

Take the following example. I had two mutually exclusive tables, fairly flat and uncomplicated. Both queries returned 17 rows.

I wanted all rows returned, regardless of uniqueness.

The original solution used UNION.

SELECT col_a, col_b, col_c
FROM   table_1
UNION
SELECT col_a, col_b, col_c
FROM   table_2

I knew in this case not only was this not optimal, it was also inaccurate. This case should have used UNION ALL

SELECT col_a, col_b, col_c
FROM   table_1
UNION ALL
SELECT col_a, col_b, col_c
FROM   table_2

Such a minor difference in syntax, but in this case ~30% faster and 34 rows returned.
Had two of these records had the same column values, less than 34 rows would have been returned. Sure, in other scenarios this may be accurate, but in others this may cause data integrity issues, throw out reports or lose data.

In terms of speed, my basic test harness iterated these two queries 50000 times and got the following results:

7.27 secs -- union
.0001454 secs per iteration

5.54 secs -- union all
.0001108 secs per iteration

Should this query only be executed once per day, probably not an issue in terms of speed. However if it were required thousands of times per minute then this would probably make an impact.

I think every little bit helps. Take the anology of Aussie Rules - Australia's favourite sport. Commentators say all those one percenters count, they can make the difference to the end result. Imagine taking 100 separate Oracle efficiency tips and bundling them within your software. Collectively, how much wait time will it ultimately save the user?

July 2009 Branch Meeting

Thanks everyone for the great feedback from last night's presentation. I was happy with how it went, even though we were both a little rushed due to time constraints. For those who missed it, it's one for the catalogue...

My presentation is now available on my presentations page, and you can find the other Scott's presentation here.

Forty years ago...

I don't aim to make too many off topic posts, but I think this one is worth a mention for such a poignant occasion - to my interests anyway.

Congratulations to the entire team involved (and their respective families) in putting Neil Armstrong, Buzz Aldrin on the moon, & Michael Collins who had the opportunity to orbit it (which facilitated the most isolated meditative experience known to mankind!)

I also look forward to seeing the higher resolution pictures the Lunar Reconnisance Orbiter (LRO) will take of the Apollo landing sites. Here are the first images taken during flyover whilst it's orbit is still settling down. I'm sure there was limited coincidence in these images becoming available near the 40th anniversary, even though there was question marks over available launch dates a few weeks ago for this little machine.

I think Buzz Aldrin's autobiography would be a good read...

Scott Squared

For those not subscribed to the AUSOUG emails, there is a branch meeting this Wednesday night with two presenters - me & my namesake - Scott Squared, I suppose. Mine won't go for too long, just some thoughts I needed to get off my chest on a topic I think is important but often overlooked.

My abstract:
If you're a database developer, regardless of whether you have 6 months or 6 years experience, you need a good reference manual. By good, I mean one that you can locate what you need within seconds. I know you know what I mean...

I'd like to show you how easy the free Oracle supplied documentation really is to use, and if for some reason it still doesn't cater to your needs, I'll show you some other methods and destinations that might save you a few headaches.

In this short presentation, I'll show you how to find most day to day documentation requirements in 2 clicks, maybe 3 if you're unlucky - without connecting to the net. You might also hear some funky new words such as Ubiquity & Bookmarklets.

I also look forward to Scott Hollows' presentation as there are some nifty features listed that are sometimes overlooked:

• Subqueries everywhere - SELECTs in places where you didn't know you had places
• Functions pretending to be tables
• Outer joins and hierarchical queries - beyond the basics
• Pivot and Unpivot your data
• Fast track coverage of Analytical Functions
• Rollup and Cubes
• Nulls - SQL and PL/SQL issues, new nulls functions
• Issues, Limitations, Workarounds and more

Event details

Topic 1: Weird SQL
Topic 2: Oracle Documentation: RTFM (Really Terrific Finding Methods)
Date: Wednesday 22nd July
Time: 4:15pm to 6:00pm - Aim to start first presentation at 4:50pm
Where: eCentral TAFE, 140 Royal Street, East Perth
Presenters: Scott Hollows and Scott Wesley
Cost: Free to members + bring a friend for free!

Free to members, as long as you RSVP with details. Please RSVP no later than 20th July, 2009 to Ailsa Wotherspoon at vicepresident.wa@ausoug.org.au to assist in planning for numbers.

Hope to see you there!

Presentations

Many moons ago when I first started as an Oracle Developer, I worked on a project with Penny. I wasn't under her employ then, but we were playing with materialized views in an 8.1.7 database. Fairly cutting edge at the time she suggested I do a presentation on it for the conference. Green as I was straight out of university I graciously declined, but ever since I searched for a topic I'd be confident in presenting. One day on a blog I stumbled on someone's response to a simple query that suggested using the Model Clause and for some reason this peaked (not piqued) my interest. I've never looked back since and ideas just keep coming.

So to those contemplating it, just go for it. It's like riding a bike. And you learn so much about Oracle when you're essentially forced to tinker & play.

Below is a list of presentations I've done so far.
(And yes, it turns out I'm a fan of alliteration)

Update December 2017
There is a larger, more up-to-date list on this dedicated page:
http://www.grassroots-oracle.com/p/presentations.html

***************

Oracle Apex 4.1 Security  ( prezi | bookmark )

There have always been many options for securing Oracle Apex applications, and many of them don't require much effort - just a little understanding.

This presentation will cover all things Apex Security. Scott covers discussions and examples of many of Apex's security features, including changes to Authentication & Authorisation in 4.1 towards using plug-ins.

AUSOUG WA Conference 2011 - Awarded Best Paper

Oracle Apex Performance  ( pdf | prezi | bookmark )

Over the years there have been countless technical and social presentations doting on 5, 10, 12 ways to improve this, that and the other.

I will go through various performance tweaks (not tweets) for Application Express without limiting myself to a golden number.

These improvements will vary from simple PL/SQL refactoring; to monitoring for bottlenecks in your application; to cutting down maintenance time - which relates to the performance of you as an Oracle developer with only 24 hours in a day.

We may even visit a little Apex instrumentation on the way.

AUSOUG WA Conference 2010
AUSOUG SA Conference 2011
Virtathon 2011
InSync Sydney Conference 2011

Apex with Oracle Text ( pdf | inline | bookmark )

Oracle Text is a facility within the database that provides more advanced indexing & search techniques - including the ability to index documents stored in your database; on your server; or even the web!

Now you can incorporate this functionality into your web application using Application Express.

This presentation will demonstrate how easy it is to combine the two, and give you a platform for further expansion and exploration within a very powerful product.

AUSOUG WA Branch Meeting 2010

Trials & Tribulations of an Oracle Forms -> Apex Conversion ( pdf | bookmark )

Abstract: One of the hot topics these days questions how long you should keep hold of your long standing, hard working Forms application. Oracle support timeframes for Forms has been quite fluid over recent years and ultimately you may need to make a move..

Depending on the size and complexity of your application, a number of options present themselves.
Application Express is one such option. The question remains, however, is there some black box tool we can use to plug our Forms in and have it pump out Apex pages? And how much assistance does this tool need before and after the conversion process?

This presentation will cover some of the considerations you may need to make when contemplating this event. We'll show what happens when you convert a form with various types of components and reveal the gaps, if any, you'll find at the other end.

Let's not forget the other components of our Forms application. What about PL/SQL libraries, reports, menus? Is it worth tackling some of these items manually? We shall see...

AUSOUG WA and Vic National Conference 2009 - Oracle ACE ODTUG Stream

Oracle Documentation ( inline | bookmark )

Abstract: If you're a database developer, regardless of whether you have 6 months or 6 years experience, you need a good reference manual.
By good, I mean one that you can locate what you need within seconds. I know you know what I mean...

I'd like to show you how easy the free Oracle supplied documentation really is to use. And if for some reason it still doesn't cater to your needs, I'll show you some other methods and destinations that might save you a few headaches.

In this short presentation, I'll show you how to find most day to day documentation requirements in 2 clicks, maybe 3 if you're unlucky - without connecting to the net.
You might also hear some other new words such as Ubiquity & Bookmarklets.

AUSOUG WA Branch Meeting 2009

11g New Features ( inline | ppt | bookmark )

Abstract: There are a wealth of new features available in the 11g database release. This presentation touches on SQL & PL/SQL features I found of interest, and concentrates particularly on virtual columns.
Relevant scripts are available here.

ACTOUG May 2009

Creative Conditional Compilation ( inline | ppt | bookmark )
Abstract: Oracle released a feature in 10g Release 2 they thought worthy of facilitating in previous versions via patch sets - so I thought it was worthy enough for a closer look.

Conditional compilation isn't a foreign concept in the programming world, and for the developer aficionado it's a wonderful paradigm to explore.

Conditional compilation was designed with the main intention of being able to create database version specific code. With the recent advent of 11g, developers can actually start adding 11g features to their 10g code today!

However it provides the savvy PL/SQL developer to enhance their code in more ways than just gearing up for the next release… Dust of your software engineering hats and discover how to utilise conditional compilation to explore concepts such as latent self tracing code; latent assertions; and enhanced prototyping for your unit tests.

This seminar will illustrate several examples of conditional compilation that will open your mind; ultimately benefit your users; and can be implemented as far back as 9.2!

AUSOUG WA and QLD National Conference 2008
AUSOUG VIC Branch Meeting 2009
AUSOUG Qld Branch Meeting 2010


Be a Bulk Binding Baron ( inline | pdf | bookmark )

Abstract: Developers - If you are not using Bulk Binds you are not writing PL/SQL efficiently!

Bulk binding has been around for a long time, yet there are sites out there that don't utilise this feature to its full extent, if at all. Every release of Oracle improves on this functionality so obviously it's a topic worthy of consistent awareness.

In PL/SQL and SQL, there are a few nifty features related to bulk binding you may not have seen - it's not all about BULK COLLECT. Whether you're on 8i, 11g or anything in between, you'll benefit from the concepts described in this seminar and become a Bulk Binding Baron!

AUSOUG WA Branch Meeting 2008
AUSOUG SA Conference 2011

The Model Clause ( inline | bookmark )

Abstract: The session will breakdown the Model clause into its fundamental components and provides some basic real-world examples to demonstrate its greater potential.

Though most developers have heard of the SQL Model clause in 10g, many may baulk at the idea of using it - daunted by seemingly foreign syntax that might well have come out of a FORTRAN program.

Look a little closer and you'll find it's just like building a spreadsheet. Concise, easy to read syntax that provides the functionality for demanding calculations that would normally require elaborate joins, unions, analytics or PL/SQL. In addition to the development and maintenance burden, we are also faced with the all too familiar problem of business customers duplicating data to an Excel spreadsheet that is shared and erroneously modified around the workplace.

This session uses the Model clause as a high performance tool that can simplify approaches to every day problems. It demonstrates that Model is an extension to SQL that forms multi-dimensional arrays with inter-row & inter-array calculations that automatically resolves formula dependencies.

AUSOUG WA and VIC National Conference 2007

Inaugural post

Hello World. My name's Scott Wesley and I'm a Oracle Database Consultant & Trainer with Sage Computing Services. We're based in Perth, Australia - the most isolated capital in the world.

I'm a huge fan of most things PL/SQL, involved in ETL, Forms & Apex projects. I'm a frequent contributer to the Australian Oracle User Group and enjoy presenting on niche topics. My past presentations are coalesced here.

My aim with this blog is to become more involved in our Oracle Community, and share various snippets I encounter day-to-day and have stored in my back catalogue of randomness. No doubt I'll want to share my perspective on various Oracle news and I'll have the occasional insight and will to post something larger.

When deciding on a name for this blog I had a few choices:

• Use my own name - for some people I think that works rather well. Some people gathered some form of identity before they started blogging, so their name can become similar to a brand.
• Refer to an Oracle product - I didn't want to pin myself to one particular product. While I consider myself a PL/SQL developer, I also use Forms, Apex etc. Over time all of us might migrate into other product lines. If I so chose to rabble about something Oracle, I wouldn't feel out of place.
• Do something else - I like to be different. There are some successful blogs out there (Oracle and otherwise) whose name doesn't obviously reflect the content involved.

I chose Triangle Circle Square because I can reflect on my training in budo. When you meet some form of adversary, we need to confront them in a strong triangular stance. For many techniques we'd pivot around in some form of circular movement then finish in a strong squatting stance that whose posture resembles a square.

Triangle - beginning of all forms; Circle - infinite; Square - 10000 things / the Universe. Read from right to left we start with the infinite basis of everything; the triangle as the beginning of some tangible form; then the triangle doubled into a square. This overlaps to infinity.

In the Oracle world we meet a problem. This always contributes to the learning process. Regardless of whether it's a problem we've faced before, or a new challenge - it will contribute to our ongoing learning process. Strengthening good paths, weakening poor ones. This is my triangle.

We then need to define a solution. Awareness is important - where to look for documentation, who to ask, what knowledge to reference. Iteration always helps to refine this solution. This is my circle.

Then of course we need to finish the job, and do it in an efficient manner. I'm always keen to apply all the little techniques I'm familiar with to ensure my code is as efficient as I can make it. Small things alone don't make your software fast, but lots of small things added together save you a tuning headache later on. This is my square.

The artwork in the heading of my blog is from a painting by a fellow who lived on the island known as Japan in the 18th century. Much can be interpreted from this philosopher's doodling - the rough English translation of this painting is "Universe". David Suzuki comments on it well, but many like to contemplate for themselves and build their own interpretation.

By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest.
- Confucious