Tony Andrews on Oracle and Databases

The curse of the cursor

2011-04-19T09:58:00.010Z

For some reason, many Oracle developers avoid SELECT INTO as if it were dangerous, preferring to "have more control" over their code by using cursors for everything. This morning I spent over an hour debugging some code written by one such developer, only to find that the issue I was trying to fix was one that would have been caught by the original developer immediately had he used SELECT

Apex MP3 Player item plug-in

2011-04-04T16:18:00.010Z

I have developed a plug-in item type that renders an MP3 player to play a specified audio file using the free Premiumbeat Single Track Flash MP3 Player. The file may be specified via a URL of as the ID of a file stored in APEX_APPLICATION_FILES. There is a demo of this available here on apex.oracle.comThe plug-in has the following settings available:They are mostly fairly self-explanatory (and

Dutch Oracle User Group's Apex Day 2011

2011-03-25T11:32:00.011Z

This week I attended the Dutch Oracle User Group (OGh)'s Apex Day as a presenter, along with an ex-colleague from Northgate, Nigel Blair. We were talking about how we converted 1500+ Forms modules to Apex. There was quite a lot of interest from the 250 people present. We hope the interest wasn't due to a mix-up in the agenda that had us down as Nigel Andrews and Tony Blair!Luckily we were on

Fame at last for my biggest Apex project to date

2010-08-09T16:32:00.011Z

I'm very pleased to see that the Apex project I started and worked on for several years is now the subject of an entry under Customer Quotes on the OTN Apex page."At Northgate Revenues & Benefits, we have used APEX to replace our legacy Oracle Forms system comprising around 1500 Forms. Our user interface has 10,000 end users daily, across 172 clients, who this year sent out over 12 million

SQL Overlap Test

2010-06-23T10:20:00.006Z

Many times I come across SQL where the developer is trying to check for overlap between two ranges (usually date ranges, but sometimes numbers). For example, to meet the requirement "select all employees whose hired from and to dates overlap with the project start and end dates".The developer sketches out all the possible overlap scenarios and finds four:1) End of range A overlaps start of range

Shameless boasting

2010-06-04T10:44:00.004Z

I hate to boast but...StackOverflow has become one of my favourite forums for reading and sometimes answering Oracle-related questions (though it covers all programming topics in fact).Today I am the first person ever to be awarded the Oracle badge for having earned 1000 upvotes for my answers to questions with the Oracle tag:Of course, this may just mean I have too much time on my hands...

Chris Date Seminar

2010-03-24T13:31:00.004Z

Chris Date is giving a 2 day seminar called SQL and Relational Theory - How to Write Accurate SQL Code in Edinburgh, 13th-14th May 2010. I really wish I could go to it myself. I attended one of his seminars back in about 1997 and it was both riveting and highly educational.

Apex 4.0 - no more dummy branches required

2010-03-04T17:43:00.004Z

How many times do you see this error page while building an Apex page?ERR-1777: Page 1 provided no page to branch to.Please report this error to your application administrator.To avoid that, I have a habit of creating an unconditional branch back to the same page in every page I build, with a sequence number higher than all the conditional branches. If I then add new conditional branches I have

Trying Out Apex 4.0 Dynamic Actions

2010-03-02T16:34:00.015Z

In my spare moments I am currently familiarising myself with the Apex 4.0 Early Adopter edition. One of the many exciting new features is Dynamic Actions. These allow you to add functionality to your pages that would previously have required writing Javascript, AJAX calls and On Demand PL/SQL processes, but can now be done declaratively.The following very simple example shows 3 uses:1) Enabling

Flashback saves the day (again)

2010-02-18T14:57:00.003Z

