Santa brings 11g instant client

The 11g Instant Clients are available for download off OTN, at least for Windows and Linux 32bit. Nothing for Max OSX users though, and I didn't check the others.

The "What's New for SQL*Plus 11g" here includes goodies like automatic error logging to a table and querying BLOB and BFILE columns.

The licence for Instant Client is less restrictive than the OTN licence for the 11g database server, so you could use it in production, for example against XE.

Merry Christmas folks.

Secret Squirrel on the move

I've finished, for now at least, my voyage into Teradata, and am half back in the Oracle world.
I say half because I am also using SQL Server (for the first time).

I'd recommend any Oracle developer try to familiarise themselves with some of Oracle's ANSI-standard constructs, like COALESCE instead of NVL, CASE instead of DECODE and the ANSI OUTER JOIN syntax. It gives you a bit of a headstart when attacking these alien worlds. Despite Larry's intentions, at some point you WILL come across a non-Oracle database.
Even ANSI isn't perfect though. Simply updating one table from another is a major switch between Oracle and SQL Server and there's no universal syntax for it. Plus I have to keep reminding myself that Oracle is practically unique in not differentiating nulls and empty strings. Even EnterpriseDB doesn't go down that prickly path. Also collation and ordering has been an interesting challenge.

I'll also recommend Secret Squirrel. Okay it is actually SQuirreL SQL Client rather than the undercover cartoon rodent, but it was a secret to me. It is a Java-based cross-database query tool. While I've only used it for Oracle and SQL Server, it looks like it will cope with pretty much anything you'll come across, as long as you can find a JDBC driver for it. I should have tried it for Teradata.

Squirrel is more compact (at about 25Mb) than SQL Developer, and also only needs the Java Runtime, not the full JDK. It is also better at handling multiple sessions against the same DB server. [Note: I'm trying to use the term 'DB server' because 'database', in SQL Server and Teradata, is closer to what Oracle bods consider a schema/user.]

Squirrel isn't an IDE to the extent SQL Developer is, but then I never really used that for coding PL/SQL anyway, preferring to stick to SQL*Plus and my favorite PSPad editor (which I've got working under Wine in Ubuntu). It also doesn't have the level of Oracle specific support (eg built-in reports). The Explain Plan seems basic (not using some of the new columns), but I prefer DBMS_XPLAN.DISPLAY anyway.

It is also cluttered. That may be because I'm still working out what I use and don't use, and what I can turn off or hide.

Finally, as you'd guess from the SourceForge location, it is Open Source (Lesser GPL if that is important to you), and seems pretty active. The latest version has a nice built-in for copying data from one database to another (like the old SQL*Plus COPY).

Importance, Urgency and 'Noise'

About a month back, Tom posted that "It's all about the data", saying that "Applications come, applications go. They are secondary."

My own take is slightly different, because there's a difference between Importance and Urgency. I'll agree with Tom that in the vast majority of cases (and more so for database applications), the data is far more important than the application.

