Custom Syntax Highlighting in SQL Developer

A few years ago I raised an enhancement request for SQL Developer to add custom highlighting, specifically to “grey out” all the calls to logger throughout my code. I blogged about this here.

Oracle SQL Developer 18.3 adds this feature with PL/SQL Custom Syntax Rules – and the best thing is, these rules are enabled by default so you don’t have to do anything. Any calls to logger, dbms_output and apex_debug will be (more...)

ODC Appreciation Day: why I’m “on Oracle” #ThanksODC

Hamersley Gorge, Karajini National Park, Western Australia. ©2018 Jeffrey Kemp

Pictured: Hamersley Gorge, Karajini National Park, Western Australia

Why is this blog called “Jeff Kemp on Oracle”?

Pretty much my entire career has been underpinned by the Oracle database. My first few I.T. jobs involved maintaining and building Oracle Forms and Reports. SQL and PL/SQL have been my indispensable two-pronged tool since day one and remain so today. Through fortuitous circumstance I had the opportunity to swap Forms for APEX quite a few years (more...)

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...)