David S. Gale

Programming, Life, and what I think about it all

APEX 5.0.2 Released; Versioned Types Bug Details

by Leave a Comment

APEX 5.0.2 has been released. This is, for the most part, a bug-fix release; all the really cool stuff is still going through the pipeline for version 5.1. Most of the packaged apps are not touched by this patch; the exceptions are:

  • Data Reporter
  • P-Track
  • Application Standards Tracker
  • Sample Reporting
  • Live Poll

In the release notes, we mention that there’s a known issue with the first four of these. It’s an oddball, doesn’t-reproduce-on-most-systems bug, but if you run into it, it can be annoying.

Basically, if you had one of these packaged apps installed in 5.0, upgrading to the version included in 5.0.1 could fail with compilation errors (the same errors would occur if you uninstalled the packaged application and then tried to reinstall it). The issue is caused by the fact that these packaged applications include a PL/SQL package which includes a pipelined function returning a type defined in the package, and when the database tries to drop or update the package, it may–under some configurations; we haven’t been able to determine the exact recipe–not drop the correct objects, resulting in an “ORA-04043: object SYS_PLSQL_D8210ED9_307_1 does not exist” error. For more details, check out the “Issue 3” on this page; they explain it better than I can.

The good news: if you have one of the affected packaged apps installed, you can upgrade to the 5.0.2 version and all will work. The bad news: if you uninstalled one of the relevant apps and then tried (and failed) to install it, the 5.0.2 version won’t work–we discovered this right before release. It’ll be fixed in 5.1, sorry.

However, if you’ve run into this bug and you’re impatient, there is a workaround. After uninstalling Data Reporter, look to see if the package EBA_DP_FILTER_FW still exists (for P-Track, the package would be EBA_PROJ_FILTER_FW; for Application Standards Tracker, EBA_STDS_FILTER_FW; and for Sample Reporting, EBA_DEMO_IR_FILTER_FW). If it doesn’t, you’re fine and can go on with your life. If it’s still hanging around, though, you need to identify the “ghost” type and drop it. To do this, try to manually drop the package; you’ll get an error message like the one I reproduced above. Then take the name of the object it complains about (SYS_PLSQL_D8210ED9_307_1, in my example) and replace the number before the “_1” with the word “DUMMY”, and drop that type:

drop type SYS_PLSQL_D8210ED9_DUMMY_1;

At this point, you should be able to drop the EBA_DP_FILTER_FW package, and then reinstall the new version of Data Reporter.

For other developers, the “fix” for this is to declare the type returned by the pipelined function as a SQL type outside of the package, rather than a PL/SQL type in the package. There’s some loss of functionality–you can’t specify default values for the elements in the type or use booleans, for instance–but at least it avoids running into a versioned types bug.

Case when decode_is_bad then use_case end;

by 1 Comment

This post is, largely, a note to myself. You may find it useful if you've developed the same habits I have, or it may be old news to you.

Recently, I've been working on changing from using dates to timestamps (with local time zone). Generally, this is pretty easy, but I've run into an issue which required me to change how I do things.

My biggest problem is that I use this pattern a lot:

select ...
    decode(sign(trunc(date_col) - trunc(sysdate)),
        1, 'Future', 0, 'Today', 'Past') when_is_this,
   ...
from ...

For dates, this works fine, because the difference between two dates is the number of days between them. But the difference between two timestamps is an interval, and you can't take the sign of an interval, so you get an error: “ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND”. Very annoying. The solution, of course, is to use CASE instead of DECODE, because you can use relational operators in CASE:

select ...
    case
        when trunc(date_col) > trunc(sysdate)
            then 'Future'
        when trunc(date_col) = trunc(sysdate)
            then 'Today'
        else 'Past'
    end when_is_this,
   ...
from ...

Honestly, the CASE syntax is easier to read than DECODE, since it's trivial to tell what the conditions are and what the return values are; with DECODE, you need to count commas and/or rely on good formatting habits. And it's more flexible, since you can have your entire condition in the WHEN statement, so you're not restricted to comparing one value to a list of possibilities. I'm not sure why I use DECODE far more often than CASE; I suspect that it's because CASE was introduced after I learned SQL, but I haven't been able to verify that yet. Regardless, I'm going to be using it more in the future.

