Tuesday 28 August 2012

SQL Developer 3.2 prepares to connect to the cloud

Have you downloaded SQL Developer 3.2 yet? check this out:

sql developer cloud connections
I guess Oracle Open World will have a few announcements this year...

I'm not going, but I find solace in that I'll be at the AFL grand final this year - which is the day before.
At least I know Pearl Jam is eliminated from the list of acts for the grand final - still to be announcement.

How long before someone jokes about connecting to "heaven" as a cloud connection? ;-)

Monday 20 August 2012

pre-Insync12

BEGIN

Off to Melbourne for Insync12 today. This month's entertainment from Qantas doesn't excite me much, so instead I might nerd up & watch some Oracle presentations I worked out how to cache on my phone.

I feel horribly under-prepared this year, no thanks to any awful experience with a laptop hardware failure. Don't get me wrong, the presentation is ready to rock - but I feel behind in so many other ways.

Some key people seem to be missing from the line-up too, but I look forward to seeing interstate friends I've met over the years - it sure helps make these events more fun!

If you're there, look out for me @swesley_perth or find me State 1 Room 4.15pm Wednesday talking APEX 4.1 Security.
If you're not, maybe I'll see you in Perth in October.

Scott

GOTO domestic_airport;

Friday 17 August 2012

2012 Perth Conference Abstracts

Just a quick note - abstract submissions for the 2012 AUSOUG Perth Conference have been extended to the 27th August... just say'n.

Head here for more info
http://www.ausoug.org.au/2020/

In other news, Penny & I will be at the Insync conference next week. I'm on Wednesday afternoon - but I think my different presentation technique should keep you sustained after the lunchtime feast. Not sure when Penny is, but be sure to seek us out and say hello. We look forward to seeing friends and meeting new ones.

If you're on twitter, I'll leave the green pigs up so keen an eye out for them @swesley_perth
And I'm sure @InSyncEvent will liven up shortly.
I might have some pigs with me, but they'll certainly be at the booth in Perth.

Also check out for the Western Australian Oracle User Group twitter feed - be gentle, it's still learning

Anyone considering attending, consider this - 2 day 5 stream Oracle conference, third day Real World Performance with Tom Kyte, Andrew Holdsworth and Graham Wood. Speakers at the conference will include local regulars Penny Cookson, Connor McDonald, Chris Muir (& myself), plus some of those not native to the Australian continent. We will announce those as we confirm them.

Plenty going on with the user group over next next few months. Wednesday's breakfast double header was a success - well done Anita, Kevin & Andi.
Another interesting topic next month by Jeff Kemp, plus I saw a few e-mails announcing other events. Get involved!

Scott

Wednesday 8 August 2012

Fun with SQL analytical functions

I had an interesting SQL problem at work recently, and I came up with a solution that I'm not sure is completely ideal - so I thought I'd attempt to replicate it here.

Some of the complexity is lost while I obscure and simplify the problem (it involved a hierarchical query), but I think the key elements remain.

Consider a table of codes with an order sequence.
create table my_values (code varchar2(10), order_seq number(5));
insert into my_values values ('A', 10);
insert into my_values values ('A', 20);
insert into my_values values ('A', 30);
insert into my_values values ('B', 40);
insert into my_values values ('B', 50);
insert into my_values values ('C', 60);
insert into my_values values ('C', 70);
insert into my_values values ('C', 80);
insert into my_values values ('D', 90);
My requirement was to see the next and previous code. For instance, when listing B records, I wanted to see A and C in the same row - the solution screamed analytical functions so I started with my favourite:
select code, order_seq
      ,row_number() over (partition by code order by order_seq) rn
from my_values
order by order_seq;

CODE       ORDER_SEQ RN
---------- --------- --
A                 10  1 
A                 20  2 
A                 30  3 
B                 40  1 
B                 50  2 
C                 60  1 
C                 70  2 
C                 80  3 
D                 90  1
I struck the results down to just the first row for each code, incorporating lag/lead to get the info I needed.
select s.code
      ,lag(s.code) over (order by s.order_seq) my_lag
      ,lead(s.code) over (order by s.order_seq) my_lead
