Sometimes in your PL/SQL code you need to query a table which may or may not contain what you’re looking for. This post will cover some different ways of doing it.

The problem

Suppose you need to find an employee’s ID named SAM. If he doesn’t exist in the table then insert a new employee record.

Side note: Technically you can use a merge statement for this functionality but want to highlight that a row may not be found.

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

declare
  l_empno emp.empno%type;
begin
  select e.empno
  into l_empno
  from emp e
  where e.ename = 'SAM';

  if l_empno is null then
    -- ... insert new emp
  end if;

  -- ...
end;
/

-- Errors
ORA-01403: no data found
ORA-06512: at line 4
1.     00000 -  "no data found"

An error is raised if the employee (SAM in this case) isn’t found. Here are some options to get around this.

Explicit exception block

This is the most correct way to know if the value exists or not. Some don’t like to use this approach as it can clutter code (due to additional lines of code), on the positive side it is very explicit.

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

declare
  l_empno emp.empno%type;
begin

  begin
    select e.empno
    into l_empno
    from emp e
    where e.ename = 'SAM';
  exception
    when no_data_found then 
      l_empno := null;
  end;
  
end;
/

Using max

Using an aggregate function will always return a value even if no rows are returned. For example:

1
2
3
4
5
6
7
8

select nvl(max(dummy), 'still returns value') val
from dual
where 1=2 -- Always false
;

VAL          
-------------
still returns value

Using this logic with the base example we can do:

1
2
3
4
5
6
7
8
9
10
11

declare
  l_empno emp.empno%type;
begin
  
  select max(e.empno)
  into l_empno
  from emp e
  where e.ename = 'SAM';
  
end;
/

I’ve used this in the past, however it comes with a caveat. If used on a column that isn’t unique it will still return one value. If multiple values are present (ex: all emps who’s job is MANAGER) it can lead to false positives.

Using left join

Similar to the previous option, selecting from dual (always returns a row) we can left join to optionally find the value.

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

declare
  l_empno emp.empno%type;
begin
  
  select e.empno
  into l_empno
  from dual
    left join emp e on 1=1
      and e.ename = 'SAM';
    
end;
/

I prefer this approach compared to the max approach since if multiple values are possible an error is returned (which in most cases we want):

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

declare
  l_empno emp.empno%type;
begin
  select e.empno
  into l_empno
  from dual
    left join emp e on 1=1
     and e.job = 'MANAGER' -- Will return multiple values
  ;   
end;
/

-- Error
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

Suppose you have a set of data in Oracle, how can you find any other matching sets of data? This is different then most queries in SQL which just have “standard” predicates. For example most queries answer the question “find me all cars that are red“ (where color = 'red') whereas set comparison is answering the question “find me all cars that are the same as a given set of cars“.

A good example of this problem is if you need to find orders that are exactly the same as a given order. An order can have multiple products in it. It’s not as easy as comparing one row to another, rather sets of rows to other sets of rows.

Set Theory

Before continuing it’s important to go over some basic set theory. One way to think of sets are as arrays of data. When comparing for equivalence of sets we need to ask two questions:

• Is everything in A in B? (i.e. A - B = 0)
• Is everything in B in A? (i.e. B - A = 0)

At first glance, the second point may seem unnecessary but it isn’t as A may be a subset of B.

Take for example the following arrays:

• A = 1,2,3
• B = 1,2,3,4

In the above case, everything in A is in B, however everything in B (i.e. 4) is not in A.

Example

Suppose we have a table (tab_a) and we want to find any other tables with the exact same column definition.

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

-- Base table to be compared against
create table tab_a (
  id number,
  first_name varchar2(255),
  birth_date date
);

-- Close to tab_a but has an additional column (last_name)
create table tab_b (
  id number,
  first_name varchar2(255),
  last_name varchar2(255),
  birth_date date
);

-- Same as tab_a
create table tab_c (
  id number,
  first_name varchar2(255),
  birth_date date
);

The above script will create three tables. If we start with tab_a only tab_c is the exact same. Using the set theory logic above, and the minus set operator clause in Oracle you can find matching tables using the following query:

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

