Aside from the obvious, it’s been disappointing for a lot of people in our community seeing all the canceled conferences. Conferences are a great time to learn and meet new people. I was recently talking about this to Adrian Png and Trent Schafer and we decided to create a one-day mini conference called [email protected]. Since our initial discussion others have gotten involved (Joel Kallman, Shakeeb Rahman, Peter Raganitsch, and Niels de Bruijn) and it’s blown up into a full one day 24-hour conference - for FREE!

[email protected] will be a bit different from normal webinars in the sense that everyone will be presenting from the comfort and safety of their homes (thus the name, [email protected]). We can expect some interruptions from kids, pets, etc. and we’ll make the most of it.

Everything you need to know:

• When: Thursday April 16th starting at 00:01am EDT going until 23:59pm EDT
• Where: Subscribe at apex.oracle.com/officehours so you can receive reminders for this special event.
• Format: Each talk will begin on the hour. They will be 45 mins followed by a moderated 10min Q&A then a 5 min break where the speakers will change.

The full schedule and links to talk can be found here. The schedule includes a link to add each talk that you want to attend to your calendar.

[email protected] is for the community, by the community. Please help spread the word to your friends, colleagues, schools, clients, etc!

- Adrian, Martin, and Trent

Starting with APEX 19.1 a new form type was introduced that replaces the old “Auto DML” model with much more robust functionality. Carsten Czarski wrote an in depth article about the new form regions and I suggest you read it before continuing with this post.

One really nice feature with the new form regions is the ability to have a PL/SQL process instead of automatic DML to save changes. This allows for all the great functionality to load data and manage lost updates, while having the customization of PL/SQL to process your data. An example of a Form Region with PL/SQL Code:

Note the PL/SQL code sample was taken from the help section for demo purposes.

Processes associated to a Form Region will only run if a value directly associated to that Form Region has changed. For example in the screen shot below, P4_SAL is associated with the Emp Form Region. If this value is changed than the process, Process form Emp (from the previous screen shot), will be executed.

If a field is not associated with the Form Region and it’s changed then the process associated to that Form Region will not run. For example, suppose a new page item is added: P4_SAL_INCREASE. It is not associated to any Form Region:

The Form Region PL/SQL Processing code is then changed to:

1
2
3
4
5
6
7
8

...
  -- Updating
  when 'U' then
    update emp
    set sal  = :SAL + nvl(:P4_SAL_INCREASE, 0),
    ...
    where rowid  = :ROWID;
...

If a user only updates P4_SAL_INCREASE then the page process Process form Emp (i.e. the process associated to the Form Region) will not run since none of the associated values have changed.

They’re several work arounds to this, each have their pros and cons:

• Create a computation or PL/SQL process (that runs before the current process) to set :P4_SAL := :P4_SAL + nvl(:P4_SAL_INCREASE, 0). This will change the associate P4_SAL if P4_SAL_INCREASE has a value and therefore trigger the Form Region process to run.
• Change the process type to PL/SQL Code (no associated Form Region). This will make things very straight forward but the built-in row locking and lost update prevention functionalities will be lost.

If you’ve created a modal page in APEX you’ve inevitably run into the issue of needing to refresh a modal page after changing it. You can’t use the normal browser’s refresh button or use the keyboard shortcut ctrl/cmd + r as it refreshes the underlying page, not just the modal page. Instead you either need to close and reopen the modal page or right click and select the reload frame option as shown below:

Instead of struggling with the reload situation I usually create a “Modal Reload” button that will show up in all modal pages and only work in development environments. Here’s how to add one:

On Page 0, add a region called DEV_ONLY Modal. The region will be automatically included on modal pages.

• Appearance > Template: Blank with Attributes
• Configuration > Build Option: DEV_ONLY
  • This assumes you have a Build Option called DEV_ONLY. If not it’s fairly easy to create.
• Server-side Condition > Type: Rows Returned. SQL Query:

1
2
3
4
5
6

select 1
from apex_application_pages aap
where 1=1
  and aap.application_id = :app_id
  and aap.page_id = :app_page_id
  and lower(aap.page_mode) = 'modal dialog'

Add a button to this region called RELOAD_MODAL:

• Button Name: RELOAD_MODAL
• Behavior
  • Action: Redirect to URL
  • Target
    • Type: URL
    • URL: javascript:location.reload();
