Oracle Apex: Set Default Value for an Item

This Oracle Apex tutorial shows you, how to set a default value for an item. In Oracle Apex, default value assigns to an item at the time of the initialization of the record, whether it is a form or a grid. So it means it executes one time only. The following ways you can set the default value for an item in Oracle Apex:

Default Value Types in Oracle Apex

1. Static - (Set to the value entered in Static value.)
2. Item - (Set to the value returned from the session state for the Item selected.)
3. SQL Query - (Set to the value returned from the SQL Query entered.)
4. SQL Query returning Colon Delimited List - (Set to the colon-delimited list of values returned from the SQL Query entered. Applies to checkbox, radio group, or shuttle item types.)
5. PL/SQL Expression - (Set to the value returned from the PL/SQL Expression entered.)
6. PL/SQL Function Body - (Set to the value returned from the PL/SQL Function Body entered.)
7. Sequence - (Set to the value returned from the Database Sequence entered.)

Static Default Value Example

In the following example, it will set the static default value to 'Y' for the Status checkbox.

Set Default Value Using Item Type Example

The following example sets the default value for a field EMPLOYEE_ID in Interactive Grid so that whenever new record created the Employee ID can be set from the Master Region item P3_EMPLOYEE_ID.

Set Default Value Using SQL Query Example

SQL query should return one row with one column. In the following example, it will set the COMMISSION column default value to an average commission of the EMPLOYEES table.

Select avg(commission_pct) from employees

SQL Query Returning Colon Delimited String

If the item type, is a checkbox, radio group or shuttle then you can use the following SQL query to return the default values separated by a colon:

SELECT LISTAGG(category, ', ') WITHIN GROUP (ORDER BY category) "Categories"
FROM category_master;

Using PL/SQL Expression to Set Default Value

Use any valid PL/SQL expression to set the default value. In the below example, it will set the HIRE_DATE as SYSDATE:

SYSDATE

Set Default Using PL/SQL Function Body

You can write a PL/SQL anonymous block to return a value to set as the default value for an item. In the below example it will set the employee id to maximum employee id number plus one.

declare
  n_emp_id integer;
begin
  select nvl(max(employee_id),0)+1 into n_emp_id from employees;
  
  return n_emp_id;
end;

Using Database Sequence

You can specify the incremental number to a field using the Oracle database sequence object. Just specify the sequence name in the area. For example:

seq_employee_id

Reference: Defining Default Values for page items - Oracle Apex 19.1

Related Tutorials:

• Oracle Apex 19.2: Create Star Rating Page Item (New Feature)