Sydney Oracle Lab

Crazy Crossovers

2012-01-08T16:55:00.000+11:00

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

2012-01-04T20:29:00.000+11:00

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.

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.
Sometimes retrieving posts suffers from irritating lags. My guess would be that, even for Google, they still need to work on scaling.
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.
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.
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.
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.

Newsflash - the replacement for the Oracle Support HTML interface will be....HTML

2011-12-29T20:14:00.003+11:00

Like many other Oracle peeps, I got an email telling me that the current Flash and HTML interfaces for My Oracle Support were to be replaced by a new snazzy ADF based UI.

What has irked me is the way that some people have implied that the new interface won't be HTML.One example is the Oracle Infogram ("There's a new version of MOS in the works, not Flash and not HTML"), though I've seen similar (especially on Twitter which is admittedly constrained by the 140-character limit).

Let's clarify this. Browsers mostly render HTML (though they deal with straight text, images, maybe video and some other stuff). Through plugins, they can do Flash or Java.

But the new Oracle UI will be HTML. HTML spewed by a bunch of App Servers running Java and ADF, true, but still HTML. Going further, if you actually do a 'View Source' in the current HTML support application, right at the top it has meta name="generator" content="Oracle ADF Faces"

I don't know how much, if any, of the ADF layer of the current HTML application is being re-used for the new UI. They may well have done a complete throw-away and rewrite. But it isn't a novel technology stack being used here.

Maybe this is nit-picky. I just had a horrible feeling that there might be some people out there in Oracle land (and, if I'm being blunt, I don't mean developers) who don't realise that Oracle Forms stuff is an exception and that the main web development paradigm consists of mid-tier code generating HTML (probably with lots of javascript too, but that's a separate issue).

DBMS_METADATA and hashed passwords

2011-11-26T10:38:00.001+11:00

