COLLECT and DISTINCT

In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.

SQL> select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver'
, 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')


SQL>
SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea'
, 'Nanette', 'Oliver', 'Peter', 'Sundar')

However, if you get into PL/SQL, things are different. Without the DISTINCT, it works fine.


SQL> declare
  2    v_tab sys.dbms_debug_vc2coll;
  3  begin
  4    select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  5    into v_tab
  6    from HR.employees
  7    where job_id = 'SA_REP'
  8    and manager_id in (147,145);
  9    dbms_output.put_line(v_tab.count);
 10  end;
 11  /
12

With the DISTINCT an error is reported.


SQL> declare
  2    v_tab sys.dbms_debug_vc2coll;
  3  begin
  4    select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  5    into v_tab
  6    from HR.employees
  7    where job_id = 'SA_REP'
  8    and manager_id in (147,145);
  9  end;
 10  /
  select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

That's the case in 11.2.0.2. I'm not sure if there's a patch for it, or whether it will be fixed in 12c. [And if they want to get that out in the twelfth month of 2012, they don't have long left.]

SQL Developer, COLLECT and CAST

This one is a quick hint for SQL Developer and the COLLECT function.

In case you're not familiar with it, COLLECT is an aggregate function that was introduced in 10gR2 and simply gathers all the items up into a VARRAY/TABLE style collection type. If you use it in PL/SQL programs, you can handle the collection programmatically.

But sometimes it can be handy to see the elements in a quick ad-hoc query.

In SQL*Plus, you'll get an output that is ugly, but usable:


SQL> l
  1  select country_id, collect(city)
  2  from hr.locations
  3* group by country_id
SQL> /

CO
--
COLLECT(CITY)
--------------------------------------------------------------------------------
AU
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Sydney')

BR
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Sao Paulo')

CA
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Toronto', 'Whitehorse')


In SQL Developer, the same query will return you an error:

ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

As described in the documentation for COLLECT, you should apply a CAST. If you don't already have a convenient TYPE that is a TABLE OF VARCHAR2, and can't create one, there's a built-in one you can use, SYS.DBMS_DEBUG_VC2COLL. Unusually for a SYS object, you do need to specify the schema name as there's no public synonym.


select country_id, cast(collect(city) as sys.dbms_debug_vc2coll) cities
from hr.locations
group by country_id

I'm mostly using 11gR2 now, which has the more powerful LISTAGG, but if you still haven't upgraded all your databases, ad-hoc COLLECTs can be useful. And in programs, a collection is easier to parse than a string.

Toastmasters - Testing public speaking away from 'production'

Every Wednesday lunch (pretty much) there's a Toastmasters meeting at the company where I'm working. After having been there a year, I finally got around to turning up a couple of weeks ago. Hey, they also have live music on Wednesday lunch. It's a busy place.

Yury, from Pythian and the Sydney Oracle Meetup, has mentioned Toastmasters. Given my audience is mostly developers, let me sum it up my way: Toastmasters is a test environment for public speaking.

As with a test environment, you're expected to have a few "does not meet requirements" incidents. That's why it is there. You can experience those, and learn from them, and repeat with improvements. And none of that damages "production". It is a dedicated, safe environment for rehearsals.

I can't speak for all groups, but in the couple of meetings I have attended, as a guest, I've seen positive and constructive criticism. As well as learning to speak, others are evaluating...which means listening. The talks are only a few minutes, so I don't bore people with techy details. There's no sign of Powerpoint either, so it isn't quite a conference rehearsal. 

Again, like testing, this means you are using 'test' data, rather than volume testing. You can focus on the fundamentals of communicating rather than on content. 

