Unexpectedly learning something new...

Today I was exploring the new SQL Developer in regard to managing Application Express.

There is an option for example to change the alias for an application. As usual there is the facility to see the SQL so I thought why not have a look what was going on.

I was presented with a useful looking anonymous block I never really thought about before.

I thought I'd try it out:

create table a ( a number);

insert into a values (1);

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into a values (2);

  commit;
end;
/

rollback;

select * from a;

Not long after I found another potential use, I was doing some work with triggers and I was trying to decide on the method to create a new record on the same table that fired a trigger, but avoid the mutating table issue - perhaps this could do the trick without the need for a procedure defined with the pragma? Alas:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here

How about that? I like learning new things, and I happened to find something about PL/SQL while researching SQL Developer managing Application Express!

Futher empowering search products

One of my recent presentations was inspired by some colleagues talking about the difficulty they find searching the Oracle documentation to find the information they require.

During this presentation I mention a book I read recently (alas I can't remember now which one) where they refer to the concept of the world's entire knowledge being stored on a device the size of a grain of sand. Seems a little fanciful now, but imagine what sounded fanciful back in 1901, and compare that to what we have today.

Of course the reason I made this reference was that our search engines are going to need to become smarter. And we're going to need to be smarter in relation to how we pool information from all resources.

For years now hardware has been sustaining growth to the approximation of Moore's Law. Hardware companies have certainly had their day in the sun, and that indeed continues. Oracle's recent partnership with Sun, releasing information on Exadata V2 show us what's happening in our little sphere.

Microsoft's time in the sun is beginning to wane. Competition grows strong between Bing & Google, and a recent news item caught my attention.

On Google's official blog they announced their future integration with Twitter. Now I mentioned how we need to be smarter with the way we integrate our information. I feel this is a good step towards this goal. Sites like Twitter are an untapped goldmine in regard to current information, current news stories, and the current opinions of the masses.

One of my all time favourite books explores (among other things) the power of real-time information. Richard Wiseman, psychologist and skeptic, recently used Twitter as a tool to conduct a scientific experiment. I heard him speak on a podcast recently where he's straining his brain trying to work out how to use social networking sites such as this that lead the cultural meme as an effective tool to do powerful studies on mass & diverse populations. Integration of this information with powerful search engines I'm sure will aid his work.

Arthur C. Clarke's book linked above mentions a generic "Search Engine" as being a tool ultimately hard-wired into our brains, such that we can use it to search for information, such as looking at the background of someone you just met while you strike a conversation.

Considering the world's knowledge is now more than just an ancient library of information, I feel that "information finding" companies like Google will be the strong force over the next few decades.

I also believe the tide is turning away from journalism and more towards blogs. I particularly find this on the scientific blogs I read. Recently it's been mentioned that perhaps we'll need to pay a subscription for obtaining news from major sources - just like buying a newspaper. I doubt such a model will work well, and for the type of new I'm interested in, I find science bloggers to a more accurate, objective and entertaining job. So perhaps future search engines also need to tailor to our preferences, our needs of the moment. We certainly see some of this happen now when we search for books on Amazon, or listen to music on iTunes or LastFM - common purchases are linked together and strengthen connections like the neural networks I learnt about in university.

Search facilities are the next big thing.

Now before I finish, considering this is an Oracle themed blog, I best mention one of Oracle's best search facilities is Oracle Text (formerly Oracle InterMedia). I'm waiting for the opportunity to explore this feature even more after developing a wonderful search facility a few years ago. Perhaps this requires a future presentation to highlight this possibly underutilised feature.

My thoughts for this week conclude with a quote sourced from Twitter via my RSS feed.

He who does not research has nothing to teach ~ Unknown

2009 AUSOUG Conference Program

Thanks Jeff, for reminding me the 2009 AUSOUG Conference program has been released.

Jeff has indeed listed some key presentations. Having a look through the programs it seems I'll be conflicted in selection between some of the topics.

I'll be looking forward to seeing how my university friend Gabriel Ilarda goes with his first presentation, although he'll face tough competition with Tim Hall on the current schedule.

I look forward to seeing everyone again this year, and if you have registered yet, the early bird rate has been extended - so get in while you can!

I'll be there in Perth and Melbourne, so come by the Sage Computing Services booth and say g'day.

Building simple test scenarios

When I talk to people learning about how to write queries in Oracle - particularly business analysts - a typical conversation would consist of basics such as single row functions, joins and aggregates. However when the subject of DDL is raised, such as CREATE TABLE, I often get the reaction - "Oh, I don't need to know that".

I beg to differ. Regularly I create little tables to test theories with; confirm behaviour; and demonstrate test cases - in fact my schema is littered with them. I think this is an important skill, particularly the latter. If you're having trouble getting some task done, it is often helpful to simplify the problem to the lowest common denominator - a basic test case. Colleagues, DBAs, Oracle support - they'll all ask for the same thing. Visit asktom.oracle.com, he requests and uses them all the time.

So I think one should fluent with creating and dropping small test tables. Take the example of demonstrating the handling of nulls by the AVG group function.

-- always good to have drop at the top so you can repeat the test from scratch
-- ... and clean up your schema!
drop table avg_test;
create table avg_test (a number);
insert into avg_test values (1);
insert into avg_test values (2);
insert into avg_test values (3);
insert into avg_test values (null);
select sum(a)
      ,avg(a)
      ,avg(nvl(a,0))
      ,sum(a)/count(a) my_avg
      ,sum(a)/count(*) my_Avg2
      ,count(a)
      ,count(*)
      ,count(1)
      ,count(null)
from avg_test;

    SUM(A)     AVG(A) AVG(NVL(A,0))     MY_AVG    MY_AVG2
---------- ---------- ------------- ---------- ----------
         6          2           1.5          2        1.5

  COUNT(A)   COUNT(*)   COUNT(1) COUNT(NULL)
---------- ---------- ---------- -----------
         3          4          4           0

So by creating a basic table with just one column, I can insert four rows for this particular case that allows me to demonstrate & confirm the behaviour of avg vs sum/count; and how count(*) differs from count of a particular field. This obviously harmonises with the oft forgot sentence in the documentation:
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls.

With this in mind, I can then relate back to my actual data and see how the following two queries can differ in behaviour just because of the expression I've used within the count function.

SELECT  count(*), daily_rate
FROM resources
GROUP by daily_rate
HAVING COUNT(*) > 1;

  COUNT(*) DAILY_RATE
---------- ----------
         2        100
         4
         2        120

SELECT  count(daily_rate), daily_rate
FROM resources
GROUP by daily_rate
HAVING COUNT(daily_rate) > 1;

COUNT(DAILY_RATE) DAILY_RATE
----------------- ----------
                2        100
                2        120

We've always got to consider nulls, even with aggregated functions!

In the context of a person learning how Oracle behaves, the simple process of creating small test tables can be quite valuable.

Waiting for the OOW2009 announcement...

As not to be lost in the flood of Oracle Open World blog entries, I'm keeping myself quiet - instead living vicariously through the wonderful tales of the conference.

I've been particularly interested in the perspectives coming from Tim Hall, but what I'm really waiting for is the "big announcement" for this year. Sure, there is plenty to be said about the new Exadata machines but I'm a software developer!

If my time zone calculations are correct, within 20-24 hours (gee, we really are on the other side of the world) Larry Ellison should be delivering his keynote speech.

I wonder he'll include the news of a nearby launch date for Apex 4.0...

On wrapping & obfuscating PL/SQL

The Oracle database provides the ability to obfuscate PL/SQL code using the wrap utility.

Many moons ago when I was working on a 9i database, I encountered an issue where my information wasn't completely obfuscated. I had a package that was performing some encryption, and I wanted to ensure the seed to my encryption method was hidden. Take the following example:

create or replace procedure seeder is
  vc varchar2(20) := 'This string';
begin
  null;
end;
/

On line 2 I declare a string. I would expect this information to be wrapped, just like the rest of my code, however when I assessed the wrapped version of the PL/SQL after using the following command:

wrap iname=c:\seeder.sql oname=c:\seeder.plb

I found that I could still see my string definition amongst the code (this is a partial copy from the resulting output):

...
2 :e:
1SEEDER:
1VC:
1VARCHAR2:
120:
1This string:
0
...

This wasn't acceptable, so my solution was to declare variables that contained one character strings and concatenated these to form my seed. In hindsight, perhaps I also may have used CHR() to formulate a string.

Recently on discussing this topic I wondered if the current version of the database had the same issue. I tried on 10gR2 using a combination of supplied PL/SQL packages.

exec  DBMS_DDL.CREATE_WRAPPED(dbms_metadata.get_ddl(object_type => 'PROCEDURE', name => 'SEEDER'));

And the resulting code had a different feel about it:

SAGE@sw10g> select dbms_metadata.get_ddl('PROCEDURE' ,'SEEDER') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','SEEDER')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "SAGE"."SEEDER" wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 92
t5QJKAdccmuGzbpujO65PNpHmLQwg5nnm7+fMr2ywFxpnp8yMlKyCQmldIsJaefHK+fHdMAz
uHRlJXxlUMNLwlxpKPqFVisW0T6XRwzqxIvAwDL+0h3l0pmBCC2LwIHHLYsJcKamqIi2Mg==

1 row selected.

So it seems that the algorithm has improved and being able to "see" strings in the wrapped code is no longer a problem.

Once again another demonstration of how "known knowns" can change over time, and you must always test behaviour on your version; your infrastructure.

More Aces than a deck of cards

... says the latest bulletin regarding the 2009 AUSOUG Conference.

Oracle Aces presenting this year include:
Connor McDonald - whom I've had the pleasure of working with in the past.
Lucas Jellema - will also be conducting some extra seminars while in Melbourne. 
Peter Koletzke - if you're into JDeveloper, he's your man. That link is a gold mine for middle-ware papers.
Tim Hall - Best speaker from last years conference series.
Penny Cookson - My effervescent yet humble foreman. Her presentations always get a giggle.

And of course I'll be there presenting my wares. Hope to see you, too!