Doctor, it hurts when I do this!

by Leave a Comment

There's an old joke that goes like this:

A man goes to the doctor and says, “Doctor, it hurts when I bend my arm like this.” To which the doctor responds, “Well, don't bend your arm like that.”

(I didn't say it was a good joke…) What's this got to do with APEX? I'm glad you asked!

When I was building the mobile interface for WriteTrack, I kept the same approach I'd used in the normal version–a challenge selector on the global page, so that the users can change what challenge they're looking at wherever they happen to be. Turns out this doesn't work.

APEX 4.2 introduced the ability to have multiple user interfaces in the same application, which is very cool (at this point, that basically boils down to desktop and mobile, of course). To set this up, go to the Edit Application Properties page, click on the User Interface tab, and scroll down to the bottom. You'll see a big button labeled “Add New User Interface”. Click it and go through the wizard. (Incidentally, there's a bug in 4.2.0; it will ignore any changes you make and just use the defaults, so don't panic when that happens.)

The wizard will create three pages for you in the new UI–a mobile login page (defaults to 1001); a mobile home page (defaults to the first available non-zero page number); and a mobile global page (page 0 if you don't already have one, otherwise page 999). These are normal, fully functional APEX pages, and you can do whatever you want with them. For WriteTrack, I deleted the first two auto-generated pages and built my own, so that I could control which page numbers they had; the important thing to remember if you do this is to keep the page aliases.

The problem I ran into, though, came up when I added a page item to page 999. If you try to submit a mobile page which has an item from page 999 on it, you get an error: “Item ID (….) is not an item defined on the current page.” Also, the APEX Advisor doesn't seem to recognize the possibility of having multiple global pages, but that's a minor issue.

When I asked one of my co-workers (the one in charge of the development of the mobile feature), his response was basically, “don't do that.” The root reason is that the mobile pages are rendered differently from desktop pages–they're pumped through AJAX, rather than being totally fresh pages with their own DOM models–so global page items cause confusion.

So, be careful of global pages. Branches on page 0 have never worked, and page items on the mobile global page will just cause you painful errors.

Merge

by Leave a Comment

This post is partly a reminder for myself; it’s about a part of SQL, which I tend to forget to use. But if I forget about it, then that means that others are likely to as well, right? (heh)

Anyhow, I was trying to create a process for maintaining sample data in an application–the users can install an application, muck up the sample data all they want, and then hit a “reset” button which takes everything back to the beginning. Pretty straightforward; just delete the data, and re-insert it. But the sample data is just to get people started using the apps, so we can’t just wipe the table–they may be using some of the data, or may have made changes that they want to keep, etc.

So we wanted to do something like this:

insert into table1 ( id, col1, col2 )
select 1 id, 'Sample' c1, 'Value' c2 from dual
where not exists ( select null from table1 where id = 1;

That’d work, but in my case, I was loading 10 different tables, and anywhere from 3 to 15 different rows per table. I knew that I’d end up with a different id in the data I was inserting and in the not exists test sooner or later due to excessive cut-and-paste. (Alternatively, I could wrap each insert statement in a begin/end block and catch the duplicate ID exception–but who wants to do all that?) So I started trying to make something that would be simpler to maintain.

My solution was a private procedure that took a table name and an associative array of column names and values, and then built up the above insert statement using dynamic SQL. Effective, but rather ugly. I’m not even going to bother posting it here.

One of my co-workers then suggested that I use merge. The syntax is a little more complex than the above insert statement, but I can use one merge for all of the rows in a table–that’s a definite win. And I don’t have to type any of the values twice, so that source of error is gone!

merge into table1 dest using (
    select 1 id, 'Sample' c1, 'Value' c2 from dual
    union all
    select 2, 'More', 'Value2' from dual
    ...
    ) src
on ( dest.id = src.id )
when not matched then
    insert (id, col1, col2 )
    values (src.id, src.col1, src.col2 );

It’s fast, it’s secure, and it’s easy to maintain. The only downside is that it wasn’t my first approach.

New blog feature

by Leave a Comment

Ok, I confess. I kind of stopped development on the blog application once it was up and running (focusing instead, of course, on what has now become WriteTrack). And that's sad, because it was missing at least one feature common to pretty much every blog out there–the ability to have follow-up comments e-mailed to you. And without that, there's not really much chance for a dialog in the comments, for which I apologize. This has now been fixed. (Of course, if you don't want to get comments e-mailed, that's ok, too…)

Date pickers and bad examples

by 2 Comments

Here's a quickie: when you are documenting something, or adding help text to an input, always double check your examples.

I just created a date picker and wanted to set the minimum date. Since I was working quickly, I glanced at the examples in the help text, which says,

Enter a minimum selectable date. The value can be absolute value in YYYYMMDDHH24MI format or relative value with respect to today's date e.g. +7d  ('y' for years, 'm' for months, 'w' for weeks, 'd' for days) or name of the item, which holds the value e.g. &ITEM_NAME..

Example

11-JUN-2009
…

So, I punched 01-DEC-2010 into the field, saved, and ran the page. If you're reading closely, you won't be surprised at the error I got (though I was):

Error ORA-20001: Entered Minimum Date attribute value 01-DEC-2010 for item P55_RESOLUTION_DATE does not match YYYYMMDDHH24MI format.

Yep, the very first example in the help text uses the wrong format, and APEX doesn't catch it until it tries to render the page. Changing to 20101201 (note that you don't need the time data) resolved the issue.

Beware the Object Browser

by Leave a Comment

I like the Object Browser. It gives me a quick glance at my tables, views, etc., and I don't need to fire up SQL Developer or Toad to get to it. When I'm developing an APEX app, I'll often have the Object Browser open in another tab or window (often window, so I can flip to it with alt-tab). And it applies syntax highlighting to the code for views, procedures, functions, etc., a feature that is sadly missing in the SQL Commands screen–sometimes I'll create a null procedure/function in the SQL Commands window, and then pop into the Object Browser to actually write the code. It's a great little tool.

…but…

Yep, there's always a but, isn't there?

I got burned (and scared) by the Object Browser last week, I was working on a new feature, and I added a column to my core users table. To test, I edited my row in the user's table in the Object Browser, because I was too lazy to pop over to the SQL Commands screen and type up a real update statement. Big mistake. It turns out that, when you edit a table directly in the Object Browser, it's rather indiscriminate about which fields it saves–it seems to save them all, regardless of whether or not you've changed them. 99% of the time, this isn't going to be an issue. Unfortunately, this was one of the times when it was.

The problem is that I've got the user's password stored in the users table (where it belongs), and for security, the password is encrypted. When the Object Browser displayed the row, it attempted to show the password, but the encryption creates a lot of non-printable characters (of course). So when the record got saved…the encrypted password got written over with true gibberish. And I found myself locked out of my account the next time I tried to log in. Since I didn't try to log in until well after I'd updated my record, it took me a while (panic made it feel roughly ten times as long as it really took, I'm sure) to figure out what had gone wrong.

So, be careful of updating records in the Object Browser…there can be unexpected consequences.

The importance of testing

by Leave a Comment

Yes, this is something that every programmer should know, but sometimes…we forget. But always, always, always test your applications before releasing them into the wild. Preferably under multiple accounts.

I'm in the process of putting the finishing touches on a new application–I'll have more details to post soon–and my wife suggested that I put together a series of screenshots to show people what it's capable of. This made sense to me, and I wanted to do shots from the start onwards, so I created a new user, giving me a clean slate to work from. Supposedly.

Of course, it wasn't that easy (or else I wouldn't be posting about it here, now would I?). One of the first things I noticed was that certain calculated numbers were decidedly…off. A little digging around, and I found that I had some analytic functions that weren't partitioning properly; the application was adding data for my original user to my fresh one, resulting in rather screwy numbers. And it turns out (I'm ashamed to admit this, but…) I had a hard-coded value that only worked for my test account. Oops.

Luckily, I was taking the time to document the tool carefully, so I was able to notice these oddities before releasing the application. So, here's my pledge: I will always test every part of my applications with at least two accounts. And when if I fail, I'll blog about it here…so that the embarrassment will help me remember in the future.