I plan to sign up fully in the near future, so there might be more coming in this blog, or it might come through in my Google Plus posts (apparently I am in the circles of over a thousand people, but I'm none the wiser on how many actually read anything I post).

Windows 8 running Oracle 11g XE

It's been several months since my last blog post, mostly because free time is a scare resource.

But after a few false starts, I've removed the last vestige of "Windows XP" from my home, and upgraded my netbook to Windows 8. Windows 7 was never an option for this machine and Windows 8 has an afforable XP upgrade option and a simpler set of variants. The "Pro" version which has replaced my "XP Home" adds such goodies as acting as a Remote Desktop server, which was always skipped for 'home' users. I feel all grown up :)

The Jumps

The first false start was the Windows 8 upgrade assistant which fell over several times. Since it won't let you go to the 'Buy' component without going through this step, it was a significant obstacle. I uninstalled a bunch of stuff and then created a fresh administrator account. One of those fixed the problem and it informed me of several minor issues but no showstopper.

The analysis did suggest that the Oracle 11gR2 XE would not necessarily need re-installing, but I can't be sure if that was true.

The 'Buy' was the second barrier, with repeated failures when trying to produce an invoice. None of the unsuccessful attempts reached my credit card, and it eventually succeeded. I think I needed to wait until the US and/or Europe was asleep before the server could cope.

The final blocker was a failed attempt to upgrade and keep 'Personal documents'. In my position, computers tend to get a lot of stuff installed and uninstalled on them. After a couple of years, I think the XP environment was just too stuffed to manage an upgrade. 

Success

I decided to backup a bunch of stuff and did a 'kill all' install which went all the way through, and didn't actually wipe out the 'D:' partition of my disk anyway.

The 1024x600 resolution of the built-in screen means the "Don't call it metro" apps / Windows Store is unavailable. I might look into solutions to that, but it isn't a big deal.

Reinstall

Now I needed to regather all my favorite applications, and see if the VPN option to work will still run.

Google Chrome was the first application installed. I will try IE10, but I think the clutter of the default home screen for Australian users (ninemsn.com) made me flinch.

Oracle XE 11g has been re-installed and I'm downloading the latest version of SQL Developer which Jeff has had updated again.

MobaXTerm has apparently jumped to version 6 while I blinked.

PSPad has been at version 4.5.6 for a year. 

Greenshot is my favoured screen capture utility

VLC Media player has been added too.

Haven't decided whether to aim for OpenOffice or LibreOffice, and I suspect there'll be a few more applications I remember over the coming weeks.

Being just a little bit unique

A few weeks back, I saw a mention of an old Tanel Poder post about the uniqueness of ROWIDs.

This is an interesting subject as a lot of people expect ROWIDs to be unique, but don't think about how unique that is. While you can't be a little bit pregnant, you can be a little bit unique. Or at least unique only within a specific subgroup.

The only complaint I have about the piece is that it is very DBAish. Lots of moving files around the OS and all those other things that DBAs like to do.

I'm a developer, and like to stick with SQL. And it is pretty easy to demonstrate the non-uniqueness of a ROWID without leaving SQL*PLUS.


SQL> CREATE CLUSTER emp_dept
  2     (dept_id NUMBER(4));


SQL> CREATE TABLE dept_c
  2     (dept_id number(4) primary key, dept_name varchar2(20))
  3     CLUSTER emp_dept (dept_id)
  4     ;


SQL> CREATE TABLE emp_c
  2     (emp_id number primary key,  name varchar(20), dept_id number(4))
  3     CLUSTER emp_dept (dept_id)
  4     ;


SQL> CREATE INDEX idx_emp_dept ON CLUSTER emp_dept;


SQL> insert into dept_c values (10,'Accounts');


SQL> insert into emp_c values (1,'Scott',10);


SQL> select rowid from dept_c
  2  union
  3  select rowid from emp_c;


ROWID
------------------
AAAF/4AAEAAACX9AAA



SQL> select rid, count(*), min(type), max(type)
  2  from
  3    (select 'D' type,  rowidtochar(rowid) rid from dept_c
  4    union all
  5    select 'C' type, rowidtochar(rowid) rid from emp_c)
  6  group by rid;


RID                  COUNT(*) M M
------------------ ---------- - -
AAAF/4AAEAAACX9AAA          2 C D



Of course a DBA will still argue that the ROWID is unique and this one simply doesn't belong to a table.

But the takeaway is that a ROWID is not tied to a single row in a single table in a database.

Whatever happened to...Packaged Apex Applications

Many moons ago, there existed a bunch of packaged applications for Apex on the Oracle website.

Alas and alack, they were removed and all that exists in their place is a collection of sample code. They'll be making a comeback in Apex 4.2 where they will be 'prepacked' in the installation and ready to install. You can keep them vanilla, and perhaps get some support from Oracle for them (or maybe just an upgrade path), or you can unlock them and make your own customisations and enhancements.

These packaged productivity apps also got mentioned as part of the cloud.oracle.com offering. Assuming that does eventually materialize. Maybe its been waiting for Apex 4.2. (By the way, the EA is running on 11gR2 so there's no 12c hiding there.)

The only drawback is that the new packaged applications won't be available for anything earlier than 4.2. I guess it is possible for someone to unlock them and somehow achieve a backport to an earlier version, but I wouldn't count on it. Certainly don't expect that from Oracle.

Anyway, for a bit of nostalgia, I dug out one of those old packaged apps from my hard drive. The original Issue Tracker was developed for Apex 2.2 and is old enough to be attending school. The new one looks a lot more attractive. Since it caters for features such as uploading attachments, it looks much more like a usable application than a sample or demo.

Chrome, Firefox and chunky padlocks

For sometime now I have been using Chrome as my preferred browser. Once upon a time it was Firefox. But there's a few things about Firefox I dislike and this is one of them.


Some site, such as the New York Times, have a half-hearted implementation of HTTPS. I generally see this when someone who uses a tool like "HTTPS Everywhere" posts a link. The problem is that Firefox doesn't make it REALLY obvious that the https is a bit broken.


You see the regular icon, and if you bother to click on it, you get a little warning.

Now, compare that to Chrome, which gives you a big chunky warning you have to accept. 

And if you do accept it, you get a padlock with an 'x' on it, and a line drawn through the https text. This makes it obvious that it is broken.

When the https is okay, Firefox does give you the hostname but it isn't that great. Again, clicking gives you a bit more info.

Chrome gives you the padlock icon and a whole bunch of techie details are just a click away..

Ever increasing priorities

Inspired by a recent Martin Widlake post, I also have something to say about urgency.


In most problem/incident management systems there is some 'priority' level. And all the ones I've met, count it the wrong way, by starting with "1" as the highest priority.


The reason this is wrong is that, as anyone who has worked on the receiving end of such a system, there is always something that will trump the current highest priority.


"1" should refer to the lowest priority item. A typo in the help text (or the use of the phrase 'most unique'). The items that will never actually get fixed, but you record in the system just to let everyone know you are aware of the issue.


Then you work your way up to level 2 where the work might one day get done by some new guy as part of a learning exercise, but where it isn't something that is affecting the business in any material way. Actually a lot of issues at this level are trivial to fix, so if someone needs to 'get the numbers down' this is the place to target. 


Level 3 should be the 'regular' work. In theory. In practice, you'll find most stuff at this level is actually level 2 items that have been pushed up because someone wants to see some progress. But also the hard stuff tends to sit here a long time as people argue over solutions.


My level 4 would correspond to what most systems class as urgent. The business expects all issues at this level to be actively being worked on by someone. If you have 20 issues at this level and 5 team members, this won't actually be the case though.


This is where corporate politics gets involved, and who has the biggest influence. And this is why I recommend counting from the bottom up. Because as people start pushing their issues up, there is unlimited room for the priority levels to be increased. Levels 5 and 6 will quickly come into play. After a few months, someone will find a need for level 7. When people find out there is a level 7, the issues at levels 5 and 6 will be pushed up the rankings.


Don't settle for single digits though. You'll hit 9 within a year and then you'll need a high priority issue to make enough room for a level 10.

What are these dollar signs doing in my code ?

I've had the good fortune, in my current role, to be doing a fair amount of PL/SQL development in 11gR2. Boy, it feels good to be actually using up-to-date versions. Being picky, we are on Apex 4.0, rather than 4.1 but that's not a biggie.

One item I've found myself using more and more are the $$PLSQL_UNIT and $$PLSQL_LINE inquiry directives. I think they are great for logging.

Here's a handy example


create or replace procedure fizz_buzz as
begin
  for i in 1..15 loop
    if i/2 = trunc(i/2) then
      if i/3 = trunc(i/3) then
        dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Fizz Buzz');
      else
        dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Fizz');
      end if;
    elsif i/3 = trunc(i/3) then
      dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Buzz');
    else
      dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - '||i);
    end if;
  end loop;
end fizz_buzz;
/

When executed, you get a "poor man's trace" of the path the process takes through the logic.


SQL> exec fizz_buzz
FIZZ_BUZZ(13) - 1
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(13) - 5
FIZZ_BUZZ(6) - Fizz Buzz
FIZZ_BUZZ(13) - 7
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(13) - 11
FIZZ_BUZZ(6) - Fizz Buzz
FIZZ_BUZZ(13) - 13
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz



By automatically substituting in the actual program name (eg the package name) and the line, you don't have to worry that refactoring the code is going to mess up any debug or logging logic. Refactoring is a fancy name for taking chunks of code and moving them into specialised procedures and functions so you can reuse them. Most developers do refactoring but may not know the name of it, or even that it has a name. Mostly you only need to know the buzzwords at interviews.


So that is what those $$ directives are doing in my code.

Why I don't (always) care if you follow me....

This one is inspired by Jeff Smith's (yes, That Jeff Smith) post "Why I don't follow you" post.

I don't care if you follow me.

Okay, that's not completely true. I hope you didn't stop reading then. What I really meant was that I don't expect all the people that I follow to follow me back.

Here's why.

I read a lot of blogs and articles to learn stuff I don't know about. I'll subscribe to RSS feeds and add people to my Google Plus circles if they write or link to 'quality' material. By quality, I mean stuff that I find interesting or useful.

I write stuff here (occasionally, and will do an Oracle based one in the next day or too) and link to articles through Google plus. These are things that I think / hope other people may find interesting or useful.

So I follow some people and some people follow me. Inevitably there will be some overlap when people are interested in the same topics. But there are some people who know far more about a topic than I do and won't learn anything new from my posts. Or they post on a topic which I 'consume but don't publish'.

So I'm not offended if you don't follow me. Please don't be offended if I don't follow you.

PS.
Ongoing frustrations with Twitter mean that I've mostly replaced it by Google Plus.
Contrary to what I've said here, if you circle me there and have something like 'DBA' in your profile, I will circle you back because I have a special circle for DBAs. As did Dante, I believe.

I'm very flattered to be followed on Twitter, but I grew fed up of trying to twist sentences into something that is accurate and useful but still brief.

Crazy Crossovers

Some months back, when it was in a 99c special offer, I added "Pride and Prejudice and Zombies" to my book collection. If you haven't heard of it, Seth Grahame-Smith took most of Jane Austen's work (by now in the public domain) and added a zombie subplot. 


Then about a month back, one of Google Plus's more popular contributors,Tessie L'Amour, offered up the option of personalizing her (NSFW) books so that the purchaser could pick the names of the protagonists. That idea isn't new as I recall my parent getting me a personalised book when I was young. It involved a wizard who enjoyed peanut butter and had a magic sunflower. See, these things can stick with you !


Wanting to experiment with some Google Apps Mail Merging, I borrowed both concepts. Following the guide, what came out was a web-form where you can enter the desired first and last names, plus an email address, and you get a personalised PDF of the first of the Sherlock Holmes short stories, "A Scandal in Bohemia". A google search quickly found similar personalisation of other classic works.


This is the story where Sherlock meets the only woman he truly respects. The personalisation relates to the name of the woman (Irene Adler in the original), rather than Sherlock or Watson. I'd prefer an actual ebook format, but that looks a lot trickier. I've settled for sizing the PDF as A5 which makes it readable on my Kindle.


I haven't (yet) included the original Sidney Paget pictures, though I suspect they will also be in the public domain by now. That may happen in future, but work beckons for tomorrow.

Playing with Google Plus

It's a Christmas break for me and I've been reviewing my social networking commitments.


I started by thinning RSS Feeds and Twitter load. My main problem with Twitter was a lot of my subscriptions were US based and the timezone meant a poor fit for me. The feed often overflowed overnight, and relatively little would come up during the day.


I'm shifting more to Google Plus. My initial experience echoes the "Quiet here, isn't it" which I saw mentioned a lot in the media. Luckily after persisting for a few days, I found some shared circles to import, and later someone else shared a circle with me of 500 Aussies. 
This is the first failing of Google Plus - unless you are following someone when they share a circle, you will miss it. That makes it hard to find a decent starting point.


I have found Google Plus much more like Twitter than Facebook. If Facebook friends are people I'd invite over for dinner, Twitter and Google Plus contacts are a wider group of people I'd chat with in a restaurant or a pub.


Circles are a useful way to group people to read, similar to Twitter lists. For example, I have circles of writers, of Australians, of techies etc. They can be useful in distributing some material to a limited audience, but I'd recommend posting most things to Public. There are items I'll only post to my Australian or Techie circles if I think it would 'pollute' my public flow with information that most people would find irrelevant.


If there are some people I want to hear everything from, I put them in a specific circle and I can read everything in that circle. 'Noisy' individuals will go into a circle where I don't mind missing posts. 

Google Plus has a couple advantages over Twitter. 


Firstly, comments are attached to a post which allows for a conversation to develop. I don't find Twitter conversations as usable (but maybe that's my client). In Twitter, I get a few disjointed messages if I don't follow everyone who is chatting, but I can see everyone who comments on a Plus post. Another failing of Google Plus is that when you re-share another person's post, it is hard for the recipient to get back to the original. As such, they'll tend to comment on the shared post not the original.


By the way, when you comment on a post, you can't see who the post was shared with. That means you don't know who can see your comment. When in doubt, assume it is visible to everyone, including your boss, wife and mother.


Secondly, there's no 140 character limit. The posts can be fuller and include images and video. With Twitter, sometimes I get redirected through half a dozen URL shorteners to see a cartoon I read through someone else the day before. 


My gut feel is that Twitter has more to worry about from Google Plus than Facebook. 

• Migration from Twitter has few barriers. It wouldn't be hard for a client to integrate tweets into a Google Plus stream. It will be easy to pull out a list of who an individual follows for that integration. It will also be pretty easy to find accounts on Google Plus corresponding to those twitter accounts.
• Tweets are ephemeral so no-one expects to maintain history.
• Twitter doesn't have features likes games that contribute to the stickiness of Facebook.
• The content will be that bit richer on Google Plus than the forced brevity of Twitter.

Pay a few of the high-profile Tweeters to shift over, and you'll get some major traction. The biggest barrier is probably the 18 age limit on Google Plus, so Twitter might still maintain a place as a 'kiddie channel'.

Personally, I expect to abandon Twitter in the next 12 months.


The Minus about Plus 


There's still a lot that is broken in Google Plus. 

1. I had a couple of fireworks photos on my phone from New Year's Eve and Android's "Instant Upload" took at least a day and a half. 
2. Sometimes retrieving posts suffers from irritating lags. My guess would be that, even for Google, they still need to work on scaling.
3. Saying the API is half done would be generous. It doesn't allow posting so that means there are no practical third-party clients, and there's no way of automating posts (eg from an RSS feed). They shouldn't fix that until after they can guarantee the scaling.
4. I'm still managing a primary and 'Google Apps' account because the merging is yet to materialize. I've also got a separate page for SydOracle which I will have to think about.
5. Managing circle membership is cumbersome. It would be nice to have the ability to make some circles as public/shared and for them to show up on a profile.
6. To a degree, it is still 'empty'. If your interest if photography or Android, you will be spoilt for choice. Other interests or hobbies may be less well represented.

Suggestions


If you are going to join Google Plus (or get deeper into it) I recommend the following :

• Share or post a bunch of stuff over a week or so, and make it all Public. That way anyone looking at your posts will get some idea of who you are, what you are interested in etc. Sharing stuff through Google Reader works well for me. Sharing from the browser on my Android phone is less pretty.
• Then build up a LONG profile. You can search profiles for key words (or twitter handles etc) so make sure that anyone looking for you can find you.

When you put someone in your circles, they may put you in theirs. If they push some material only to particular circles, you want them to easily work out which of those might be appropriate for you. 

• Do some searches on things that interest you. If you find posts that interest you, add the author into an appropriate READ circle. See who they follow, and you may find some more people to add.
• Read up on this for finding people. 
• If someone you have circled seems well-integrated, and your profile has been sufficiently engaging that they have added you, then you can send them the equivalent of a direct-message. Ask them if they have a circle of similar people they can share with you.