But urgency is a different matter. Urgency is about doing things. Getting a lottery ticket for tonights draw is urgent, because it has to be done today. However it is also pretty unimportant. It won't make a difference if I miss it, I'm not going to win anyway. But deciding on who to marry is important, although it is generally far less urgent. [If it has become urgent, there's probably nothing left to decide.]

For a business, it is all about continuity. They need to be able to keep on doing business. When the 'system' is down, management really doesn't care if the problem is in the data files, the Database Server, a mid-tier node or the end-user application. It just needs the problem fixed as quickly as possible. The data and the application are equally urgent, and, as links in a chain, they are equally important in those circumstances. A week, or even a day or hour, without the data or the application would both be catastrophic.

Generally speaking though, an application should be simpler to recover. You need the hardware and software, but it is less dynamic. It mostly doesn't really matter if it is yesterday's software or last month's. The data is constantly changing, so while having application and data backups are equally urgent when it comes to recovery, having a TIMELY backup is more important for data.

And finally, there is noise. The amount of discussion, meetings and general 'buzz' about a topic. Noise is all about emotion. People get emotional about the applications they use, THEIR applications. Apple Fan Boys, MS-haters and flame wars. You've seen it before. A clunky application generates lots of bad feeling, a smooth one gets praised. Business users generally aren't dealing with their own data, but that of the customers or suppliers. Incorrect data may make the job harder sometimes, but a bad application can make it harder all the time. People wll love, loathe and occasionally be indifferent to their application. People blogged about 'creating passionate users'. People don't get excited by data. Well not the sort of data I see anyway.

Noise has NOTHING to do with importance or urgency. We recently passed the anniversary of a young girl being abducted from her bed where she was sleeping with her two younger siblings. Not, in this case, Maddie McCann, but Rahma el-Dennaoui , who disappeared two years ago in Sydney. Since she was at home, there were no big media inquisitions about the competancy of the foreign police . Because she is dark-haired, there's been no hunting parties to far off countries sparked by mere sightings of blonde girls . No soccer stars , tycoons or famous authors throwing around money and publicising it world wide. Different noise levels. Same level of importance.

So there may be more noise about applications rather than data. That doesn't reflect their relative importance. The whole data-warehousing/data mart/business intelligence movement reflects the fact that businesses are seeing the value in their data and putting money into realising that value. if we can just get them to pay the same level of attention to securing that data...

Now I'll let you get on and read the next dozen posts from OpenWorld

Happy Halloween

Okay, it was yesterday here in Oz, but you guys in the US are just warming up to it, so I thought a halloween post was in order.

One of my namesakes happens to be a writer of horror stories (see Gary Myers) in the H.P.Lovecraft / Dunsany style. Thats sort of a fantasy style where any barbarian hero is likely to end up a gibbering wreck. I'd like to point out I didn't discover him by ego-surfing, but rather when my wife bought me a collection of short stories which included one of his (The Last Night of the Earth ). The book was the 'Azathoth Cycle - the Blind Idiot God' and its contents are listed here. The subsequent story, the Daemon Sultan, was written by Donald Burleson, a name familiar to many in the Oracle field. Okay, to spoil the story, the story's author was actually Donald R. Burleson and the Oracle personality has the middle name of Keith, according to www.dba-oracle.com/resume_don.htm.

The moral of this story is that, if you have a sufficiently large data set, coincidences will happen.

PS. One thing I don't get about Halloween is, we spend all year telling our kids not to talk to strangers, let alone accept sweets from them. Then they go off to strangers' houses DEMANDING them. I only had one visit last night, and I didn't recognise them (though perhaps my wife would have). They got a small chocolate bar though.
Then I took my two out, with a rubber mask each, to 'Nana next-door' who had a biscuit for them. But they like to visit her anyway.

I've finally gone 11g

Typical. I finally resort to rebuilding my laptop with linux and install 11g, and Oracle come out with the Windows version.

Okay, to be honest, the linux install wasn't just to load up 11g. My windows XP startup, after 18 months, was abysmal. So I wiped it, fully intending to re-install it from scratch. But. There's always a but. Acer don't give you Windows install disks, or at least not for me. Instead you get a hidden partition on the drive that is supposed to be able to recover it as of the factory state. And you can create a Restore disk.

The restore disk didn't. The recovery partition didn't either. This was possibly due to my earlier conversion of the standard FAT32 C and D partitions to NTFS.

So I tried to install XP off the disks I had for the desktop machine. But that doesn't work with the laptop licence key, so I faced a long talk with either Acer or Microsoft or both. Bugger that.

I've opted for Ubuntu. I've tried that before, but it was a good while ago. I'm more impressed this time. It picked up my wifi connection without much fuss, which Oracle's Enterprise Linux 4 failed to do. I tried bits and bobs with OEL but gave up. Given my router is stuck in the bottom of the wardrobe (don't ask), no wifi is a no go for me.

Ubuntu 7.04 (sorry, the gimmicky names are a bit too much for me) was on the DVD for one of this month's computer magazines, so was an easy choice. Howard's articles here and here provided the details. Thanks, Howard !
I've also added rlwrap as I can't cope with SQL*Plus without a history. And this page told me how get my APEX going. While APEX is installed with 11g by default, it still needs a couple of commands from the DBA before it is available from the browser. Since the apxconf script is executed AS SYSDBA, it's a shame they didn't put the grant for DBMS_SYS_SQL in there too. I feel it would have made the default database install that little bit safer, neutering the FLOWS_nnnnn account until Apex is specifically switched on. But maybe I'm a worry wart.

I understand that the 11g Apex is the same 3.0.1 that is available for the general download and use (give or take a bug fix), which I installed on my XE install on my desktop machine. My plan is to do a little playing around, developing a small application one evening per week.

I may still download the Windows 11g next week. I noticed the Linux version didn't give an option of 'Personal Edition', which is apparently Windows only. I've no idea why it is Windows only. In fact, I've got very little idea what the market for Personal Edition is, apart from being the cheapest fully supported Oracle database (and so, I think, the cheapest way to get Metalink access and tempting for an individual consultant).

An Oracle coder in a Teradata world

My blog goes through quiet patches. The current one is because I'm not doing any Oracle work, but am actually using Teradata. There's not much blogging about Teradata. I can't even recall a mention in Pythian's Log Buffer. Maybe they just don't talk much.

Using a different SQL syntax does make you think a little.
For example, Teradata doesn't have DUAL. Instead you can just write "SELECT current_date" and leave it at that. [Yeah, it doesn't have SYSDATE either.] But you can also write "SELECT table.column" too, rather than "SELECT column FROM table". You can even do "SELECT table1.column from table2" which is equivalent to "select table1.column from table1, table2" in Oracle.

So what you DON'T want to do is create a few working tables with very similar names and then set off a query like
"SELECT oct23.val, ref.desc FROM oct22 JOIN ref ON oct22.ref_id = ref.ref_id".

Like SQL Server, a teradata "database" is more like a schema in Oracle. Rather than do an ALTER SESSION SET CURRENT_SCHEMA=..., you can do a SET DATABASE ...
And just to add to the spice of it, you can actually set up a sort of path of databases in which it will look for the tables. So if you mistype your table name or alias, it can go looking for that name in all sorts of places.

I'll follow the practice of the terrible programmer mentioned by Tom Kyte, and program defensively. No SET DATABASE for me. I'll specify the schema (or database) myself, and use aliases for all the tables.

There's no ROWNUM in Teradata either. Instead you do a "SELECT TOP 5 * FROM table". But if you do "SELECT TOP 5 * FROM table ORDER BY column" it does the ORDER BY first, then returns the first five rows.

Like pretty much every-one but Oracle, Teradata differentiates between a zero-length string and a null value.

It does have a few niceties that I'd like Oracle to adopt. Firstly you can specify an expression alias in the GROUP BY clause.
"SELECT CASE
WHEN column1 BETWEEN column2 AND column3 THEN column4 ELSE column5 END val,
COUNT(*)
FROM table
GROUP BY val"
Boy, does that save some COPY/PASTE.

There's also a QUALIFY clause. You know how HAVING is like a WHERE for a GROUP BY ? Well QUALIFY is like a WHERE for an analytic function, so you can do something like
SELECT id, start_date, end_date
FROM table
QUALIFY rank() OVER (PARTITION BY id ORDER BY start_date desc) = 1
rather than making the whole thing an inline view.

Remember, the "S" in SQL stands for Structured, not Standard. It just ain't that portable.

The Teradata documentation is all PDF and I haven't found it as easy to navigate as the Oracle documentation set, but that might be more familiarity with where to look. I did upload all the PDFs into the XE database on my laptop so I could use Oracle Text to search them (using the Apex Document Library application). Is that cheating ?

Still blogging

After a brief consultation, Howard has been persuaded that Dizwell is a popular and useful resource, and settled for a spring clean instead.

He did suggest letting others have their blogs hosted on Dizwell. I do find Blogger has its drawback. Mostly it is difficult to include supporting material, such as code or trace files, as it only provides for images and anything else needs to be hosted elsewhere. I find this a bit surprising given that it is all Google and it seems that they wouldn't find it difficult to have a more seamless integration with googlepages or their Docs. I expect that it is because these stretch the concept of blogging beyond the norm.

But being hosted on Dizwell.Com would be a bit scary too. It reminds me of Kerry Packer, who used to own the Channel Nine television network in Australia. During a dinner party, one of the guests raised the topic of "Australia's Naughtiest Home Videos", being debuted on his channel. He took a look at the program and promptly rang the station and ordered them to "Get that shit off the air!". They did, with just half an hour of the program shown.

It comes down the "the buck stops here". Dizwell is Howard's brand/company and opening it up to others, as he did with the Wiki and later the Knowledge Base, puts that on the line. That's one reason I have respect for the corporate blogs from the likes of Pythian, Amis and LogicaCMG . It's a brave company that is confident enough to put its technical knowledge on show for all to gawp at.

I'm glad Howard is sticking around. And if he does find 'room-mates' for his blog, I look forward to reading them too.

PS. Talking of brave, I'll be following the MEEP blog closely too. It sounds like an interesting experiment. Actually educating someone in Oracle, rather than just giving them code and expecting them to absorb knowledge by osmosis. A radical idea.

Sydney Technology Summit

Well it isn't a conference, but Sydney does rate a Technology Summit at the end of this month.

There's an RSS feed here that will keep you updated of "in person" events in Australia or here for Sydney. You can pick up events in Melbourne and Perth too. But Perth doesn't rate a "Technology Summit" so they'll just have to live with Chris Muir's grandma.

I'm not quite clear what the summit involves. It says :

Leverage on Oracle’s next generation of innovation - Oracle Database 11g:

• Performance and Scalability
• High availability and disaster recovery
• Self-management and automation
• Security and compliance
• Business Intelligence and data warehousing
• Database application development

A bit vague as to whether it is pitched at the CIO, DBA or someone in between. Anyone have the answers ?

Building up the Oracle user community

AppsLab is asking what we want from them. They are the "think-tank developed to drive adoption of new web patterns and technologies across Oracle’s business and products"

I'm not sure where the OTN forum membership fits in their remit, or more likely in Justin's, but I'd like to see them built upon. Discussion forums are one of the oldest of all online communities (after mailing lists) and given the recent developments that have occurred in this field, it is surprising that so little is new in the OTN forums. There has been some recent development with the Ospace community for Apps users and some forums that are industry / interest based rather than "solve my problem". To be honest, I'm not convinced that the current forum mechanism is appropriate for that community feeling, but I could well be wrong. Private messaging may help, but that opens up a whole can of worms (eg for monitoring).

Apparently the forum software is an out-of-the-box implementation of Jive Forums, which limits what can be done on a structural basis. However the underlying data is accessible, and it is the member profiles I'd like to see opened up and encouraged. The Jive documentation and viewing an OTN user's profile indicate that "Occupation", "Location", "HomePage" and "Biography" go into the Public Profile (if set) and "Time Zone Id" and "Locale" are only used internally.

Anyone listing a homepage can reasonably supposed to be encouraging visitors to that page, and anyone can set up a blog or googlepages etc.. Biographies could be used to publish details about whatever Facebook-like application users may want to connect with.

So the first option is to let people search for people with homepages and biographies. That may not be built into Jive itself, but the Profile data can be replicated easily enough and an Apex application quickly built for this level of functionality. Then add in an RSS feed for new and changed homepages/biographies.

We instantly get a mechanism for any OTN member to launch their blog or resource page. Maybe they could get an icon, like the ACEs and ACE Directors. Being in Oz, I suggest we title them 'Mates'.

Some OTN forum members may not be the blogging type; they may be happier responsing to questions or are looking for help themselves. The 'Mate' search can work off that by adding in the potential to build linguistic and geographical contacts, using the Locale and Location values. Locale is chosen from a list and would be good for an initial filter option, with the free-text location as an option below that. That can encourage people in the same area to make contacts, perhaps form or join a user group or mentoring or even employment opportunities.

The presentation you attend when not attending a presentation

Everyone is talking about going to OpenWorld and I'm jealous. So I'm proud to present: Oracle ClaytonWorld. For those not familar with Australian slag, "Claytons" was a non-alcholic drink, sold under the slogan "the drink you have when you are not having a drink".

What I'm looking at is presentations without a presenter. You can 'attend' on your own at home, with your work-mates in a conference room, as a user group in a hired function room (or the pub if there isn't a football game on [or its just England getting hammered in the Rugby]) or pretty much anywhere with a large screen.