I'm posting this as much for my future reference as anything. This morning I accidentally dropped a package body from the schema I was working in - that is to say I intentionally dropped it, but then realised I shouldn't have. And since this package body was the work-in-progress of another developer who likes to build their code using Toad, directly into the database (a hanging offence if it

Accessible Apex

2009-04-14T16:17:00.004Z

I am often asked whether Apex can be used to build applications that comply with web content accessibility guidelines. It can, as this demo Apex application aims to prove.It is a very simple application consisting of 3 pages:LoginReportFormThe report page shows some records from a table, and has a link on each row to open the form page for editing, and a Create link in the report header to open

UKOUG - Apex SIG

2009-02-15T14:42:00.007Z

I was lucky enough to be able to attend the UK Oracle User Group's Application Express SIG on Friday - my first experience of any UKOUG event. The event was a "sell out", with all available places taken up well in advance. This in itself is great news, because it shows that APEX really is taking off, which means I can be fairly confident I haven't backed the wrong horse career-wise.David Peake,

Apex Impact Analysis script (v2)

2007-11-01T12:36:00.001Z

After receiving useful comments from Patrick Wolf on my previous post, here is an enhanced version of the script that also checks condition expressions. I'm not including condition_expression2 because I don't think that ever contains anything other than literal values (am I right?)column obj_name format a50undef search_textaccept search_text prompt "Enter search text: "select application_id,

Apex: impact analysis script

2007-10-31T11:10:00.001Z

Ever wondered where a particular package is being used by your Apex application(s)? This simpleSQL Plus script may be of use:column obj_name format a50undef search_textaccept search_text prompt "Enter search text: "select application_id, page_id, 'Region' objtype, region_name obj_namefrom apex_application_page_regionswhere lower(region_source) like '%&search_text.%'UNION ALLselect

Apex 3.0

2007-02-11T15:40:00.000Z

I have finally got access to the Apex 3.0 evaluation instance, after some problems reading the email containing my login credentials. It looks good. The "What's New in Apex 3.0" document details lots of interesting new features; however there a couple of things on my "wish list" that are not mentioned there, but have been added. These are not very exciting, but assist the developer:Region

Why I Love HTMLDB

2006-01-04T13:04:00.001Z

Over the last 3 years or so I have got to know HTMLDB well, and have become a big fan of it - as a development tool, not as a "power user toy". I have successfully built and deployed a number of HTMLDB applications. I find it appeals to me in a way that .Net and J2EE never have. Why is this? Here are some reasons:all programming is in PL/SQL, my favourite programming languageall the "boring

PL/SQL So Doku Solver

2006-01-02T19:42:00.001Z

So Doku has taken over the UK, if not the world, in the last year. I am now addicted, but at first I couldn't see the point in these puzzle and prefered to write a program to solve them for me. What was the point in that? Absolutely none! But in case you ever feel the need to get a Su Doku puzzle solved without actualy doing it yourself, here is the code - think of it as a late Christmas

What does GROUP BY CUBE do?

2005-08-24T16:30:00.001Z

This is one of those "new" features that has actually been around for a long time, but I had never used it. Then recently I was asked to tune a long-running report, and it occured to me that it might just be what I needed.The report consisted of 4 summary queries, each of which presented the same data but summarised by a different "dimension". Imagine it was based on the EMP table, then the

Business Logic APIs

2005-04-20T16:45:00.001Z

Database application development generally has two major components:1) The user interface2) The databaseThese have to talk to each other somehow, typically by an "Application Programmatic Interface" or API. What should such an API look like logically?Let's take as an example a screen that allows the user to transfer funds from one account to another. It looks something like this:Source Account: .

Vadim Tropashko on Complex Constraints

2004-11-23T16:04:00.001Z

I am flattered to find that my first article here (Enforcing Complex Constraints in Oracle) is credited in the Acknowledgements of this interesting article by Vadim Tropashko on DBAZine:http://www.dbazine.com/tropashko8.shtmlAs well as approaching the subject from first principles, the article also suggest further applications of such constraints, e.g. "foreign keys" to more than one parent table

OTLT and EAV: the two big design mistakes all beginners make

2004-10-21T14:37:00.001Z

Many people (myself included) start of as programmers, and only later start to work with databases. After a while, the developer notices two things about databases:Tables have a fixed set of columns; adding a column to a table involves changing any application code that accesses it.There are lots of “lookup” tables that typically have a code and a description.Being a natural problem solver, and

"Pivot" Queries

2004-10-15T15:27:00.002Z

One of the frequently asked questions on SQL forums is how to present data “horizontally” rather than “vertically”, known as a “pivot query”. For example, instead of this:DEPTNO JOB HEADCOUNT---------- --------- ----------10 CLERK 110 MANAGER 120 ANALYST 220 CLERK 220 MANAGER

Parsing delimited fields in a character string

2004-10-15T14:50:00.001Z

Often we have a need to parse a character string to get data from fields within it. Of course, SQL Loader handles this nicely, but sometimes we may be getting the data via a different route such as from a table or via UTL_FILE.The following package facilitates this:CREATE OR REPLACE PACKAGE parse AS /* || Package of utility procedures for parsing delimited or fixed position strings into

Enforcing complex constraints in Oracle

2004-10-15T14:17:00.001Z

Oracle supports various kinds of declarative integrity constraints: Primary Key: Uniquely identifies a row in the table Unique: Other columns that must be unique Foreign Key: Column value must match value in another table Check: Simple single-table, single-row data rules. Examples of possible check constraints are:“start_date <= end_date” “check_ind in (‘Y’,’N’)” “amount between 0 and 99999.99”