from 
(
  select code, order_seq
        ,row_number() over (partition by code 
                            order by order_seq) rn
  from my_values
  order by order_seq
) s
where s.rn = 1
order by order_seq;

CODE       MY_LAG     MY_LEAD  
---------- ---------- ----------
A                     B          
B          A          C          
C          B          D          
D          C                    
Finally, to combine my results I created an in-line view with a subquery factoring statement.
with sub as (
  select s.code
        ,lag(s.code) over (order by s.order_seq) my_lag
        ,lead(s.code) over (order by s.order_seq) my_lead
  from 
  (
    select code, order_seq
          ,row_number() over (partition by code 
                              order by order_seq) rn
    from my_values
    order by order_seq
  ) s
  where s.rn = 1)
select m.*, sub.my_lag, sub.my_lead
from my_values m, sub
where m.code = sub.code;

CODE       ORDER_SEQ MY_LAG     MY_LEAD  
---------- --------- ---------- ----------
A                 10            B          
A                 20            B          
A                 30            B          
B                 40 A          C          
B                 50 A          C          
C                 60 B          D          
C                 70 B          D          
C                 80 B          D          
D                 90 C                     
So my question is - any ideas for a simpler solution?

Monday 6 August 2012

Why you should get involved with your local Oracle User Group

Thanks to our Sydney Pythian friend Yury, we have a video from last year's Oracle AUSOUG Conference available for viewing.

This was a panel discussion on why you should submit an abstract to an Oracle user group event.

It features Oracle ACE card carriers Debra Lilley, Chris Muir, Connor McDonald, Penny Cookson, Tim Hall

You can watch it via this link http://www.youtube.com/watch?v=TeXvxmrywDs or using the embedded video below.

It is conference length, and viewing via video certainly does the discussion no justice - but I hope if you are even considering / contemplating / had passing thoughts about presenting at either a conference or a monthly meetup, you might be enthused to take it a few steps further. We can certainly help you along with this, in fact on the 15th August we have 3 presenters making their debut in a Perth branch meeting with thanks to Penny's guidance.


Abstract submissions for this year's Perth conference is closing soon.

Deadline for submissions has been extended until just after the Insync Conference - get in soon!

I look forward to seeing your submission - and if you don't have time this year to prepare one, I'll be more than happy to talk with you at the conference about what your options are for getting more involved in your local user group - it will be very beneficial to your career!

Scott

Thursday 2 August 2012

Thursday Thought - A continuously wondrous age

Forget for a moment the Internet exists. Can you do that?

Recently when I was talking with my Dad, I suggested I'd love the opportunity to experience perhaps a week of the 1950's - just to try to comprehend what life was really like 50 years ago without the presumptions of today. This could really apply to many decades in history, some of which I think would really enable people to understand why certain achievements haven't been repeated because of the climate of the day (I'm thinking moon landing here...)

These thoughts were drifting through my head has I played with my new toy - a Samsung Galaxy 3. Compared with previous smart phones, this thing is quite big - the perspective shown with the pen here doesn't do it any justice. The screen alone is about the same size as the current iPhone.


In particular, I was having a look at the battery settings, and I took a screenshot because I was admiring the wealth of information provided in that 4.8in screen, and the technology it probably takes to obtain it.

From the top, I have a notification from my wife ready for me to make a move in our scrabble-esque game; a notification to update some applications; my alarm ready to wake me tomorrow; wifi action going on; battery percentage remaining; the time - and this is all in the top line.

I wonder what it takes to gather individual application data regarding battery usage!

Back to my opening line, imagine this device without the Internet - what would you have? Perhaps compare it to the first computer you ever owned (and I'm thinking people of at least my vintage here - remember Commodore 64 or perhaps an IBM XT using DOS 3.x)

I'd see myself holding in my hand a device that can take high quality photos; high definition video; operate various time keeping options; an alarm; a torch; calendar; diary; journal; book reader; road map; calculator; gaming device; audio player... oh, and a telephone!

I wonder what people from the '50s would think of a device that did all that, without even comprehending what all the features having Internet access can provide. Kudos to those in history who imagined a potential world. It's getting harder though - what are we going to have in the next few decades?

Regardless, sometimes I think we have to remember how lucky we are to have what we have.

Happy days,

Scott.