• Optional (that I used for this demo)
  • Button Template: Text with Icon
  • Template Options > Type: Warning
  • Icon: fa-refresh

Demo of the Reload button in action

SQLcl supports aliases which can save a lot of time when running the same queries or scripts by not having to type them each time. A simple example of an aliases is to show the current date. Instead of having to type select sysdate from dual; each time you could do this:

1
2
3
4
5
6
7
8
9
10
11
12

-- Only do this once
alias today=
  select sysdate
  from dual
;

-- To run it just type: today

SQL> today
     SYSDATE
____________
11-MAR-20

If you are new to SQLcl aliases or the concept of login.sql (which runs each time SQLcl is launched) I suggest reading the following articles before continuing with this article.

• Intro to aliases by Jeff Smith
• login.sql

If you use multiple machines or want to share aliases you may think you’re limited to sending code snippets to people or blogging about them. Luckily SQLcl can reference files stored on the web! I’ve posted some of my SQLcl aliases on Github here. To load them just run @https://raw.githubusercontent.com/martindsouza/oracle-sqlcl/master/aliases.sql in SQLcl. If you want to constantly get an updated version of these aliases just add the line above to your login.sql file. I’m currently working on fine tuning my SQLcl aliases and will keep Github file up to date.

One example of a alias I have is called invalid which lists all invalid objects in your schema. Example:

1
2
3
4
5
6
7
8
9
10

SQL> invalid
                OBJECT_NAME     OBJECT_TYPE
___________________________ _______________
BIU_OOW_DEMO_EVENT_LOG      TRIGGER
BI_OOW_DEMO_HIST_GEN_LOG    TRIGGER
EBA_DEMO_IG_TEXT_PKG        PACKAGE BODY
OOW_DEMO_GEN_DATA_PKG       PACKAGE BODY
OOW_DEMO_PREFERENCES_BIU    TRIGGER
PKG_ORDS                    PACKAGE BODY
PKG_ORDS                    PACKAGE

Some closing thoughts:

• If referencing other people’s aliases (via the web / .sql file) be careful to either review the file or ensure it comes from a trusted source as malicious code could be easily added.
• Instead of having a lot of content in your login.sql file you can keep it to one line (referencing a file on Github for example) and keep the Github file updated with everything. This way if you change machines or use multiple machines they’ll all have the same experience.

Pipelined Functions are functions whose output is referenced as a table in a SQL statement. Here’s an example of a simple pipelined function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- Prefix: Create supporting objects
create or replace type emp_info as object (
  ename varchar2(255),
  job varchar2(255)
);
/

create or replace type emp_info_arr as table of emp_info;
/

-- Pipelined Function (with error handling)
create or replace function f_pipeline_demo
  return emp_info_arr pipelined
as
  l_scope logger_logs.scope%type := lower($$plsql_unit);
begin
  for x in (
    select ename, job
    from emp
  ) loop
    pipe row (emp_info(x.ename, x.job));
  end loop;

exception
  when others then
    logger.log_error('Unhandled Exception', l_scope); 
end;
/


-- Query First 5 rows of Pipelined Function (they're 14 ros in emp table)
select *
from table(f_pipeline_demo)
where rownum <= 5
;

   ENAME          JOB
________ ____________
KING     PRESIDENT
BLAKE    MANAGER
CLARK    MANAGER
JONES    MANAGER
SCOTT    ANALYST

The above demo serves two purposes. First to provide a basic example of a pipelined function and second to show a “hidden error” in its implementation. No errors were raised when querying the pipelined function, however looking at the logger output shows that an error was raised:

1
2
3
4
5
6
7
8
9

select logger_level, text
from logger_logs_5_min
where 1=1
  and scope like 'f_pipeline_demo'
;

 LOGGER_LEVEL  TEXT  
 ____________  ____
            2  Unhandled Exception ORA-06548: no more rows needed ...

Taking a step back, the emp table has 14 rows in it. Querying f_pipeline_demo was limited to the first 5 rows by adding where rownum <= 5. When a pipelined function is called and it realizes that it no longer needs to process additional rows it stops by raising the no_data_needed.

At first glance this may seem like a weird/bad outcome but it makes sense. Pipelined functions sometimes contain some costly computations in each loop. If only 5 rows are returned, doing the additional computations isn’t required.

Since I log all exceptions in my code, I don’t want to log no_data_needed exceptions as it’s not a “bad” exception. To get around this I can simply change the exception block in the pipelined function to:

1
2
3
4
5
6
7
8
9
10

...
exception
  -- Make sure this is before the "when others then" block
  when no_data_needed then
    null;
  when others then
    logger.log_error('Unhandled Exception', l_scope); 
end;
/

Now when the pipelined function is queried with a restricted set of rows the no_data_needed exception won’t be logged.

In Oracle, most triggers are defined similar to the snippet below:

1
2
3
4
5

create or replace trigger trigger_name
  before insert or update or delete
  on my_table for each row
begin
...

All demos below reference the following table:

1
2
3

create table demo_table(col_x varchar2(10), col_y varchar2(10));
insert into demo_table values('hello', 'world');
commit;

They’re some cases where you only want the trigger to be run when updating specific columns. They’re various ways to do this which are shown below.

In Trigger Definition (update of ...)

Using the update of clause you can explicitly list the columns that will execute the trigger when updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

create or replace trigger demo_trigger
  before update of col_x
  on demo_table for each row
begin
  dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x);