select at_match.table_name
from dual
  -- Using the all_tables view so we have a large data set to compare against
  join all_tables at_src on 1=1
  join all_tables at_match on 1=1
    -- Exclude the same table
    and at_match.table_name != at_src.table_name
where 1=1
  and at_src.table_name = 'TAB_A'
  -- A - B = 0
  and not exists (
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_src.table_name
    minus
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_match.table_name
  )
  -- B - A = 0
  and not exists (
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_match.table_name
    minus
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_src.table_name
  )
;

TABLE_NAME
----------
TAB_C

Prior to 12.2 validating dates and numbers was a bit of a pain as you had to write your own custom PL/SQL function. OOS-Utils has a quick solution to this issue and I recommend using oos_util_validation.is_number and oos_util_validation.is_date.

If you’re using Oracle 12.2 or above you can (and should) use validation_conversion instead. Here are some examples of how to use it along with results:

1
2
3
4
5
6
7
8
9
10
11

select
  validate_conversion('123.34' as number, '999.00') valid_number,
  validate_conversion('abc' as number) invalid_number,
  validate_conversion('01-Jan-19' as date, 'DD-MON-YYYY') valid_date,
  validate_conversion('01-BAD-19' as date, 'DD-MON-YYYY') invalid_date
from dual
;

VALID_NUMBER INVALID_NUMBER VALID_DATE INVALID_DATE
------------ -------------- ---------- ------------
           1              0          1            0

validate_conversion will return 1 for valid conversions and 0 for invalid conversions.

Note: in the next release of OOS Utils, the validation functions will use validate_conversion if your database is 12.2 or older internally.

APEX has the ability to easily have Read Only (RO) pages. The screen shot below shows how to define the page’s RO attribute. For this demo it’s set to Always but it could use any of the usual conditions in APEX.

When the page is loaded, all its page items are not editable:

They’re a few things to consider when using the Read Only attribute for a page:

Hacking a page item with JavaScript

In the previous image I can not modify the page item by standard means. I can still modify it using JavaScript (JS): apex.item('P2_NAME').setValue('martin'); Submitting the page will trigger an error (which is good):

This information is relevant as you should probably disable any Dynamic Actions (DA) that modify page items when the page is in Read Only mode. Conditionally running DAs can easily be done by applying the declarative condition to a DA Page/Region is Read Only. You could also use the apex_page.is_read_only if you have additional server-side conditions for a DA.

Page can still be submitted

In this demo, a page process has been created that logs a message using Logger.

If a user clicks the Save button the process is still run, despite the page being in Read Only mode. The next logical thing to do is to put a condition on the Save button so it does not appear when the page is in Read Only mode. Users can bypass this by running apex.page.submit('SAVE'); in the console. The following demo highlights these concerns:

Similar to the previous section, if your page uses the Read Only attribute you should also apply conditions on the corresponding processes to restrict them when the page is in Read Only mode.

Suppose you have a region with a set of cascading LOVs and report that references both of these items. When either of these items is refreshed, the report should be refreshed. When the parent item is changed it will trigger multiple refreshes of the report. The following example highlights this problem:

p2_deptno:

1
2
3

select d.dname d, d.deptno r
from dept d
order by dname

p2_empno:

1
2
3
4
5

select e.ename d, e.empno r
from emp e
where 1=1
  and e.deptno = nvl(:p2_deptno, e.deptno)
order by e.ename

Cascading LOV Parent Item(s): p2_deptno

Report Region

Type: Classic Report

SQL Query:

1
2
3
4
5

select empno, ename, deptno
from emp e
where 1=1
  and e.deptno = nvl(:p2_deptno, e.deptno)
  and e.empno = nvl(:p2_empno, e.empno)

Page Items to Submit: p2_deptno,p2_empno

Next, create a Dynamic Action (DA) that will trigger a refresh on the Report region each time any item has changed:

When p2_deptno is changed, 3 Ajax requests occur:

1. Report region is refreshed (reason: Page items to Submit contains p2_deptno)
2. p2_empno LOV is refreshed (reason: Cascading LOV Parent Item(s): p2_deptno). This triggers another refresh to the Report region (see next step)
3. Report region is refreshed (reason: Page items to Submit contains p2_empno)

