tag:blogger.com,1999:blog-173297582011-08-17T11:12:27.082+08:00I've moved! www.jeffkemponoracle.comJeffrey Kempnoreply@blogger.comBlogger931100tag:blogger.com,1999:blog-17329758.post-59393933519124178242010-04-06T10:16:00.000+08:002010-04-06T10:16:29.473+08:00

I know how annoying it is to get these notices in your feed reader, so I'm sorry! But I've moved this blog to a new address - jeffkemponoracle.com. Please update your links and readers, thanks!

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-60887012364881165742010-03-29T10:11:00.001+08:002010-06-21T15:02:17.587+08:00

Got an email this morning from the Australian Oracle User Group... [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-7898963598364353252010-03-23T21:33:00.003+08:002010-06-22T14:00:49.760+08:00

Bobby Table's got himself a car :) [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-31920863686641718912010-03-17T13:12:00.004+08:002010-06-21T15:02:59.333+08:00

I think this would make a reasonable question for would-be database developers: [Full Article]

Jeffrey Kempnoreply@blogger.com7tag:blogger.com,1999:blog-17329758.post-53921843171100071592010-03-11T13:31:00.003+08:002010-06-21T15:03:27.892+08:00

Comment out all "DROP TABLE" commands in my scripts. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-69610003955385210592010-03-02T11:37:00.004+08:002010-06-21T15:04:01.845+08:00

As Feuerstein says, We all know that hard-coding is a bad thing in software. But most developers think of hard-coding simply as typing a literal value into your program whenever you need it. ... So what's wrong with doing this? Nothing - as long as the value is never going to change. But what's the chance of that happening? In fact, what's the chance of anything staying the same (never changing)

Jeffrey Kempnoreply@blogger.com9tag:blogger.com,1999:blog-17329758.post-10106827090392162572010-02-03T12:59:00.003+08:002010-06-21T15:04:34.662+08:00

Tom Kyte offers some astute observations regarding EAV data models in general. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-90614419311189643122010-01-24T08:17:00.005+08:002010-06-21T15:05:09.729+08:00

Every day it seems, Googlebot scans my site. You wouldn't think that would affect the performance of the site, or if at all, would make it worse, right? [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-15608357899322989722009-12-31T21:34:00.003+08:002010-06-21T15:05:43.190+08:00

Almost exactly ten years ago, as all the Y2K bug projects were quietly winding down, I'd been getting ready for a new decade, which for me was full of uncertainty and promise. It was in 2000 that I got married, and started my first job in the I.T. industry (but not in that order). Many people thought the world was going to end, but for me it felt like the dawn of a new personal era. [Full

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-5112396265519283682009-12-23T10:35:00.005+08:002010-06-21T15:06:15.618+08:00

Is it possible to change the ORDER BY on a query, if the SQL is defined in a wrapped PL/SQL package? A colleague asked me this interesting question today - and the answer was quite simply, Yes and No. [Full Article]

Jeffrey Kempnoreply@blogger.com4tag:blogger.com,1999:blog-17329758.post-20706181059797542892009-11-10T18:35:00.008+08:002010-06-21T15:06:49.382+08:00

It was great to catch up with former colleagues at the conference today. As I go from site to site I'm gradually collecting more and more former colleagues and it's great to hear how they're going and what they're working on. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-11252329539061027162009-10-26T21:44:00.008+08:002010-06-21T15:07:36.879+08:00

Problem: when Google indexes my Apex web site, it considers the following URLs to be different pages: http://www.site.com/apex/f?p=100:1:1234567890:::: http://www.site.com/apex/f?p=100:1:0:::: http://www.site.com/apex/f?p=MYAPP:1:46346346346:::: http://www.site.com/apex/f?p=MYAPP:1:34634634636:::: http://www.site.com/apex/f?p=MYAPP:HOME:46346346346:::: http://www.site.com/apex/f?p=MYAPP:HOME:0:::

Jeffrey Kempnoreply@blogger.com4tag:blogger.com,1999:blog-17329758.post-45128510292312822032009-10-23T17:12:00.005+08:002010-06-21T15:08:12.764+08:00

Finally the AUSOUG 2009 conference program is out! [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-90805385435850396182009-08-29T16:13:00.005+08:002010-06-21T15:08:40.538+08:00

Out of a discussion with a colleague at work I had two questions to answer: Can a stored outline change a query - e.g. the FROM or WHERE clauses? If a stored outline hints the query to use a function-based index, and a user doesn't have execute privilege on the function, what happens if the user runs the query? [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-58998817227899951352009-07-25T21:00:00.006+08:002010-06-21T15:09:10.719+08:00

This will create a file called "emp.xls" with a nicely formatted spreadsheet based on the query specified. The header row is based on the column names, and is frozen. The column widths are set according to the max width of the data. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-71228593022730674832009-07-24T20:20:00.005+08:002010-06-21T15:09:59.241+08:00

An excellent use of Oracle's inbuilt formatting function - much simpler than parsing the input and multiple concatenation operators... [Full Article]

Jeffrey Kempnoreply@blogger.com3tag:blogger.com,1999:blog-17329758.post-11114296316844752822009-06-29T09:45:00.003+08:002010-06-21T15:10:35.445+08:00

I've learned that with Python, if something takes more than a few lines to write, there's a very good chance that there's another way to do it but with one or two lines of code. Chances are someone else has already come across a similar problem, and written a module to solve it - and it's just a matter of importing their module and reusing it. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-44727874296861863652009-06-27T20:00:00.024+08:002010-06-21T15:11:06.876+08:00

Quite a few months ago I read Wrapping Your Brain Around Oracle + Python and was somewhat interested but too busy to look into it in depth. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-19135906802245664272009-06-05T10:56:00.002+08:002010-06-21T15:11:39.524+08:00

Short-circuit evaluation! [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-25108267347906996292009-05-22T08:54:00.003+08:002010-06-21T15:12:24.702+08:00

"A woman asks her husband, a programmer, to go shopping:..." [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-88424375361623181032009-05-11T21:14:00.006+08:002010-06-21T15:13:00.645+08:00

When I first learned to drive, all our cars were automatics - and so that's all I learned to drive. This never posed an issue to me until I married Rosalie who owns a manual. She tried to teach me, and I managed - kind of. It wasn't until I had a chat with my uncle-in-law, who's a mechanic, that it "clicked" with me. This is because he explained to me basically how the gear-change system works,

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-45164897445282943542009-03-27T17:15:00.006+09:002010-06-21T15:13:30.665+08:00

I was working happily on my laptop in the living room, kids playing on the rug, birds were singing, et cetera. All of a sudden, a blood-curdling scream erupts from the office. It was my wife. "Jeff! Is there an "undo" function in the roster program??!?!?" Uh oh. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-19582078211306823152009-03-11T23:38:00.007+09:002010-06-21T15:14:17.723+08:00

Interesting to see openings specifically for Apex developers in Perth. For example: Oracle Developer - APEX (Applications Express) ... [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-12325124161022213702009-02-24T10:39:00.006+09:002010-06-21T15:14:54.990+08:00

I've been trying a number of strategies to improve the performance of a very complex form (Oracle Forms 6i) currently in development. We've already done a fair amount of work making the code as efficient as possible, while still being reasonably maintainable, so there don't seem to be any more low-hanging fruit we can pick off easily. [Full Article]

Jeffrey Kempnoreply@blogger.com6tag:blogger.com,1999:blog-17329758.post-81178221040683836512008-10-18T14:30:00.006+08:002010-06-21T15:15:53.791+08:00

I wanted to allow users to select one or more colours from a list, and to control the order of the colours, so I've used a Shuttle item. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-10849434199526920342008-10-13T14:00:00.007+08:002010-06-21T15:16:26.211+08:00

There are several reasons you might get this error in Oracle Forms, e.g. another user has modified the record before you saved it (as the error message suggests), or a table trigger has modified the record and your form's DML Returning Value is set to No. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-62105819602344657112008-10-08T09:01:00.011+08:002010-06-21T15:17:18.421+08:00

One of the worst times to get an ear infection, I learnt, is the night before you present at a conference. I hardly got any sleep and I’m deaf in one ear, making my voice echo in my head. [Full Article]

Jeffrey Kempnoreply@blogger.com9tag:blogger.com,1999:blog-17329758.post-30447558803019373592008-10-02T12:17:00.004+08:002010-06-22T08:32:28.376+08:00

I've been in bed the last few days with a virus, but I should be ok in time for Monday. Had a look through the programme, and again it's chocablock with very interesting titles. Quite a bit of Development Blue, but not so much DBA Cyan this time (unfortunately, in my opinion). There's a lot of Apex sessions which I'm guessing reflects the growth of interest in Apex development here in Perth. [

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-23392699934352228222008-08-07T20:42:00.004+08:002010-06-22T08:33:13.157+08:00

at the AUSOUG conference this year. First time for me, so please be gentle :) [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-3631646506205877242008-08-04T08:18:00.007+08:002010-06-22T08:33:45.741+08:00

It's a really bad thing to do, but that's ok because "we put comments in that say it's bad". [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-10487279455983580232008-07-14T15:39:00.009+08:002010-06-21T14:48:33.005+08:00

Want to run a prefix search using the LIKE operator, but your search pattern includes wildcards like _ or %? A common example is searching the v$parameter view for parameters starting with the underscore (_) character. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-58744957150858823232008-05-08T21:10:00.004+08:002010-06-22T08:34:20.538+08:00

Something to file under "pointless fun"... Allow your users to feel like they're part of a community by letting them know who else is using the app at the same time. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-243302158662317852008-05-02T09:23:00.001+08:002010-06-22T08:34:55.532+08:00

A work colleague needed to make a change to a report and came up against a brick wall. He knew what he wanted to express in SQL, but Oracle wouldn't accept his syntax. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-4889501600595321642008-04-21T20:04:00.006+08:002010-06-22T08:35:34.623+08:00

[Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-53579075322455276332008-04-21T09:51:00.002+08:002010-06-22T08:36:02.498+08:00

How many features can you use in one SQL query to solve a problem? (that is, how many necessary features do you need without deliberately obfuscating your code) [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-36969170325233340172008-04-11T06:58:00.005+08:002010-06-22T08:36:35.040+08:00

I had this problem with an Apex application I'm building, and finally found the cause this morning, so I thought I'd share it. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-73759189270282462082008-03-10T21:28:00.007+09:002010-06-22T08:37:02.833+08:00

I used to assume that whenever a TOO_MANY_ROWS exception is raised, the target bind variables would be left untouched. Until today I've never written any code that relies on the bind variables being in any particular state when a TMR exception is raised, so was surprised. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-80596540097777812942008-01-17T15:56:00.004+09:002010-06-21T14:49:43.558+08:00

A tester for our client raised a problem where searches like 'ABC%' would perform satisfactorily, but searches like '%ABC' would time out. Of course, the reason is that the index on the column cannot be used if a wildcard is at the start of the string, so a full table scan is always performed. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-73892536355486191602008-01-04T09:02:00.002+09:002010-06-22T08:37:35.177+08:00

Having done this myself using only my ten fingers, I can verify it's as easy as it looks... [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-12570044243321024582007-11-22T21:42:00.003+09:002010-06-22T08:38:12.463+08:00

It was mentioned at the AUSOUG conference by one of the speakers that he couldn't get the database to use a function-based index based on the regexp functions. I thought this was a little strange so decided to try for myself. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-37270964798797668012007-11-22T16:10:00.002+09:002010-06-22T08:38:35.297+08:00

This query crashes the session when it's run or parsed. I haven't checked if this appears in other versions or not. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-24459746191280428362007-11-21T20:09:00.003+09:002010-06-22T08:39:03.353+08:00

I enjoyed today's programme very much. John Garmany gave a very easy-to-understand intro to the world of Regular Expressions. Luie Matthee spoke about virtualised Oracle 10g instances on Red Hat Enterprise Linux 5, most of which went over my head but I'm learning. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-3980938434442896632007-11-20T12:43:00.002+09:002010-06-22T08:39:40.985+08:00

A great start to the Perth conference, great to catch up with old and not-so-old friends. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-44933058853661449052007-11-03T15:53:00.003+09:002010-06-22T08:40:44.835+08:00

This is a followup on my earlier series on setting up Apex on Linux. In it I described how I used Apache web server in front of Apex, instead of accessing Apex directly via OWA (this was so that I could use the same port to serve ordinary web pages and files via HTTP). [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-82093593712241255922007-09-27T18:12:00.001+08:002010-06-22T08:41:56.606+08:00

Dirt Cheap Oracle step #6 of 6: Migrate the Apex applications [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-7461097754104446992007-09-26T22:36:00.004+08:002010-06-22T08:42:36.874+08:00

The link here is to a diagram that illustrates what I talked about in recent posts, how to set up a Dynamic DNS service and how the various IP addresses and ports relate to your router and devices connected to it. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-44337372432441818362007-09-25T06:44:00.005+08:002010-06-22T08:43:27.323+08:00

I want people to get to both my Apache web server (port 8000) as well as Apex (port 8080) via the default HTTP port (80) through the router, but you can't map one port to two ports - otherwise, how is the router to know which port to pass requests on to? There's a good reason why I've chosen the web server to get all the traffic, and that's because I have an idea how to get Apache to pass traffic

Jeffrey Kempnoreply@blogger.com7tag:blogger.com,1999:blog-17329758.post-48942262382871781872007-09-25T06:40:00.003+08:002010-06-22T08:44:27.688+08:00

I can access Apex from the Linux box, but not from my WinXP machine across the network. This is because the firewall in Linux by default blocks most ports. I've got Apex (using EPG) listening on port 8080, and Apache web server (for the static web pages and images) listening on port 8000, so I open up those two ports (System menu -> Administration -> Security Level & Firewall). [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-36520859937294653822007-09-21T20:20:00.002+08:002010-06-22T08:44:59.482+08:00

Ok kids, play time is over, it's time to install Oracle, upgrade Apex and then turn out the lights for a good night's sleep. After double-checking that they haven't released 11g XE yet :(, I download Oracle XE 10gR2, as well as Apex 3.0 for good measure (no Apex 4.0 yet either...). [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-14509034126662925352007-09-20T21:37:00.002+08:002010-06-22T08:45:27.840+08:00

Ok, I've got all the hardware I need (took a few minutes with a vacuum cleaner to carefully suck out as much of the dust that was caked in there), and hooked it all up. The computer starts up ok, but it needs an OS - the hard disk was wiped clean of all government secrets, as expected. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-86172167349877428912007-09-19T18:09:00.003+08:002010-06-22T08:46:14.300+08:00

My main computer, used by my wife as well, runs XP and I want to remove Oracle from it because it takes too long to boot up, and Oracle can suffer when I run CPU-intensive applications on it. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-89658856354402145572007-09-18T21:01:00.002+08:002010-06-22T08:46:41.637+08:00

Seeing how Oracle XE and Linux is free, I want to see for just a minimum of outlay I could get a working database and web server running. I am also keen to give Linux a try; I've been a Windows user ever since my dad upgraded his computer from MS-DOS 3.3 to Windows 3.1, and while I've always worked for companies that used Unix I've never really had to learn much about it. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-82165548878976742262007-09-09T07:58:00.001+08:002010-06-22T13:38:19.736+08:00

I've got two computers at home both running Apex, and when I log into Apex the page looks exactly the same - except for the IP address in the URL, which is just slightly different. If you're forgetful like me it's easy to confuse which machine I'm logged in to. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-52586367537647681552007-08-24T08:39:00.003+08:002010-06-21T14:49:09.607+08:00

Let's just call it Legacy because I'm not going to say what the source is. It is a single-user desktop application that after a little investigation (i.e. searching the online forum for the app) was found to be storing its data in tables readable by MS Access. I wanted to get at this data, analyze it, maybe do some smart things to it, and then present it via Apex. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-27794356399471925362007-08-08T13:09:00.002+08:002010-06-22T13:40:55.801+08:00

For you Oracle + D&D/RPG fans out there... "Among my possesions was a +1 Ring of tuning..." [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-77127039441130352822007-07-17T11:54:00.002+08:002010-06-22T13:41:22.416+08:00

I just noticed the difference - I was wondering why I couldn't see the virtual columns (i.e. columns that support function-based indexes) on a table, and it was because I was querying USER_TAB_COLUMNS. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-58391811271129866132007-07-04T14:23:00.001+08:002010-06-22T13:41:47.011+08:00

I was annoyed that to get to my main PL/SQL page I have to type in a full URL like this: http://host:7777/mydad/home [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-51733542190918621412007-07-02T15:53:00.006+08:002010-06-22T13:42:13.501+08:00

I managed to get Apex 3.0 working on Oracle 10.2.0.1 using the embedded PL/SQL gateway (i.e. without installing Apache), contrary to the advice given here. I used apex_epg_config instead of apex_epg_config11. For it to work, however, I had to remove some security on XDB so it's not a recommended option. [Full Article]

Jeffrey Kempnoreply@blogger.com14tag:blogger.com,1999:blog-17329758.post-49295476716813431042007-06-28T08:25:00.003+08:002010-06-22T13:42:43.509+08:00

I've had a few blogs in my head the last few months but haven't had a chance to write them up. This is partly affected by the release cycle at work, and some extra-curricular volunteer work, but mostly by the birth of our second child. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-63254849369273835692007-06-22T09:42:00.002+08:002010-06-22T13:43:06.687+08:00

Pooled storage, transactional control, snapshots... sounds awfully familiar to me. Oh yeah, features that Oracle have provided in the database for years. :) [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-46548838181417815592007-06-12T06:32:00.002+08:002010-06-22T13:43:32.917+08:00

"Applications Developer - desirable skills: Soccer or Tetrinet" [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-76652854514846746592007-03-30T17:16:00.004+08:002010-06-22T13:43:45.551+08:00

I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time. [Full Article]

Jeffrey Kempnoreply@blogger.com5tag:blogger.com,1999:blog-17329758.post-1164633224827861452006-11-27T21:10:00.002+08:002010-06-22T13:44:16.338+08:00

This is a list of all the table types I've found in Oracle-supplied packages, e.g. OWA_UTIL in 10g supplies the type: TYPE datetype IS TABLE OF varchar2(10) INDEX BY binary_integer; [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-1164015792994030352006-11-20T17:40:00.001+08:002010-06-22T13:44:41.220+08:00

An interesting column comment encountered. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1163073930411864122006-11-09T20:02:00.001+08:002010-06-22T13:45:04.221+08:00

Another enjoyable conference. I've attended it every year since 2000 and this year's was packed with presentations. Over 300 attendees spread over up to six presentations meant less crowding, I think; although as always there were a few packed sessions. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1162869615242242792006-11-07T11:15:00.001+08:002010-06-22T13:45:29.302+08:00

Looking at the column comments on this table, I can sympathise with the poor soul who after painstakingly describing 100 columns just decided that it just wasn't worth it... [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1161993937640044192006-10-28T08:01:00.001+08:002010-06-22T13:45:49.799+08:00

I'm starting to come around to Oracle SQL Developer. At home I only use free software so that's obviously a big plus, but at work I'm still using PL/SQL Developer (Allround Automations) and SQL*Plus. [Full Article]

Jeffrey Kempnoreply@blogger.com3tag:blogger.com,1999:blog-17329758.post-1161993658908662402006-10-28T07:59:00.004+08:002010-06-22T13:46:16.684+08:00

I upgraded Oracle SQL Developer from 1.0.14.67 to 1.0.15.27, just for the heck of it. Unfortunately, all my saved connections had disappeared! [Full Article]

Jeffrey Kempnoreply@blogger.com4tag:blogger.com,1999:blog-17329758.post-1161781935128913942006-10-25T18:52:00.004+08:002010-06-22T13:46:26.181+08:00

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with Apex version 2.2 but should work on earlier versions of Apex or HTMLDB. [Full Article]

Jeffrey Kempnoreply@blogger.com5tag:blogger.com,1999:blog-17329758.post-1161643443799234192006-10-24T06:40:00.001+08:002010-06-22T13:46:46.990+08:00

Noticed that the final conference programme is now available. Unfortunately I don't have a laptop so I can't attend some of the hands-on sessions. [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-1161643230119278652006-10-24T06:39:00.001+08:002010-06-22T13:47:16.231+08:00

I've been enjoying learning Oracle APEX the last few weeks, and have started to appreciate the depth of the product - there's more than meets the eye. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1156499683781972082006-08-25T17:50:00.001+08:002010-06-22T13:47:39.977+08:00

Just read the latest draft timetable, looks like there's going to be some very interesting presentations this year. It looks a lot bigger than before, mainly because of the merger with Apps. [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-1153356317624181362006-07-20T08:39:00.003+08:002010-06-22T13:49:07.921+08:00

What is the return value of SHOW_ALERT if the user doesn’t click one of the available buttons? [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-1150433613366095862006-06-16T12:49:00.002+08:002010-06-22T13:50:32.965+08:00

I've worked on some new PL/SQL packages and Forms which work quite well. They are efficient in their use of resources, and are easy for me to debug and maintain. After some other developers have had to do some changes (some from changes to requirements, some from bugs I didn’t find), I've learned that the highly modular style of code I use is difficult to modify without introducing new bugs. [

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1146803445645676432006-05-05T12:23:00.001+08:002010-06-22T13:51:50.104+08:00

Came across this in a form (6i) to be run on a 9i db. Not only is this code about 33 lines of code too long and issues any number of unnecessary database queries, its name is quite unrelated to its intended function. [Full Article]

Jeffrey Kempnoreply@blogger.com3tag:blogger.com,1999:blog-17329758.post-1144829925622878382006-04-12T16:08:00.001+08:002010-06-22T13:53:15.284+08:00

I've had to create test data a number of times, including data for tables that had mandatory foreign keys to existing tables. It was not feasible to just create new master rows for my test data; I wanted to refer to a random sample of existing data; but the code that generates the test data had to perform reasonably well, even though it had to pick out some random values from a very large table.

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1142663419530475862006-03-18T14:28:00.002+08:002010-06-21T14:47:13.721+08:00

Seen in the wild: SELECT ROWID BULK COLLECT INTO t_rowids FROM my_table WHERE ... FOR UPDATE NOWAIT; IF t_rowids.COUNT > 0 THEN FORALL i IN t_rowids.FIRST..t_rowids.LAST DELETE FROM my_table WHERE ROWID = t_rowids(i); END IF; [Full Article]

Jeffrey Kempnoreply@blogger.com7tag:blogger.com,1999:blog-17329758.post-1140433776798815372006-02-20T19:02:00.001+08:002010-06-22T13:53:44.342+08:00

Answer this in your head before trying it out or looking it up. Assume the following script is run in a single session on an Oracle database... [Full Article]

Jeffrey Kempnoreply@blogger.com5tag:blogger.com,1999:blog-17329758.post-1140008010432315192006-02-15T20:52:00.001+08:002010-06-22T13:54:04.394+08:00

Oracle Database SQL Reference 10g Release 1 (10.1): Datatype Comparison Rules - Numeric Values [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1139644749184643702006-02-11T15:52:00.001+08:002010-06-22T13:54:28.335+08:00

I came across this at dbdebunk (ON THE NOTHING THAT’S WRONG WITH NULLS with Hugh Darwen, Fabian Pascal). Couldn't let that go so I tested it under 9i (9.1) and 10g (10.2) and got identical results. The last two statements are logically equivalent, but gives different results! [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1139642562615139592006-02-11T15:19:00.001+08:002010-06-22T13:54:57.245+08:00

PL/SQL User's Guide and Reference (10.2): "4 Using PL/SQL Control Structures - Using the NULL Statement" "...Note that the use of the NULL statement might raise an unreachable code warning if warnings are enabled." / [Full Article]

Jeffrey Kempnoreply@blogger.com1tag:blogger.com,1999:blog-17329758.post-1139642096966664022006-02-11T15:11:00.001+08:002010-06-22T13:55:23.781+08:00

PL/SQL User's Guide and Reference (9.2 and 10.2): "NULL Statement" "The NULL statement and Boolean value NULL are unrelated." [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-1139493508108863462006-02-09T21:56:00.002+08:002010-06-22T13:55:39.551+08:00

How many times have you seen '' used for a NULL string? [Full Article]

Jeffrey Kempnoreply@blogger.com2tag:blogger.com,1999:blog-17329758.post-1138145749744742292006-01-25T07:30:00.001+08:002010-06-22T13:56:03.066+08:00

Simplify your SQL - Avoid Unnecessary Joins I can't count how many times I've seen SQL like the following: SELECT b.some_column FROM a, b WHERE a.id = :p_id AND b.id = a.id; [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1133737320924976642005-12-05T06:54:00.001+08:002010-06-22T13:56:23.989+08:00

If you're using the SAMPLE clause in Oracle 9i, in combination with a join, be aware of this small gotcha. I found a workaround, thankfully. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1130127023115934202005-10-24T12:05:00.001+08:002010-06-22T13:57:21.934+08:00

Background: see my earlier posts Avoiding Lost Updates and Avoiding Lost Updates #2: Updateable Views I've discussed this problem with my colleagues and have come up with a number of potential solutions, one of which we believe is bulletproof. [Full Article]

Jeffrey Kempnoreply@blogger.com3tag:blogger.com,1999:blog-17329758.post-1129889519077506842005-10-21T18:08:00.001+08:002010-06-22T13:57:58.571+08:00

This is a followup to my earlier post "Avoiding Lost Updates: Protecting Data in a Multi-user environment". In this post I'll talk about how this problem can be solved when you're creating updateable views. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1129876054061580092005-10-21T14:15:00.003+08:002010-06-21T14:51:32.688+08:00

There is a particular problem with Oracle and other databases where access to data is not serialized (by default), and there are a number of ways this problem is generally dealt with. The problem crops up when you have many users, some of whom may wish to update the same data at around the same time. [Full Article]

Jeffrey Kempnoreply@blogger.com9tag:blogger.com,1999:blog-17329758.post-1129710611187715212005-10-19T16:26:00.003+08:002010-06-21T14:45:32.062+08:00

The application I'm working on involves a number of views that provide an interface between two databases that have quite different models. The views implement inserts, updates and deletes via Instead-of triggers. On one of these views, the update trigger needs to actually do an update and an insert on the source table: it must update the existing row to mark it as "closed", and then insert a

Jeffrey Kempnoreply@blogger.com7tag:blogger.com,1999:blog-17329758.post-1129094411680251792005-10-12T13:13:00.001+08:002010-06-22T13:59:09.998+08:00

Came across this SQL problem in Eddie Awad's blog, where he gives a solution to a problem posted to the Oracle-l mailing list. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1128925352997599032005-10-10T14:01:00.002+08:002010-06-22T13:59:39.327+08:00

In software development, quite often the problem you encounter has virtually nothing to do with the thing you're trying to do. I'm creating some views which will provide an interface to a database. The views will have Instead-of triggers so that all DML will be passed to my own (packaged) procedures. [Full Article]

Jeffrey Kempnoreply@blogger.com0tag:blogger.com,1999:blog-17329758.post-1128433772716593612005-10-04T21:36:00.002+08:002010-06-22T14:00:01.616+08:00

Upgraded from 1.6. I've already done some playing on 2.0 on htmldb.oracle.com, already liked the little interface and management improvements. [Full Article]

Jeffrey Kempnoreply@blogger.com4tag:blogger.com,1999:blog-17329758.post-1128244607258540772005-10-02T16:57:00.003+08:002010-06-22T14:00:26.742+08:00

I'll post here results from some experiments I'm doing with Oracle. I want to explore the implications and possible benefits of mapping a truly relational logical database model directly to the physical model. [Full Article]

Jeffrey Kempnoreply@blogger.com0