end;
/

set serveroutput on

-- Update another column
update demo_table
set col_y = 'world';

1 row updated.

-- Note: trigger wasn't run as it's not in the list of columns defined in "update of ..."


-- Update the "triggered" column
update demo_table
set col_x = 'goodby';

-- Note: This output shows the trigger was executed
***COL_X*** is being updated: goodby

1 row updated.

Checking Column (updating('col_name'))

In this example, the trigger will run for all update statements and explicit checks can be added to see if a given column is being updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

create or replace trigger demo_trigger
  before update
  on demo_table for each row
begin

  dbms_output.put_line('Trigger started');

  if updating('col_x') then
    dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x);
  end if;
end;
/

-- Running the same examples as first demo
set serveroutput on

-- Update another column
update demo_table
set col_y = 'world';

Trigger started

1 row updated.

-- Note: trigger was run (as shown by "Trigger Started" message) but COL_X wasn't updated

-- Update the "triggered" column
update demo_table
set col_x = 'goodby';

Trigger started
***COL_X*** is being updated: goodby

1 row updated.

-- Trigger was run and COL_X was updated

I recommend reading the Oracle documentation on Conditional Predicates for Detecting Triggering DML Statement

apex_string.split is a great utility that allows you to take a delimited list and make it queryable. Here’s a small example:

1
2
3
4
5
6
7
8
9

select column_value
from apex_string.split('John,Sally,Bob', ',')
;

   COLUMN_VALUE
_______________
John
Sally
Bob

You can read more about using apex_string.split and how to parse CSVs here.

When I use the split function to filter a table I used to do an explicit join as shown below. There’s nothing wrong with this concept but it can be a bit clunky to read and write.

1
2
3
4
5
6

select e.*
from dual
  join emp e on 1=1
  join (select column_value job from apex_string.split('CLERK,MANAGER', ',')) x on 1=1
    and x.job = e.job
;

Recently Stefan Dobre tweeted about how you can use value member of apex_string.split(...) to accomplish the same thing. Here’s an example:

1
2
3
4
5

select *
from emp e
where 1=1
  and e.job member of apex_string.split('CLERK,MANAGER', ',')
;

Using member of is a nice clean solution however since apex_string.split is a PL/SQL function there’s the potential of a lot of context switching (between SQL and PL/SQL) that can slow your code down. To verify this I made a simple wrapper function which logs all the calls to the function:

1
2
3
4
5
6
7
8
9
10
11
12
13

create or replace function wrapper_split(
  p_str in varchar2,
  p_sep in varchar2)
  return wwv_flow_t_varchar2
as
begin
  logger.log('START', 'wrapper_split');
  return apex_string.split(
    p_str => p_str,
    p_sep => p_sep
  );
end wrapper_split;
/

Using the previous example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

select *
from emp e
where 1=1
  and e.job member of wrapper_split('CLERK,MANAGER', ',')
;

-- ...

-- Check Logger:
select count(1) cnt
from logger_logs_5_min
where scope = 'wrapper_split'
;

   CNT
______
    14

They’re 14 rows in the emp table and apex_string.split was called 14 times. When using this on larger tables it could have significant performance impacts. Thankfully there’s a simple solution: scalar subqueries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

select *
from emp e
where 1=1
  and e.job member of (select wrapper_split('CLERK,MANAGER', ',') from dual)
;

-- ...

