Skip navigation.

Feed aggregator

InSync10 Day 2

Jeff Kemp - Wed, 2010-08-18 08:00
Another good day in Melbourne. Heard Richard Foote talk about Indexing New Features in Oracle 11g release 1 and 2. One thing he demonstrated was the creation of an index on only part of a table – normally I’d use a function-based index for this sort of thing, but his technique results in an index [...]

Never satisfied

Jeff Kemp - Wed, 2010-08-18 03:00
So I followed the great advice here to use the new COLLECT function in Oracle 10g to solve the common string-aggregation-in-SQL problem. I chose this solution over the others because it sorts the results as well, which was a user requirement. This is because the COLLECT function optionally takes an ORDER BY clause – even [...]

InSync10 Day 1

Jeff Kemp - Mon, 2010-08-16 05:00
After a scrumptious breakfast at the Armoury I headed in what I believed was the general direction of the Melbourne Convention Centre – after making a wrong turn I eventually spotted a footbridge over the river that rung a bell from my GoogleEarthing; after taking some photos I was finally at InSync10. The first session [...]

Editing Oracle Reports

Jeff Kemp - Wed, 2010-08-11 01:00
If you’re like me, the above picture will probably have given you a frisson of dread. Just a little. The example here is not a very complicated report, just a few repeating groups based on quite a nice data model. What catches me every time, however, is a few little apparent quirks in Oracle Reports [...]

Directory File List in PL/SQL

Jeff Kemp - Tue, 2010-08-10 01:00
UTL_FILE, unfortunately, does not allow you to query the contents of a directory. One option is to create a java routine. Note: this is not my code – a colleague sent the basics to me and I’ve just modified it somewhat to suit my purpose. CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirectoryLister" AS [...]

Purge all Recyclebins without SYSDBA

Jeff Kemp - Mon, 2010-08-09 01:00
Want to purge all the recyclebins for all users in your database? Can’t (or don’t want to) log in as each user individually to run this, one by one: PURGE RECYCLEBIN You could log in as SYSDBA and run: PURGE DBA_RECYCLEBIN But, what if you don’t have SYSDBA access? (Yes, this has happened to me, with [...]

Forms Library: PKG_DEBUG

Jeff Kemp - Fri, 2010-08-06 01:00
This is just a post to document a standard utility functions that I like to create in a Oracle Forms library, if equivalents are not already available. Suggestions or critiques are welcome. “No warranty is express or implied about the suitability of this code for any purpose.” This package could do with some improvements: use [...]

In Oracle, ” = NULL but NULL != ”

Jeff Kemp - Wed, 2010-08-04 19:00
When I get the result of my PL/SQL quiz for the day, I’m pleased when I got it right, but if I got it wrong, I’m either annoyed or overjoyed: If I disagreed with the result, I’m annoyed. If I agreed with the result, I’m overjoyed – because I learned something new, or I was [...]

Forms Library: General bits and pieces

Jeff Kemp - Wed, 2010-08-04 01:00
This is just a post to document a standard utility functions that I like to create in a Oracle Forms library, if equivalents are not already available. Suggestions or critiques are welcome. “No warranty is express or implied about the suitability of this code for any purpose.” I’ll only create those things which I find [...]

Forms Library: PKG_MESSAGE

Jeff Kemp - Tue, 2010-08-03 01:00
This is just a post to document the standard library that I like to create in Oracle Forms, if equivalents are not already available. Suggestions or critiques are welcome. “No warranty is express or implied about the suitability of this code for any purpose.” Forms Package Specification: PKG_MESSAGE PACKAGE PKG_MESSAGE IS PROCEDURE error (message IN [...]

Tag wikis on StackOverflow

Jeff Kemp - Mon, 2010-08-02 01:00
StackOverflow now allows the creation of wiki articles and tag synonyms. I’ve gone ahead and started a few articles about a few topics dear to my heart: oracle plsql apex – synonyms: htmldb and application-express These articles are not intended to be replacements for the documentation or Wikipedia, but primarily as a guide for people [...]

The Templating Way

Jeff Kemp - Thu, 2010-07-29 18:00
Today, grasshopper, you will learn the Way of the Template. The Templating Way is the path by which complex output is produced in a harmonious fashion. The Templating Way does not cobble a string together from bits and pieces in linear fashion. htp.p('<HTML><HEAD><TITLE>'||:title ||'</TITLE></HEAD><BODY>' ||:body||'</BODY></HTML>'); The Templating Way separates the Template from the Substitutions; by this [...]

A fascinating SQL problem

Jeff Kemp - Thu, 2010-07-29 00:00
Can you solve this problem in SQL – i.e. without resorting to a procedural solution? SQL combine multiple identifiers to create a group id for duplicate records “I have a data extract with three different identifiers: A, B, C Each identifier may appear in more than one row, and each row may have one or [...]

Priority #1: Keep it simple

Jeff Kemp - Wed, 2010-07-28 03:00
Every place has a different way of assigning priority and/or severity to defect reports – some bigger places have many different ways (unfortunately). I’ve not been subjected to Prince2 training so here’s my take on this subject. I reckon, the simpler the scheme, the more likely it will be used consistently. Every defect should have [...]

Current Record Visual Attribute problem

Jeff Kemp - Mon, 2010-07-26 22:00
If you have a multi-record block, and you use the handy Current Record Visual Attribute (CRVA) to set, say, a different background colour on all the items in the currently selected record, you may have run into this problem. If you want to conditionally switch the visual attribute for certain items, at the item instance [...]

Forms Library: PKG_FORM

Jeff Kemp - Sun, 2010-07-25 22:00
This is just a post to document a standard library that I like to create in Oracle Forms, if equivalents are not already available. Suggestions or critiques are welcome. “No warranty is express or implied about the suitability of this code for any purpose.” Specification PACKAGE PKG_FORM IS PROCEDURE centre_window (windown IN VARCHAR2); PROCEDURE check_record_is_saved; [...]

Forms Library: PKG_ITEM

Jeff Kemp - Thu, 2010-07-22 22:00
This is just a post to document a standard library that I like to create in Oracle Forms, if equivalents are not already available. Suggestions or critiques are welcome. “No warranty is express or implied about the suitability of this code for any purpose.” Specification The main point of this package is to (a) easily [...]

Negative, Captain

Jeff Kemp - Wed, 2010-07-21 19:13
Seen in the wild: ... WHERE substr(amount,0, 1) != '-' If you wanted to query a table of monetary transactions for any refunds (i.e. where the transaction amount is negative), how would you do it? Perhaps you’d think about avoiding problems that might occur if the default number format were to change, hm? (before you say [...]

I&#8217;m off to InSync10

Jeff Kemp - Mon, 2010-07-12 18:00
Perhaps you were introduced to Apex in a similar way to me. When I started using Apex, I was pleased to see a good selection of simple themes and templates I could use straight away. There was no need to worry about creating a consistent look-and-feel across my applications, I could dive right into building [...]

If at first you don&#8217;t succeed&#8230; it&#8217;s impossible.

Jeff Kemp - Mon, 2010-07-12 00:00
How many times have you tried something, got either an error or unexpected results, and decided what you were trying to do was not possible? Have you later on discovered someone quietly doing the impossible? I think this phenomenon is a form of the “correlation-implies-causation” fallacy. Unfortunately, this seems to happen too often, if the [...]