You build yourself a virtual (or real, come to that) CentOS server, on which you plan to install Oracle. You have prepared your Oracle DVD ISO in the manner I described In an earlier post here. You insert that DVD (or get the virtual machine manger to present the ISO as though a physical DVD had been inserted) and CentOS helpfully auto-mounts it. You become the oracle user and invoke the runInstaller script… and you get told
−bash: /media/CDROM/runInstaller: /bin/sh: bad interpreter: Permission denied
What’s wrong? Well, CentOS (like a lot of distros before it) has ‘broken’ auto-mount by ensuring that “noexec” permissions are set for auto-mounted CDs and DVDs. The idea is that ordinary users can’t run executables from removable media -and, in most circumstances, that might be a sensible security measure to have in place. But not in this case! In this case, the oracle user (who counts, in this context, as decidedly ‘ordinary’!) must be able to execute that runInstaller script. How then to allow that? Easy:
mount -o loop /dev/hdc /media
Those two commands (both issued as root) first dismount the automounted CD or DVD and then re-mount it as a loop device. Manually mounting the drive causes the “exec” permission to be set… and thereafter, the oracle user will be able to run the installation script without a problem.
Of course, the specifics of those commands will need to be changed to suit your local circumstances. The DVD drive might not be /dev/hdc, for example, and you might want to mount the contents of your ISO image somewhere other than /media. But hopefully, you get the idea.
Incidentally: if you don’t know what device name identifies your DVD drive, try mounting it after the drive has already auto-mounted. For example, when I ‘insert’ my ISO image, it gets mounted as /media/CDROM. So if I then try, as root, to mount /media/CDROM again, I get told:
mount: /dev/hdc already mounted on /media/CDROM
…and right at the beginning of that error message, there’s your proper device identifier.
(Note: this post was transferred from the old Dizwell dokuwiki website. Its content may no longer be relevant).
If you get your free OTN copies of Oracle 11g from here, then you’ll be the proud owner of a zip file called something like linux.x64_11gR1_database.zip. That’s perfectly fine if you don’t mind unzipping the files on your hard disk and installing Oracle from there. But if I create a virtual machine to run Oracle, I would usually prefer to be able to install Oracle from a DVD (or, at least, a DVD image in the form of an ISO), because that way I needn’t consume nearly 4GB of virtual disk space by copying the zip file to it and then unpacking it. So the question then arises, how do you turn that downloaded zip file into a functional DVD image?
Well, one thing you can’t do is use Brasero (the default Ubuntu/Gnome CD and DVD creation tool) to simply create a disk image of the extracted zip file! That’s because Oracle uses a lot of nested directories, and Brasero doesn’t let you configure the necessary extensions to the ISO standard to allow all those directories to be read properly from the finished DVD image. You’ll just get execution errors the minute you try using a DVD image that’s been constructed that way.
I am sure various GUI tools could be employed to get around this problem (and if I was a KDE user, I know that K3B can do the job with ease), but the simplest thing is for confirmed Gnome users is to issue the following command in a terminal window:
What all that means is that you’re using the program genisoimage (which should be installed by default in Ubuntu 9.04: if not, use aptitude or the Synaptic Package Manager to install it) to output a file called (in my case) ora11gx64.iso in my home’s Desktop directory. The image should use the Rockridge and Joilet extensions (which is what lets those multiple nested sub-directories work OK). The -N switch means ‘don’t append version numbers to files’, which would screw things up badly. And the -U means ‘please be relaxed about filenames which violate ISO9660 standards’. Finally, the -iso-level is set to 4, which allows very long file names. Note that the source of the entire DVD image is to be the Desktop/database directory (which will only exist, of course, if I’ve already extracted my downloaded zip file on my desktop).
The output from that lot takes just a few moments to create, but it will be usable in an Oracle installation. You can use the ISO output file directly if installing Oracle in a virtual machine; or you can use Brasero to burn it to a physical DVD.
Update: The 11g Release 2 OTN downloads come in two parts. That is, there are two separate zip files, each about 1GB in size. Those need to be unzipped into a single directory (called database) before the genisoimage command can be pointed at it. So, for that release of Oracle, you have to do the following:
Download both zip files to your desktop
Open a command prompt and unzip the linux_11gR2_database_1of2.zip file. That will create a database directory.
Now unzip the linux_11gR2_database_2of2.zip file. The contents of this file will be extracted and merged into the already-existing database directory.
Now you can create your ISO image from the Desktop/database directory, using the command shown above.
(Note: this post was transferred from the old Dizwell dokuwiki website. Its content may no longer be relevant).
My final comments on that thread were, unfortunately, censored: it’s apparently OK for Lewis to tell me that my factual critique of his nonsense is nothing but “smarmy crock” and that I should “go crawl back under your rock”, but not OK for me to make a factual rebuttal of Lewis’ claim that, in the end, I agree with him on the licensing issue. So I’ll take this opportunity to re-state the gist of what would have been my final post:
His statement that “you end up agreeing with me anyway” is mere wishful thinking on his part. He posted originally that you needed to pay for Oracle the minute more than one person made use of the OTN-downloaded database. You don’t.
He posted originally that you needed to pay for Oracle the minute you wanted to develop in a RAC or Dataguard environment. You don’t.
Every substantive point Lewis made in his original blog piece about the OTN license and its applicability I disagree with. I can understand his wanting to extricate himself from this mess of his own creation; but trying to do so by asserting agreement between us on matters about which we have diametrically opposite opinions is, frankly, ridiculous.
Short story, therefore: Lewis was wrong about the OTN License. He remains wrong. He owes people a withdrawal of and considered apology for his comments about it. But instead, he merely attacks the only person who bothered to point out his errors in entirely factual terms.
Meanwhile, I see someone called Alex Andrews has contributed to that thread yet more muck and murk, claiming that “the key licensing term for Oracle products is the following statement; “Other Upon 45 days written notice Oracle may audit the use of the program.” He concludes from this that “unless you noted the date that you downloaded the product and saved a copy of the license agreement, your compliance [is] in doubt.”
This is not the case, of course, since that statement about ‘45 days notice’ doesn’t appear once in the OTN License that is actually the topic under discussion …though, somewhat predictably, the Great Expert Lewis fails to mention this not-so minor point in his reply. All the OTN License says is, “We may audit your use of the programs”, with not a time-scale or a costs-component clause in sight. Alex’s point may well apply to full-blown Oracle licensing issues, in which case fair enough. But it’s got sod-all to do with the free OTN License that Lewis got all worked up about and decided to scare everyone else about as a result. Unfortunately, this tendency to swirl all sorts of non-relevant issues into the mix at the drop of a hat is all-too-common in “discussions” about the OTN license in particular and software licenses generally.
Incidentally, I thought Tod Tomlinson’s contribution to that thread some of the most cogent I’ve seen about the issue: “sales reps are hungry for new license opportunities and if you have a rep who is smart enough to put 2+2 together to see that helping you prove your environment will result in new license revenue for them – they’ll likely step up and help you succeed“. Or, as I would have put it, the OTN License is there to encourage you to use Oracle products and come up with applications and environments which will make use, eventually, of paid-for licenses. The sensible thing to do, therefore, is to read the OTN License in that light: permissive, liberal, encouraging. Scare stories about how trivial it is to inadvertently step outside its bounds and into paid-for territory miss that point by a wild mile.
I conclude from this latest licensing storm in a tea-cup merely that (1) there’s one born every minute; (2) anyone proclaiming themselves an expert is probably not; (3) providing you’re being honest about it, your usage of the OTN license is almost certainly just fine.
I wish people wouldn’t get so worked up about software licenses.
They’re rational documents (normally) that seek to protect the software developer’s intellectual property rights and which can be understood by most people that can think logically and calmly.They thus tend not to be evil, cynical ways of screwing a user for fees, nor unintelligible nonsense designed to make you buy things.
A year ago, we had a scare that the OTN license couldn’t be used by developers for free or for learning purposes.
I argued strongly at the time that the particular reading of the license that gave rise to that interpretation was, er… seriously wrong. In fact, I thought it perverse, since OTN is explicitly a forum for self-learners and developers, and for an OTN license to exclude both constituencies would be mighty peculiar.
Happily, OTN seemed to agree with me on the matter, because they ended up adding an explicit statement right at the top of this page to clarify the self-learning and development issues: both are fine.
Well, a year on, and a new scare arises. LewisC posts that “We run RAC… Do we need to purchase a license to develop and test in this environment?” He goes on to conclude, “That is pretty clear. [The OTN download is] Limited to one person and to one server”… and thus cannot be used by a developer team (>1 person) or to develop RAC applications (>1 server).
To reinforce his point, LewisC appeals to an unnamed “friend” for advice on the matter, and this friend refers him “to another license explanation document”… and this “document” turns out to be a PDF which very clearly states (in its footer) “[This document] may not be incorporated into any contract and does not constitute a contract or a commitment to any specific terms.” So, to seek clarification on what a license means, LewisC is pointed to a document that is definitely not part of the contract and which is therefore entirely nugatory.
I despair at this flaccid line of thinking.
If you want to know what a license means, read the license, not some other document that explicitly states it’s of no legal effect, and which isn’t part of the legal agreement you enter into when downloading OTN software, anyway.
And if you do that; if you simply stick to reading what’s in the document that actually governs the contractual relationship between you and Oracle when using OTN-sourced software, here’s what you will find.
First, the license is granted to “you”, and the license explicitly defines “you” as being either an individual or an entity. And an entity (such as a development team or an IT department) can consist of more than 1 person.
Now Gary Myers, in comments attached to LewisC’s article, points out the OTN license says the software is licensed only to “one person”. And so it does (and I missed that first time round). But it turns out the phrase “one person” is irrelevant. Again, read the actual license document. Does it define what “person” means? No it doesn’t. Therefore the word must be assigned the meaning a regular dictionary or legal or conventional usage would assign it. And the dictionary is pretty explicit about it:
Law. a human being (natural person) or a group of human beings, a corporation, a partnership, an estate, or other legal entity (artificial person or juristic person) recognized by law as having rights and duties.
“A group of human beings”. More than one person. Case closed, yer’onor.
Now to the ‘more than one server’. The license is again explicit: “The programs may be installed on one computer only”. However, the license doesn’t define what “computer” means, so it might be possible to argue that a ‘cluster’ is a computer for the purposes of the license: a computational ‘entity’ in the singular, happening to be comprised of multiple individual servers. But it in fact isn’t necessary to resort to such linguistic twists to deal with the issue.
The OTN license has to be agreed to each time you download the Oracle software. That’s one license per download, then. And if you need to run Oracle on more than one computer, you simply have to obtain more than one license… which means, you simply have to download the software more than once. Problem solved.
To answer LewisC’s original question (”Does Oracle Require a License For a Development Database?”), the answer is “of course… and it’s the OTN one, available for free, usable by more than one person, and in a RAC or Data Guard environment provided each install is performed using freshly-downloaded software”. (Slight update: these days, you only install RAC onto the first node of a cluster, and Oracle itself pushes a copy of the relevant binaries onto other nodes of the cluster -so I don’t think the ‘multiple downloads’ thing applies anyway. You download once, you install once, and the software operates to hook in other nodes as required. One install, one license… no matter how many nodes you have).
And that answer is not difficult to arrive at, if you just stick to reading the legal document you agree to when downloading the software and read it rationally and logically. Start appealing to anonymous friends or secondary (and irrelevant) documents, however, and I accept that you’ll have difficulty coming to obvious conclusions. But they remain obvious nonetheless.
Besides, if you have any experience of the range of software licenses out there, you’ll recognise the OTN for what it is: intended only to stop commercial exploitation of a free, giveaway copy of Oracle’s software. The fact that so many key words (like ‘computer’ and ‘person’) are NOT explicitly defined gives the game away: a legal document that sought to screw things down water-tight and unarguably would not let such critical terms through without careful definition. So this legal document is seeking only to establish broad limits and general red lines, not to prescribe particular behaviours with precision. Read in that light, I come back to what I was writing a year ago: please, people, stop being so paranoid and panicky! Oracle is not “out to getchya!’ and the OTN license is loosely drawn up, presumably deliberately (their lawyers know how to draft precisely when occasion calls for it), precisely so that it seeks to do only one thing unambiguously: prohibit commerical use of the software. So long as you don’t try doing that, the OTN license doesn’t get too picky about what you get up to.
No doubt Oracle will have to add yet another ‘clarification’ to their OTN page making it explicit that RAC and Data Guard are covered, but if they don’t, it will only be because they can’t be bothered modifying their web pages every time someone who doesn’t grasp the legal fundamentals over-reacts.
Now, all of the above is indeed just my interpretation of the license, and therefore it’s subject to no more certainty than someone else’s interpretation of it. Only courts of law decide on whose interpretation is the correct one, after all. But this interpretation is, at least, based on a plain reading of the words actually used in the legal document under discussion, and hasn’t had to resort to secondary documents or anonymous friends to form or shape it. Neither is it coloured by the cynicism or plain ignorance that seems to inform a number of the comments found attached to LewisC’s original piece.
Relax, people. The sky’s not falling in, and the OTN license is fine, just so long as you don’t make any money from it.
Hyper-V is Windows 2008’s answer to VMware’s ESX Server. That is, it’s ‘bare metal’ virtualisation and adding it as a role to a Windows 2008 Server is supposed to ‘lift up’ that installation so that it sits on top of a virtualisation hypervisor. In effect, your Windows 2008 install becomes a virtual 2008 install. Once that’s happened, you can use standard Windows management consoles to create additional virtual machines and manage them from within that new, virtualised, ‘parent machine’.
It’s a great idea in theory, and it’s certainly only a matter of moments to add the Hyper-V role to the server, plus an inevitable reboot. But things went downhill for me pretty rapidly after that. Yes, the management interface is pretty darned sexy, and I was able to create new virtual machines very easily, running XP and Centos 5. But they both took a long time to install, much longer than they would have in VMware Workstation. When they’d been built they seemed to run at a reasonable pace, but certainly not as fast as I would have expected. To be fair, Hyper-V can run “suitable” operating systems (i.e, Vista, 2008 itself and Suse Enterprise Server) at near-native speeds because they have the necessary code built in to enable effective interfacing with Hyper-V’s particular way of virtualising things, whereas non-enabled OSes (of which XP and Centos are both examples) are merely ’emulated’, and therefore inevitably run slower. But still: the two most important OSes to my current needs run worse than they would do in good old VMware.
What’s even worse is that, immediately after I enabled the Hyper-V role, my multimedia playback in the ‘parent’ OS (that is, my original and -to my eyes- very physical 2008 Server installation) went down the plug-hole, big time. I already mentioned how to cure audio ‘stuttering’ in the last post -but as soon as Hyper-V was installed, it was as if that step had never been taken. Without any virtual machines running at all, the mere typing of “www” into the Firefox address bar had audio playback coughing and spluttering uncontrollably. Trying to resize a window caused Foobar2000 to lock up for a good five seconds or more. Clearly not an acceptable mode of operation -especially given the fact that it was running like this on a Quad Core with 4GB of RAM. Not exactly resource-starved, in other words!
Removing Hyper-V immediately put audio playback back to the happy state it had been in before. You can, therefore, pretty much imagine that Hyper-V has gone and VMware Workstation is back. My “legacy” OSes virtualise better and faster in that anyway, and I can still listen to music and watch movies, even with two virtual machines running at the same time.
That said, I liked Hyper-V as a concept, and its implementation looked excellent. It’s just that functionally, as far as a desktop/workstation experience was concerned, it was totally unworkable. In a proper server environment, I’d expect it to be much more suitable. On the other hand, I would point out this article, which explains the inherent limitations of Hyper-V, even in proper server environments, better than I can, and in great detail, to boot.
So, no Hyper-V in a ‘workstation’ version of 2008, not if you fancy playing multimedia properly, anyway.
I had a couple of other gripes about 2008 as a desktop OS: as someone who doesn’t mind the odd game of Solitaire, the fact that no games whatsoever get installed with 2008 was a bit of a shock, albeit not exactly a show-stopper. Flight Simulator installed without fuss on TOH’s machine, and worked fine straight off the bat: better and smoother than in 64-bit XP, that’s for sure -so a definite thumbs-up from that quarter. Civilization IV fared much less well on my machine, though. First off, I declined to install DirectX 9 (as the Civ 4 installer starts off by trying to do), because Win2008 ships with DirectX 10. Big mistake: you have to add the DirectX9 libraries to Windows 2008 otherwise you just get lots of ‘direct X library files missing’ errors when you try running the program. Fortunately a re-install without skipping the DirectX9 stuff fixed that problem, though I also had to run the thing in Windows 2000 compatibility mode before anything would work properly.
Having triumphed so far, I then installed the 1.74 Civ 4 patch… which promptly made everything non-functional again. But by reinstalling DirectX9 (from here), I was actually able to get things working again even without the Windows 2000 compatibility mode. So it ended up being possible to run it “natively” and with zero glitches of any sort.
I am still Solitaire-less, but I’ll settle for rock-solid and bloody-fast with lots of eye-candy. With VMware Workstation.
I noticed yesterday that this blog had become far too technical, with lots of posts about abstruse topics such as Oracle databases and Linux distros. Time, therefore, to apologise for that and to do something about it.
First, whilst I wouldn’t normally share this sort of thing with anyone, I think it appropriate to announce the fact that two nights ago, I had my first-ever lucid dream. This is a peculiar state that I first heard about when I was at Uni in the early 1980s: you are completely asleep, you are dreaming, and you are completely aware of the fact that you are dreaming. I was totally aware of the fact, for example, that I couldn’t move my fingers (immobility being a feature of even normal dream states), and that TOH was breathing er… “rather heavily” at my side! I also knew that Byron Road County Primary School couldn’t possibly look that way these days and that I would get into trouble if I walked past the headmaster’s office without putting my head round the door and saying hello. It is, as they say, a very strange state to be in altogether.
But the wonderful feature of this odd state is that you can control what you dream about. So, naturally, I did what all lucid dreamers tend to do the first time… and jumped out of the nearest window. Then, you see, you get to fly. I mean really fly: wind through the hair, arms outstretched, diving and soaring -with not the slightest hint of an aeroplane in sight. Flight au naturel, in short, as an eagle would do it… utterly exhilerating. In fact, it was a little too exhilerating, because I was so excited by the proceedings that I woke myself up, thus bringing my maiden flight to a sudden halt! This was a bit of a shame, because I’d planned that my next stop was to the pit of a world-renowned orchestra, which I would conduct in some serious Benjamin Britten. That, however, will have to wait for the next time.
If you’ve never had a lucid dream, I would encourage you to try to have one. It even turns out that there is a website that will help you have one! I can recommend the reality checks that page suggests, by the way: I noticed, for example, that I cannot read music in a dream, the notes going all indistinct and vague (which makes the chances of me conducting a good version of anything written by Britten pretty remote, I suppose!). Next time I look at a piece of vague, indistinct music I will either think, “Lay off the Whiskey!”, or “I’m dreaming!” (or, I suppose, that I’m holding a piece of Stockhausen in my hand).
Anyway, enough of lucid dreams (though I can tell you I’m as excited at finally having one as I was getting my first glimpse, after 30+ years of trying, of Saturn and noticing that, cor blimey! it’s got rings!!)
The other thing I thought I should share by way of utterly non-technicalities is the latest from the ever-growing clan of Wallabies. Ever-growing, incidentally, because Rachel is pregnant yet again.
I’ve had some emails wondering whether we’re doing the right thing, plying these marsupials with bread. Well, I offer this by way of evidence that it’s not just bread we give them (though bread tends to be the first thing they choose to eat!):
That’s Chandler, bread, birdseed and a rather nice rocket salad… and she picked the bread herself.
But it’s not just bread and birdseed (or even rocket salad). Oh no. Boise is rather partial to something completely different:
That will be him with his head in a bag of cat food, then! That would be the Ocean Platter flavour of cat food, too. I was unaware that wallabies would eat fish, but there you go!! I’d like to be able to say that I’ve checked the ingredients for this particular brand of cat food biscuit and discovered that it’s all lovely stuff that even the fussiest of wallabies (or cats) would be mad to turn their noses up at, but I’m afraid the ingredients list starts off with “Crude Protein 30%”, descends almost immediately to “Crude Fat 15%” and even takes in “Ash 8%”. Not exactly appetising stuff, methinks… but Boise thinks otherwise, apparently.
But he can be rather more ‘refined’ about the process, too:
That’s Boise being s a little more polite about it, disdaining the bag and preferring the ceramic bowl with insightful commentary! Classy, I think!
The cats, incidentally, do not apparently mind their food being purloined by what, to them, must seem like giant rats. But then again, I wouldn’t argue with a giant rat, so I don’t suppose they’re going to start, either!
The results of all this exoitic food are plain to see:
Oh, OK, it’s nearly twilight and wallaby eyes seem to be particularly prone to what, in humans, would be called ‘red eye’ but which in the marsupial world should probably be called ’silver eye’. The real point about this picture is not the un-Earthly look of a wallaby’s eyes when reflecting flashlight but that we see Chandler in the front of the picture being vigorously, er… “stroked” by Boise right behind her… and Boise appears to be wearing a particularly satisfied grin. Yes, it’s post-coital statisfaction that looms large over Boise’s face -and it’s nice to see that Chandler kept nibbling her bread throughout the proceedings!
Exotic the food may be, in other words, but it’s certainly not putting a damper on anyone’s normal, healthy non-food appetites!
Which no doubt explains this picture:
That looks like four wallabies: Rachel, Chandler, Boise and Ben. But, in fact, Rachel is carrying yet another Joey (as yet unnamed), so that’s a picture of five of them. I’m open to names for the new member of the troupe, incidentally, though names of Friends characters are now rather passé.
So there you have it. Dreams, Wallabies and very little Databases. I hope that counts as a sufficient apology for letting the technical push aside the non-technical!
A contracted developer recently had me roll a piece of code into production that contained the following nugget:
if (orderRec.image_size >= vpMin_size or vpMin_size is null or vpMin_size = 0))
Nothing too outrageous, then: check image_size, and if it’s greater than or equal to some number supplied when the procedure is invoked, the record qualifies.
What I hadn’t noticed (black marks all round, then, because no-one else had spotted it either!) is that IMAGE_SIZE is a VARCHAR2 column. Yes, really. We’ll come to the reasons why in just a moment!
Meanwhile, we all soon found out about this when the website broke with the infamous ‘ORA-06502 character to number conversion’ error: a record with an image_size value of ‘4.7mb’ had fallen foul of the troubles that ensue when you try to perform mathematical comparisons on the phrase ‘mb’!
I suggested to the developer that we convert the column to a NUMBER data type. Can’t do that, he said, because the field had been deliberately created as free text, because (get this!) they needed to be able to see a “nice” rendering of the image size (that is, one qualified with ‘MB’ or ‘KB’ descriptions) on printed invoices. Turns out, indeed, that this field contains such size gems as “small”, “medium” and “large”!!
Instead, the developer put into production (without telling me first) this fix:
if (nvl(trim(regexp_replace(lower(orderRec.image_size), '[^[:digit:]]', '')), 1 >= vpMin_size or vpMin_size is null or vpMin_size = 0))
Now, I am not a fan of regular expressions at the best of times. They are difficult to read, harder to interpret. They make code dense and obscure -especially when (as in this case) not one word of commentary is made in code comments to explain what it’s trying to do. Moreover, they are a fairly new feature in Oracle, and I don’t like using new features unless they are compelling -providing something you couldn’t do any other way, for example. That’s mainly because Oracle has a habit of not implementing new features very well (think ‘ANSI-compliant join syntax’ in 9i, as a case in point!). I only glanced at it, but typing “REGEXP_REPLACE” into Metalink’s bug database got me 18 matches this morning -at which point, I’d just as sooner not use the things, really!
Anyway, that’s a side issue… because what really got me about this developer’s particular regular expression was that it wouldn’t work! Ostensibly, it seeks to strip out any non-numeric character from the field and turn NULLs into ‘1′ -and in those particular endeavours, it works well. The only slight fly in the ointment is that a full stop (period) counts as a non-numeric character, so that an entry of “4.7mb” gets turned into one of “47″ -inflating the size by a factor of 10 and thus rendering the comparison to ‘vpMin_size’ rather redundant!
When I pointed this out to the developer, I was sent this:
The regular expression is doing exactly what I want it to do… I cant believe we have wasted so much time discussing it.
Well, faced with such supreme confidence in his own coding abilities, what could I do, but repeat my objections to a piece of code which was (a) trying merely to work around a fundamental problem in the data model whereby free-form text was suddenly being used to perform mathematical comparisons and (b) getting it wrong by a factor of 10 in the process.
Happily, persistent paid off in this case:
After reviewing the code again, you are right in that it wasn’t working exactly as I wanted it too
Unhappily, this particular developer is reluctant to change his work habits and instead of implementing a proper numeric field to perform mathematical comparisons with, he came up with this suggestion:
when instr(lower(image_size),'kb') > 0 then to_number(nvl(trim(regexp_replace(image_size, '[^[:digit:].]', '')), 0))/1024
when regexp_instr(lower(image_size),'mb|megabytes|meg') > 0 then to_number(nvl(trim(regexp_replace(image_size, '[^[:digit:].]', '')), 0))
when isnumber(image_size) = 1 then to_number(nvl(image_size,0))
end) as image_size2
So, we get two calls to REGEXP_REPLACE to replace the original one, with an extra REGEXP_INSTR to keep things jolly. We still have the ‘strip out all non-numerics’, but by cunningly spotting whether the text being stripped out mentions ‘kays’ or ‘megs’, we can divide appropriately and come out winners. Of course, there is no mention of what to do if the user has entered “0.46Gb” instead of “460Mb”, but why quibble. As the developer puts it:
This will take care of kb (I pulled out TB because no-one would use it.)
Yup, we can just ignore the ‘TB’ (and presumably the ‘GB’ problem) because “no-one would use it”. Never mind that everyone could use it because free-text entry means exactly that: FREE. You can’t know what someone would or wouldn’t use and your code had better be able to deal with all eventualities, or it will break! Let’s just hope some user doesn’t ever a size of “4.7 m. bytes”, shall we?
My developer, however, remains sublimely confident:
This is a temporary but better workaround.
Er, no it isn’t. It’s more complex than ever, and thus no better than before! It seeks to fix the earlier error by ignoring possible conditions, rather than tackling them and thus is, again, no better than before. In fact, it makes assumptions about what things users might type into fields, which makes it much worse than before. It still doesn’t use a number field to do mathematical computations, which is the fundamental problem here.
So, to sum up.
A developer performs a mathematical computation on a field, not noticing it’s text;
When the inevitable data type conversion error pops up, his code workaround inflates image sizes by a factor of 10;
When I point this out, I am told I’m wasting his time and that the code does ‘exactly what I want it to do’;
When I re-point it out, I finally get acknowledgment that the logic flaw was not, after all, ‘exactly what I wanted’.
But instead of fixing the data model (”very, very expensive”!) we simply get more complex code than before -which still fails to deal with the logical fundamentals at all.
The DBA’s job can seem ever thus: to annoy and frustrate developers who think that their latest piece of whizz-bangery is ‘exactly what I want’ by pointing out that it probably isn’t (and definitely isn’t what the client who’s paying for it all wants!)
I must say, the old saw about DBAs and Developers being at each others’ throats is just that: a lame old joke with little truth in it. I have always got on fine with all the developers I’ve worked with, though I have seen notes of frustration in their eyes when I check their code for things I might not like instead of just rolling it ‘on trust’! My job is to slow them down, to some extent; or at least to act as a sort of ‘chamber of review’, a House of Lords (or Senate) to the developer’s House of Commons (or Representatives) -and until very recently, I would have said that all the developers it’s ever been my privilege to work with have recognised that, so there have been no major dramas.
But I do now see that there is at least one developer who will (apparently) always see the DBA as a problem, rather than a part of the solution.
Just another little cross for the DBA to bear, therefore…
Update: I’ve reluctantly agreed to another developer-proposed workaround. We’ll change the web page that is used to access this particular field. Users will now select entries from a list of purely numeric values displayed in a combo-box. The letters ‘MB’ will be hard-coded on the web form so users know what their number selection ‘means’. Only numeric data will therefore get sent to the database (though it will still be stored in a VARCHAR2 column). This effectively means a free-form text field is no longer free-form; it also means we’re pretending that a text field is actually a numeric one. But we are also not changing the structure of the table and thus potentially breaking the 1001 reports based on it -at least, not until the business makes an informed decision later on whether or not to go down the route of fixing the fundamentals. I can’t say I’m happy about it, especially because ‘making a decision later’ usually turns out to mean ‘forgetting all about it’… but at least we get rid of some distinctly iffy-looking regular expressions in the meantime.