Page Item Default Values in APEX: Which Type to Pick and When It Actually Runs

Default values are one of those APEX features you set up on day one and then never really think about again. You pick "Static Value", type something in, move on. And honestly? For a lot of cases that is totally fine.

But there are a few things about default values that trip people up, and once you understand them, you will start working with them differently.

First, the one rule that explains everything

Here is the thing to keep in your head the whole way through this post: a default value only kicks in when the item is null.

That is it. That is the rule. The default is not "the value this item always has." It is "the value this item gets if it does not already have one."

So when does it run? When the page renders and the item has nothing in it. New form, brand new record, the item is empty, APEX looks at your default and fills it in. But if the item already has a value (because the user navigated back to the page, or a computation set it, or it came from a database column on an existing record) the default is ignored completely.

This is why some developers get confused when their default "stops working." It did not stop working. The item just was not null anymore.

The types you can choose from

When you open up the Default section of a page item, you get a Type dropdown. Let's go through the ones you will actually use.

Static Value

The simple one. You type a value, APEX uses it. Y, PENDING, 10, whatever you need. Use this when the default never changes. A status that always starts as "Open", a quantity that always starts at 1. No logic, no query, just a value.

One thing worth knowing: you can use substitution syntax in here. If you put &APP_USER. as a Static Value, APEX will substitute the current username. So "static" does not mean "completely fixed", it just means "no SQL or PL/SQL block."

SQL Query (return single value)

You write a query, APEX takes the value from the first column of the first row.

select max(order_number) + 1
  from orders

Use this when the default comes from your data. The next number in a sequence-like column, a setting from a config table, a value that depends on what is already in the database.

Keep it returning exactly one row and one value. If your query returns more than one row, APEX takes the first one, and "the first one" is not something you want to leave to chance. Add enough filtering or aggregation that there is only ever one answer.

SQL Query (return colon delimited list)

This one is specifically for items that hold multiple values: checkboxes, shuttles, multi-select lists. Your query returns several rows, and APEX joins them with colons into the format those items expect.

select category_id
  from categories
 where is_default = 'Y'

If you have a shuttle and you want three items already sitting on the selected side when the page opens, this is how you do it. The query returns those three IDs, APEX hands them to the shuttle as a colon-separated string.

If you ever try to default a shuttle with a plain Static Value and it does not work the way you expect, this is probably the type you actually wanted.

PL/SQL Expression

A single expression that returns a value. Not a full block, just an expression.

sysdate

or 

case when :P10_REGION = 'UK' then 'GBP' else 'USD' end

Use this when the default needs a tiny bit of logic but not a whole program. It is shorter to write than a function body and it reads cleanly. If you can express what you want as one expression, this is the nice middle ground.

PL/SQL Function Body

The full one. A complete anonymous block, and it has to return a value.

declare
   l_next_id number;
begin
   select nvl(max(employee_id), 0) + 1
     into l_next_id
     from employees;

   return l_next_id;
end;

Use this when the logic genuinely needs room to breathe. Multiple steps, a query plus some processing, a few conditions. Anything where a single expression would get ugly.

A quick tip: do not reach for the function body just because it feels powerful. If a SQL Query or a PL/SQL Expression does the job, use that instead. The function body is the right tool when you actually need it, and overkill when you do not. Future you, reading this page item six months later, will appreciate the simpler option.

Sequence

If you just want the next value from a database sequence, you do not need a function body wrapping seq.nextval. There is a Sequence type. You pick it, you name the sequence, done.

How to actually choose

Most of the time the decision is quick:

The value never changes? Static Value.

The value comes straight from a sequence? Sequence.

The value comes from your data with no real logic? SQL Query.

The value needs a small condition or calculation? PL/SQL Expression.

The value needs proper multi-step logic? PL/SQL Function Body.

It is a checkbox, shuttle, or multi-select and you want some options pre-selected? SQL Query returning a colon delimited list.

The instinct to reach for the function body for everything is worth resisting. The simpler types are easier to read, easier to debug, and they make your page item tell the next developer what it is doing at a glance.

A couple of gotchas to save you some debugging

Default values run once. They fire when the record is initialised. They are not going to re-run because the user changed another field on the page. If you need a value to update in response to something the user does, that is a job for a Dynamic Action or a computation, not a default.

Defaults and existing records. On a form showing an existing row, the item is usually already filled from the database, so your default never gets a look-in. Defaults are mostly a "new record" thing. If you are testing your default and it never seems to apply, check whether the item is already getting a value from somewhere else first.

There is also a "Default Value Type" called Item and one called Preference. Item lets you default from another page or application item. Preference lets you default from a stored user preference. They do not come up as often, but if you ever find yourself writing a function body that just reads one other item, the Item type already does that for you.

That is the whole picture

Default values are not complicated once the core idea clicks: they fill in a blank, they do it once, and the type you pick is really just a question of how much logic you need to produce the value.

Pick the simplest type that gets the job done, remember they only apply when the item is null, and you will spend a lot less time wondering why a default did or did not show up.