Using apex_item.checkbox2 with multiple identifiers

Normally, in a report you can add a checkbox to select records like this:

select apex_item.checkbox2(1,x.id) as sel
      ,x.mycol
      ,...
from mytable x

And process the selected records using a process like this:

for i in 1..apex_application.g_f01.count loop
  l_id := apex_application.g_f01(i);
  -- ... process ...
end loop;

Since we have set the value of the checkbox to the record ID we can just get that ID from the resulting g_f01 (more...)

Many happy birthdays

Last year I *(not pictured) celebrated my 42nd circuit around the sun. In accordance with time-honoured tradition, it has been celebrated some time around the same day each September with variations on the following theme:

  • a get-together with friends and/or family
  • my favourite meal (usually lasagne)
  • my favourite cake (usually a sponge coffee torte, yum)
  • a gift or two
  • the taking of photographs, to document how much I’ve grown since the last one

Each year, (more...)

Add a “Share Link” to your application

Sometimes my customers need to be able to get a direct link to a record in their APEX application which they can share with others (e.g. to publish it on a website, or send by email to a colleague).

They don’t want people to be able to easily “guess” the link (so it needs to have some degree of randomness and complexity), but it’s not so sensitive that if someone somehow gets the link (more...)

Quick tip: List all interactive reports with email features enabled

I was working on an application in an APEX instance that was not configured for email (and would not be), but a number of interactive reports were allowing users to use the “Subscription” or the “Download as Email” features. If they tried these features, those emails would just go into the APEX mail queue and never go anywhere, so I needed to turn these off.

I listed all the interactive reports that need fixing with (more...)

Quick tip: List all tabular forms

I needed to get a list of all the tabular forms in my application along with which table they were based on. This query did the trick:

select page_id
      ,attribute_02 as data_source
      ,page_name
      ,region_name
      ,attribute_03 as key_column
from   apex_application_page_proc
where  application_id = <my app id>
and    process_type_code = 'MULTI_ROW_UPDATE'
order by 1, 2;

Quick tip: default only for new records

If you have an ordinary Oracle APEX form with the standard Automatic Row Fetch process, and the page has an optional item with a default value, APEX will set the default on new records automatically. However, if the user queries an existing record, Oracle APEX will also fill in a missing value with the default value. This might not be what is desired – if the user clears the value they would expect it will (more...)

A quick browse of the Beta APEX 18.1 documentation

Noticed a few additions, one one omission.

    • APEX_EXPORT – new package with supported methods for exporting APEX applications and other scripts into a CLOB: get_application, get_workspace_files, get_feedback, get_workspace
    • APEX_JWT – new package for JSON Web Tokens – encode, decode, validate
    • APEX_SESSION – new procedures: create_session, delete_session, attach, detach
    • Missing documentation: Legacy JavaScript APIs – not sure if anyone will miss this, they could just refer to the 5.1 documentation for this
    • I should mention also (more...)

Interactive Grid: Custom Select List on each row

I had a column in an editable interactive grid based on a Select List, which takes valid values from a table that supports “soft delete” – i.e. records could be marked as “deleted” without affecting existing references to those deleted records.

The SQL Query for the LOV was like this (my example is a view on a reference table of school “year levels”):

select name, id from year_levels_vw
where deleted_ind is null
order by  (more...)

Report query with optional parameters

I received a question today from a developer who wanted to write a single static SQL query that could handle multiple optional parameters – i.e. the user might choose to leave one or more of the parameters NULL, and they’d expect the query to ignore those parameters. This is a quite common requirement for generic reporting screens, and there are two different methods commonly used to solve it.

Their sample query, using bind variables (more...)

Convert an APEX Application to Multi-Tenant

So you’ve built an APEX application to solve a problem for one client, or one department, or just yourself – and you think it might be useful for others as well. How do you make that application available for other users, departments, or companies to reuse, while ensuring each sees only their own data and cannot mess around with others’ data?

Architecting a Multi-Tenant Application

To make your application multi-tenant you have a few options.

(more...)