There's a fair bit on offer, with special thanks to Quest, Hotsos and Pythian.

You don't have to fit it all in a couple of days and I'd prefer to watch a presentation or two a week (or each month for if shown by a user group), but it is possible to schedule up your own two-day conference.

An Oracle conference must start off with some Oracle spiel about the new wonder of the database world. It's a tradition, old charter or something. The 11g launch got a webcast from Oracle which fits the bill.

After that, I'd recommend a Flash movie on SQL injection from Console Pictures. It's one of those things you can watch, but don't have to take detailed notes, so makes good material for a morning session. It also puts you in a 'security' frame of mind for the rest of the stuff you listen to.

After coffee, you can pick some more of Oracle's "Web Events".
I may try a look at Warehouse Builder or Data Warehousing with 11g

After lunch, you can continue courtesy of Quest software's provisions on YouTube including Steven F.'s PL/SQL best practices and Common mistakes in PL/SQL programming for Developers, Database Change Tracking and Testing 10g RAC scalability for DBAs, and
Data Modelling for the models thrown out of London Fashion Week for being too skinny.

Then developers can finish off the first day with Carl Backstrom's preview of Apex 4.0
[The 'AVI' appears to be an 'OGM', but he recommends an appropriate player.]

For the DBAs, Pythian have their Goodies, a video camera recording of their "guided debates". They have that live, unrehearsed feel, just like the Blair Witch Project but without the hype.
They include Flash Recovery Area, Disk IO Basics and Parallel Execution Basics

The second day is "Oracle Performance Day", thanks to Hotsos.
Mike Rothouse pointed out Hotsos's Monthly Newsletter earlier this week but if you look at https://hotsos.webex.com, you'll find a treasure chest of over a dozen webinars. You need to get to the list of events, then go to recorded events. They need the WEBEX Player (which also comes with a recorder so you can roll your own).

If there is anyone not interested in performance, the "Files" link on AskTom also gives a presentation on "Content database". That isn't much of an alternative, but that's because EVERY-ONE should be interested in performance.

Finally, to finish up the conference, , the Man himself can be there in the form of "The Things You Know" (Thanks to Don Seiler for the pointer in his blog )

There's plenty more presentations, papers and podcasts that can be downloaded. Unfortunately, without that Audio/Visual combination, that's just a step too far away to be a Clayton's presentation. More like swigging the dregs or sniffing the empty bottle at the end of the night.

You can see that I've got nothing for the Apps crowd. Primarily it is because that's not my area, so I don't look for anything there. I'm not saying it isn't out there, just that it would be under my radar. Similarly with Java or Fusion. If you've got any pointers, schedule your own conference (or just add a comment for individual items and see if there's enough material).

While we have plenty of volume for a conference, the major contributors are Oracle (of course), Hotsos, Quest and Pythian. We could probably do with a little more breadth and some independents would be welcome. Maybe we can really annoy Oracle if anyone knows of something about EnterpriseDB ? Links are welcome.

I'm afraid you'll have to assemble your own goodie bag though. Still maybe we can build up some interest amongst those of us in places too insignificant to rate a real conference. [I dunno, we can host APEC but not a decent Oracle meet-up. Maybe I should switch to Python ]

Oracle self-education budget

I'm contemplating how hard to hit my budget.

My AUSOUG membership is due for renewal, and registration for the conference in Melbourne is open too. The membership is about $150 and gives access to their quarterly magazine and (most) of the conference presentation material. There's also the occasional meeting in Sydney.
The conference, including travel and accommodation, would be in the region of $1000. While the experience last year was good, I don't think I can justify the dollars.

Part of the problem is the conference format.

Firstly, there are some things that are simply best explored in a written format, either a book or a white paper. You have plenty of time to take the material in at your own pace, maybe testing examples or exploring any ideas that are prompted.

Workshops can be valuable, but you really need to be able to get stuck into that new area before the knowledge gets stale. I attended Penny Cookson's Apex workshop last year, but had forgotten a lot of it by the time I actually started using it. If your organisation intends to use Apex as a development platform, I'd recommend the session though.

Presentations have their place too. A good speaker can be entertaining, but a presentation has to have some intrinsic value. Sales presentations are an obvious example, but the value there is for the presenter more than the audience. The value of a presentation can be identified by a few questions.

• Does the attendee get the presentation slides or a white paper at the end ?

• If so, could they get the same understanding from the handouts without attending the presentation ? Is there 'added value' with the presentation, or is it an introduction to the real material ?

• If not, how much information will they be able to remember two days later ? This last point is especially relevant to conferences, where people might attend a dozen different presentations in quick succession.

• If the presenter starts editing the material to act solely as a reminder to those at the presentation, then are they more interested in 'being a presenter' than 'disseminating knowledge' ? Also a 'reminder' may work for a few days, but is unlikely to be effective a couple of months later and a presentation at an annual conference probably won't coincide with when the knowledge will be applied.

In all, I'm not convinced that a conference is worth the money from an education point of view.

So then I noted that Jonathan Lewis has penciled in an appearance in Sydney in March (and Melbourne too). That means no travel or accommodation issues for me. That counts for something as the AUSOUG conference last year are the only days when I haven't seen my kids. No news on costs for Jonathan seminar yet, but I've expressed an interest.

Finally, there's always a few books out there worth picking up. There's a book on Apex due out in a week or so. I'm also hoping that Tom's quietness is due to him working on his next book and Jonathan originally promised three volumes on the CBO and there are rumours that Harry Potter dies in the last book (but I may have got that confused with the evil Buffy Cash-Hitz).

Laptop security

There was a 1/8th page advert in Friday's Daily Telegraph (that's the Sydney tabloid paper) that caught my eye. A $20,000 reward for the return of a lost laptop containing "family photos and videos". There's a report here.