The following demo highlights this problem. The report refreshes so quickly the only way to see the problem is to look at the differnet Ajax calls. Note: the order of the Ajax responses may change since it’s asynchronous.

If your report runs relatively quick you may not even notice the duplicate refresh. I had a situation where we had five cascading LOVs and the underlying report was refreshed five times and was very noticeable to the users.

Thankfully there’s a simple fix to this problem. When a JavaScript (JS) change event occurs and is triggered “by a user” the JS event e.originalEvent contains a value. In the above example step 3 was not really triggered by a user, rather triggered by a cascading LOV. In this case the JS event attribute originalEvent is null.

Using the event attribute originalEvent we can solve the problem by adding the following Client-side Condition to the DA: this.browserEvent.originalEvent !== undefined

Now when p2_deptno is modified only two Ajax calls occur (which is correct):

Thanks to Adrian Png for helping me with this.

While developing standard reports in APEX I usually do the following repetitive steps. Modify something to do with the report (query, setting, attributes, source data, etc). After saving or committing my changes I refresh the page to see how it looks. This gets frustrating when doing a lot of small edits as a full page refresh is not required.

I recently created a script to solve this problem. The script adds a small refresh icon in the first column header of each standard report. Clicking it will refresh the current report. The following demo shows it in action and how you don’t need a full page refresh to view changes to a report: (note: you don’t need to run this in the console, which will be discussed at the end of the post)

The following steps outline how to integrate this directly in your application:

• On Page 0, create a new Dynamic Action (DA)
  • When > Event: Page Load
    • Action > True > JavaScript:

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

var buttonHtml = '<span aria-hidden="true" class="apex-dev-refresh fa fa-refresh fa-anim-spin"></span>';

$('.t-Report').each(function(){
  var $this = $(this);

  // Add button to screen
  $this.find('th:first').prepend(buttonHtml);

  // Regenerate the refresh button after a region refresh
  $('#' + $this.data('regionId')).on('apexafterrefresh',function(){
    $(this).find('th:first').prepend(buttonHtml);
  })

})

// Register event listener
$(document).on('click', ".apex-dev-refresh", function(){
  $(this).closest('.t-Report').trigger('apexrefresh');
});

The DA should look like this:

You can change the icon and its settings to anything you want (i.e. by modifying the buttonHtml). You can use the APEX Icon Generator tool to generate the code for a new icon.

Though not required, I highly recommend you add a Build Option (read Scott Wesley’s article - I have a Build Option called DEV_ONLY) to restrict the DA to development only.