In the wonderfully named "ORAganism" blog (don't try oragasm - it is used for something QUITE different) Neil Johnson remarks on the privilege requirements for DBMS_METADATA.

I like DBMS_METADATA. In the old days, you had scripts that tried to re-engineer CREATE scripts from DBA_TABLES and so on. They were complicated and worked in most situations. DBMS_METADATA is much more powerful.

One feature worth noting about DBMS_METADATA is that it can also extract details about users which can be handy in the post 11g world of Oracle.

In 11g the PASSWORD column in the DBA_USERS returns null, rather than hashed version of the user's password as it did in 10g and earlier. That made it a lot harder to extract those password hashes from the database. There is some underlying SYS object that shows the hashes, but SYS objects are really tricky to access.

DBMS_METADATA gives a backdoor to that information. Not to everyone, but it should be usable by 'regular' DBAs without jumping through hoops.

select dbms_metadata.get_ddl('USER','GARY') a from dual;

CREATE USER "GARY" IDENTIFIED BY VALUES 'S:8A9EA7B75F8899D7163336AD9D29F9019C0361518594E6984E1EF1C4EDB8;34BCEDBA9E0AB83F'
DEFAULT TABLESPACE "APEX_4875120311438442"
TEMPORARY TABLESPACE "TEMP"

Before you try to crack my password, don't bother. In my little home environment, it is the same as the username.

Wish List IV - Read only and mean it

2011-11-18T19:00:00.000+11:00

Did you know that the SELECT privilege actually allows you to do a SELECT ... FOR UPDATE ? Yes, that means a user with just that privilege could lock an entire table.

I want a SELECT_NOLOCK privilege. The user can still do a SELECT, but they cannot have a lock on the table. Okay, while an SQL is executing, I guess that get the shared table lock. But that's it.

Wish List IV - The Whole Truth

2011-11-17T19:00:00.000+11:00

This is another little annoyance that, I hope, would be a simple change.

I want a column in USER_TAB_COLUMNS that tells me whether the object is a table or a view. Surely linking off to USER_TABLES to exclude views is an unnecessary burden.

Wish List III - The Sequence Cometh

2011-11-16T19:00:00.000+11:00

This is another of those 'chores' for which every DBA and his dog has a script.

Copy a new version of a table from Test to Dev and then you have to recreate or adjust the associated sequence. My proposed syntax would be

ALTER SEQUENCE blah_seq SYNCHRONIZE WITH blah_table (blah_column);

That would lock the table, get the highest value of 'blah_column' and reset the 'last_number' of the sequence to that value. Even better, retain the fact that the sequence has been synchronized with that table/column so there is some metadata about the relationship.

Wish List II - Ultimate Destruction

2011-11-15T19:00:00.000+11:00

I'm continuing on my theme of a wish list for 12c (or beyond), and this time I'm taking aim at destroying stuff.

Juggling development and test areas, every so often it is tempting to wipe the slate clean and start with a fresh schema. The simple way to do that is with a DROP USER blah CASCADE

It would be nice to have something one step below the 'nuclear' option. I'm thinking

DROP ALL TABLES UNDER SCHEMA blah

plus similar options for dropping sequences, views, procedures, triggers and so on. The user/schema continues to exist, with all their privileges and defaults. But you get to clear out the objects the schema owns.

Yes, you can script this. The trick is to disable all the referential integrity constraints first, so that you can drop the tables without worrying about the dependencies.

Cross-schema constraints could still be a problem, as could firing DDL triggers. I'm open to alternative mechanisms. Perhaps DBMS_METADATA could generate a script for dropping objects.

12c Wishlist - Part 1

2011-11-14T11:53:00.000+11:00

Last week Nuno posted his 'wish list' for 12c. Here's the "Number 1" on my list:

Oracle Personal Edition for Linux.

A long, long time ago...I can still remember
when Windows was the OS that ran on the PC on your desk. It made sense that someone running their 'personal' database would just install it natively on that desktop machine, and that meant a Windows install.

Too often that would just be an install on top of a Standard Operating Environment pre-configured with Office, a virus checker and the other bloat that hangs around. Yuck.

What I'd like to recommend is that you start with an OS that is stable and decidedly unglamorous, such as RedHat or Oracle Linux. That could run on a commodity x86 under your desk. It could run in a VirtualBox virtual machine on your SOE machine, or in a VMWare or Oracle VM environment managed by real server admins. It could migrate happily between any of them.

But when I go to the documentation I see "Personal Edition is available on Windows platforms only"
Oracle are saying go and buy software off Microsoft before you talk to us about running our database ?

I want Oracle Personal Edition on Linux. Why not a bundle price for the database and Oracle Linux ? You could even sell it as a pre-configured VirtualBox appliance - just add disk. You could 'give away' a one-year Personal Edition licence (cost around $100) with every OCP certification. But push Linux as the preferred platform.

I'm not sure of the production use cases for Personal Edition. I heard, many years ago, that it had market share in Life Sciences, but it may have since been eclipsed by more specialised solutions, open source RDBMS or NoSQL data stores. Maybe it won't even be offered in 12c, squashed out between Express Edition and MySQL.

-----------------------------------------------------------------------------------

I've got a few more items on my wish list which I'll post over the coming days. I figured I'd do them as individual posts. In theory that means Plus +1 and retweets might give a guage as to the support for each idea.

What's the difference

2011-11-12T19:43:00.001+11:00

I enjoy working with Apex. However one challenge is that it doesn't sit too well with conventional source control systems. Plus the fact that not all organisations use source control systems, especially for the small departmental applications which are a particular sweet spot for Apex.

One item in my little library of scripts is based on the ApexExport utility. This is part of the main Apex install which you'll probably find on the server. If you don't have access to the server (and some DBAs are understandably cautious about that) ApexExport is also available as part of the Apex Listener. That's a much more compact download, and I've based my script on that.

The Apex Listener download is a zip file. When you unzip it you get a WAR file. If you unzip the WAR file, you find the "apex.jar" file, which is what you need. If you like playing Russian dolls, you can even unzip the JAR file into the class files, but I don't see the point.

My Apex diff script simply extracts the same application (based on the id) from two Apex workspaces and does a compare of the results. This is handy to see if the version in a development workspace is the same as that in Testing/Production (eg has another developer done some work on it). An alternative use case is to ensure that no-one has been sneaking unapproved amendments into Prod.

My script is a zip because downloading BAT files is often blocked. It is trivially small, so the code is shown below too.

I've been lazy and hard-coded the username password here. Feel free to replace them with more parameters. Similarly with directory names, and you can replace the MS-DOS "fc" call with one to your favorite diff utility. I've used ExamDiff for a long time and my preferred editor, PsPad has one built in as well.

REM
REM Usage "diff JDBC_conn1 JDBC_conn2 nnnn"
REM
REM Note: Use JDBC connection format 127.0.0.1:1521:xe
REM

del f%3_1.sql
del f%3_2.sql

cd G:\oracleXE_11\apex_listener\WEB-INF\lib
set CLASSPATH=%CLASSPATH%;.\;.\ojdbc6.jar;.\apex.jar

java oracle.dbtools.apex.utilities.APEXExport -db %1 -user gary -password gary -application %3
rename f%3.sql f%3_1.sql

java oracle.dbtools.apex.utilities.APEXExport -db %2 -user gary -password gary -application %3
rename f%3.sql f%3_2.sql

fc /c /l /n /w f%3_1.sql f%3_2.sql

How to prevent your SQL Developer sessions from being killed

2011-11-05T09:03:00.000+11:00

A while back I worked on a site where the network would terminate connections that were idle too long (around 20-30 minutes). It is pretty frustrating when you've been working on something for a while then you go grab a coffee or have a meeting and come back to find the connection had been killed. It was even more frustrating when the session was still alive in the database and had to be killed there, so that it could rollback the work so that it could be repeated all over again.

What I'd like is a SQL Developer feature that would periodically send a lightweight request over to the database so the connection is kept alive. Alas, the powers that be seem averse to providing such a function.

I also accept that there are barriers in trying to get people to change their opinions.

The good news is that SQL Developer has the concept of extensions.
If something you want is too specialised to be included by Oracle themselves, or they haven't got around to it or simply don't want to do it, then an extension can help.

In this case, a bloke called MinChen Chai has published an extension to add KeepAlive functionality to SQL Developer.

Just download it and add the .jar to the directory sqldeveloper\sqldeveloper\extensions and the Right-click menu on a connection will include a Keep-Alive item.

If you want to be on the cautious side, you can use unzip or similar to extract the class files from the JAR file. Then you can use the Java Decompiler (or similar) and view the source from the class files. The SQL used is in the Pinger.class and is a pretty inoffensive SELECT SYSDATE FROM DUAL.

Warning : I haven't tested to see if it works if you spawn out an unshared worksheet.

If your environment is locked down to prevent extensions being added, then all I can suggest is a User-Defined Report with a refresh set to the maximum delay. Unfortunately this grabs focus in Windows and the maximum delay is just two minutes which is much too frequent in my opinion, but it is hard coded. As an aside, the 'reports' component also sits in the extensions directory (oracle.sqldeveloper.report.jar).

Should we be doing this ?

I accept there are situations where it is appropriate for a DBA to terminate sessions. And this won't stop an explicit ALTER SYSTEM KILL SESSION.

However I think terminating sessions automatically based on 'being idle' is something that is abnormal, and is inappropriate for most environments.
Before terminating a session, several factors should be considered. These include

Is the session actually doing something important - like the monthly pay cheques
Is the session blocking other sessions (and which session is more vital to the business)
Is there a need to preserve session state (PL/SQL global variables, temporary tables etc)
Is there an active transaction
Can the transaction be (safely) committed
How much rollback would need to be applied if it is killed
Is the session consuming lots of memory
Can the user/client cope gracefully with a failure

If your DBA doesn't consider such factors and is happy to terminate sessions without worrying about the effect, then you have a problem. You'll just have to grin and bear it and hope that that there's some form of karma.

It may help to read a DBA's blog post about a hosting provider admin deleting some of his log files . I love the comments that "anything with 'log' in the name is by convention disposable but useful for diagnosis" and "Log files do build up and unless there is meaningful data in them you can remove them."

A short story about death

2011-10-31T23:42:00.002+11:00

Let me tell you about an application I worked with some time ago.

It is an 'intranet' application. The application is just used within an organisation, though the database it runs against is also used by public web applications too.

Unusually for a web application, it doesn't use a connection pool. Users log in and a dedicated connection is spawned. And if they do certain operations, those also get a dedicated connection.

None of those connections is allowed to be idle for more than 25 minutes. Every five minutes, a house keeping job comes along and kills off any connections that are idle for more than 20 minutes.

On the face of it, that is a resource saving measure. Otherwise all those idle connections would pile up and kill the server. But actually it isn't. If you look at the 'life' of a session, it spends a few minutes being active, then sits around for 20 minutes doing nothing before being killed. In fact most of the sessions on the database are idle, waiting for their death sentence to be actioned.

If it wasn't for that house-keeping job, someone would have come along and actually fixed the source of the problem. That is, after the operation is complete, the session would be disconnected by the application. Or the logout button on the application would actually do something - like log out of the application.

It is like keeping the shower running after you get out, then coming back ten minutes later to turn it off so as to not waste water.

The best time to end a session is immediately after it stops doing useful work. If you choose to implement a 'kill if idle' option, you are saying it is okay for resources to be wasted for the period up until you decide it has been 'too idle'. Sometimes it is - it may be too difficult or expensive to fix the source of the problem.

But don't pretend this is the most efficient mechanism to manage resources.

Bought to you by my "Campaign for the Humane Treatment of Database Sessions'

An underhand compilation

2011-10-11T07:00:00.000+11:00

Oracle cheats. Or, more generously, it sometimes plays by different rules. Normally a DDL will do an implicit commit of any outstanding transaction. Actually it does a couple of implicit commits, one before it tries the DDL and a second if it succeeds.

Furthermore, an 'ALTER PACKAGE .. COMPILE' is a DDL statement. And it does an implicit commit.

However, if the compilation occurs implicitly, because you are trying to execute a package that it is invalid, then your outstanding transaction ISN'T committed. That is what you want as your application can't cope with having partial transactions committed just because it needs to compile a package.

The question really is, if the commit isn't actually necessary, is it being performed by the ALTER PACKAGE simply to be consistent ?

Demo script follows:

XE> DROP TABLE plch_data PURGE;

XE> CREATE TABLE plch_data
2 (id NUMBER, col_a NUMBER, col_b NUMBER);

XE> CREATE OR REPLACE PACKAGE plch_pkg
2 IS
3 FUNCTION ret_count RETURN NUMBER;
4 END plch_pkg;
5 /

XE> CREATE OR REPLACE PACKAGE BODY plch_pkg
2 IS
3 FUNCTION ret_count RETURN NUMBER IS
4 v_num NUMBER;
5 BEGIN
6 SELECT COUNT(id) INTO v_num
7 FROM plch_data;
8 --
9 RETURN v_num;
10 END ret_count;
11 END plch_pkg;
12 /

XE> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name = 'PLCH_PKG';

OBJECT_NAM OBJECT_TYPE STATUS
---------- ------------------- -------
PLCH_PKG PACKAGE VALID
PLCH_PKG PACKAGE BODY VALID

XE> ALTER TABLE plch_data DROP COLUMN id;
XE> ALTER TABLE plch_data RENAME COLUMN col_a TO id;

XE> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name = 'PLCH_PKG';

OBJECT_NAM OBJECT_TYPE STATUS
---------- ------------------- -------
PLCH_PKG PACKAGE VALID
PLCH_PKG PACKAGE BODY INVALID

XE> INSERT INTO plch_data (id) VALUES (10);

XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1

XE> ROLLBACK;

XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
0

XE> REM =================================================================
XE> ALTER TABLE plch_data DROP COLUMN id;
XE> ALTER TABLE plch_data RENAME COLUMN col_b TO id;

XE> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name = 'PLCH_PKG';

OBJECT_NAM OBJECT_TYPE STATUS
---------- ------------------- -------
PLCH_PKG PACKAGE VALID
PLCH_PKG PACKAGE BODY INVALID

XE> INSERT INTO plch_data (id) VALUES (10);

XE> ALTER PACKAGE PLCH_PKG COMPILE BODY;
XE> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name = 'PLCH_PKG';

OBJECT_NAM OBJECT_TYPE STATUS
---------- ------------------- -------
PLCH_PKG PACKAGE VALID
PLCH_PKG PACKAGE BODY VALID

XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1

XE> ROLLBACK;

XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1

It's Enterprise and won't be cheap....

2011-10-08T07:00:00.000+11:00

Most of OOW seemed to be about Boxes, the bigger the better.

I'm not a DBA. The people at Pythian are, and they've said nice things about the Appliance. I think it suits their business model, which is external DBA support. If a company already has good DBAs and listens to them, it shouldn't fare too badly on getting the correct hardware setup. If it doesn't have good DBAs (or it doesn't listen to them), then it probably won't hear about the appliance, and they'll end up with some commodity kit (maybe running MySQL or even SQL Server).

The Exalytics machines is way out of my field. It is probably good. It is probably expensive. It is probably something I won't get my hands on. The same goes for the rest of this Big Data stuff. Clusters of scores of machines are expensive.

We do have a nice NoSQL solution. I'll probably download that to play with, but it is something that is really for Java developers (or some of these newer languages that get fashionable every couple of years). I think I'll have to re-title myself as a 'Relational Database Developer'.

So the 'cloud' bit was the part that appealed most. Not the Fusion Apps stuff. Its nice that it is finally out there, but that isn't my field either.

Guy Harrison's tweets have included some nuggets "DB cloud service will not support direct SQL from outside oracle public cloud - stateless REST calls only." and "DB cloud service gives you a schema in a shared DB, not a whole instance like Amazon RDS. More like SQL azure than RDS"

Pricing won't be cheap. This is Enterprise Edition folks. It's not for startups trying to run a business on pizza, coke and little white pills. It is for people with big pockets.

Roel Hartman indicates that SQL Developer 3.1 will have some special sauce that will allow you to connect to these cloudy databases. I think the twitter-stream has a screen shot, but I've lost it. [Twitter was in turbo mode the past few days - unfortunate for us down under where a lot of the action happens while we are in bed.]

Other IDEs may need to do some catching up to work with those databases. I'd also suspect that it might prove a barrier to any applications not running on Oracle's cloud too. While Java (and other JVM languages ?) might be doable, I wonder whether Forms will make the cut.

Application Express seems to be making its presence felt. Though after Larry aired his views on Salesforce, I wonder whether the "apex" name will become a battleground as a "cloud language". Hey folks, the appex.com domain name appears vacant at the moment.

Oh yes, the Social Network thing. My last company used Yammer, which was pretty similar to what Salesforce offers with Chatter, and the company I'm working at now has something similar. Yes they have a Facebook 'feel', but the focus is 'employer' related. People will have both and won't have a problem keeping them separate, mostly because your employer 'owns' one and all your friends who work elsewhere won't be there.

One head, plus one body (slightly damaged)

2011-10-07T20:44:00.002+11:00

We all know that two heads are better than one. Apparently a head without a body can be more useful than one with a broken body.

This train of thought was prompted by an interesting question on the PL/SQL Challenge earlier this week (October 6, 2011 - to be precise). The question posed the puzzle about what happens when you reference a constant defined in the specification of a PL/SQL package if there was an invalid body.

My initial thought was that it is quite valid to have a package specification without a body, and you can safely references types and constants in that specification. I incorrectly assumed that an invalid body wouldn't be a problem.

One reason why it fails is that a body can contain an initialisation section. That is you can have:

CREATE OR REPLACE PACKAGE plch_pkg

g_stuff VARCHAR2 (10) := 'Stuff';

PROCEDURE show_dummy;

END plch_pkg;

CREATE OR REPLACE PACKAGE BODY plch_pkg

PROCEDURE show_dummy

BEGIN

DBMS_OUTPUT.put_line ('Dummy');

END;

BEGIN

dbms_output.put_line('Start it up');

END plch_pkg;

exec dbms_output.put_line(plch_pkg.g_stuff);

This would output both "Start it up" and "Stuff". But of course it can only do that if the body is valid and can be executed by Oracle, which is why Oracle has to try to recompile an invalid package body and must throw an error if it fails.

Laying down the law over warnings

2011-09-18T08:30:00.001+10:00

Tim commented on yesterday's post by saying "I would not be overly disturbed if Oracle made this clause [DEFINER/INVOKER RIGHTS] mandatory in future."

With such a broad range of software that runs against Oracle, some of it from third party vendors, I don't see Oracle making changes that would break existing applications.

But they could and have allowed for you to make it mandatory yourself. Perhaps they even make it so in their internal development.

Start with a simple procedure:

create or replace procedure do_it is
begin
dbms_output.put_line('Humbug');
end do_it;
/

If we compile it with all the warnings enabled, we get the "Missing AUTHID clause" warning.

XE> alter session set plsql_warnings='ENABLE:ALL';

XE> alter procedure do_it compile;

SP2-0805: Procedure altered with compilation warnings

XE> select text, attribute from user_errors where name = 'DO_IT';

TEXT ATTRIBUTE

-------------------------------------------------------------------------------- ---------

PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used WARNING
XE> exec do_it

Humbug

But we can use the PLSQL_WARNINGS setting to make enforcement more "Judge Dredd".

XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','ERROR:05018';

Warning: Procedure altered with compilation errors.

XE> select text, attribute from user_errors where name = 'DO_IT';

TEXT ATTRIBUTE

-------------------------------------------------------------------------------- ---------

PLS-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used ERROR

XE> exec do_it

BEGIN do_it; END;

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00905: object GARY.DO_IT is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Rather than just enabling the warning, we can treat it as an error, effectively making the clause mandatory in our environment. Obviously it doesn't make sense for this particular warning to be set at the program unit level. It might make sense for other warnings to be set as errors at the schema or database level, and then individual overrides can be set for exceptional program units.

WARNING, WARNING, DANGER, DANGER !

2011-09-17T19:23:00.000+10:00

Yes, the catch-cry from the "Lost in Space" Robot. But I'm more interested in PL/SQL Warnings.

My new contract is working with an Oracle 11gR2 database. [Aside: It's great using up-to-date versions and with the new 11.2.0.2 version of XE, it's almost like Christmas.]

With new versions come new features and several new PL/SQL compiler warnings have been added to 11gR2. One of those is "PLW-05018: unit <program unit> omitted optional AUTHID clause; default value DEFINER used". This message was described by Steven Feuerstein as "one of those "nuisance" warnings that will pop up on almost every one of our compiles, because so few developers use AUTHID (for invoker rights) and legacy code certainly does not include this clause"

This description was in the answer to one of the Championship playoff questions which asked players to look at some code and give the number of warnings that it would generate. Most players underestimated the answer, partly because the number of warnings is going up with each version.

For most developers this warning is pretty much superfluous. It is pretty rare to be developing DEFINER rights code for applications. However utilities and some of the built-in schemas (eg MDSYS etc) might have elevated privileges and exposing that power through a procedure is something that needs careful consideration.

It is possible to enable and disable specific warnings. Personally I use the fine-grained option available on the command line through ALTER SESSION.

ALTER SESSION SET PLSQL_WARNINGS=
'ENABLE:ALL','DISABLE:07203','DISABLE:05018','DISABLE:06009';

The DBAs can set these using ALTER SYSTEM, or might set up LOGON triggers on specific schemas to set appropriate values. You may also want a different level of warnings in your development databases that your testing and production databases.

It may also be possible to set your preferred options in your IDE. However looking at "SQL Developer", the options in the GUI to enable/disable these warnings are pretty coarse-grained, so I wouldn't recommend this solution.

SQL Developer options for setting PL/SQL Warnings

Another location for defining the PLSQL_WARNINGS level is on the object itself.

XE> create or replace procedure do_it (i_text IN OUT VARCHAR) is
2 begin
3 dbms_output.put_line(i_text);
4 end do_it;
5 /

XE> alter session set plsql_warnings='ENABLE:ALL';
Session altered.
XE> alter procedure do_it compile;
SP2-0805: Procedure altered with compilation warnings
XE> select text from user_errors where name = 'DO_IT';
TEXT
-------------------------------------------------------------------------------
PLW-07203: parameter 'I_TEXT' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used

This simple procedure gives two errors when all warnings are enabled. If I then recompile it with those warnings disabled, it comes out clean.

XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','DISABLE:07203','DISABLE:05018';
Procedure altered.
XE> select text from user_errors where name = 'DO_IT';
no rows selected

If I do a straight compile on that procedure afterwards, it will use the current session settings (which will be inherited from the system settings if not set specifically).

XE> alter procedure do_it compile;
SP2-0805: Procedure altered with compilation warnings
XE> select text from user_errors where name = 'DO_IT';
TEXT
--------------------------------------------------------------------------------
PLW-07203: parameter 'I_TEXT' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used

However you can specify 'REUSE SETTINGS' so that the explicitly define settings are retained.

XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','DISABLE:07203','DISABLE:05018';
Procedure altered.
XE> alter procedure do_it compile reuse settings;
Procedure altered.
XE> select text from user_errors where name = 'DO_IT';
no rows selected

My recommendations would be for the development environment to have at least some warnings enabled, either at the system level or for specific schemas. Then, if individual modules need differing settings, these can be specified and retained (assuming your IDE is up to the job).

For testing environments, having warnings enabled might be a useful step as this may be the first 'independent' compilation of the code. A second set of eyes reviewing the warnings could be a basic 'code review'.

I don't see a benefit in warnings in production environments. But I don't see a real downside either.

Oracle 11gR2 Express Edition is FREE

2011-09-02T11:55:00.003+10:00

I've just wiped my Oracle XE 10g Edition on my home PC and replaced it with a fresh clean 11g Express Edition.

For those who haven't met XE before, it is the free edition of Oracle.

It allows for up to 11Gb of data (up from 4Gb in the old XE) and will use 1 CPU and 1 GB of RAM. The server can have more power, but it won't get used by the database.

You can use it for free (developing, testing, running production applications and even give training using it).

You don't get some of the snazzy Enterprise Edition features. No FLASHBACK DATABASE or FLASHBACK TABLE. No PL/SQL Function Result Cache. No Diagnostic or Tuning packs. Or table compression.

The big 'Standard Edition' feature I can see that is missing is Java in the database.

It comes with Apex 4.0 pre-installed.

Swapping the white shirt for a towel ?

2011-08-31T10:09:00.003+10:00

For almost three years I have been a consultant. Dark suit, white shirt, tie. The official uniform of the 'You can trust us' brigade. [Aside, I was reading Superfreakonomics recently and learned that ties are often germ ridden so the soon we all stop wearing them, the better!]

Next week I start a new job. Back to contract work, with a role doing Application Express development. Yeah ! I do like Apex. I'd previously remarked about the feeling of accomplishment I get when building code. It is nice to see something you've built being used.

I'm contracting through an agency (BSIPeople) who supplied me with a snazzy Sports Bag on sign up. The bag contained the ubiquitous pen, a hat, a drink bottle and a towel. My wife's reaction to the towel was an immediate reference to the Hitchhikers Guide to the Galaxy.

"any man who can hitch the length and breadth of the galaxy, rough it, slum it, struggle against terrible odds, win through, and still knows where his towel is, is clearly a man to be reckoned with"

There's something appropriate about going back to contracting armed with a towel. I'm looking forward to it.

Canadian dogs on drugs

2011-08-28T08:05:00.004+10:00

This is what happens when you get your data model wrong.

I subscribe to RISKS digests, and the latest issue has an interesting post on a poor Canadian bloke who got confused with his dog.

The dog was on some prescription medicine. Dogs can't go into the local pharmacy and buy this stuff themselves, so the owner performed this duty. The drug purchase/pickup got entered into the nationwide recording system against the purchaser's name.

The problem comes when this guy goes into hospital and that purchase shows up on his records. There's no indication that the drugs are actually intended for a different recipient (Cooper the dog) so the doctors didn't know this. Fortunately the owner was conscious and lucid and able to tell the doctor that he wasn't on those drugs and the mixup got sorted.

It is perfectly reasonable to have a pharmacy record that doctors in hospitals can easily refer to so that they can find out what medications a patient is on. My dad takes various pills for things, and i'd be pretty sure he won't remember the individual names. Plus they deal with accidents and cases where the patient may be unconscious or unable to talk.

I suspect they've also started to use the same pharmacy records to pick up people who buy too much of particular drugs. Here in Oz they've gone as far as to stop selling various cold remedies whose ingredients were used in making illegal drugs. So drug purchases for pets get thrown into the same bit bucket.

The problem is that corruption of the data model, of the meaning of the data, has impacted the primary purpose of the system. Keep your data clean folks

Redo log buffer or redo log file ?

2011-08-21T14:29:00.003+10:00

There's a very interesting post on Jonathan Lewis' blog under the unassuming title of REDO.

Oracle treats a transaction as committed when the change and the commit has been written to the redo log BUFFER and doesn't require it to be written to the redo log FILE.

This can cause issues if the instance fails before that log data is flushed to the the file. If the disk write fails BECAUSE the instance fails, it is very unlikely that anything would have had a chance to look at the data in that tiny gap (but even rare risks WILL happen somewhere, sometime). If the disk write fails but the instance continues for a time (which is simulated in Jonathan's post) then the risk gets higher.

I think RAC throws a few more variables in there. Conceivably, a transaction in one instance may have committed data (not written to redo log file) shunted to another instance where it can be amended again and written to that instance's redo log file. If that first instance fails without a persistent copy of that initial transaction, then it can never be re-applied in its entirety.

But then I'm a developer and maybe I'm missing something in the the way instance recoveries are managed in RAC.

So is Oracle 'broken' ?

One problem is the notion of 'durability' is vague. A committed action should last beyond 'system failure', but in a 'cheap' system the CPU and disks can be in the same rack (or the same server) and a failure, such as a fire, could destroy both. Does that mean a transaction shouldn't be viewed as 'committed' until the log file is archived and shipped elsewhere ? Conversely, an expensive 'Data Guard' architecture with a maximum protection mode/level might not be impacted by the failure of a redo log write on a single node.

I suspect this one will run on for a while, and it is worth keeping an eye on Jonathan's post as the experts weigh in.

The casino approach to security

2011-08-19T14:00:00.003+10:00

We can learn a lot from casinos. At least that's one view on application security. The rules have changed from being a case of what is allowed versus what isn't allowed, to something a bit fuzzier.

As an example, on my way to the Sydney Oracle Meetup 'Expert' event I had a phone call from my bank. Apparently my credit card had just been rejected in a Australia Post office as a suspect transaction. Since I'd been on the train it wasn't me. The card was in my wallet too, so it hadn't been nicked. The card had been skimmed somewhere.

The suspect transaction wasn't above my credit limit. It's not like I've never used it in an Australia Post either (though never for the amount involved). It may have been an odd location, but I hadn't had any rejections on a recent holiday. In short, it probably wasn't any single factor that tripped the security wire, rather a whole set of criteria that marked it as suspect. Okay, maybe it came down to a suspicious employee of Australia Post, but this is a database blog not an episode of Law and Order.

There's a careful balancing act the bank has to perform (and it did it very well in this case). It has to, in the words of the article, "let the people play". It can't stop every credit card transaction, but has to look for rogue behavior. Its not something I've directly worked in, but I've done fuzzy algorithms for data matching. You add some points for one fact, a few more for another and at the end you get a resulting numeric value for 'how likely'.

That requires lots of logging of activity, and there's an increasing requirement to analyse these logs, sometimes in real time (or near real time) for those behavioral factors. Hadoop seems to get some mentions for this form of analysis, though I'm sure there are other solutions in the NoSQL world. I'm sure VoltDB would offer themselves up here too. My gut feel for this style of application would be some form of distributed data store.

There are rumors of an Oracle Hadoop Appliance which may or not be confirmed in a few weeks at OOW. That said, there have long been rumors of an Oracle Games Console, and I'm thinking of applying for a trademark for ExaPhone before Larry gets in there. Oracle do have a pedigree of dealing with vast amounts of data (anyone mention CERN ?) so it will be interesting to see how they play out in this area.

An evening with some Oracle Experts

2011-08-19T13:02:00.003+10:00

This week has been InSync11 week in Sydney. Actually it was Tuesday and Wednesday (though Tom Kyte stuck around afterwards for a 'Day of Real World Performance'). The bad news is that my dollars didn't stretch to any of those events.

The good news is that it did stretch to the (free) Sydney Oracle Meetup event mentioned by a couple of our guest experts, Richard Foote and Tim Hall. We were able to tempt the experts away to a local RSL club with the promise of alcohol and Oracle users.

Each of the experts got to pick a question to answer from a carefully selected list.

Tim picked out one on the worth of certifications. He's got lots and doesn't think a lot of them. Or rather, as a debate with Marcelle drew out, believes they are limited in the practical benefit of them in the workplace. They do work in exposing areas of new functionality, so that you can think about them. Marcelle is of the opinion that learning to deal with users, developers and managers is more important than learning syntax.

Richard waxed forth on the importance of knowing about indexes. Apparently he doesn't actually spend his working days poring over block dumps and counting blevels. He actually does a lot of normal DBA work and became synonymous with indexes when his Rebuilding the Truth paper went viral, plus a bet about how long he could blog about indexes (and David Bowie).

Marcelle chose the question about dealing with people rejecting an expert's advice. Some of this was drawn from the debate over her paper on Foreign Keys. Given the rise of the NoSQL / distributed database concepts, and the 'eventual consistency' model, the paper raised some valid points. Equally the advice is irrelevant to many people who are not faced with those problems.

Tom talked about the fear of presenting. Apparently this is best resolved by doing it lots. [Tony Jambu won the autographed copy of Tom's book. It is impressive when you have an Oracle ACE in the audience.]

Chris Muir talked about his crush on Lucas Jellema when answering about his favorite expert. Apparently he is very enthusiastic and it can be contagious. [He didn't actually say "crush", but I find it fun to use since Kellyn talked of her DBA Crushes.]

Connor talked about user groups and how it is fun to be around people who think this database stuff is fun. He also complemented us on the turnout (around 60 - our biggest event so far). Not sure who got the autographed copy of his book (or rather the one he did a chapter for) but I'm envious.

Craig picked up a question on social media. The upshot seemed to be that having a social media presence doesn't make you an expert, but it can help your expertise get noticed. A lot of our 'experts' didn't feel quite comfortable under that label. They pointed out that there are a lot of 'quiet experts' out there with excellent experience and knowledge. They just happen to go around talking about it.

This also came across in Guy's response to how long it takes to become an expert. It isn't really about time. If you do something a lot, you will become good at it, but it reaches a point where it doesn't add any more. Furthermore, with the pace of change in IT, after a couple of years it actually becomes less relevant as it is out-dated.

There was also some final discussion on balancing Oracle with a 'non-oracle' life , such as family. Apparently it helps if you don't watch Masterchef. It doesn't look easy when lots of travel is required though.

Is it just me

2011-07-25T19:40:00.001+10:00

I'm thinking about starting a petition against the misuse of Powerpoint. Unusually though, I'm not campaigning against 'Death by Powerpoint' problem (scourge though it is). My campaign is "If it isn't a presentation, it isn't a Powerpoint'.

In a meeting with a colleague last week, he said that he'd put together a few slides for a client meeting next week. I mentioned that previous meetings had been in a room without a projector, so it wasn't likely to be 'presentation-friendly'. I added that he should just use Word. Apparently though he wasn't expecting to present the material, just use Powerpoint to produce a document.

The product wasn't going to be a detailed report. It would be a few observations, suggestions and recommendations. Maybe with a couple of diagrams. I suspect it wouldn't be any harder to produce such a document in Powerpoint. It may even be easier. But it gives me the squirms.

Powerpoint is (or should be) are creating slides for a presentation. They don't have to include pretty animations and transitions and there's nothing wrong with being able to print out the presentation for reference. Using it to build something intended as a printed document seems wrong though, like storing a date in a string.

It is that form of misuse that ensures Microsoft have to allow the use of 8 and 10pt fonts, and default to new slides to streams of bullet points. Things that kill 'real' presentations. Frankly, it turns Powerpoint into a mule. [I'm not sure whether Apple's Keynote suffers the same problem.]

And we shouldn't judge a document on the number of pages it has. If you can fit it all on a single page, there's nothing wrong with that. Blowing up the font size to 'Super Large', only fitting three or four sentences to a page and then delivering it in ten pages doesn't help. And it either wastes paper, or you end up printing it two-pages per side of paper and just making it look ugly.

So save Powerpoint for when you expect to be clicking through the slides,

PS.

Martin Widlake and Tim Hall have both blogged about Powerpoint recently. Martin has been developing his image skills. Apparently gradients fills are more natural. Tim points out that the only time you see a floppy disk these days is on a toolbar icon.

Next week I've got a one-day course on delivering successful presentations. For that I've got to prepare a two minute presentation, so I'll see if I can manage a '2 Minute Application Express'. Will practice that over the next few days, and maybe post it as a webcast.

Turning it around

2011-07-20T19:20:00.002+10:00

I wanted to share an insight into the REVERSE operation inspired by a stackoverflow question.

The question was about using the built-in REVERSE function to find the characters at the end of a string. Sticking the REVERSE keywork in the CREATE INDEX statement wasn't doing the job. Adam Musch responded to say that REVERSE should be used solely to avoid contention and that the optimizer doesn't do range scans on reverse key indexes.

Markus Winand suggested a manual function-based index rather than just using the REVERSE keyword in the CREATE INDEX statement. While that might work sometimes, it isn't a solution I'd recommend.

With a quick experiment you can see one reason why.

select 'é', dump('é'), dump(reverse('é')) from dual;

'É'	DUMP('É')	DUMP(REVERSE('É'))
é	Typ=96 Len=2: 195,169	Typ=96 Len=2: 169,195

A multi-byte character has the BYTES reversed. As such, the character is not actually preserved and cannot be matched. LIKE, BETWEEN and regular expressions matches against another string (column or literal) become meaningless. The only thing that can be done is to reverse another string and do a direct byte-to-byte comparison.

That means you might be okay if you do a

SELECT * FROM table

WHERE reverse(column) LIKE reverse(:var)||'%'

Unless you can come up with a failure case....

More on Checksumming

2011-06-30T19:55:00.089+10:00

Prompted by a comment I realise that I need to put some more flesh on Checksumming.

WHY ?

A good use case is regression testing. A decent regression test will include a script to set up data so you can execute your task (preferably in an automated manner) and check your actual results match your expected results. That is pretty easy if the expected result is a value returned by a function. When it is a data change, it gets a bit trickier.

You can set up your expected results in a table (or a set of tables matching your main schema), run the tests and check the actual result data matches the expected result data. That can be as simple as

exec comp_tables('TEST_ACTUAL','TEST_EXPECTED');

Depending on your testing mechanisms, that can raise an exception if it fails, return an error code or record the results in a table. My example code just dumps it out to DBMS_OUTPUT. Also, a regression test will probably have expected results in several tables, so you'll need multiple comparisons.

Just to complicate things, often the tables will contain sequence generated ids or use the current date as a 'created / modified date'. So you might want to exclude some columns (or rows) from comparison.

begin

comp_tables(

'SELECT val_char, val_number from TEST_ACTUAL WHERE val_char=''ABC''',

'SELECT val_char, val_number from TEST_EXPECTED WHERE val_char=''ABC''');

end;

HOW ?

I've uploaded my code to the SydOracle CodeSpace location as Comp_Tables.sql

It works in several steps.

1. Take the entered query (eg 'SELECT 1 a, 2 b FROM DUAL') and determine the columns.

2. Create a wrapper query around that. That would give

SELECT ora_hash(a) a, ora_hash(b) b

FROM (SELECT 1 a, 2 b FROM DUAL)

The hashes turn all the values (of the main datatypes) into numbers, which makes them easy to aggregate, and comparing aggregated values is easy. The hashing also practically guarantees no collisions. That would give a result set like

         A             B 

2342552567    2064090006

3. Create another wrapper to mock up a hash for each row.

SELECT mod(a,100) * mod(b,100) row_hash, h.* FROM

(SELECT ora_hash(a) a, ora_hash(b) b

FROM (SELECT 1 a, 2 b FROM DUAL)) h

Paraphrasing Eric Morecombe, it is fine having all the right columns, but you also want them in all the right order. These results sets will show the same total hashes for columns 'A' and 'B' as the contents of those columns are identical. if you have one row of (1,'A') and a second of (2,'B'), a 'column-only' match would quite happily accept (1,'B') / (2,'A') since all the column values are identical.

By creating another column based on all the values for the entire row, we get a summary of the row. We can't simply add the hashes from the columns together as the sum of all the 'A's plus the sum of all the 'B's is mathematically the same as the sum of the As+Bs. In short, we'd lose the checksumming effect as we aggregate the row values. So I've used multiplication with a MOD so the numbers are unlikely to get too big.

ROW_HASH           A           B 

     402  2342552567  2064090006

4. Finally we aggregate the values for all the rows. I've used SUM, though AVG would work just as well (and would give smaller, more manageable values for larger datasets). That gives an SQL like

SELECT SUM(row_hash),SUM(a),SUM(b)
FROM (SELECT mod(a,100) * mod(b,100) row_hash, h.* 
      FROM(SELECT ora_hash(a) a, ora_hash(b) b 
           FROM (SELECT rownum a, 2 b 

                 FROM DUAL CONNECT BY LEVEL < 5)) h) 

(I've changed the SQL here to give multiple rows, so you can see the effect of the sum).

ROW_HASH             A             B
     402   2342552567   2064090006
      36   2064090006   2064090006
     354   2706503459   2064090006

186 3217185531 2064090006

========================================

     978   10330331563    8256360024 

5. That gives us a one record encapsulation of an entire table (or subset of a table).

If we do that once for the 'actual' and once for the 'expected' we can compare those two records and see where they match.

So if we compare the previous SQL with a similar one with a different value from 'B'

SELECT SUM(row_hash),SUM(a),SUM(b)
FROM (SELECT mod(a,100) * mod(b,100) row_hash, h.*
FROM(SELECT ora_hash(a) a, ora_hash(b) b
FROM (SELECT rownum a, 3 b

FROM DUAL CONNECT BY LEVEL < 5)) h)

ROW_HASH            A            B

    9617  10330331563  10826013836

In the above example, the final row shows us that column 'A' matches but 'B' does not. We would then investigate the code to find out why 'B' wasn't always being derived as expected.

Caveats

While you could just store the aggregate hash of the expected results, that would prevent you drilling down to investigate issues, so I don't recommend it.

Also, since the code accepts SQLs, you can do exotic things like joins, functions and analytics. Again, I'd recommend against it on the 'Keep It Simple' principle.

And if you use weird column names (ie mixed or lower case with strange characters that require quoted identifiers) you are on your own.

Check summing a table row

2011-06-28T17:01:00.001+10:00

Oracle has a built-in package for optimistic locking called OWA_OPT_LOCK. This can be used to generate a checksum for any row like this:

select owa_opt_lock.checksum('SCOTT','EMP',ROWID)
from empwhere empno = 123;

This nugget fell into the bucket of "Things I discovered, but couldn't actually find when I wanted to use it". Then it cropped up as a Stackoverflow answer, so I'm putting it in here so I can find it.

Apparently it is a very simple checksum involving summing up the binary values and inverting it. It isn't a sophisticated hash designed to give low chances of collisions. The code isn't wrapped, so you can have a peek. It seems to do a simple concatenation of the values (implicitly converted to strings), so definitely isn't bulletproof or super-secure. Honestly, it wouldn't have done the job I was attempting when I couldn't find it.

If you want a simple sanity check that a small table on one database has the same contents as it does on another, then it will generally work okay. But for each call (and therefore each row, if you want it for an entire table's contents), it will do queries against all_tab_columns. So it won't be quick.

Anything big, and I'd hack up my own select based on

select 'sum(ora_hash('||column_name||')),'
from all_tab_columns
where table_name = '...'

which would also say WHICH columns had differences.

The exceptional case of the backwards null

2011-06-22T19:28:00.000+10:00

Yes, Virginia, there is a unique NULL. The trick is to go backwards.

At my son's school, children in the younger years are buddied up with a child from an older year. Using this as example, I'm going to use the database to ensure that no child is buddied up to more than one other child. If we have an odd number of children, an unlucky one will have no buddy, but if our uniqueness constraint can ensure that there can only be one null, we can make sure we never have more than one 'un-buddied' child.

CREATE TABLE child

(child_id NUMBER NOT NULL,

child_name VARCHAR2(20) NOT NULL,

buddy_id NUMBER,

CONSTRAINT child_pk PRIMARY KEY (child_id)

)

CREATE UNIQUE INDEX child_uk ON child (buddy_id);

insert into child (child_id, child_name)

values (1,'Adam');

insert into child (child_id, child_name)

values (2,'Bill');

insert into child (child_id, child_name)

values (3,'Chris');

update child set buddy_id = 3 where child_id != 3;

ERROR at line 1:

ORA-00001: unique constraint (PERFORM.CHILD_UK) violated

The UPDATE fails because of the uniqueness constraint. But it didn't object to multiple NULL values on the INSERTs.

Reading the documentation, we find out that DESC indexes will treat multiple NULLs as duplicates and so will not allow them.

Let's give it a try

TRUNCATE TABLE child;

DROP INDEX child_uk;

CREATE UNIQUE INDEX child_uk ON child (buddy_id DESC);

insert into child (child_id, child_name)

values (1,'Adam');

insert into child (child_id, child_name)

values (2,'Bill');

ERROR at line 1:

ORA-00001: unique constraint (PERFORM.CHILD_UK) violated

Yup, documented behavious matches actual behaviour.

Why ? Well, the DESC index is actually a function-based index using SYS_OP_DESCEND. That takes every byte of the input and subtracts its ascii value from 255. Then it sticks an extra byte of value 255 on the end (so the descending order has 'ABC' coming after 'ABCD'). The NULL gets converted to a single byte with the value 0.

At least in 10g, the optimizer isn't smart enough to use the index for a 'WHERE buddy_id IS NULL' predicate, but will for a one with a predicate of SYS_OP_DESCEND(buddy_id) =SYS_OP_DESCEND(null)

PS. I'm not enforcing referential integrity on the buddy_id, so children may end up with imaginary friends. I'm also not enforcing that if Fred's buddy is Barney, then Barney's buddy is Fred. Those are left as an exercise for the reader.

SQL Injection and the variable IN list

2011-06-20T22:40:00.000+10:00

Pete Finnegan tweeted a link from a guy who discovered an SQL injection vulnerability on the CNN.com website. Actually he discovered it over a year ago, and reported it. And still no-one has got around to fixing it yet.

It is an Oracle database (apparently 9.2.0.4 - so they're not keeping up with patches either). And not only is there an SQL injection vulnerability, but if the SQL is invalid, it throws the whole statement out to the web-page as part of the error 'handling'.

The vulnerability is in a requirement which gets regular questions on stackoverflow. Specifically, given a string with a list of ids (such as '1,2,3,4'), how do I put that in an 'IN' list.

The problem is thinking in specifics not abstracts. You need to make the mental jump and realize that you are have IN (:variable) not IN (:var1,:var2,:v3).

So here's my recommendation for this. Firstly a function that will turn that CSV value into a collection. Here I use the built-in SYS.DBMS_DEBUG_VC2COLL, though it is tidier to create your own collection type.

create or replace function csv_to_list (i_list in varchar2) return sys.dbms_debug_vc2coll is

  v_list varchar2(4000) := i_list;

  v_ind number;

  t_list sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll ();

begin

  v_ind := instr(v_list,',');

  while v_ind > 0 loop

    t_list.extend;

    t_list(t_list.count) := substr(v_list,1,v_ind-1);

    v_list := substr(v_list,v_ind + 1);

    v_ind := instr(v_list,',');

  end loop;

  if v_list is not null then

    t_list.extend;

    t_list(t_list.count) := v_list;

  end if;

  return t_list; 

end;

/

Next, you take that collection, and turn it into a dataset with the TABLE operator. Finally, you use that as the source for a single-column subquery.

select table_name from user_tables 

where blocks in 

  (select column_value from table(csv_to_list('1,2,3,4,5')));

Then you can BIND the variable rather than concatenating it into a dynamically executed statement.

Locked down in Oracle Amazon RDS

2011-06-03T09:10:00.000+10:00

If you dig down into the Oracle on Amazon RDS, you can find the some security restrictions. These are things that Amazon won't let you do with your database and include the following:

· Alter database

· Alter system

· Create any directory

· Drop any directory

· Grant any object privilege

· grant any privilege

· Grant any role

· Restricted session

BlueGecko notes a couple of others:

· Datapump / imp / exp FULL_DATABASE privileges

· Alter user, revoke or any DDL on SYS, SYSTEM or RDSADMIN users

· Drop tablespace RDSADMIN

· Create public synonyms for objects belonging to SYS, SYSTEM or RDSADMIN

Most of those are pretty obviously 'dangerous'. These are the items that, in an organisation with a hierarchy of DBAs, would only be available to the Top Gun. And in this environment, Amazon holds that position.

The 'CREATE ANY DIRECTORY' is one where you need to think evil before you work out why it is so dangerous. So I shall adopt the persona of the villainous toad, 'Baron Silas Greenback', sit back in my black leather chair and stroke my furry white pet while I explain the plot. Then you can escape my henchmen and save the day.

The plan is cunning in its simplicity. Simply create an Oracle directory that points to the OS directory containing the Oracle data files. Then I could use UTL_FILE to read and even update those datafiles directly, without going through the protective SQL layer. The operation through UTL_FILE is, as far as the operating system is concerned, indistinguishable from an official, controlled change through an INSERT statement.

In fact any of the Oracle database files could be accessed this way. Control files, redo logs, audit trails... In short, directory privileges are REALLY nasty things.

The www of sydoracle.com

2011-06-02T19:30:00.001+10:00

When I moved my blog to sydoracle.com, I intentionally put it under a "blog" subdomain rather than at www.sydoracle.com. The idea was that I'd eventually get around to building a conventional website. By that I mean that I am in awe of Tim Hall's oracle-base.com (content, structure, etc) and have delusions of grandeur about making something similar.

Apparently this subdomain split isn't great for search engine optimization as it treats them as different sites. I guess search engines don't want to draw assumptions for mastercheesemakerbook.wordpress.com from jonathanlewis.wordpress.com. So don't do this.

For over a year, it was pretty much Lorem Ipsum, with tumbleweeds rolling about, and the sounds of far-off Whipporwills in the hills.

Partly inspired by playing with the /seo and /seourl search tags in blekko, I spent some time thinking about Search Engine Optimization, and decided to get my www subdomain in some sort of order. It is all built using the standard Google Sites (which you can think of as a very simple, and free, Content Management System).

Most of it is pretty standard web site stuff. Bio, a couple of pages of links, some articles (mostly to see if I could write something with the aim of search results, rather than just as a brain dump) and copies of a few presentations I've made.

Then I tried to stretch things to some of the other Google gems.

Upcoming Events leverages Google Calendar, and currently shows up the Sydney Oracle Meetups calendar. I may merge in other calendars later.

The 'Contact Me' part is a form that feeds off to a Google Docs spreadsheet and notifies my GMail account.

The 'Book Me' part is a Google Apps gadget for "You Can Book Me", because I wanted to link in something from the Google Apps Marketplace.

Then there is OuterJoin.sydoracle.com which runs off Google Moderator. It's sort of a Q&A, crowd vote thingy. I'm not particularly keen on its fit for use. Apparently stuff there doesn't come up in search, you've got no control over formatting (which is a pain for anything code related) and the layout is pretty fixed. But if you've got any questions, feel free to sling them in.

I've also been trying out writing some Google Apps Engine code in Java. It's cool. I'm not sure whether it is the tutorial or the fact that I haven't had to try to install web servers, app servers and frameworks, but I've found it easier to focus on the coding process there than my previous attacks on the subject. Now if someone could write a decent Forum / FAQ / Q&A system in that.....

I fully intend to add some of my standard code packages to the site at some point. I've never been completely happy with large chunks of code in my blog, and this would be a tidier solution.

I'm open to other suggestions.

Licensing can be fun....NOT

2011-06-01T19:30:00.002+10:00

Some notes to help me work out what I am, and more importantly, am NOT allowed to use.

Check your edition. V$VERSION will tell you if you are on Standard, Enterprise, Express or Personal Editions.

Enterprise Edition can use Diagnostics and Tuning Features if the option is licensed. Personal Edition includes "all of the options that are available with Enterprise Edition", except RAC. Shame it is Windows only. Standard and Express are definite No-No's

For 11gR2, check the parameter CONTROL_MANAGEMENT_PACK_ACCESS.

A canny DBA will have this set to NONE if the Diagnostics and Tuning pack are not licensed. The 11gR2 Beta for XE has this value set to NONE so maybe they'll do that as a default in future.

If the package dbms_awr is installed, then there's a good chance it was done so in order to disable AWR data gathering.
Check the name, detected_usages and currently_used columns from DBA_FEATURE_USAGE_STATISTICS

If it isn't disallowed, disabled and has been used then there's a reasonable chance it is licensed, but check with the DBA anyway. But at least you won't annoy them when it is obviously not allowed.

What is off limits (based on the 11th revision of 11gR2 licensing document - E10594-11) includes :

Packages : DBMS_WORKLOAD_REPOSITORY, DBMS_ADDM, DBMS_ADVISOR (for ADDM, Tuning Advisor or Access Advisor), DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT, DBMS_SQLTUNE, DBMS_XPLAN.DISPLAY_AWR (which uses DBA_HIST views)
V$ Views : V$ACTIVE_SESSION_HISTORY, V$SQL_MONITOR, V$SQL_PLAN_MONITOR
Other views: DBA_ADDM_, DBA_HIST_ (except DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT) DBA_STREAMS_TP_PATH_BOTTLENECK and DBA_STREAMS_TP_COMPONENT_STAT (some), DBA_ADVISOR_ (some)
Reports : awrrpt.sql, awrrpti.sql, awrgrpt.sql, awrgrpti.sql, awrgdrpt.sql, awrgdrpi.sql, addmrpt.sql, addmrpti.sql, ashrpt.sql, ashrpti.sql, awrddrpt.sql, awrddrpi.sql, awrsqrpi.sql, awrsqrpt.sql, awrextr.sql, awrload.sql, awrinfo.sql, spawrrac.sql

Generally you are licensed for "internal business operation". At the recent Sydney Oracle Meetup, Mogens Nørgaard pointed out that you aren't allowed to use your databases to train people outside your company. One exception is Express Edition which expressly (sorry) states "(c) you may use the programs to provide third party demonstrations and training; "

So if anyone around Sydney wants some training on Oracle SQL, PL/SQL or similar, I'm open to offers :) But you may want to wait until the XE 11gR2 goes live.

Bullet proofing entire record inserts/updates

2011-05-12T17:30:00.001+10:00

There's a handy feature in PL/SQL that lets you insert an entire record into a table without specifying each individual field in the record. Here's an example.

create table wide_test
(id number,
col_a varchar2(10), col_b varchar2(10), col_c varchar2(10));

declare
cursor c_wide is
select *
from wide_test;
v_rec c_wide%rowtype;
begin
v_rec.id := 1;
v_rec.col_a := 'A';
v_rec.col_b := 'B';
v_rec.col_c := 'C';
--
insert into wide_test
values v_rec;
end;
/

select * from wide_test;

But if you give that to a code reviewer, they may tell you that using SELECT * is bad practice. What happens when a new column is added to the table ? So he makes you recode it.

declare

cursor c_wide is

select id, col_a, col_b, col_c

from wide_test;

v_rec c_wide%rowtype;

begin

v_rec.id := 2;

v_rec.col_a := 'A';

v_rec.col_b := 'B';

v_rec.col_c := 'C';

insert into wide_test

values v_rec;

end;

select * from wide_test;

Everyone is happy....until a new column is added to the table.

ALTER TABLE wide_test ADD col_d VARCHAR2(10) DEFAULT 'D' NOT NULL;

declare

cursor c_wide is

select id, col_a, col_b, col_c

from wide_test;

v_rec c_wide%rowtype;

begin

v_rec.id := 3;

v_rec.col_a := 'A';

v_rec.col_b := 'B';

v_rec.col_c := 'C';

insert into wide_test

values v_rec;

end;

insert into wide_test

ERROR at line 12:

ORA-06550: line 12, column 15:

PL/SQL: ORA-00947: not enough values

That didn't quire go as planned. You get the same error if you try

insert into wide_test (id, col_a, col_b, col_c)

values v_rec;

Perhaps the SELECT * was the right way to go ?

declare

cursor c_wide is

select *

from wide_test;

v_rec c_wide%rowtype;

begin

v_rec.id := 3;

v_rec.col_a := 'A';

v_rec.col_b := 'B';

v_rec.col_c := 'C';

insert into wide_test

values v_rec;

end;

declare

ERROR at line 1:

ORA-01400: cannot insert NULL into ("PERFORM"."WIDE_TEST"."COL_D")

Nope ! The "col_b" in v_rec isn't set so remains NULL, but the database column is NOT NULL. Because a value is specified in the INSERT the default doesn't kick in.

Happily there is a solution:

declare

cursor c_wide is

select id, col_a, col_b, col_c

from wide_test;

v_rec c_wide%rowtype;

begin

v_rec.id := 3;

v_rec.col_a := 'A';

v_rec.col_b := 'B';

v_rec.col_c := 'C';

insert into

(select id, col_a, col_b, col_c

from wide_test)

values v_rec;

end;

select * from wide_test;

We use an inline view as the target of the insert. Since the order of the columns is specified in the inline view, this will continue to work even if someone re-arranges the columns in the underlying table. A regular, permanent, view in the database works just as well.

This is called the Insert Statement Extension and there's a similar facility for UPDATE

DECLARE

cursor c_wide is

select col_a, col_b, col_c

from wide_test;

v_rec c_wide%rowtype;

BEGIN

v_rec.col_a := 'A1';

v_rec.col_b := 'B1';

v_rec.col_c := 'C1';

UPDATE

(SELECT col_a, col_b, col_c

FROM wide_test)

SET ROW = v_rec;

END;

Encouraging women in information technology

2011-05-10T17:30:00.008+10:00

I participated in a twitter dialogue with @datachick (Karen Lopez) last week on the #WIT (women in technology or women in information technology ?) hash tag.

I don't think there'd be much argument that women are under-represented in IT, and that a more balanced split would benefit the whole industry.

My question was what can us blokes do to encourage more women into the industry (or to stay in the industry). There were a couple of helpful suggestions. Karen's first point was to encourage girls into STEM courses (Science, Technology, Engineering and Maths). No problem there. We could also do with a few more teachers specializing in those areas, but that's a separate topic.

Another point was that women may not push their opinion into a discussion, and it could be helpful to actually ask for their input. Or ask for their help if you are stuck. They know stuff. I follow several women tweeters/bloggers, including DBAKevlar, datachick, Erin Stellato, Lisa Dobson, Gwen Shapira, Mary McNeely, Boneist.... Not because they are women, but because they contribute interesting content.

Karen then recommended challenging any ant-diversity practices and @zippy1981 raised the matter of introversion. There are a lot of introverts in IT. Sitting in a corner, coding away. I suspect there will be fewer in years to come, at least in development. The increasing pace of change, and a closer dependence of the business on IT, means less scope for solitary activity. Again, another topic.

But in the meantime, introverts are not going to be the ones giving talks at schools on career day. Or challenging the alpha-males in the executive positions who might skimp on diversity-friendly practices.

True Story: One place I worked had the ladies toilet larger on each floor than the gents (because cubicles take up more room than urinals), except on one floor. The large skew in the IT section meant that the gents got extra space. This sometimes confused women visiting the floor....but never more than once. There's a lot of gray area between recognising an existing imbalance, reinforcing it and falling back to the "it's an industry norm" excuse.

I don't have any solutions. It is just something to think about.

Oh, and the recent XKCD is sort of appropriate.

This is a reconstructed / edited extract of the twitter conversation:

syd_oracle: How can we (men) encourage without being patronising ? #WIT

datachick: Best? Talk to young people about your career and the opportunities it provides. Encourange them to tak STEM courses.

MelikaNoKaOi: I see a lot of women in IT who are afraid to offer their ideas, so ask them for their ideas directly :) #WIT

retracement: Simple. Actually talking to women in IT (at minimum) as our equals, which quite frankly shouldn't be that hard to do. #WIT

retracement:Men are generally useless even talking to other men, never mind women so that's probably the real issue. #WIT

datachick: People can help by challenging immediately anyone who contributes to anti-diversity practices.

zippy1981: So your asking a bunch of introverts to challenge the alphas, who became successful by choosing not to be the alphas

datachick: Are you saying that Women are introverts? Not my experience...

datachick: I want to solve the problem of girls tinking that IT is a solitary job. None of my work has been.

datachick: Or that the only IT job is programming. I still fight that one, though, with adults.

zippy1981:Well for some it is an ideal. Don't get me wrong I appreciate good PMs, despise the mediocore ones, and respect great admins 1/2

zippy1981:so head dwn pgrming is not the only path, but if its your ideal path your not gonna push your daughter to be a sales engineer 2/2

datachick: Generally young girls are attracted to more collaborative jobs, which is why I want them to know IT has those.

zippy1981:I'm all for them taking those jobs so I can sit in the corner. Give me a fast Computer and a bug report to steer her by.

zippy1981:I'm saying that MIT that would appreciate more #WIT would encourage them to follow the same path that got them there.

zippy1981:I guess what I'm saying is I'm going to encourage a person who wants to be in my field to follow what worked for me.

datachick:That makes sense. I would do the same (and I spent only a trivial amount of time as a dev.)

Crossover

2011-05-10T15:43:00.002+10:00

When should I retweet someting ?

I follow a lot of Oracle peeps, including the big names such as Cary Milsap. I've got a few followers too, and the vast majority of them are also Oracle peeps. If Cary tweets something then there's not much point in me re-tweeting it because very few of my followers wouldn't have seen Cary's original.

I also follow a few antipodean tweeters, and a couple in the security area and some SQL Server ones. The same pattern applies to blogs I follow. I've got Google Reader set up so that if I share something there, it comes out as a tweet.

I'm more likely to share/tweet/retweet something from the 'non Oracle' field as I think it will reach a different audience. Crossover is important. Mary McNeely went to talk Oracle to some Unix people.

I can't see myself as a world-class Oracle guru. But I am one of a small number of Australian Oracle bloggers. It is the crossover aspects which allows us to contribute into the community

From CRUD to AAAR - Pirates of Tahiti

2011-05-07T14:39:00.000+10:00

When I wore a younger man's clothes, everything was cruddy. CREATE, READ, UPDATE and DELETE. Which equated to 'INSERT, SELECT, UPDATE and DELETE' in SQL terms. We didn't have MERGE in them days.

DELETE was the first to go. It was too hard to get back something you'd deleted, and something ALWAYS got deleted by mistake. We first moved to a 'soft delete', which meant setting a flag on the record indicating it should be (generally) ignored. After we ended up with tables that were 90% 'deleted' records, we went a step further. That's my first 'A' ; Archiving - moving the record into a separate table where it was easier to ignore.

UPDATE also gives problems. When your system was used by three men and a dog, you didn't run into too many concurrency conflicts. They generally worked on different records and only very occasionally had to wait on another user's locks. As you start to scale up, you run into more conflicts. Everyone would be trying to update the daily balance at the same time. You've all seen the effect at traffic lights. You reach the load point where more people are being added to the traffic queue than are being let through on each green light. It only sorts itself out when the load drops, and try telling sales that you need fewer customers.

So rather than UPDATE, you do INSERT or CREATE or, as I'm calling it to fit the pirate theme, APPEND. My second 'A'. Say you have 100 iPads in stock, and have them on a single stock record. Every sale you make, you need to update that record to reduce the available quantity. Only one person can update it at a time and everyone else has to wait. So rather than update the record, you append a transaction record saying how many you sold. I can insert a record and you can insert a record and neither of us has to wait for the other to finish. [Oracle can actually be smart so that when two people are inserting records at the same time, they actually go into different database blocks.]

Some of the NoSQL databases go further. Say I've got a profile record giving my account id, name and email address and I want to update the email address. Rather than do an update of the record, I actually append a new record with a later timestamp or version number and the new email address. This gives me a better audit history, and an easier replication mechanism between distributed copies of the data (because the timestamp settles arguments about the order in which things happened).

The third 'A' is for Aggregation. I take my set of sales transaction and total them up to see how many I sold. I have another set of data indicating how many I've bought. Eventually they'll balance out, even if I have to append some '-1' records to my sales and give refunds to a disappointed fanboi or two. Or with my profile, I take the details from the record with the highest version. Eventually the older ones may be archived.

At the end we will always have 'R' for reading. There's not much point in collecting data that no-one ever reads.

Anyway, that's why there are so many pirates involved in databases these days. I don't have any explanation for any cowboys in the industry.

PS. tahiti.oracle.com is Oracle's Document Search engine. I don't think it was very big on pirates, but if I had mentioned the Caribbean I probably would have ended up with Disney's lawyers.

Why we learn maths

2011-05-05T20:15:00.002+10:00

I've got a parent table of movies and child table of ratings where people give the film a score on a scale of 1 to 10. In my display, I want to display an average rating for the film.

I don't want to calculate the average of scores of rows for each display. With a large ratio of 'child' to 'parent' that could be very expensive. I want to denormalise but only just enough.

Say "Thor" has a current average score of 7 and a new review adds a rating of 2, what does that do to my average ? You can't tell from those figures, because an average is a ratio. Whenever you derive one value from multiple values, you are going to lose some functionality.

What I should do is take a step back and store on my movie table a TOTAL of people's rating, plus a COUNT of the number of ratings. Now, rather than an average of 7, I've got 10 reviews with a total rating of 70. Add in my new row and I've got 11 reviews with a total of 72 (giving an average of a bit more than 6.5). I don't need to re-read all those individual rows to recalculate the average.

This is why we learn maths !

The next issue will be contention. Fifty people coming out of a screening, all adding their ratings at the same time. Scary ? INSERTs are good at concurrency. There might be a few brief latches/mutex's over sequences and index blocks, but no actual locking problems. Updates are a different matter, and if we get fifty people tring to update the total/count values at the same time it will get ugly.

This is where queues come in. Have those 50 inserted rows join a queue and a single process dequeue them to calculate and update the movie's total and count.

This is why we learn computer science. Of course similar things happen in the real world too, and queuing theory is....a part of maths.

The queue / de-queue model may show inconsistencies while waiting for ratings to be processed off the queue. Is 'Eventually Consistent' good enough ? If you need transactional consistency then leave the ratings in a 'pending' state until after they've been de-queued. If you need immediacy AND transactional consistency, then you'll end up with a potential bottleneck. Recognising immovable objects is a vital lesson to learn too.

Inspired by a stackoverflow question.

Worried about gamification and gender bias

2011-05-03T07:30:00.002+10:00

One fun part about blogging is that you don't need to be an expert in everything, you don't need to know all the answers, and sometimes you can throw out an opinion or questions and see what reaction you get. This is one of those posts.

I play Steven Feuerstein's PL/SQL Challenge pretty much every day. I see it as a fun way to keep my knowledge base exercised. I also follow theappslab which has covered gamification for a while, and I recognize the Challenge as applying gamification to learning, with its leaderboard and achievements (for both ranking and participation), plus prizes at the end of the Quarterly Quest.

Then I turn around and see that "Female IT Staff Are Still Getting Paid Less Then Males", also reported here.I think salary is too simplistic a measure. I had lunch with one Java programmer a couple of weeks back andwe did discuss general pay and conditions between her (public sector) job and my one as a consultant. I may get paid more, but I have a longer working week and less control about where I'm working (plus I've been in the business a bit longer - though there's a point where years don't usefully equate to experience).

I follow @datachick who often tweets about Women in technology (and NASA and barbie and a whole bunch of stuff). And I look around a lot of the places I work and see more blokes than women. I do think we, as an industry, need to do something to encourage more equal participation.

I wonder whether gamification offers any opportunities for that encouragement, or whether it may actually make things worse. I've dug around a bit but haven't really found much useful data on gender bias here. Nick Yee has looked at EverQuest and found that female players were more interested in the social aspects rather than goal-focussed. The vast majority of players are men which may indicate that the population was weighted towards a particular type of personality more common amongst men, but perhaps the same applies to the technology field.

I don't want to pick on the PL/SQL Challenge. I enjoy it. I also like the elements of gaming in StackOverflow with its achievements and "powers" as points levels are gained. But I'd like some insight into how these might shape a male-dominated profession. You can pretty much guarantee that when an employer or a recruiter looks through CVs, they will be checking that person on Google and LinkedIn. Advertiser funded sites will use gamification to favour frequent participants.

Developers need to understand licensing too

2011-04-29T16:28:00.000+10:00

I am not a DBA. Well possibly by very loose standards, but I'm not professionally or personally responsible for any databases that contain any valuable data.

However I do work with databases on a daily basis and that means I need to be aware of licensing 'things'. Often I get access to accounts with privileges for various dictionary views. And there are some views and tables which can only be queried after supplying large amounts of folding paper to your friendly Oracle reseller.

That's one of my dislikes about Oracle. I'd be much happier if those views and packages which require the diagnostic, tuning or whatever packs were secured by some additional role to make them EXTREMELY obvious.

And development and test databases are another thing. It's all very well pretending things can be covered by OTN licences, but some things aren't. Throwing up another database for testing isn't necessarily covered by your existing licences. And what about that readonly standby ? Or that one on the VMWare server ?

Anyway, I've got my diary booked in for Mogens Nørgaard talking to us Sydneysiders about the joys of Oracle licensing in just under two weeks. If you've got any questions you want to pose (apart from why are they so bleeding expensive), add a comment.

I'd like to know more about Amazon Oracle pay-by-the-hour, due in the next couple of months, but I think we'll all have to wait for that.

Downtime, SLAs, compensation and other fairytales.

2011-04-29T15:45:00.001+10:00

My brother-in-law in England has a beautiful framed photograph in his living room. It is of a small cottage with some trees just as a small sprinkling of snow has fallen on it. It's something that wouldn't look out of place on a Christmas card. But it isn't a commercially bought print. It is a photo that he took when chance put him in the right place at the right time. Plus he knows a thing or two about cameras and was able to do justice to the scene.

It is all the more poignant because he took the negatives in to a photo shop to get some more prints and the shop lost the negatives. He would have got a few pounds in compensation for that loss. He'll never see the negative again though. For youngsters, cameras used to use film, which was processed into negatives from which prints were made.

There were some places where you'd drop off the film, they'd send it off somewhere else to be processed, who'd deliver it back to the shop where you'd get the prints. Like a 'cloud' service, you never really knew where the film actually went. Keen photographers generally went somewhere which did their own development on the premises and things were less likely to go wrong. At the really cheap places, if something went wrong, you'd get a replacement film, blank of course.

This isn't about 'cloud'. It is about compensation. You don't get the photos back. Having an SLA promise you 50% off your month's hosting fee of $20 if they have excessive downtime is pretty pathetic if you've lost $1000 in business.

If you are paying for a service, whether it is power, telephone, data storage or CPUs, you are paying less than the service is worth to you. [If you are paying $50 for something you only value at $25, then your boss's nephew probably owns the supplier.] The provider has costs too, so the benefit (profit) to them in having you as a customer is less than the benefit (avoided loss) to you in having them as a supplier.

If you own it, you have to look after it.

In which I apologize for the Amazon AWS Outage

2011-04-25T22:13:00.000+10:00

Honestly, I didn't mean it to happen.

I'm a (very) occasional user of AWS. And it is one of those businesses on the Internet where I pay using a pre-paid Visa card. Its one I have to remember to charge up every so often. And, in my defence, I've been a bit busy the past couple of weeks.

On April 18th I got an "urgent" message from Amazon saying that their attempt to charge me $0.09 for March usage had failed. And that if I didn't rectify the problem, they might suspend my account. But I was still busy so I didn't get round to recharging the card.

On April 21st, I got an even more urgent message from Amazon about the 9c I owed them. My account was going to be suspended. That was sent about 2:30 am PDT. Apparently that 9c was pretty important as Amazon then had its tummy ache.

I have since recharged my card and today got a message that Amazon had successfully processed the 9c charge. Hopefully all problems will be over. If I had known that my 9c would bring down half a data centre, I'd have made sure I was charged up. I'll try to make sure it doesn't happen again.

Thank you for your patience.

PS. Some 'scientists' may start talking about the difference between 'correlation' and 'causation' and the lack of any technical analysis in my reaction. But I remember reading "For want of a nail" in primary school so its quite logical to me ! Besides my horoscope said that coincidences don't exist, it's all part of a larger design and don't trust Aquarians.

PPS. For a more serious take on cloud storage, try Joyent's blog (link via Robert Young at "Dr Codd was Right" )

How COLLECT displaced User Defined Aggregates

2011-04-02T17:45:00.002+11:00

A recent PL/SQL Challenge question focussed on a user defined aggregate. Not a lot of people got it right, and one reason is that they have never been particularly popular. Tom Kyte's STRAGG function (dating back to 2001) was probably the best known usage. But they are basically complicated and a bit ugly.

When 10g came along, it added the COLLECT aggregate function. That meant that the old style user defined aggregates were pretty much redundant. It is a lot easier to aggregate up using COLLECT and then pass the collection to a regular function.

It is also more flexible. As an example, since the user-defined aggregates could only have one parameter, it wasn't possible to pass a delimiter to the STRAGG function. This implementation, based on a 'regular' function overcomes that:

CREATE OR REPLACE TYPE TAB_CHAR IS table of VARCHAR2(4000);
/

create or replace function coll_to_delimited
  (i_coll_type in tab_char, i_delim in varchar default ',')
  return varchar2
is
  v_ret varchar2(32767);
begin
  FOR i IN 1..i_coll_type.count LOOP
   IF instr(i_coll_type(i),i_delim) > 0 THEN
   RAISE_APPLICATION_ERROR(-20001,
   'Delimiter present in string:'||i_coll_type(i));
   END IF;
   v_ret := v_ret || i_coll_type(i);
   IF i != i_coll_type.count THEN
   v_ret := v_ret||i_delim;
   END IF;
  END LOOP;
  RETURN v_ret;
end;
/

select coll_to_delimited(cast(collect(table_name)
as tab_char),'|')
from user_tables;

I've even included a check to indicate where a string already includes the delimiter. I don't bother to include a specific check on length as that would be an error anyway.

Generate an SQL replay file using TKPROF

2011-04-02T10:07:00.000+11:00

Handy hint. You can use TKPROF's "record" option to extract a 'replay' script from a trace.

SQL> exec dbms_monitor.session_trace_enable(binds => true);

PL/SQL procedure successfully completed.

SQL> create table trace_test (id number, val varchar2(20));

Table created.

SQL> insert into trace_test values (1,'abcef');

1 row created.

SQL> insert into trace_test values (2,'xyz');

1 row created.

SQL> disconn
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Then switch to the udump directory and find the trace file you created.
In my case it was xe_ora_3972.trc

tkprof record=rec.txt trace= xe_ora_3972.trc output= r2.txt

TKPROF: Release 10.2.0.1.0 - Production on Sat Apr 2 10:02:28 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Then you can rerun the output from your record.

H:\oraclexe\app\oracle\admin\XE\udump>more rec.txt
BEGIN dbms_monitor.session_trace_enable; END;
/
BEGIN dbms_monitor.session_trace_enable(binds => true); END;
/
create table trace_test (id number, val varchar2(20)) ;
insert into trace_test values (1,'abcef') ;
insert into trace_test values (2,'xyz') ;

How to un-confuse SQL Developer with ORA-00932

2011-03-31T18:30:00.003+11:00

Sometimes SQL Developer will throw back a message like "ORA-00932: inconsistent datatypes: expected NUMBER got -". One way to get this is to try to execute

select collect(column_name) from user_tab_columns

The problem the tool is having is that it is getting back a data type which it doesn't recognize.

If you don't already have a defined collection type, then go create one as they are really useful. This is mine

CREATE OR REPLACE TYPE TAB_CHAR IS table of VARCHAR2(4000);

Then you can do an explicit CAST in the select statement and the error will go away.

select cast(collect(column_name) as tab_char) from user_tab_columns

Implicit rounding on INSERT

2011-03-30T18:00:00.002+11:00

Last week I had another quiz on the PL/SQL Challenge. There was a twist in its tail which meant that only about a quarter of players got it right.

Consider the following table and procedure.

CREATE TABLE plch_products
(
   product_id NUMBER (2, 0)
, product_name VARCHAR2 (10)
, stage NUMBER (2, 0)
, CONSTRAINT plch_products_pk PRIMARY KEY (product_id)
)
/

BEGIN
   INSERT INTO plch_products
   VALUES (1, 'Mouse', 10);
   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE ins_upd_product
   (i_product_id IN NUMBER
   , i_product_name IN VARCHAR2)
IS
BEGIN
   INSERT INTO plch_products (product_id, product_name, stage)
   VALUES (i_product_id, i_product_name, 20);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
   UPDATE plch_products
   SET product_name = i_product_name, stage = 30
   WHERE product_id = i_product_id;
   WHEN OTHERS
   THEN
   UPDATE plch_products
   SET product_name = i_product_name, stage = 40;
END;
/

If I pass in a product_id of 1.1, the value will get implicitly rounded during the INSERT to match the scale/precision of the table column. Because of this the product_id gets rounded to 1 and the insert fails due to a duplicate key. However in the exception handler, the UPDATE does NOT round the value for comparison so no rows match the criteria and none are updated..

So what can we do to prevent this problem.

Firstly, at least in 10g, the PL/SQL warnings don't cover scale/precision mismatches. They only cover data type mismatches (date to number, or even CHAR to VARCHAR2).

%TYPE anchoring can help, but it has to be in the declare section. Using %TYPE in the procedure signature doesn't help because, again, those just relate to data type.

CREATE OR REPLACE PROCEDURE ins_upd_product
   (i_product_id IN NUMBER
   , i_product_name IN VARCHAR2)
IS
  v_product_id plch_products.product_id%type;
  v_product_name plch_products.product_name%type;
BEGIN
   v_product_id := i_product_id;
   v_product_name := i_product_name;
   INSERT INTO plch_products (product_id, product_name, stage)
   VALUES (v_product_id, v_product_name, 20);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
   UPDATE plch_products
   SET product_name = v_product_name, stage = 30
   WHERE product_id = v_product_id;
   WHEN OTHERS
   THEN
   UPDATE plch_products
   SET product_name = v_product_name, stage = 40;
END;
/

With this variation, the rounding happens within the PL/SQL assignment. The rounded value is used in both the insert and the update, so the latter will succeed. Moreover, if someone passes an excessively large product name to the procedure, this will get picked up on the relevant assignment line, rather than in the execution of the update statement.

Personally, I prefer the assignment statements to happen after the BEGIN, rather than assigning the value in the declaration section. This allows for any exceptions to be caught in the local exception handler. The disadvantage is that you can't use a CONSTANT.

Everything happened at once

2011-03-29T19:30:00.001+11:00

Okay, it has taken around 14 billion years for everything to happen so far, and there's still a lot more to go. However I'm interested in the concept of 'time' in Oracle terms.

Redo log sequence

In a single Oracle instance, the redo log views everything as a sequence with one change after the other. Only in a RAC setup can you have multiple instances of the same database doing things simultaneously. Outside of recovery we are not too fussed with the redo log.

Concurrent Activity

Individual Oracle sessions can make changes to separate data blocks concurrently, though you are limited by the number of CPU cores (because only the CPU can actually process instructions to read or change a block). You can even configure multiple DBWR processes so that you can write multiple blocks to disk at the same time. Again, you might be throttled by the hardware of spindles and disk heads.

System Change Number

Bridging the gap between the redo log and the 'parallel' activity of the database is the SCN. Every commit increments the SCN, plus it gets incremented every three seconds even without a commit. The LGWR also writes at least every three seconds. Coincidence ?

Flashback Query

If your transaction performs three inserts then all three inserts may be under the same SCN or they may be under different SCNs depending on the activity in other sessions, and the time taken between those inserts.

Since you can't match an SCN to points in a transaction, Oracle sensibly decided to keep things simple and return the COMMITTED state of the data as it was at the SCN. That was the key for my Challenge question earlier this month.

In some ways, you can view a flashback query as a very lightweight autonomous transaction that only permits reads.

Undo sequencing

Within a transaction there is a sequence of changes, and you can 'navigate' that sequence using SAVEPOINT and ROLLBACK. This navigation walks back through UNDO to reverse the sequence of operations within a transaction. SAVEPOINT doesn't affect the SCN, so flashback can't be used to look at a table as it was when a SAVEPOINT was issued. All you can do is rollback to a savepoint, and once you've done that you can't rollforward again.

In theory you could walk back through the undo blocks to see everything the transaction did. If you could do that, you'd also be able to tell what records are locked by the transaction. But I haven't heard of any tools that support this in practice. There's no SQL to access undo directly, and the undo blocks are not necessarily written to disk as you'd find in a redo log, but nor are they guaranteed to still be in the SGA (though they would be in one or the other).

An exceptional question for the optimizing compiler

2011-03-26T11:30:00.003+11:00

I thought about submitting this as a PL/SQL Challenge quiz, but decided it was unfair, undocumented and unhelpful.

Consider the following procedure

create or replace procedure plch_test is
  v_num number(1);
  v_date date;
  e_no_day_for_month exception;
  pragma exception_init(e_no_day_for_month, -1839);
begin
  for i in reverse 1 .. 30 loop
   v_date := to_date(i||'-02-2011','dd-mm-yyyy');
   v_num := 10;
   dbms_output.put_line(v_num||':'||v_date);
  end loop;
exception
  when e_no_day_for_month then
   dbms_output.put_line('Date error');
  when value_error then
   dbms_output.put_line('Num error');
end;

Then I execute it twice with different optimization settings. What error is returned for the executions ?

alter session set plsql_optimize_level =0;

alter procedure plch_test compile;

exec plch_test;

alter session set plsql_optimize_level =2;

alter procedure plch_test compile;

exec plch_test;

In the first case, with no optimization, the "date error" path is taken as there isn't a 30th of February.

In the second case, you may wonder what effect the optimization might have. The assignment of the number 10 to v_num doesn't need to be in the loop. This isn't a cursor loop or a WHILE loop, so the code inside the loop is guaranteed to be executed. If it was executed once prior to entering the loop, it would result in a value_error.

In fact, a date error is still returned. Possibly there is no such optimization. Or possibly it is done in a way that means it is only executed once, in the correct sequence, but is not repeated. Tests shown in AskTom indicate that there is some optimization happening.

Ultimately, the PL/SQL optimizing compiler is a black box. It would be nice to assume that it wouldn't change the behaviour of the code. But I can't help looking at the procedure and wondering, would it be "wrong" (or more specifically a bug) if an optimization switched it to return a value error.

Monitoring with Images and Slugs

2011-03-17T19:22:00.002+11:00

After my Blogging Jeopardy! post, John Piwowar recommend I use FeedBurner to better keep count of my audience. My blind spot is aggregators. I'm very happy to have my content aggregated, and it goes out through OraDBPedia, OraNA, OraFAQ and probably some others too. Those aggregators pick up my feed, extract the records (content and metadata, such as author and date posted) and merge it into their feed. And people pick up that merged feed without ever touching the original feed, and that's where I lose track of them.

But images are magic. In an RSS feed, images are just a URL. The actual image data has to picked up by the end viewer. My images go through Picasa, as all my blog/website stuff is tied up in Google. Picasa tells me that an image in a recent post of mine was downloaded about 400 times, indicating that post had about that many viewers.

A similar tactic is exploited by mass mailers. When your email client talks about blocking remote content, it is probably talking about images, though Javascript libraries and CSS files are another, more dangerous, culprit. But outside of the security aspect, the email client warns you about 'privacy'. That is because there is a good chance that the image is being used to track whether you opened the email.

If I do a mass email to Tom, Dick and Harry, I can include a unique image URL in each one, in the same way as I address them individually with Dear Slug. I'll obfuscate it a bit to hide the fact, but essentially I'll have src="http://images.sydoracle.com/mailImage?id=Tom" If Tom wants to see the image, he has to get the content by calling the URL which tells me that Tom's message has been opened.

In my Oracle focussed world, I'd have one table of 'email templates' (with the HTML text and image data as CLOB and BLOB respectively), one 'subscriber' table with names and email addresses, and an 'email' table representing the many-to-many between the two. The email table would have the personalized URL. I'd use the Apex Listener to supply images in the same way as I did for PDFs, but this time I'd use a procedure. The listener calls the procedure, which picks the email via the personalized URL, marks it as read and then returns the actual image data from the parent email template.

If anyone wants an itty-bitty Apex app for that, let me know through a comment (or twitter, email or LinkedIn...) 11.2.0.2 (maybe we should call it 11gR2d2) added SSL for UTL_SMTP, making it a better option for sending out emails. Whether the XE version will have that remains to be seen. Anything I have would use the old, unencrypted technique.

PS.
If anyone doubts the approach of having the image data in the database, I can recommend Marcelle's ODTUG webinar on "Audio & Video Management Using PL/SQL". The ODTUG webinars are recorded and you get the URL if you are registered for the webinar. Not sure if I'm allowed to post the URL, but you could ask Marcelle.

Blogging Jeopardy!

2011-03-13T19:00:00.002+11:00

Please phrase your article in the form of a question.

I'm not quite sure how many people read my blog (see footnote). I can be sure that about a thousand people take the PL/SQL Challenge every working day. They take the time to visit the site, read the question, think about it and come up with an answer. I assume most will actually check the correct answer the next day (and maybe think about that too). So if I can phrase my article or concept in the form of a question for the Challenge, I can reach a much wider audience that I do through my blog. And they will think about what I wrote.

So far I've had one question published (25th of Jan 2010), taken by 1090 people with a typical time of 2 minutes and about two thirds got it all correct. And about 200 submitted a survey afterwards. That is a lot of eyeballs. I've got another one coming soon and I recently submitted a third.

So if you've got something you want people to know, whether you have an existing blog or not, if you can phrase it in the form of a PL/SQL question, go submit it. Alternatively, just go and play. The challenge awaits.

Footnote:

Google Analytics talks of about a thousand page views last month (though misses a week when I did something and lost my old stats). Blogger stats says about 2,700 which is more comforting. I expect some will pick me up through an aggregator and won't get counted and I'm not sure if a bunch of people who take my feed online, eg though Google Reader, are counted individually or not.

Why I don't trust deferrable constraints

2011-03-12T18:50:00.002+11:00

I don't like deferred constraints. I don't like deferrable constraints either, because there is the risk of someone making them deferred.

There's a Pythian blog on one potential corruption pitfall where you can end up with duplicate primary keys.

A little over a year ago, I commented on a blog by Chrisian Antognini to demonstrate where the optimizer gives incorrect results by invalid table elimination with a deferred constraint.

I've recently retested this in the 11.2.0.2 environment on apex.oracle.com and seen a change in behavior, though the bug is still there.

Firstly, create the parent and child tables, linked by a deferrable constraint that is INITIALLY IMMEDIATE (ie not deferred by default).

CREATE TABLE t1 (
   id NUMBER NOT NULL,
   val varchar2(5),
   CONSTRAINT t1_pk PRIMARY KEY(id)
);

CREATE TABLE t2 (
   id NUMBER NOT NULL,
   t1_id NUMBER NOT NULL,
   val varchar2(5),
   CONSTRAINT t2_pk PRIMARY KEY(id),
   CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1
   deferrable initially immediate
);

Then add a view to encapsulate the join

CREATE VIEW v12 AS
SELECT t1.id AS t1_id, t1.val AS t1_val,
   t2.id AS t2_id, t2.val AS t2_val, t2.t1_id AS t2_par_t1
FROM t1, t2
WHERE t1.id = t2.t1_id;

Because I was testing on the apex.oracle.com instance, I needed to do it as a single request (because the web-based SQL Worksheet treats a request as a transaction). So I used a stored procedure.

CREATE OR REPLACE PROCEDURE test_v AS
BEGIN

  EXECUTE IMMEDIATE 'alter session set constraints=deferred';
  INSERT INTO t2 (id, t1_id, val) VALUES (10,2,'Child');
  --
  FOR i IN (SELECT t2_id, t2_val, t2_par_t1 FROM v12) LOOP
   DBMS_OUTPUT.PUT_LINE('1:'||i.t2_id||'/'||i.t2_val||
   '/'||i.t2_par_t1);
  END LOOP;

  FOR j IN (SELECT t1_id FROM v12) LOOP
   DBMS_OUTPUT.PUT_LINE('2:'||j.t1_id);
  END LOOP;

  FOR k IN (SELECT t1_id, t1_val FROM v12) LOOP
   DBMS_OUTPUT.PUT_LINE('3:'||k.t1_id||'/'||k.t1_val);
  END LOOP;

ROLLBACK;

END;
/

The procedure sets the transaction to use deferred constraints, then inserts a row into the child table (without the corresponding parent row). Then there are three selects taking different columns from the view.

The first loop picks details that exist only in the child table, the second picks the common identifier and the third picks up details from the parent table.

In XE, only first loop returns records, but the second and third do not. The first loop "works" because the join to the parent table is eliminated on the assumption that the constraint requires that a parent row exists.

In 11.2.0.2, the first and second loops return records. The second loop can work if you assume that, because the ids are common, then it makes no logical difference if you take it from the child table or the parent table. If you assume the parent table will have at most one row for the key then the number of records returned is fully dependent on the child table. It is therefore more efficient to just hit the child table, and it is am improvement to eliminate the join to the parent.

In neither situation does the third query return a row. Because it requires the parent table to be hit, the optimizer cannot eliminate it and when it is hit, it is found to be empty.

Ultimately, the 'solution' would require every cursor to have additional dependencies on the state of every deferrable constraint for the query...or to simply assume that any constraint that is deferrable might be deferred and to optimize it as if it doesn't exist.

DBAs and Developers - complementary skill sets

2011-03-11T19:45:00.000+11:00

This is a comment on one slide from Cary Millsap's NoCoug presentation

The slide showed the skill sets needed by Developers and DBAs. He suggested that there was a partial intersection in skills.

DBAs	Developers
Operations	Algorithms
Resource management	Languages
Troubleshooting	Frameworks
Physical data design	Design
System interfaces	User interfaces
Data modeling	Data modeling
SQL optimizer	Debugging, profiling, tracing
Oracle product features	Business-domain processes

Personally, I'd match up the developer's Debugging skill against the DBAs Troubleshooting. Both are forms of problem resolution, but the developer's focus will be on algorithm/process with the DBA responsible for the Operations.

I like the thought that both need to understand data modelling. I'm not convinced where the Developer's responsibility for the data model passes to the DBA, or whether there is an analyst/architect/designer column missing. It probably varies between organizations. DBAs are definitely responsible for tablespaces and file concepts. Constraints and indexes fall into a DMZ between the two. I recall getting into a heated discussion with HJR on the old Dizwell forums about whether the DBA should be able to make constraints deferrable by default. I still think constraints are a 'logical' concept arising from the business model.

System interfaces are about the only other area I'd be fuzzy on. I'd still put these as heavily 'business/process' constructs that sit with the developers.

Any major skill sets missed ? Anything in the wrong box ?

Listening for my PDFs

2011-03-10T19:10:00.032+11:00

After the last post I had some PDFs in my database. I therefore took advantage of the opportunity to user the Apex Listener to get them out. It can just as easily serve up images, video, audio and all manner of other files.

I used the standalone mode. That means I downloaded the listener, and just ran it, through java, from the command line. No tomcat, glassfish or any other application server involved. I used port 8083 as I was running on the same machine as the database and already had the embedded gateway listening on 8080.

java -Dapex.port=8083 -Dapex.images=D:\oraclexe\apex\images -Dapex.erase=true -jar d:\oraclexe\apexlistener\apex.war

'Install' is pretty much down to setting a couple of passwords to start it up.

Then go to the admin screen and point it at an Oracle install just as you would any other client. In the database I had to unlock APEX_PUBLIC_USER, and make sure it had grants to the table I wanted to use. I could have used another username/password though. I could have several listeners running on different ports attached to different schemas.

Then switch to the Resource Template tab, enter the URL and query and set the Type to Media Resource.

Downloading the file is just a matter of putting the filename in the URL. Of course in a real application, there would be a proper sequence generated primary key rather than a filename.

http://127.0.0.1:8083/apex/pdf?filename=NoCOUG_Journal_200602.pdf

I ran the browser on the same machine too, but that isn't necessary or normal. The database can be sitting listening on 1521 on machine "Blue", the apex listener on machine "Green" connected to the database on 1521 and listening on 8083, and the browser can run on machine "Red" and just talk to "Green" on 8083.

In an XE environment you might run the Apex Listener on the same machine as the database (as XE is limited to 1 CPU and 1 GB of memory, so even a basic machine still has resources spare). This is actually quite secure as you can use the firewall to shut off port 1521 from external machines, forcing all database activity through a more controlled and less privileged account, just RESTful APIs through the URL.

Hey, that could take off. Just need a cool name for accessing a database as a service with no SQL.

Using Oracle to convert PDF to HTML

2011-03-08T18:59:00.004+11:00

I've used Oracle Text on a couple of assignments. One of the handy things it can do is convert a PDF into an HTML or plain text document. Actually, you can do this sort of thing through Google Docs too, by uploading a PDF and saving it back to your desktop as a different file type. But I'm going to demo the Oracle mechanism.

Firstly, you need an Oracle directory. You may already have one. If not, your DBA will need to create it as the CREATE ANY DIRECTORY privilege is needed and this is one of those very dangerous privileges that a cautious DBA will keep under lock and key. If, like me, you are using a personal XE environment, you can do what you want.

create or replace directory pdf_files
as 'WHATEVER YOUR SOURCE DIR IS';

grant all on directory pdf_files to public;

The next step is a table to store the PDF in (as a BLOB) and the appropriate Oracle Text index.

create table ctx_demo
  (filename varchar2(30),
   text blob)
/
create index search_idx
  on ctx_demo (text)
  indextype is ctxsys.context
  parameters ('sync (on commit)')
/
 

The third step is a few simple procedures. One to store the file in the blob, the second to get the converted HTML as a CLOB, and the final procedure to write the CLOB as a file. In practice, they'd live happily together in a package.

create or replace procedure load_file
(i_filename in varchar,

   i_dirname in varchar2 default 'PDF_FILES')
is
l_blob blob;
l_bfile bfile;
begin
insert into ctx_demo (filename, text)
    values ( i_filename, empty_blob() )
    returning text into l_blob;
l_bfile := bfilename( i_dirname, i_filename );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile
    (l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/

create or replace function get_html
(i_filename in varchar2) return clob
is
v_rowid rowid;
v_res clob;
begin
select rowid into v_rowid
from ctx_demo
where filename = i_filename;
--
CTX_DOC.FILTER(index_name => 'SEARCH_IDX',
    textkey => v_rowid,
    restab => v_res,
    plaintext => false);
    return v_res;
end;
/
create or replace procedure write_html

(i_filename in varchar2)
is
v_clob     clob;
v_out_file UTL_FILE.file_type;
v_buffer   VARCHAR2(16000);
v_amount   BINARY_INTEGER := 8000;
v_pos      INTEGER := 1;
v_clob_len INTEGER;
begin
v_clob := get_html(i_filename||'.pdf');
v_clob_len := DBMS_LOB.getlength(v_clob);
v_out_file := UTL_FILE.fopen('PDF_FILES',
                      i_filename||'.html', 'w', 32767);
WHILE v_pos <> v_clob_len LOOP
DBMS_LOB.read (v_clob, v_amount, v_pos, v_buffer);
    UTL_FILE.put(v_out_file, v_buffer);
    UTL_FILE.fflush(v_out_file);
    v_pos := v_pos + v_amount;
END LOOP;
UTL_FILE.fclose(v_out_file);
END;
/

Once all the preparation is done, it is a simple matter of loading the PDF and saving the html.

For this demo, I'm using an old NoCoug journal, as they recently loaded issues for the past ten years. If you are interested in the complete collection, I've got the links listed on my NoCoug Journal page. Using XE, this conversion only works up to the August 2006 issue. The later ones (except for February 2009) don't get any useful output. Possibly a later Oracle edition would cope better with these PDFs. I'm crossing my fingers that the Easter Bunny will bring me a chocolate covered XE 11g.

begin
  load_file('NoCOUG_Journal_200605.pdf');
  write_html('NoCOUG_Journal_200605');
  commit;
end;
/
 

Remember PDFs aren't designed to be reverse engineered to HTML documents, and Oracle Text is just interested in the text, so there aren't any pictures.

How do I filter data when doing an import or export

2011-03-05T08:04:00.000+11:00

When you need a subset of data for testing, it is very useful to be able to specify the filtering in the export or import. That's a lot easier than copying a full dataset and waiting while it deletes most if it.

This is very easy using the Datapump tools in 10g. Both expdb and impdb have the facility to accept a filter criteria. As a quick example:

Step 1 - Create the table. It has 16,000 rows (roughly)

>sqlplus gary/gary

SQL*Plus: Release 10.2.0.1.0 - Production
SQL> create table bunch_of_data
  2 as select owner, table_name, column_name, data_type
  3 from all_tab_columns;

SQL> select count(*) from bunch_of_data;

  COUNT(*)
----------
   16346

Step 2. Export a subset of the data

>expdp userid=gary/gary dumpfile=bunch.dmp directory=ext_tables
   tables=BUNCH_OF_DATA query='WHERE DATA_TYPE=''DATE'''

Export: Release 10.2.0.1.0 - Production on Friday, 04 March, 2011 17:45:17

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "GARY"."SYS_EXPORT_TABLE_01":
userid=gary/******** dumpfile=bunch.dmp
directory=ext_tables tables=BUNCH_OF_DATA
query='WHERE DATA_TYPE=''DATE'''

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "GARY"."BUNCH_OF_DATA" 30.10 KB 513 rows
Master table "GARY"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GARY.SYS_EXPORT_TABLE_01 is:
  D:\APPS\AR122007\BUNCH.DMP
Job "GARY"."SYS_EXPORT_TABLE_01" successfully completed at 17:46:00

Step 3. Import a subset of that export (into another schema in this test)

>impdp userid=gary_dba/gary dumpfile=bunch.dmp
   directory=ext_tables
   tables=BUNCH_OF_DATA
   query='WHERE OWNER = ''SYSTEM'''
   remap_schema=gary:gary_dba

Import: Release 10.2.0.1.0 - Production on Friday, 04 March, 2011 17:46:02

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "GARY_DBA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "GARY_DBA"."SYS_IMPORT_TABLE_01":
  userid=gary_dba/******** dumpfile=bunch.dmp directory=ext_tables
  tables=BUNCH_OF_DATA
  query='WHERE OWNER = ''SYSTEM''' remap_schema=gary:gary_dba
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "GARY_DBA"."BUNCH_OF_DATA" 30.10 KB 2 out of 513 rows
Job "GARY_DBA"."SYS_IMPORT_TABLE_01" successfully completed at 17:46:11

Partial export and partial import all complete.

FOR EACH ROW trigger fires at least once per row

2011-03-02T19:00:00.025+11:00

I have a table with a 'BEFORE UPDATE FOR EACH ROW' trigger.
The table has just one row and I update that row a thousand times. How many times does the trigger fire ?

It depends...though I'm not sure on what. This was the basis for one of Steven Feuerstein's PL/SQL Challenge questions. Here's a demonstration.

DROP TABLE plch_employees purge;
CREATE TABLE plch_employees
( employee_id INTEGER
, salary NUMBER)
/

INSERT INTO plch_employees VALUES (100, 0);

CREATE OR REPLACE PACKAGE plch_emp_count
IS
   g_count NUMBER;
END plch_emp_count;
/

CREATE OR REPLACE TRIGGER plch_employee_changes
   BEFORE UPDATE
   ON plch_employees
   FOR EACH ROW
BEGIN
   plch_emp_count.g_count := plch_emp_count.g_count + 1;
END;
/

CREATE OR REPLACE FUNCTION f_trg_test
   (i_iterations IN NUMBER default 1000000)
RETURN VARCHAR2 IS
  v_ret varchar2(100);
  v_cnt number := 1;
BEGIN
  plch_emp_count.g_count := 0;
  WHILE v_cnt < i_iterations LOOP
   UPDATE plch_employees
   SET salary = salary + 1
   WHERE employee_id = 100;
   IF plch_emp_count.g_count > v_cnt THEN
   v_ret := 'MisMatch at :'||v_cnt;
   v_cnt := i_iterations;
   END IF;
   v_cnt := v_cnt + 1;
  END LOOP;
  v_ret := NVL(v_ret, 'Reached '||plch_emp_count.g_count);
  commit;
  return v_ret;
END;
/
set serveroutput on
exec dbms_output.put_line(f_trg_test);

When I run it on my XE instance, I get a mismatch, generally after 718 rows. I get another, smaller, peak at 11518. These indicate a restart which is discussed in a couple of Tom Kyte posts listed here.

In summary a restart is where the session has found a row that needs update, fires the row trigger, then tries to update the row...and doesn't. It then retries the update, finding the row again and succeeds.

Apparently there is some discussion about 'single-session' restarts being related to UNDO.

I'll leave the reasons open. But it is worth remembering that with a BEFORE UPDATE trigger, FOR EACH ROW means it will fire at least once for each row, not exactly once.

Keep the faith

2011-03-01T19:00:00.003+11:00

A frequent query request is that, when you've used MAX or MIN to find the highest, latest or otherwise most favoured row(s), you want the values of the other columns in that row.

Often a suggested answer is the use of a sub-query. But this overlooks one of those less well known features in Oracle SQL, the KEEP clause.

Consider the test case:

create table test_max
  (id number,
   colour varchar2(20));

insert into test_max values (1,'Red');
insert into test_max values (2,'Orange');
insert into test_max values (3,'Yellow');
insert into test_max values (4,'Green');
insert into test_max values (5,'Blue');
insert into test_max values (6,'Purple');
insert into test_max values (7,'Black');
insert into test_max values (8,'White');
insert into test_max values (9,'Yellow');
insert into test_max values (10,'Yellow');

The maximum value of the 'colour' column is 'Yellow'. To make it a bit trickier, I've included three rows for the colour Yellow, with the IDs 3, 9 and 10. This allows us to see what happens if more than one row matches that maximum value.

select max(colour),

min(id) keep (dense_rank last order by colour asc) min_id,

max(id) keep (dense_rank last order by colour asc) max_id,

count(id) keep (dense_rank last

order by colour asc) cnt_col,

count(id) keep (dense_rank last

order by colour asc, id desc) cnt_id

from test_max;

MAX(COLOUR) MIN_ID MAX_ID CNT_COL CNT_ID

------------- ------ ------- ------- -------

Yellow 3 10 3 1

The query tells us that the highest colour is yellow, and of the 'yellow' rows, the lowest id is 3, the highest is 10 and that there are three in total. Whether you use DENSE_RANK LAST and ORDER BY ... ASC or DENSE_RANK FIRST and ORDER BY ... DESC is a readability issue, and should be judged on a case-by-case basis.

The final count demonstrates that, by adding further ordering criteria, it is possible to detail down to an individual result.

If you have Tom Kyte's STRAGG installed, you can use that too. [My own implementation is odd in that I expect the delimiter to be concatenated into the parameter value, rather than coding it into the aggregate function.]

I haven't found a way to pull in the new 11g LISTAGG function which has its own syntactic peculiarites.

select stragg(id||',') keep (dense_rank last

order by colour asc) cnt_col,

stragg(id||',') keep (dense_rank last

order by colour asc, id desc) cnt_id

from test_max

CNT_COL CNT_ID

---------- ----------

3,9,10, 3,

Blekko /oracledb Needs You !

2011-02-24T19:00:00.003+11:00

Despite popular opinion, there is more than one search engine. And I'm not talking Bing either. I do not use the MS word :) I mean blekko.

I do admit to being somewhat wedded to Google. I use GMail, Google Reader, Blogger, Sites, Docs. Oh, and the search engine. I'll also admit to once believing that Google Search (or rather, AltaVista, which was the big one before Google came into the picture) would be doomed to failure. Quite simply, I couldn't see how one site could hold a copy of the entire Internet. I still can't, but they come close enough to managing it.

Back then, Yahoo was in the picture with a human moderated directory of sites but without the ability to text search within those sites. It wasn't search as it is understood today. Blekko have merged the concept of the directory with the text search aspect, under its concept of /slashtags.

Part of the idea, as I understand it, is to keep out content farms and scrapers.

So a week or so ago, they talked to the folks at StackOverflow (my favorite Q&A forum) to get some of their experts in to organize some programming related slashtags. With a bit of debate, /oracledb has been set up as "our" slashtag.

I've put myself forward (and been accepted) as one editor for the slashtag. I've listed a bunch of useful sites in there which previously lived as my bookmarks or feed sources.

Please, try some searches on the /oracledb tag. If you find important sites missing, I can add them in if you throw me a comment. Here is the current site list. And some more reputable editors would be good too. ACEs or Oak Tablers have that extra credibility.

When does a foreign key not reference a primary key ?

2011-02-21T19:00:00.001+11:00

Generally your foreign key will point to the primary key in the parent table. However it is actually possible to point a foreign key to any column, or set of columns, where uniqueness is enforced through a constraint.

One situation where this may be applicable is a type / subtype on your entities. I'm using the familiar employee, department and orders model as it was used in the Stackoverflow question that prompted this. Incidentally, the "Subscriber Content" (ie questions, answers and comments) on StackOverflow are all under licensed as Creative Commons, so there are no legal issues with copyright as might be experienced elsewhere.

The problem was that of ensuring orders could only be owned by certain types of employees, the salesmen. By the way, is anyone else worried that the abbreviation for Sales and Marketing is S&M. Now if you are a data modeling fundamentalist, you would normalize out 'SALESMAN' as a separate entity from 'EMPLOYEE', with an optional one-to-one relationship. I'm not saying that is the wrong solution, but simply offering an alternative which may offer practical benefits in some circumstances (eg reduced storage and de-duplication, and maybe tighter consistency in column statistics).

Firstly, I create the employee table. I add an extra field which is always populated for salesmen, and never for another type of employee. I enforce the value of this to be the same as the employee id, although this is not really essential as long as it is unique. And that uniqueness MUST be enforced by a constraint and not just by an index.

CREATE TABLE t_emp
(emp_id number PRIMARY KEY, emp_name varchar2(20),
dept_name varchar2(10),
sales_emp_id number,
CONSTRAINT sales_emp_id_sales_ck CHECK
      ((sales_emp_id IS NULL AND dept_name != 'SALES') OR
       (dept_name = 'SALES' AND sales_emp_id = emp_id
                            AND sales_emp_id IS NOT NULL)),
CONSTRAINT sales_emp_id_uk UNIQUE (sales_emp_id));

I insert some employees, with the last three expected to fail due to the constraint violation.


INSERT INTO t_emp VALUES (1,'Alan','SALES',1);
INSERT INTO t_emp VALUES (2,'Bill','ACCOUNTS',NULL);
INSERT INTO t_emp VALUES (3,'Chuck','ACCOUNTS',3);
INSERT INTO t_emp VALUES (4,'Dan','SALES',NULL);
INSERT INTO t_emp VALUES (5,'Ellen','SALES',6);

SELECT * FROM t_emp;

Now I create the ORDERS table. Rather than use the primary key of the EMPLOYEES table, I use the unique salesman's identifier. This ensures that orders can only belong to a salesman.

CREATE TABLE t_orders
(ord_id number PRIMARY KEY,
salesman number,
CONSTRAINT salesman_fk FOREIGN KEY (salesman)
       REFERENCES t_emp(sales_emp_id));

Finally, a simple test to show that only the sales employees can own orders.

INSERT INTO t_orders VALUES (1,1);
INSERT INTO t_orders VALUES (2,2);

A game of Truth and Falsehood ?

2011-02-18T19:00:00.005+11:00

Here's a quick quiz for you. I create the following table:
CREATE TABLE t_truth
(truth_id NUMBER NOT NULL,
dare_id NUMBER,
CONSTRAINT truth_dare_id CHECK (truth_id = dare_id));

Which of the following statements will successfully insert a row into the table.

INSERT INTO t_truth VALUES (1,null);
INSERT INTO t_truth VALUES (2,2);
INSERT INTO t_truth VALUES (3,0);

.
.
.
.
.
.

Actually both the first and second statements succeed.

The check constraint condition "truth_id = date_id" will evaluate to unknown when dare_id is null. The constraint is only violated if the condition fails.

Because 1.25 isn't actually a number

2011-02-12T17:02:00.001+11:00

Prompted by a stackoverflow post, here's a little demo of the role of NLS_NUMERIC_CHARACTERS in a TO_CHAR.

SQL> alter session set nls_numeric_characters=',.';

Session altered.

SQL> select to_char(1.25) from dual;

TO_C
----
1,25

So what happens ?

Firstly 1.25 might be a number to us, it isn't actually a NUMBER. It is a numeric literal representing the value "1 and a quarter". Oracle's first step is to convert it into its internal representation.

SQL> select dump(1.25) from dual;

DUMP(1.25)

---------------------

Typ=2 Len=3: 193,2,26

To paraphrase Crocodile Dundee, "Now, that's a NUMBER". Okay, it's actually the dump of the NUMBER but it is a condition of Australian citizenship that you work a Mick Dundee reference in at least once a year.

The same concept applied to date literals, such as date '2011-02-13' but the 'date' keyword makes it more obvious to us that we are telling Oracle that it is something special.

The NLS values don't play any part in this conversion to the internal format. The internal value itself is NLS neutral so it doesn't have the concept of a period or a comma as a decimal separator.

The NLS settings do play a part in the TO_CHAR conversion from the internal format. That's where the comma is being formatted into the string. If you really want to be sure about the formatting, you can add this to the TO_CHAR, and the matter will be settled once and for all.

select to_char(1.25,'999.99','NLS_NUMERIC_CHARACTERS=.,') from dual;

Fluffy white Oracle clouds by the hour

2011-02-03T20:34:00.000+11:00

Pay-by-the-hour options are becoming more common, with Amazon and Oracle are getting even more intimate in the next few months. Yes, you too will be able to pay for a quickie with the king of databases (or queen if you prefer that as a mental image).

No prices have been announced, so it will be interesting to see what they come up with. Anyone with their own licenses can still use them. Anyone who is already running Oracle on Amazon would find it easy (and legal) to throw up another one for testing (maybe Real Application Testing, if you've got the money) or perhaps year-end reporting.

It would also be handy for demos and development outside the scope of the OTN licenses.With the facility to push your RMAN backups to the S3 cloud, you may even have scope for using Amazon as a DR.

Of course if you're in that group who are chucking terabytes of data around on a daily basis, then your hardware is near and dear to your heart and all this cloud stuff is a load of vapour. And then there's whatever restrictions your application, industry or country has on storing data as bits and bytes floating rounds the US.

If nothing else, the hourly rates will be interesting to factor in to any future negotiations about licensing for those database servers you don't need running 24x7.

You can sign up to be ~~pestered by marketing~~ kept informed of developments through the Amazon Oracle page

Sydney Oracle Lab

Crazy Crossovers

Playing with Google Plus

Newsflash - the replacement for the Oracle Support HTML interface will be....HTML

DBMS_METADATA and hashed passwords

Wish List IV - Read only and mean it

Wish List IV - The Whole Truth

Wish List III - The Sequence Cometh

Wish List II - Ultimate Destruction

12c Wishlist - Part 1

What's the difference

How to prevent your SQL Developer sessions from being killed

A short story about death

An underhand compilation

It's Enterprise and won't be cheap....

One head, plus one body (slightly damaged)

Laying down the law over warnings

WARNING, WARNING, DANGER, DANGER !

Oracle 11gR2 Express Edition is FREE

Swapping the white shirt for a towel ?

Canadian dogs on drugs

Redo log buffer or redo log file ?

The casino approach to security

An evening with some Oracle Experts

Is it just me

Turning it around

More on Checksumming

Check summing a table row

The exceptional case of the backwards null

SQL Injection and the variable IN list

Locked down in Oracle Amazon RDS

The www of sydoracle.com

Licensing can be fun....NOT

Bullet proofing entire record inserts/updates

Encouraging women in information technology

Crossover

From CRUD to AAAR - Pirates of Tahiti

Why we learn maths

Worried about gamification and gender bias

Developers need to understand licensing too

Downtime, SLAs, compensation and other fairytales.

In which I apologize for the Amazon AWS Outage

How COLLECT displaced User Defined Aggregates

Generate an SQL replay file using TKPROF

How to un-confuse SQL Developer with ORA-00932

Implicit rounding on INSERT

Everything happened at once

An exceptional question for the optimizing compiler

Monitoring with Images and Slugs

Blogging Jeopardy!

Why I don't trust deferrable constraints

DBAs and Developers - complementary skill sets

Listening for my PDFs

Using Oracle to convert PDF to HTML

How do I filter data when doing an import or export

FOR EACH ROW trigger fires *at least* once per row

Keep the faith

Blekko /oracledb Needs You !

When does a foreign key not reference a primary key ?

A game of Truth and Falsehood ?

Because 1.25 isn't actually a number

Fluffy white Oracle clouds by the hour

FOR EACH ROW trigger fires at least once per row