To me, that is a lot of money. The machine (an Acer TravelMate) is probably worth 5% of that. I accept that there are circumstances which could mean some photos could be irreplaceable (though generally I'd expect such photos to have at least been emailed to friends or family, or videos put onto a DVD to watch) . I can even accept that there are people who can put their hands on $20,000. The laptop was stolen from Wahroonga, which is quite a well-off suburb.

Now personally, I've got a couple of external USB drives and photos don't get erased from the camera until they are on both the PC and a backup drive. Our video camera is old enough to have tapes, which get kept rather than overwritten. And the only time I see $20,000 is in my dreams. So no-one would get that sort of money for my laptop. I'd guess for most individuals, backing up their data would remove any premium value fo their laptop. In this case, the backups appear to have been stored with the laptop.

But to a business, the value of their data on a laptop (even if it is backed up elsewhere) could be massively more than the cost of the machine. There's a whole mess of legal and compensation issues if the information is required to be kept confidential.

In my latest role, I've been supplied with a work laptop. I've taken precautions. Firstly, I added ClamWin, an open source virus scanner and did a scan. It may not be as pretty as some scanners, but I don't have to pay for it so I'm content. Then I added TrueCrypt (again, no dollars required) and created a couple of encrypted volumes. Any work data and documents can get recorded in those. Next of the freebies was Eraser and a single pass overwrite of unused diskspace. I set the PC to "Clear Virtual Memory Pagefile" on shutdown, and disabled hibernate too.

I won't fool myself that this is bullet-proof. I read the Security Monkey blog (and recommend it heartily), and should be looking at whole-disk encryption for a better level of lockdown.
However these precautions are sufficient for me to be able to sleep at nights knowing that someone casually browsing the disks won't pick up anything.

Database 11g and Apex by default

I've spent a few weeks working with Apex. Or more accurately, it was the older HTMLDB version with an upgrade not expected for another few months.

I was intrigued by the security model, that the apparently lowly privileged session on the database was able to handle all the database inserts, updates and so on without any special grants.

There is a super version of DBMS_SQL called DBMS_SYS_SQL with a very powerful PARSE_AS_USER function. That much is common knowledge to anyone interested in Oracle security (black or white hat). A user with execute access to DBMS_SYS_SQL is anyone they want to be. It is a scary privilege. It is granted directly to the FLOWS_nnnnn schema, which means that invoker rights procedures owned by that schema run with that privilege. FLOWS_nnnnn procedures are executable by the database user used for running Apex applications. That's the magic that allows the lowly account used for Apex to ignore the lack of specific grants.

It is a very scary privilege when you have sessions routinely logged onto the database with that level of access and using it (so you can't even AUDIT it usefully).

There's a whole bunch of settings that tell Apex which schemas can, should and should not be used for all the SQL and PL/SQL coded into the Apex application so that applications, developers and even Apex administrators don't get all high and mighty. Also the FLOWS_nnnnn user is locked and bolted. But if you have ever been worried about the SQL injection flaws fixed in Oracle's Quarterly Patches, you should be thinking harder about this schema. Those flaws are often where SQL can get executed where Oracle didn't expect SQL. Oracle can (and does) use tools to help identify those. Apex is all about executing your application's SQL that Oracle don't even know about.

I'm not saying that your Apex applications are vulnerable to SQL injection in ways that other Oracle applications aren't. Maybe I am. Apex developers do need to realise that, in someway, every PL/SQL and SQL they write in Apex is being executed dynamically, and use of substitution variables needs to be carefully considered, reviewed, monitored and preferably shredded.

But my main concern is that the FLOWS_nnnn API is a whole new front on the SQL injection war. According to AMIS a default 11g database install will include Apex. I'd prefer the other way. If a business plans on using Apex, they can install it. If they don't plan on using Apex (or even know what it is), then don't give it to them.

Some of the beta-talk about 11g has mentioned that the security of some of the database packages are being beefed up to allow fine-grained grants (eg allowing UTL_HTTP only to specified IP addresses). I hope that DBMS_SYS_SQL is given similar functionality. Security would definately be improved by allowing the DBA to limit the FLOWS_nnnnn to running SQL as pre-defined users. Of course the Apex code may find a different security mechanism for its SQL.

I like Apex. I agree with Noons that it is a very exciting development from Oracle. But in the words of the not-so-ancient not-so-Chinese curse, it may lead us to live in Interesting Times.

Microsoft Excel, CSV and leading Zeros.

If you've worked with an application that outputs plain CSV files for users, you may have encountered the issue where Excel will turn any value that looks vaguely numeric into a number cell. Sometimes it just makes the alignment look odd, other times you may lose some leading zeros or trailing zeroes after a 'dot' when you weren't expecting it.

It's not a bug in Excel. Excel deals with spreadsheets which are just a collection of independent cells. Just because a cell is surrounded by numbers doesn't mean it should be a number, or vice versa. It is an annoyance though.

I was working with Apex, and needed a routine to extract some data into Excel. I didn't want this issue to crop up, and I also was looking for a way to have the columns sized automatically. So like any decent Oracle developer looking for a solution, I started at AskTom, and dug up owa_sylk

Then I tried it and found it mucked up with strings and numbers too. In this case it was a to_number test in the PL/SQL causing the problem, and not Excel, so I switched the code to use the col_type from the DBMS_SQL.DESCRIBE.

Unfortunately the SYLK format is under-documented (at least as far as I can Google). I've put a couple of comments in where I have worked out what does what, but if you are going to use it you may have to put some effort in. For example, I added some code to allow for a carriage return in a column header based on making the change in Excel and saving it as a .slk and seeing what it put in. I've no idea whether it will work for other spreadsheet applications, or break them. If you have some spare money, googling sylksheets will turn up an organisation that are willing to explain it.

I'll point out another couple of 'More Excel-than-CSV' solutions from the Apex forums, one being Denes' package using HTML here and an XML based solution here

create or replace
package owa_sylk as
--
  type owaSylkArray is table of varchar2(2000);
--
  procedure show(
      p_query         in varchar2,
      p_parm_names    in owaSylkArray default owaSylkArray(),
      p_parm_values   in owaSylkArray default owaSylkArray(),
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 65000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' );
--
  procedure show(
      p_cursor        in integer,
      p_sum_column    in owaSylkArray  default owaSylkArray(),
      p_max_rows      in number     default 65000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' );
/*
  Copyright (c) 1991, 1996, 1997 by Oracle Corporation
    NAME
      owasylk.sql - Dump to Spreadsheet with formatting
   DESCRIPTION
     This package provides an API to generate a file in the
     SYLK file format.  This allow for formatting in a
     spreadsheet with only a ascii text file.  This version
     of owa_sylk is specific to Oracle8.
   NOTES

   MODIFIED     (MM/DD/YY)
     clbeck      04/08/98  - Created.
                 May/2007 - Use col_type, remove blank header line and 1st col.
*/
--
end owa_sylk;
/
create or replace
PACKAGE BODY owa_sylk AS
--
    g_desc_t    DBMS_SQL.desc_tab2;
    g_lengths   owa.vc_arr;
    g_sums      owa.vc_arr;
--
--
    PROCEDURE debug (p_text IN VARCHAR2) IS
    BEGIN
        null;--For any debugging tracing you may use
    END debug;
    --
    PROCEDURE p( p_str IN VARCHAR2 ) IS
    BEGIN
        IF p_str IS NOT NULL THEN
            htp.p( p_str );
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in p');
            RAISE;
    END p;
    --
    PROCEDURE print_comment( p_comment VARCHAR2) IS
        --This appears to be a debugging routine to include comment lines in output
    BEGIN
        debug(p_comment);
        RETURN;
        p( ';'  chr(10)  '; '  p_comment  chr(10)  ';' );
    END print_comment;
    --
    FUNCTION build_cursor(
        p_query     IN VARCHAR2,
        p_variable  IN owaSylkArray,
        p_bind_val  IN owaSylkArray ) RETURN INTEGER
    IS
        v_cursor INTEGER := DBMS_SQL.OPEN_CURSOR;
        i NUMBER := 1;
    BEGIN
        dbms_sql.parse (v_cursor, p_query, dbms_sql.native);
        WHILE i <= p_variable.count LOOP
            DBMS_SQL.bind_variable( v_cursor, p_variable(i), p_bind_val(i) );
            i := i + 1;
        END LOOP;
        RETURN v_cursor;
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error '||sqlerrm||' in build_cursor');
            RAISE;
    END build_cursor;
--
--
    FUNCTION str_html ( p_line IN VARCHAR2 ) RETURN VARCHAR2 
    IS
        v_out_line  VARCHAR2(32767) := NULL;
        v_char      VARCHAR2(1);
        in_html     BOOLEAN         := FALSE;
    BEGIN
        IF p_line IS NULL THEN
            RETURN p_line;
        END IF;
        FOR I IN 1 .. LENGTH( p_line ) LOOP
            v_char := substr( p_line, i, 1 );
            IF v_char = '>' AND (in_html = TRUE) THEN
                in_html := FALSE;
            ELSIF v_char = '<' AND (in_html = FALSE) THEN
                in_html := TRUE;
            END IF;
            --
            IF NOT in_html AND v_char != '>' then
                v_out_line := v_out_line || v_char;
            END IF;
        END LOOP;
        --
        RETURN v_out_line;
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error '||sqlerrm||' in str_html');
            RAISE;
    END str_html;
--
    PROCEDURE print_heading(
        p_font          IN VARCHAR2,
        p_grid          IN VARCHAR2,
        p_col_heading   IN VARCHAR2,
        p_titles        IN owaSylkArray,
        p_show_null_as  IN VARCHAR2 )
    IS
        l_title varchar2(2000);
        v_line  VARCHAR2(32767);
        v_header_row_height NUMBER := 1;
    BEGIN
        p( 'ID;ORACLE' );
        print_comment( 'Fonts' );
        --Size 10, use M160 for size 8, M240 for size 12 etc
        p( 'P;F'  p_font  ';M200' );
        p( 'P;F'  p_font  ';M200;SB' ); --Style
        p( 'P;F'  p_font  ';M200;SUB' ); --Style Bold Underline
        --
        print_comment( 'Global Formatting' );
        IF upper(p_grid)!='YES' THEN
            p('F;G');
        END IF;
        IF upper(p_col_heading)!='YES' THEN
            p('F;H');
        END IF;
        p(v_line);
        FOR i IN 1 .. g_desc_t.COUNT LOOP
            --(;F) Format (G) General (0) Zero decimal Places (G) Text left/Numbers right
            --(;S) Style (M) ? (0) 1st line of 'P's [because it is zero based]
            p( 'F;C'  to_char(i)  ';FG0G;SM0' );
            IF p_titles.exists(i) THEN
                l_title := p_titles(i);
            ELSE
                l_title := g_desc_t(i).col_name;
            END IF;
            IF instr(l_title,chr(10)) != 0 THEN
                v_header_row_height := greatest(v_header_row_height,
                    (length(l_title)+1) - length(replace(l_title,chr(10))));
            END IF;
        END LOOP;
        --
        print_comment( 'Title Row' );
        --(;F) Format (G) General (0) Zero decimal Places (C) Centre
        --(;S) Style (M) ? (2) 2nd line of 'P's [because it is zero based]
        IF v_header_row_height = 1 THEN
            p( 'F;R1;FG0C;SDM2' );
        ELSIF v_header_row_height = 2 THEN
            p( 'F;R1;FG0C;SDM2;M540');
        ELSE
            p( 'F;R1;FG0C;SDM2;M800');
        END IF;
        p( 'C;Y1;X1'); --Set default starting point
        --Title Lines
        FOR i IN 1 .. g_desc_t.COUNT LOOP
            g_sums(i) := 0;
            IF p_titles.exists(i) THEN
                l_title := p_titles(i);
            ELSE
                l_title := g_desc_t(i).col_name;
            END IF;
            IF instr(l_title,chr(10)) = 0 THEN
                g_lengths(i) := greatest(length(l_title),nvl(length(p_show_null_as),0));
            ELSE
                g_lengths(i) := greatest(instr(l_title,chr(10)),
                                    length(substr(l_title,instr(l_title,chr(10)))),
                                    nvl(length(p_show_null_as),0));
            END IF;
            l_title := replace(l_title,chr(10),chr(27)' :');
            p( 'F;X'   to_char(i));
            p( 'C;K"'  l_title  '"' );
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in print_heading');
            RAISE;
    END print_heading;
--
    FUNCTION print_rows(
        p_cursor        IN INTEGER,
        p_max_rows      IN NUMBER,
        p_sum_columns   IN owaSylkArray,
        p_show_null_as  IN VARCHAR2,
        p_strip_html    IN VARCHAR2 ) RETURN NUMBER
    IS
        v_row_cnt   NUMBER          := 0;
        v_line      VARCHAR2(32767) := NULL;
        n           NUMBER;
        v_col_value VARCHAR2(32767);
    BEGIN
        LOOP
            EXIT WHEN v_row_cnt >= p_max_rows;
            n:= DBMS_SQL.FETCH_ROWS( p_cursor );
            EXIT WHEN n = 0;
            --
            v_row_cnt := v_row_cnt + 1;
            print_comment( 'Row '  v_row_cnt );
            p( 'C;Y'  TO_CHAR(v_row_cnt+1) ); --Add 1 to allow for header row
            --
            FOR col_seq IN 1 .. g_desc_t.COUNT LOOP
                -- Get the value
                dbms_sql.column_value( p_cursor, col_seq, v_col_value );
                --Remove line breaks and semi-colons
                v_col_value := translate( v_col_value,  chr(10)chr(9)';', '   ' );
                IF upper( p_strip_html ) = 'YES' THEN
                    v_col_value := str_html( v_col_value );
                END IF;
                -- Output the value
                v_line := 'C;X'  to_char(col_seq)  ';K';
                IF v_col_value IS NULL THEN
                    v_line := v_line '"'p_show_null_as'"';
                ELSIF g_desc_t(col_seq).col_type = 2 THEN --This is a number column
                    v_line := v_line  v_col_value;
                ELSE
                    v_line := v_line '"'v_col_value'"';
                END IF;
                p( v_line );
                -- Keep track of the largest length
                g_lengths(col_seq) := greatest( nvl(length(v_col_value),0 ), g_lengths(col_seq) );
                -- Keep the sums going 9if required)
                IF v_col_value IS NOT NULL
                AND p_sum_columns.EXISTS(col_seq) THEN
                    IF UPPER( p_sum_columns(col_seq)) = 'Y' THEN
                        BEGIN
                            --Remove the currency and thousands separators
                            n := to_number( replace(ltrim(v_col_value,'$'),',') );
                            g_sums(col_seq) := g_sums(col_seq) + n;
                        EXCEPTION
                            WHEN value_error THEN null; --Not a number
                        END;
                    END IF;
                END IF;
            END LOOP;
            --
        END LOOP;
        RETURN v_row_cnt;
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in print_rows at 'v_row_cnt);
            RAISE;
    END print_rows;
    --
    PROCEDURE print_sums(
        p_sum_columns  in owaSylkArray,
        p_row_cnt      in number ) IS
    BEGIN
        IF p_sum_columns.count = 0 THEN
            RETURN;
        END IF;
        --
        print_comment( 'Totals Row' );
        p( 'C;Y'  to_char(p_row_cnt + 4) );
        p( 'C;X1;K"Totals:"' );
        --
        FOR i IN 1 .. p_sum_columns.COUNT LOOP
            IF UPPER(p_sum_columns(i)) = 'Y' then
                p( 'C;X'  to_char(i+1)  ';ESUM(R3C:R'
                                to_char(p_row_cnt+2)  'C)' );
            END IF;
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in print_sums');
            RAISE;
    END print_sums;
--
    PROCEDURE print_widths( p_widths owaSylkArray ) IS
    BEGIN
        print_comment( 'Format Column Widths' );
        p( 'F;W1 1 7' );
        FOR i IN 1 .. g_desc_t.COUNT LOOP
            IF p_widths.exists(i) THEN
                p( 'F;W'  to_char(i)  ' '
                            to_char(i)  ' '
                            to_char(to_number(p_widths(i))) );
            ELSIF g_sums.exists(i) THEN
                p( 'F;W'  to_char(i)  ' '
                            to_char(i)  ' '
                            greatest( g_lengths(i), length( g_sums(i) )));
            ELSE
                p( 'F;W'  to_char(i)  ' '
                            to_char(i)  ' '
                            g_lengths(i));
            END IF;
        END LOOP;
        p( 'E' );
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in print_widths');
            RAISE;
    END print_widths;
--
    PROCEDURE show(
        p_cursor            IN INTEGER,
        p_sum_column        IN owaSylkArray DEFAULT owaSylkArray(),
        p_max_rows          IN NUMBER       DEFAULT 65000,
        p_show_null_as      IN VARCHAR2     DEFAULT NULL,
        p_show_grid         IN VARCHAR2     DEFAULT 'YES',
        p_show_col_headers  IN VARCHAR2     DEFAULT 'YES',
        p_font_name         IN VARCHAR2     DEFAULT 'Courier New',
        p_widths            IN owaSylkArray DEFAULT owaSylkArray(),
        p_titles            IN owaSylkArray DEFAULT owaSylkArray(),
        p_strip_html        IN VARCHAR2     DEFAULT 'YES' )
    IS
    --
        l_row_cnt   NUMBER;
        l_col_cnt   NUMBER;
        l_status    NUMBER;
        v_val       VARCHAR2(32767);
    BEGIN
        dbms_sql.describe_columns2( p_cursor, l_col_cnt, g_desc_t );
        --
        FOR i IN 1 .. g_desc_t.COUNT LOOP
          dbms_sql.define_column( p_cursor, i, v_val, 32765);
        END LOOP;
        --
        print_heading( p_font_name,
                       p_show_grid,
                       p_show_col_headers,
                       p_titles,
                       p_show_null_as );
        l_status := dbms_sql.execute( p_cursor );
        l_row_cnt := print_rows(
                       p_cursor,
                       p_max_rows,
                       p_sum_column,
                       p_show_null_as,
                       p_strip_html );
        print_sums( p_sum_column, l_row_cnt );
        print_widths( p_widths );
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in show (cursor)');
            RAISE;
    END show;
--
    PROCEDURE show(
        p_query             IN VARCHAR2,
        p_parm_names        IN owaSylkArray DEFAULT owaSylkArray(),
        p_parm_values       IN owaSylkArray DEFAULT owaSylkArray(),
        p_sum_column        IN owaSylkArray DEFAULT owaSylkArray(),
        p_max_rows          IN NUMBER       DEFAULT 65000,
        p_show_null_as      IN VARCHAR2     DEFAULT NULL,
        p_show_grid         IN VARCHAR2     DEFAULT 'YES',
        p_show_col_headers  IN VARCHAR2     DEFAULT 'YES',
        p_font_name         IN VARCHAR2     DEFAULT 'Courier New',
        p_widths            IN owaSylkArray DEFAULT owaSylkArray(),
        p_titles            IN owaSylkArray DEFAULT owaSylkArray(),
        p_strip_html        IN VARCHAR2     DEFAULT 'YES' ) IS
    BEGIN
        show( p_cursor => build_cursor( p_query, p_parm_names, p_parm_values ),
                p_sum_column => p_sum_column,
                p_max_rows => p_max_rows,
                p_show_null_as => p_show_null_as,
                p_show_grid => p_show_grid,
                p_show_col_headers => p_show_col_headers,
                p_font_name => p_font_name,
                p_widths => p_widths,
                p_titles => p_titles,
                p_strip_html => p_strip_html );
    EXCEPTION
        WHEN OTHERS THEN
            debug('Error 'sqlerrm' in show (query)');
            RAISE;
    END show;
--
END owa_sylk;
/

And from Apex, you can call this with a process based on the following PL/SQL block.

create or replace
package owa_sylk as

declare
  v_filename varchar2(50);
begin
  htmldb_application.g_page_text_generated := true;
  wwv_flow.g_excel_format := true;
  --
  v_filename := 'extract_'to_char(sysdate,'YY_MM_DD')'.slk';
  htp.p('Content-type: text/spreadsheet');
  htp.p('Content-Disposition: attachment; filename="'v_filename'"');
  owa_util.mime_header( 'text/spreadsheet');
  --
  owa_sylk.show(
    p_query =>
      'select table_name "Table 'chr(10)'Name", 'chr(10)
      '  column_id "Column Position", column_name "Column Name", 'chr(10)
      '    data_type "Data Type" 'chr(10)
      ' from user_tab_columns order by 1,2');
  --
  owa_util.http_header_close;
end;

Humour:My First SQL Primer

Oracle security meister Pete Finnegan has become a Dad.
He actually said "Now all that needs to be done is to start to teach him about Oracle."

My four-year old son started school back in January. Most nights now I spend 10 to 15 minutes with him and a book with a title like "Grandma" or "My Letter" .
One night a few weeks back, after a beer or two, I was struck by an idea. I couldn't resist.
I now present the unofficial and unsupported "My First SQL Primer"
Yes, teach your toddler SQL, and get him (or her a headstart) in Oracle.

Okay, I've had to play with the syntax a bit, but this is an age-group who think the Wiggles are the best music ever. But it still beats "See the red ball" at story time.

Changing jobs in Sydney

Like Howard (who is following Doug), I am also on the cusp of moving jobs.
For the past eight and a half years, I've been contracting in Australia. Initially, as my wife and I did some travelling, contracting was more fitting to the moving around. After we settled down in Sydney, it just carried on out of habit. Admittedly since I've only had about 4 contracting roles in the last six years the issue didn't come up too often.

After over 18 months in my current workplace, I decided it was time for a fresh challenge. A good, permanent, opportunity arose with a consulting firm based in North Sydney (and Melbourne and Brisbane). The company impressed me with its commitment to quality (starting with ISO 9001, but backed up with phrases like"Money Back Guarantee" and "Lifetime Warranty") and the job should allow me to keep my Oracle developer focus and still get some variety as well. It looks like my first assignment there will involve some Application Express, which should prove interesting. I'll be able to weigh in on the JDeveloper vs Apex debate.

[As an aside, the 2-Day Apex 3.0 Developer's Guide refers to the embedded PL/SQL gateway in "Oracle Database 11.1 or higher or Oracle Database 10g Express Edition". Is this an indication that that the database version naming is being overhauled, with no i,g or f suffix for v11 ?]

Tutorial on the MODEL clause

Joe Fuda has started up a new resource for Oracle developers. SQL Snippets looks like one to keep an eye on. I liked the tutorial on the MODEL clause which gives a clear explanation of the components.

The only thing lacking is a RSS feed, but I recalled Eddie Awad mentioning PONYFISH which allows you to define a feed for a site that doesn't have one, and created one here

Pipelined functions implicitly create SQL level TYPES

In the "I learn something new every day" category, Solomon Yakobson pointed out something in a post on the Quest Pipelines forum, "under PL/SQL Tables, Nested Tables, & VARRAYS » Problem in executing Function which returns collection".

Apparently, under the sheets a PIPELINED function will create SQL types and collection types.

If you run...

CREATE OR REPLACE PACKAGE PFP_CONSTANTS_PKG IS
TYPE v_rec IS RECORD
 (
   v_date DATE,
   v_mon NUMBER(2)
 );
 TYPE v_array IS TABLE OF v_rec;
 function dummy return v_array pipelined;
END;
/

and then check USER_TYPES, you'll find that it creates three types prefixed by SYS_PLSQL_, followed by a number (which looks like it corresponds to the OBJECT_ID visible in USER_OBJECTS) and the rest of the identifier.

So if you find some types with that sort of prefix, you'll be able to track back to the package which created them.

Oracle Hacker's Handbook

My copy of the Oracle Hacker's Handbook arrived from Amazon a couple of weeks ago (and very promptly too, considering shipping from the US).
Before my review, I'll remind readers that I'm an Oracle developer not a DBA. I have an interest in Oracle security, but it has never been one of my responsibilities.

Firstly, this book makes a couple of references to the Database Hacker's Handbook (which I haven't read). If you are ordering the Oracle book, it's probably worth going for the other too, as I feel that I've missed out on something there. I'll have to save some more pennies. I would have liked to seen a page on what the DHH book covered.

For a person in charge of security for an Oracle database, it won't be a reassuring read. You'll come away wanting to revoke practically every grant in the database after reading about how vulnerabilities in standard Oracle packages can be exploited. SQL injection seems to be the primary theme of the book, recurring in most of the chapters. About the only chapters that don't involve SQL injection are coverage of the network level and of interaction between PL/SQL, SQL and the Operating System.

My main issue with the book is that it doesn't really describe counter measures. It isn't an instruction manual for DBAs on how to secure Oracle (though Oracle supply that here). While the title advertises it as a "Hacker's Handbook", the target would be someone in charge of Oracle security who needs to UNDERSTAND the approaches, weaknesses and exploits and has enough Oracle skills of their own to work out how they might apply to their environment and how they can be countered in that environment. I can safely reassure Nuno that there's no cookie cutter treatment here.

Of course, there will be people who buy it to try to crack open an Oracle database. So one other target is the DBA who wants to get management to take security seriously. Buy the book, wave it in front of them, tell them that anyone cleaning the floors in the building can go and buy this book, that you need to do X,Y and Z to stop them cracking the database... Hey, it MAY work. [To be fair, it wouldn't tell a cleaner how to crack Oracle, but if someone proficient in Oracle had a job as a cleaner....]

My only other criticisms are the pages of code listings and the appendix of default usernames and passwords. Given the code is downloadable, some of the larger listings seem like page fillers. For the appendix, a DBA who hasn't already checked and resolved default username/password issues is not going be buying this book.

For my piece, I learnt -
A bit more more about Oracle's network level (eg you can tell the difference between an eight character password and a nine character password from the network traffic)
That when it comes to mod_plsql applications, you REALLY need to lock things down
Hackers can be devious buggers
DBAs who say No to certain privileges may have good reasons (but they may not)

If you want a book that tells you how to make Oracle safe, this isn't it. If you want a book that tells you that there isn't a 'safe', just a 'safer', then you'll probably appreciate it.

Column level dependencies in 11g

One of the reported 11g enhancements is "No recompilation of dependent objects when columns added to tables"

This is going to be an interesting challenge for Oracle as it is not as easy as it first seems. Or at least if they take the simple route it's going to disguise a gotcha for some shoddy code.

Take this little procedure (a useless stub just to demonstrate the issue)

PROCEDURE prc_test (pi_location in VARCHAR2) IS
  v_num number;
BEGIN
  SELECT count(*) INTO v_num
  FROM emp e, dept d
  WHERE location = pi_location
  and dept_mgr = emp_no;
END;

A decent PL/SQL developer would immediately spot that we can't see which of emp or dept has the columns location, dept_mgr or emp_no. However, if location and dept_mgr are only in dept, and emp_no is only in emp, then the SQL will parse successfully and the procedure compiles fine.

So what will happen in 11g if 'location' or 'dept_mgr' is added to emp or 'emp_no' to dept ?

• Option 1 is that Oracle will not only maintain the dependencies on columns that exist, but also on ones that don't exist. That way it can perform the necessary invalidation. This is no different to the rows with 'NON-EXISTENT' type that appear in the user_dependencies view when you refer to objects that rely on public synonyms.

• Option 2 is that the procedure isn't invalidated but the SQL falls over if the SQL is executed.

• Option 3 is that the procedure isn't invalidated and the SQL continues to run as always....until something else forces recompilation of the procedure somewhere down the line and THEN it fails.

Options 2 and 3 aren't pretty, but I guess Oracle could get away with those without much complaint.

But what happens if a column 'pi_location' was added to emp or dept ?

A decent Oracle shop has variable naming standards in place and a big reason for that is to ensure that the names of PL/SQL variables can't conflict with column names. If every Oracle shop had that in force and guaranteed, Oracle wouldn't have a problem. If....

To accommodate this, Option 1 has to extend to recording dependencies on the names of PL/SQL variables used in SQLs. With this, as in the current versions of Oracle, the procedure is marked as invalid when the column is added. It can recompile successfully, but testing should show up the new error (and the invalidation of the procedure should prompt for it to be tested).

If Oracle doesn't maintain that dependency, then the procedure continues to run as normal (using a pi_location bind variable) until at some point it is recompiled and switches to seeing pi_location as a column and produces unexpected results. If a procedure suddenly produces unexpected results, then you tend to look at what has changed recently. Who is going to look at causes that may have been happened months ago ?

I'm not an 11g Beta tester, so I don't know what approach Oracle has taken. It may be Option 1 and we may benefit from an insight into our PL/SQL variables. It may have a totally different solution. One option would be a tentative recompile if the table structure changes, and only invalidating the original if the tentative recompile comes up different. Another approach may be to change the precedence of PL/SQL variables and column names. Currently, if you create a database function with the same name as a PL/SQL variable used in an SQL, the PL/SQL variable is still used. It is only column names which take precedence.

Whatever is offered in 11g, it's just another reason to enforce some decent variable naming standards.

Developer or DBA

Two posts had me thinking last week.

The first was Howard's mention that his employer in Sydney is looking for a ground-to-middling level DBA. The second was the Alchemist's posting on the DBA / Developer divide.

I'm a developer. I coded Pro*Cobol (when dinosaurs ruled the Earth), wrote Pro*C, developed with Oracle Forms and Reports, threw together some Perl and created buckets of PL/SQL. I've also built a reasonably good understanding of Oracle. I know what segments, extents and tablespaces are about, why we have undo and redo, and even the difference between a database and instance. I've got a shelf full of Tom Kyte books (okay, three of them, but also Jonathan Lewis and Connor McDonald and a couple of others so it is a heavy bookshelf). I have, in extremis, altered datafile and tablespace settings (but only in development environments).

In keeping with the great divide, I'll admit to not knowing an awful lot about what DBAs do all day. As one of the comments to the 'great divide' says, management seem intent on keeping developers and DBAs apart. At one place I worked, the DBAs were on a different continent with about six hours time difference.

I know their responsibilities include ensuring backups are being done in a way that they can actually be used, and that sometimes they'll copy databases, create users, run scripts to create the tables and objects that us mere developers have asked for. They are the gatekeepers to the great and secret passwords. They patch and monitor. And if there is a high database to DBA ratio, this alone can keep them very busy. But I have the feeling that a lot of DBAs do a lot of other things too. I just don't know what and I suspect the scope of DBAing varies a lot between employers.

I've assumed that DBAs know what developers do. There's a lot of time spent in specifications and requirements, trying to 'phrase' the business requirements in a manner that means they can actually be coded. More time is spent writing SQL, PL/SQL and maybe some other languages or in an interface development environment. Then there's getting the code to run properly with some testing. Finally, we wrap it all up in some sort of parcel and throw it at the DBAs and say "put this into test or production or wherever".

I've never used RMAN, OEM or Statspack, patched an Oracle installation, wouldn't even begin to recover a crashed database and I was totally lost with Howard's recent post on fracturing LUNs (which is something to do with disks, apparently). All of which means I'd learn an awful lot as a ground level DBA. I think I'd enjoy learning how to be a DBA.

I'm just not sure whether I'd enjoy BEING a DBA. The word 'administrator' isn't inspiring, the ringing of the phone at odd hours is unappealling, and the thought of someone standing over me saying, "Oh, and if we can't get this data back, we're all out of a job" is frankly scary.

I think I'll stick to development...but I did have to have a think about it.

High and Low values from *_TAB_COLUMNS

The various _TAB_COLUMNS views (user, all, dba) contain the high and low values of the column picked up when stats were last gathered on the columns.
The drawback is that they are in RAW format.
Even worse, SQL Developer doesn't display the contents of RAWs yet (but please add your voice to the feature request)
This query will translate the high and low values of the common datatypes into something more readable.

select column_name, data_type, data_length, nullable, num_distinct,sample_size,
case when data_type IN ('CHAR', 'VARCHAR2') then utl_raw.cast_to_varchar2(low_value)
when data_type = 'NUMBER' then to_char(utl_raw.cast_to_number(low_value))
when data_type = 'DATE' then
to_char(1780+to_number(substr(low_value,1,2),'XX')+to_number(substr(low_value,3,2),'XX'))||'.'||
to_number(substr(low_value,5,2),'XX')||'.'||
to_number(substr(low_value,7,2),'XX')||'.'||
(to_number(substr(low_value,9,2),'XX')-1)||'.'||
(to_number(substr(low_value,11,2),'XX')-1)||'.'||
(to_number(substr(low_value,13,2),'XX')-1)
end trans_low,
case when data_type IN ('CHAR', 'VARCHAR2') then utl_raw.cast_to_varchar2(high_value)
when data_type = 'NUMBER' then to_char(utl_raw.cast_to_number(high_value))
when data_type = 'DATE' then
to_char(1780+to_number(substr(high_value,1,2),'XX')+to_number(substr(high_value,3,2),'XX'))||'.'||
to_number(substr(high_value,5,2),'XX')||'.'||
to_number(substr(high_value,7,2),'XX')||'.'||
(to_number(substr(high_value,9,2),'XX')-1)||'.'||
(to_number(substr(high_value,11,2),'XX')-1)||'.'||
(to_number(substr(high_value,13,2),'XX')-1)
end trans_high, num_nulls, num_buckets
from dba_tab_columns
where table_name = '...'
and owner = '...'
and column_name in ('...','...')
order by column_id
/

If someone can do something for timestamps, please add a comment.

After a comment on Martin Widlake's blog from Maxim, the logic for the DATE datatype should be amended to
rtrim(
  ltrim(to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
    + (to_number(substr(high_value,3,2),'XX')-100),'0000'))||'.'||
  ltrim(to_char(to_number(substr(high_value,5,2),'XX'),'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,7,2),'XX'),'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,9,2),'XX')-1,'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,11,2),'XX')-1,'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,13,2),'XX')-1,'00')))


You can also build some wrapper functions around the procedures in DBMS_STATS (assuming you are allowed to create objects in the database). Thanks for the link Herald

create or replace function stats_raw_to_date (p_in raw) return varchar2 is
  v_date date;
  v_char varchar2(25);
begin
  dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
  v_char := to_char(v_date,'YYYY.MM.DD.HH24.MI.SS');
  return v_char;
exception
  when others then return null;
end;
/

Blogger Comment Feed

I've upgraded to the new blogger engine which should have feeds for comments as well as posts. If it works, I'd encourage all blogspotters to do the same, as comments are just as important as the original post.

The new blogger engine is tied up to Google accounts. As such, I've acquired a new blog, called Possum Port. While not up to a certain individuals menagerie of housemates or the wallabies on the Dizwell blog, my own neighbours deserve a place in the sun.