You could add the code to a JavaScript file and include it with your application or create a Dynamic Action plugin. (If you create a plugin for this please let me know and I’ll be sure to refreence it.

They’re certain things in life that absolutely drive me crazy. I can probably start an entire blog on this topic but instead will focus on something I see many developers do in SQL Developer.

Unfortunately I’ve see a lot of developers run their queries using a mouse instead of a keyboard shortcut (note no semicolon between queries) as demonstrated below:

You’ll notice that my second query had an error with it so I had to select it again, then click the run button to execute it.

At first glance this may not seem like a big deal however if you run hundreds of queries a day that are much longer than two lines the inefficiencies really start to add up. With the use of the right syntax you can execute everything in SQL Developer via keyboard shortcuts. In the example below everything is run using ctrl+enter

The syntax structure that you need to use is very simple:

• After every query have a semicolon (;)
• After every PL/SQL block end with a semicolon (;) and then a forward slash (/)

The following code sample is what was used in the second demo:

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

select *
from emp
;

select *
from dept
;

begin
  dbms_output.put_line('hello');
end;
/

All my releases are done via scripts. In other words I don’t deploy APEX applications from the browser, rather straight from SQLcl. Since some of my releases can take a while and puts the code in an unstable state I usually disable the application for the duration of the release. This article will show how to disable an APEX application in PL/SQL.

You can manually disable an application via Shared Components > Application Definition > Availability > Status:

When setting the Status to Unavailable it will look like this for users:

Note: they’re various status options which allow you to determine how to define the status message.

Going back to releases, the overall order things occur in are:

1. Disable APEX application
2. Run release (DDL, DML, etc)
3. Update APEX application (this will clear the disabled setting that was manually done in the first step.

PL/SQL code to disable an APEX application:

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

declare
  l_app_id apex_applications.application_id%type := 102;
begin
  -- Pre APEX 18.x
  -- oos_util_apex.create_session(
  --   p_app_id => l_app_id,
  --   p_user_name => 'RELEASE'
  -- );

  -- APEX 18.x +
  apex_session.create_session (
    p_app_id => l_app_id,
    p_page_id => 1,
    p_username => 'RELEASE');

  apex_util.set_application_status(
    p_application_id => l_app_id,
    p_application_status => 'UNAVAILABLE',
    p_unavailable_value => 'Updating application');
  
  commit;
end;
/

If using a version lower than APEX 18.x you’ll need to install OOS Utils to create an APEX session in SQL.

Before running the above code you’ll need to enable the runtime API by going to Shared Components > Security Attributes > Database Session > Runtime API Usage and check Modify This Application.

Documention for apex_util.set_application_status.

Suppose you have a requirement that all saved Interactive Reports (IR) names must be lowercase. How can you enforce it? Using some JavaScript (JS) you can “intercept” the save and delete functions and either continue on with them or not. Note my uses case was very different than a naming standard but using it as an example as it’s very simple.

The following code snippet will intercept the save functionality (you can inject your business rules accordingly). It’s important to note that this code must be run in the page’s Function and Global Variable Declaration section (as part of the page’s properties). A better alternative is to load it via a JS file (hint: use APEX Nitro). It will not work if you try to run it as is in your browser’s console.

Warning: The following code is using undocumented features in the APEX IR JS library. Use at your discretion.

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

$(document).ready(function() {

  $.widget("apex.interactiveReport", $.apex.interactiveReport, {
    
    // Saving a Default report
    _saveDefault: function () {
      console.log("_saveDefault");
      console.log(this);
      console.log(this._getId("report_name"));
      console.log('stopping');
      // return this._super();
    },

    // Saving a Public or Private reports
    _save: function () {
      console.log('_save');
      console.log(this);
      console.log(this._getId("report_name"));
      console.log('stopping');
      // return this._super();
    },

    // Deleting a report (user clicking the "X")
    _remove: function () {
      console.log('_remove');
      console.log(this);
      console.log(this._getId("report_name"));
      console.log('stopping');
      // return this._super();
    }
  });

});

I’ve commented out return this._super();. Re-enabling it will continue the function (save, remove, etc) as expected. In most cases you would wrap it in an if condition based on your requirements.

The following demo shows this code in action. Both the save and delete requests don’t complete as the JS code intercepts the requests and logs some information.

When reviewing a team’s release processes I commonly encounter developers spending a lot of time and concern over the order of their PL/SQL objects (i.e. views and packages). For example if pkg_a references view_b then the release looks like:

1
2
3
4
5

...
@pkg_a.pks
@pkg_a.pkb
@view_b.sql
...

At first glance this may not seem too bad but when working with larger applications or a high change rate of code (think initial development) this can take up a lot of wasted time. It can also get much more complicated when dealing with many packages and views to understand their dependencies to ensure that the code is compiled in the “correct order”. Thankfully you don’t need to worry about this with Oracle as there’s an API to compile all the invalid objects:

1
2
3
4

begin
 dbms_utility.compile_schema(schema => user, compile_all => false);
end;
/

For all of this to work effectively ensure that all your views are written as create or replace force view view_name note the keyword force. This will ensure that the view is compiled even if it’s invalid.

Using the dbms_utility.compile_schema procedure and leveraging the force in the create view statements, most of my releases look like the following:

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

-- views
@view_b.sql
...

-- packages
@pkg_a.pks
@pkg_a.pkb
...

-- Recompile invalid objects
begin
 dbms_utility.compile_schema(schema => user, compile_all => false);
end;
/

For very active development cycles (where a lot of the code is changing) I even have scripts to scrape both the packages and views folder and auto-inject all the file names into the release file. This way developers don’t need to worry about which packages or views were modified for a given release (nor their order/dependencies) as they will all be re-compiled.