-- Check Logger:
select count(1) cnt
from logger_logs_5_min
where scope = 'wrapper_split'
-- Note extra predicate was added to only show logged records after the previous example
;

   CNT
______
     1

This subtle difference limits it to one PL/SQL call regardless of the size of the table it’s checking. If using the member of syntax don’t forget to also reference apex_string.split in a scalar subquery.

APEX has a set of built-in substitution strings which allow developers to reference things such as the current application ID, page number, date format, etc. For more information about how to reference substitution strings read Variables in APEX.

Finding the list of built-in substitution strings can be a bit difficult for new users to APEX (thus this article). To get the complete list of built-in substitution strings:

• Got to apex.oracle.com/shortcuts
  • Find your documentation for your version of APEX
• View the Oracle Application Express App Builder User's Guide
• Search Table of Contents for Using Built-in Substitution Strings

You’ll find a complete list along with description and usage examples. If you’re new to APEX or didn’t know about all the substitution strings I suggest scanning through it so you know which ones are available and what they contain.

APEX allows you to set the maximum idle time for a given session. This is an application level attribute that is set in Shared Components > Security Attributes. Under the Session Management there is a setting for Maximum Session Idle Time in Seconds which defaults to 3600 seconds:

When a new session is created in APEX, the max idle time is “copied” from the application settings directly into the user’s session settings. If you change the setting at an application level only new sessions will use the new value. Existing sessions will use the old value.

The following example demonstrates when changes take effect. The current max idle time is set to 3600 and one session is active (i.e. one user is logged in):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- App setting
select aa.maximum_session_idle_seconds
from apex_applications aa
where aa.application_id = 101
;

   MAXIMUM_SESSION_IDLE_SECONDS
_______________________________
                           3600

-- Active sessions
select
  ws.apex_session_id,
  ws.session_max_idle_sec
from apex_workspace_sessions ws
;

   APEX_SESSION_ID    SESSION_MAX_IDLE_SEC
__________________ _______________________
    10974491127217                    3600

The Maximum Session Idle Time in Seconds setting is is changed to 200 seconds and then a new user logs in (i.e. a new session is created):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- App setting
select aa.maximum_session_idle_seconds
from apex_applications aa
where aa.application_id = 101
;

   MAXIMUM_SESSION_IDLE_SECONDS
_______________________________
                            200

-- Active sessions
select
  ws.apex_session_id,
  ws.session_max_idle_sec
from apex_workspace_sessions ws
;

   APEX_SESSION_ID    SESSION_MAX_IDLE_SEC
__________________ _______________________
     3565745405186                     200
    10974491127217                    3600

You can see that the older session (10974491127217) still retains the old max idle time (3600 seconds) whereas the new session (3565745405186) uses the new value (200 seconds).

When testing these settings it’s important that you create a new session each time a change is made.

In APEX you can save Interactive Reports (IR) default reports as both Primary and Alternate. Both of these types of default reports are available to all users. To save an alternate report, click the Actions button then Report > Save Report (Note you must be logged into the APEX developer to save default reports). Select the option to save As Default Report Settings and the save screen will look like below:

In the APEX developer you can now see the saved report (Managers in this case) and modify its alias. By default a random number will be assign as the alias. You can modify this alias name.

You can then use the alias to link to a saved report anywhere in your application.

The following query will get a list of all the default reports, their aliases, and a report link for a given page:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

select 
  pr.region_name, 
  nvl(pir.report_name, 'Primary') report_name, 
  pir.report_alias, 
  pir.report_link_example
from dual
  join apex_application_page_ir_rpt pir on 1=1
  join apex_application_page_regions pr on 1=1
    and pr.application_id = pir.application_id
    and pr.page_id = pir.page_id
    and pr.region_id = pir.region_id
where 1=1
  and pir.application_id = :app_id
  and pir.page_id = :app_page_id
  and pir.status = 'PUBLIC'
;

REGION_NAME REPORT_NAME    REPORT_ALIAS  REPORT_LINK_EXAMPLE
___________ ______________ _____________ __________________________________________
Report 1    Primary        167050        f?p=&APP_ID.:3:&APP_SESSION.:IR_167050
Report 1    Sal GTE 2000   180055        f?p=&APP_ID.:3:&APP_SESSION.:IR_180055
Report 1    Managers       MANAGERS      f?p=&APP_ID.:3:&APP_SESSION.:IR_MANAGERS