Exotic SQL: Hints that can change results

In general hints are suggestions for the Oracle cost based optimizer (CBO) that can influence performance but will never change the result. The CBO will comply with the hint at all costs – if it is possible.

True? Well not always!

There is a very small set of hints which can change the result of a select or DML statement.

The CBO is an extremly complicated piece of software, so it can happen that there are bugs in it, which in turn lead to different results. This is not what I am considering here.

So here is my collection of hints that are able to change the output of a statement.

ignore_row_on_dupkey_index

This hint allows to avoid UK errors during an insert. So only those rows are inserted that do not violate the unique index. The other rows are silently ignored.

See this example by Richard Foote about the hint:
https://richardfoote.wordpress.com/2010/12/20/oracle11g-ignore_row_on_dupkey_index-hint-micro-cuts/

Similar hints are
(CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE)

They are all called semantic hints. Which has the side effect that they are not disabled by setting _optimizer_ignore_hints=TRUE

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Comments.html#GUID-4664D3D8-6312-4C15-8E8F-4872DD7A44F8

For more examples see this older post from Rob van Wijk
http://rwijk.blogspot.de/2009/10/three-new-hints.html

driving_site

I consider the driving_site hint to be one of the “good” hints. There are many cases where we need it. It is good to set it, because it enables behaviour that otherwise the optimizer can not choose.

However there is one strange special issue that I found.
I described this behaviour in an older blog post: https://wordpress.com/post/svenweller.wordpress.com/108

If we have a distributed query then SYSTIMESTAMP and also DBTIMEZONE is affected by the driving site hint. It will return the timezone from the local (no hint) or the remote (with hint) database. SYSDATE however was not affected.

This effect happend on an older 10g database, I wasn’t able to test if the behaviour still exists in 12c.

A similar effect can be reached using the materialize hint.

opt_param(‘OPTIMIZER_SECURE_VIEW_MERGING’,’true|false’)

The opt_param hint will allow to set instance parameters only for the time while the select is running. Some of those parameters can potentially influence the outcome of queries.

One of them is OPTIMIZER_SECURE_VIEW_MERGING
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/OPTIMIZER_SECURE_VIEW_MERGING.html

An example from Tom Kyte:

the goal of this parameter [OPTIMIZER_SECURE_VIEW_MERGING] is to prevent a function owned by some user B from seeing data of some other user A that is should not see. For example, …

and after some setup…

b%ORA11GR2> set autotrace on explain
b%ORA11GR2> alter system set optimizer_secure_view_merging = true;

System altered.

b%ORA11GR2> select * from a.v v1 where f(y) = ‘ok to see’;

X Y
———- ——————————
1 ok to see

I see: ok to see

Execution Plan
———————————————————-
Plan hash value: 1931062764

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 10000 | 292K| 27206 (1)| 00:05:27 |
|* 1 | VIEW | V | 10000 | 292K| 27206 (1)| 00:05:27 |
|* 2 | TABLE ACCESS FULL| T | 10000 | 292K| 27206 (1)| 00:05:27 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“F”(“Y”)=’ok to see’)
2 – filter(“Y_F”(“X”)=1)

there it is clear that the Y_F(X)=1 is evaluated and then f(y) is

b%ORA11GR2> alter system set optimizer_secure_view_merging = false;

System altered.

b%ORA11GR2> select * from a.v v1 where f(y) = ‘ok to see’;

X Y
———- ——————————
1 ok to see

I see: ok to see
I see: NOT ok to see

whoops, my function saw data that it should not

Execution Plan
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100 | 3000 | 27207 (1)| 00:05:27 |
|* 1 | TABLE ACCESS FULL| T | 100 | 3000 | 27207 (1)| 00:05:27 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“F”(“Y”)=’ok to see’ AND “Y_F”(“X”)=1)

and now we can see the converse is true

b%ORA11GR2> alter system set optimizer_secure_view_merging = true;

System altered.

b%ORA11GR2> set autotrace off

There are also some side effects with VPD. See https://antognini.ch/2011/09/optimizer_secure_view_merging-and-vpd/

Also in some cases this parameter can result in errors.
Here is a report of such a case: https://support.esri.com/en/technical-article/000010620

Another example for a parameter that can influence result sets would be RESULT_CACHE_REMOTE_EXPIRATION
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams208.htm#REFRN10294

The default is 0. Changeing it to a positiv integer, will allow us to get “stale” data from the cache instead of fetching a fresh value from the remote source.

fresh_mv

This 12.2 hint can make a real time materialised view to refresh itself. So if the data is stale then the hint will use the MV and somehow add the missing data to it. Without the hint we would still see old data, but with it we will see new data.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Comments.html#GUID-5EF4198B-50B3-40D8-B12A-3D3115C69D9B

The FRESH_MV hint applies when querying a real-time materialized view. This hint instructs the optimizer to use on-query computation to fetch up-to-date data from the materialized view, even if the materialized view is stale.

A similar connected hint would be
no_rewrite/rewrite_or_error
All of those hints have the ability to change the behaviour, especially the usage of stale materialized views. Which then can give a different result.

Disclaimer

This list has no pretence to be complete. Also some of the mentioned behaviours might change in different database versions. Especially if it is not documented and not intended behaviour.

There are more such hints. I have recorded some more, but couldn’t consistently reproduce the issue. It might have been related to some bug or some other obscure circumstance, that I can’t remember.

Here is the current list for those “watch out” candidates:

  • first_rows
  • (no_)query_transformation
  • (no_)result_cache

Result cache in combination with deterministic functions, that are not truely deterministic can easily produce wrong/different results. However the main culprit then is the wrong usage of the deterministic pragma.

And some more strange things

The SELECT clause can also influence the number of rows. And not only via distinct.

    with tbl as (select 1 val from dual union all  
                select 2 val from dual union all  
                select 3 val from dual )  
        SELECT  CASE  0  
                    WHEN  0  
                        THEN  'Aardvark'  
                    WHEN  SUM (val)  
                        THEN  'Baracuda'  
                END  AS c  
        FROM    tbl;  

Aardvark
Aardvark
Aardvark
3 rows selected.

    with tbl as (select 1 val from dual union all  
                select 2 val from dual union all  
                select 3 val from dual )  
        SELECT  CASE  6  
                    WHEN  0  
                        THEN  'Aardvark'  
                    WHEN  SUM (val)  
                        THEN  'Baracuda'  
                END  AS c  
        FROM    tbl;  

Baracuda
1 row selected.

Discovered by Frank Kulash and discussed and tested in this OTN thread
https://community.oracle.com/thread/4097998?start=15&tstart=0

Advertisements

Apex 5.1: Interactive Grids – How to customize toolbar buttons

Introduction

I wanted to slightly change the toolbar buttons in some Interactive Grid. Since in my application all buttons have an icon, I wanted to use the same icon also for IG buttons, especially for actions like “Save” or “Add row”.

What helped me were John Snyders excellent postings “How to hack Apex Interactive Grids”. In this specific case I mostly referred to the second part of the series.

But there were some things that aren’t explained and I needed to figure them out myself.

Toolbars – before and after

The users were used how the buttons looked like in the previous Tabular Form.

Tabular Form
toolbar_tab_form

There are 4 buttons

  • Cancel: go back to previous page
  • Delete: to delete the marked rows
  • Save: To save the current changes
  • Add Row: to insert a new record

The buttons were using the template type “Primary”, thats why they are blue.

Default Interactive Grid
IG_modify_toolbar_default_IG

The “Save” button was already added declarativly, by setting the property in the Attributes/toolbar section of the IG.

The “Add Row” button is created as soon as we allow to Edit.
Attributes/Edit/Allowed Operations/Add Row

As we can see, the “Cancel” button and the “Delete” button are missing. But more importantly, the buttons have no icons and are missing the color.

Customize Interactive Grid

Here is how to change that. I’m not showing how to add the cancel button. This can easily be positioned somehwere else, like in the region header. But the Delete button needs to be added. And the visualization of the buttons should match with the rest of the application.

First give the grid a static ID. Here I use “IG_MYGRID”.

Adding the delete button

In Apex 5.1 many objects now have an “advanced/javascript code” property. This is incredibly useful. It is a kind of hook to add additional features programatically.

For an Interactive Grid we can use it to customize the grid further.

We first find our IG and its toolbar object. The toolbar consists of several toolbar groups. They are separated by thin vertical lines. The group “actions3” holds currently only the “Add Row” button, this is where we want to add the “Delete” button too.

function(config) {
    let $ = apex.jQuery,
        toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(), // copy the whole toolbar
        toolbarGroup = toolbarData.toolbarFind("actions3"); // this is the group with the action=add row

    // add a new "delete" button
    toolbarGroup.controls.push({type: "BUTTON",
                                action: "selection-delete"
                               });

    //store the config
    config.toolbarData = toolbarData;
    return config;
}

This is the result:

IG_modify_toolbar_with_delete_button

The button is there – that’s good. But it does look kind of ugly.
Not quite what we want yet.

We have to understand that an IG provides several actions. Here we used the “delete-selection” action. The actions already have several properties, like an icon. We can overwrite those properties for each visual component that is used to represent that action. In our case the toolbar button.

The trashcan icon might look better in your case, it depends on template/theme styling. In my case it comes with a slightly darker background. The same happens for the normal save icon (icon-ig-save).

Add and change the icons

Let’s add an icon also for the “Save” and the “Add Row” buttons.

Using the toolbarFind Method allows us, to identify the two new existing buttons. Check John Snyders blog to learn more about the toolbarFind method. It is very useful.

function(config) {
    let $ = apex.jQuery,
        toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(), // copy the whole toolbar
        toolbarGroup = toolbarData.toolbarFind("actions3"); // this is the group with the action=add row
        addrowAction = toolbarData.toolbarFind("selection-add-row"), //add row button
        saveAction = toolbarData.toolbarFind("save"); // Save button

    // add a new "delete" button
    toolbarGroup.controls.push({type: "BUTTON",
                                action: "selection-delete",
                                icon: "icon-ig-delete", // alternative FontAwesome icon: "fa fa-trash",
                                iconBeforeLabel: true,
                                hot: true
                               });

    
    // manipulate the buttons
    addrowAction.icon = "icon-ig-add-row"; // alternative font awesome icon: "fa fa-plus";
    addrowAction.iconBeforeLabel = true;
    addrowAction.hot = true;

    saveAction.iconBeforeLabel = true;
    saveAction.icon ="icon-ig-save-as"; // list of alternative grid icons (Font Apex):icon-ig-save,icon-irr-saved-report
    //saveAction.icon ="fa fa-save"; // list of alternative font awesome icons: fa-save,fa-check
    saveAction.hot = true;

    
    //store the config
    config.toolbarData = toolbarData;
    return config;
}

Result
IG_modify_toolbar_custom_with_icons

It is still not perfect. The “Save” and the “Add Row” buttons do have an icon now and the icon is positioned before the label. Still the icon for the “Delete” looks ugly.

I also set all buttons to “hot=true”. The save button already has that as default. Why to do so is explained in the next “css” section.

IG_modify_toolbar_hot_as_Primary

Styling the toolbar buttons with css

Instead of the normal grid icons I wanted to use FontApex or FontAwesome icons.
You have to experiment a little what works in your specific case.

The application where I added this grid, still uses FontAwesome. The users unfortunatly decided against switching to FontApex. They were use to the older items, and didn’t like how the new font looks in the menu. I sometimes regret a little that I gave them this choice. FontApex is the much better option, especially because of the modifiers that can be added. Check the Universal Them template application for examples of that.

The problem in the Interactive Grid is, that the grid icons are added with a class a-icon which in turn is fixed to FontApex. The creates two additional problems. Since I didn’t use FontApex other icons where not included, only the ones that the grid provides. But I wanted the buttons to look like all other buttons in the application, including the same icon font.

This little css needs to be added to the css inline section of the page.

#IG_MYGRID span.a-Icon.fa, #IG_MYGRID span.a-Icon.fa:before {
  font-family: FontAwesome !important; 
}
#IG_MYGRID_ig_toolbar .a-Button--hot {
    background-color: #0072b9;
    color: #ffffff;
    font-weight: inherit;
}

It overwrites the Font-Family and it uses the class “a-Button–hot” (which is added for “hot” buttons) to style the toolbar buttons blue. Unfortunatly I had to use !important for th Font-Family, because that was already so in the apex core.css.

I also did change the icons on the buttons to reuse the same icons as we had on the tabular form. Check the comments in the previous code, to see which icons are good alternatives if the correct font is available.

Result

IG with custom toolbar buttons

And we did it!

I think there are a few other ways how to reach the same result, but this is what worked well for me.

Other stuff and some comments

Page attribute “reload on Submit”

When moving from a tabular form to an interactive grid we can copy the SELECT statement and create a new IG region with that.

However we must change one page setting. The “Reload on Submit” must be changed from “Always” to “Only for Success” .

Page_Reload_on_Submit

Because of this setting, we can not use a tabular form and a IG on the same page. I would prefer to see both regions as long as the new IG is still in developement. But the setting must be different when showing a tabular form compared to showing an interactive grid.

How to find out about all this

If we want to know what actions are available we have two approaches. One is, find a button/menu point that does the action, inspect the element and look for the data-action attribute. For example this works for the add-row button.

data-action="selection-add-row"

The other way is to use the console and grab all actions.

We open the console on the page with the grid and run the following javascript commands

to show grid actions

apex.region( "IG_MYGRID" ).widget().interactiveGrid( "getActions" ).list()

or alternativly

apex.region("IG_MYGRID").widget().interactiveGrid("getActions").list().forEach(function(a) { console.log("Action Label: " + a.label + ", Name: " + a.name + (a.choice !== undefined ? ", Choice: " + a.choice : "") ); });

to show row actions

apex.region("IG_MYGRID").widget().interactiveGrid("getViews").grid.rowActionMenu$.menu("option")

Then check the object in the console to see what actions are possible, how they are named and which properties they have.

Exotic SQL: Bubble Sort with Model Clause

Introduction

This is a “just for fun” experiment. But I demonstrate some model clause effects on the way.

Task: Sort a pipe separated list using SQL.

For example this string ’29|1|3004|3|2|24′ has 6 elements separated by |. The elements should be sorted in numerical order so that the resulting string looks like this ‘1|2|3|24|29|3004’.

Standard way

The “normal” way would be to tokenize the input string, put the tokens into rows, sort the rows and aggregate the sorted result to reassemble the string.

Here is one possible solution.


with inputdata(src)
     as (select '29|1|3004|3|2|24' from dual)
    ,num_rows (part, remains)
     as (select to_number(substr(i.src,1,coalesce(nullif(instr(i.src,'|'),0)-1,length(i.src)))) as part
               ,substr(src,instr(i.src,'|')+1) as remains
         from inputdata i
         UNION ALL
         select to_number(substr(r.remains,1,coalesce(nullif(instr(r.remains,'|'),0)-1,length(r.remains)))) as part
               ,substr(remains, nullif(instr(r.remains,'|'),0)+1) as remains
         from num_rows r
         where r.remains is not null
         )
    , combine (resultdata)
as (select listagg(to_char(part,'FM9999999999'),'|') within group (order by part) from num_rows)
select * from combine;

The num_rows subquery in this example is a recursive with clause that splits the input string into rows, one row for each token. The logic to split the string into tokens is based around substr . Essentially it just cuts of an element from the beginning of the string and keeps the remainder until there are no elements left.
The combine subquery then uses listagg to sort and convert the rows into a string again.

Standard SQL technologies that each decent developer should know about.

There are multiple ways how to tokenize,  how to create the rows and how to aggregate the rows again. But the general principle is still the same.

Here is a second example solution. This time using connect by to create rows and some regular expressions to count and split.

with inputdata(src) as (select '29|1|3004|3|2|24' from dual )
select listagg(token,'|') within group (order by to_number(token)) as res
from (
  select src, regexp_substr(src,'[^|]+', 1, level) token
  from inputdata
  connect by level <= regexp_count(src,'[|*]') + 1
  )
group by src;

Exotic way

I was wondering if we can use a totally different approach.

Instead of letting the database do the hard work and implement a sort mechanism, we can do it ourselves. Here comes bubbles sort. It is one of the most inefficient sort mechanisms you can think of (unless the list is already sorted), but it is fairly easy to implement.

Since bubble sort is an iterative approach with a simple set of rules, the model clause immediately jumped to my mind.

So here is a solution based around a slightly optimised bubble sort mechanism (gnome sort). We will take it apart afterwards.
The solution here creates several rows, but this is just to see and check each step of the iteration. It is possible to do this using one row (dim=0) only, although it is much harder to develop and to understand.

with inputdata(src) as (select '29|1|3004|3|2|24' from dual)
select *
from inputdata
model dimension by (0 as dim)
measures (  src
          , 1 ele_pos1
          , 2 ele_pos2
          , cast (regexp_substr(src,'[^\|]+',1,1) as varchar2(100)) token1
          , cast (regexp_substr(src,'[^\|]+',1,2) as varchar2(100)) token2
          , regexp_count(src,'\|')+1 max_element
          , 2 hwm
          , cast (src as varchar2(500)) as res
          )
rules iterate (500)
      until (-- we can stop if the high water mark is at the last position and no switch needed
             hwm[iteration_number] >= max_element[0]
             and (ele_pos1[iteration_number] = 1 or to_number(token1[iteration_number]) < to_number(token2[iteration_number])))
( -- switch tokens if left token > right token
  res[iteration_number+1]
    = case when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
      -- do the switch
      regexp_replace(
             regexp_replace(res[iteration_number],'[^\|]+',token2[iteration_number],1,ele_pos1[iteration_number])
                            ,'[^\|]+',token1[iteration_number],1,ele_pos2[iteration_number])
      else res[iteration_number] -- no switch needed
      end ,
  -- calculate next position for token1
  ele_pos1[iteration_number+1]
  = case
      when ele_pos1[iteration_number] = 1 then
        -- we reached first position, so go back to hwm
        hwm[iteration_number]
      when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
        -- after a switch, move one position the the left and check there
        ele_pos1[iteration_number] - 1
      when ele_pos1[iteration_number] + 1 <= max_element[0]-1 then
        -- no more switch, so go to hwm and look for next element
         hwm[iteration_number]
       else
         -- just in case, don't do anything
         ele_pos1[iteration_number]
       end,
   -- calculate next position for token2
   ele_pos2[iteration_number+1]
    = case
       when ele_pos2[iteration_number] = 2 then
         --ele_pos2[iteration_number] + 1
         hwm[iteration_number] + 1
       when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
        ele_pos2[iteration_number] - 1
      when ele_pos2[iteration_number] + 1 <= max_element[0] then
        hwm[iteration_number] + 1
      else
        ele_pos2[iteration_number]
      end,
  -- calculate high water mark
  hwm[iteration_number+1]
  = greatest(hwm[iteration_number],ele_pos2[iteration_number+1]),
  -- get token1 for new calculated position1
  token1[iteration_number+1]
  = regexp_substr(res[iteration_number+1],'[^\|]+',1,ele_pos1[iteration_number+1]),
  -- get token2 for new calculated position2
  token2[iteration_number+1]
  = regexp_substr(res[iteration_number+1],'[^\|]+',1,ele_pos2[iteration_number+1])
 );

Here is the result, but I show only the important column. Feel free to run the statement yourself (works on all supported database versions) and see the other helper columns.

Result (only Res column)
29|1|3004|3|2|24
1|29|3004|3|2|24
1|29|3004|3|2|24
1|29|3|3004|2|24
1|3|29|3004|2|24
1|3|29|3004|2|24
1|3|29|2|3004|24
1|3|2|29|3004|24
1|2|3|29|3004|24
1|2|3|29|3004|24
1|2|3|29|24|3004
1|2|3|24|29|3004
1|2|3|24|29|3004

Fairly easy SQL isn’t it?

65 rows instead of 8. So if your aim is to write obfuscated SQL then this certainly is the way to go.

If you want to understand it, skip to the “algorithm explained” chapter.

Rant

Now what is wrong with the model clause!? A simple algorithm like bubble sort looks this complex?

In the recent months I have written a number of model clause solutions for different problems. However none of them is easy. Apart from very specific cases all model solutions are terrible to look at and terrible complex to build. In comparison it is much easier to do the same in excel.

I attribute this to several things.

  1. The way to reference a cell is verbose. And when we look at the code then the brackets break the eye scanning mechanism of a trained developer eye. If more than one dimension is involved it is even worse.
    Here are some examples:
    measure1[cv(),3] or  resultdata[iteration_number+1]
    It is hard to see this as one cell reference.
    In Excel this would simply be: B$3 or C2.
  2. The way to reference a cell from the previous row is complex. It requires a working calculation for the cell address. We can do this by applying the row_number() analytic function or using iteration_number (for iterative models). But this requires some extra logic.
  3. There is no simple way to set several different measures at once. A rule can only be applied for one measure. This means that sometimes we need to repeat the same logic for a different measure.
    In the bubble sort example this happened for the calculation for the position of token 1 and for position of token 2. In this case it can be simplified a lot because the position of token 2 is always exactly +1 from position of token 1. In other cases it is not that simple. Additional measures can serve as a kind of variable to capture rules that then need to applied to several other columns.

Excel Comparison

The model clause resembles Excel very much.

  • Dimensions are rows.
  • Measures are columns.
  • Rules are formulas

But why does the model clause feels so more complex that some simple excel cells.

Excel has a similar way to address cells. It is called the R1C1-style reference. It uses number coordinates to find a cell. But most of times we use the A1-Style reference. This is a little more intuitive and much shorter.

But more importantly the process to build formulas (=rules) is different. While building formulas we click on a source cell and its coordinates are used automatically. Excel then calculates the difference from the current cell to the referenced cell.

The main difference to Excel is that is has a separate formula for each cell, and we use just a simple way to fill out the formulas to the other cells. We don’t need that copy mechanism with the model clause, because the rules automatically are applied for all relevant cells. But it is hard with the model clause to give the direct reference for a different row.

Also the different styles and the different ways to use absolute and relative cell references in Excel (e.g. B$2) are more convenient than doing the same in the model clause.

Pro-Tipp: A good way to develop model rules is to build the formulas in excel first, then copy the logic to SQL.

The model clause also has several advantages over excel. Among others are

  • Excel only has 2 dimensions and no decent concept of partitions.
  • Excel columns can not be named (although single cells and cell ranges can).
  • Model allows to return only updated rows.
  • Model has a easy way to work with invalid cell references (IGNORE NAV)

Algorithm explained

Before we look at the statement, let’s look at the bubble sort logic first.

bubble sort logic

Start: 29|1|3004|3|2|24

I start with the first two tokens. 29 and 1. Token1 is always the left and token2 is always the right token.

Rule 1: If token1 is bigger than token2 we need to switch them.
Rule 2: If we are at position 1, we can go one step to the right.
Rule 3: If we don’t need to switch, then we can also go one step to the right.
Rule 4: If we did switch then go one step to the left.

Now lets see how the rules can be applied to the string.

Step 1: 1|29|3004|3|2|24 – Rule 1
Step 2: 1|29|3004|3|2|24 – Rule 2
Step 3: 1|29|3004|3|2|24 – Rule 3
Step 4: 1|29|3|3004|2|24 – Rule 1
Step 5: 1|3|29|3004|2|24 – Rules 4+1
Step 6: 1|3|29|3004|2|24 – Rule 3 (here I used an additional optimisation, by storing a high water mark and jumping as far right as the HWM allows)
Step 7: 1|3|29|2|3004|24 – Rule 1

Stop: We can finish the iteration if the HWM is to the far right and if no switch is needed anymore.

model SQL explained in detail

So how is this logic implemented in the model clause?

A token is found using a regular expression with the position of the token.

regexp_substr(src,'[^\|]+',1,4)

This finds the fourth token.

We start with a way to number our rows. The initial row is defined as 0.

dimension by (0 as dim)

0 is only used because later we address our rows using an iteration_number. If we would start with 1, then we can potentially overwrite our data. Often 0 is a good starting row.

We then define several columns. And the initial value for those columns.

measures (  src
   , 1 ele_pos1
   , 2 ele_pos2
   , cast (regexp_substr(src,'[^\|]+',1,1) as varchar2(100)) token1
   , cast (regexp_substr(src,'[^\|]+',1,2) as varchar2(100)) token2
   , regexp_count(src,'\|')+1 max_element
   , 2 hwm
   , cast (src as varchar2(500)) as res
   )

The datatype and size of the columns is automatically deducted from the initial value. This is why cast is sometimes needed, if the column size should be bigger than what the initial value indicates.

Now we define that an iterative model is to be used.

rules iterate (500) until (stop criteria)

Iterative models are good for implementing procedural logic. Or for tasks when it is unknown beforehand what the area for the calculation should be.

It gives us a variable called iteration_number which can be used as a cell address.

Now lets look at a few rules.

-- switch tokens if left token > right token
res[iteration_number+1] 
    = case when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
      -- do the switch
      regexp_replace(
             regexp_replace(res[iteration_number],'[^\|]+',token2[iteration_number],1,ele_pos1[iteration_number])
             ,'[^\|]+',token1[iteration_number],1,ele_pos2[iteration_number])
      else res[iteration_number] -- no switch needed
      end ,

The left hand side of the rule “res[iteration_number+1]” creates a new row for the res column. The right hand side of the rule references data from the previous row “token1[iteration_number]” to do the switch logic.

If data from the same row is needed, it is possible to use “token1[cv()]”. cv() stands for cell value of the current dimension. It is possible to calculate with those values. So instead of token1[iteration_number] we could also write “token1[cv()-1]” to fetch the value from the previous row. For iterative models I find it more convenient to stay consistent and use iteration_number instead.

This rule implements rule 1 (switch tokens) from the bubble sort logic. However it is not a 1:1 matching of rules. We can see this when looking at the next rule.

Instead the logic needs to be implemented for each column. To calculate the next position a similar case construct is needed.

-- calculate next position (for token1)
ele_pos1[iteration_number+1] 
       = case
         when ele_pos1[iteration_number] = 1 then
           -- we reached first position, so go back to hwm
           hwm[iteration_number]
         when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
           -- after a switch, move one position the the left and check there
           ele_pos1[iteration_number] - 1
         when ele_pos1[iteration_number] + 1 <= max_element[0]-1 then           -- no more switch, so go to hwm and look for next element            hwm[iteration_number]  
         else 
           -- just in case, don't do anything
           ele_pos1[iteration_number]
         end

Bubble sort rules 2-4 are implemented by a case construct.

As we can see in this example, the rules from the model clause do not match our business rules. Sometimes they do, but more often they do not.

conclusion

The Model clause can open up new ways to do solve SQL puzzles. Bubble sort is a good candidate for an iterative model.

For real world cases the Model clause is hardly maintainable. If we find a solid way to match business rules to model clause rules, then we have a good way to react to future changes of those business rules.

Sometimes model clause can provide a performance advantage, because of they way how the data is handled.

Model clause is a tool in our toolbox, although an extremely sophisticated and complex one. We need to train using this tool on a regular basis.

btw: Some of the constructs from the model clause can be rediscovered in other statements. For example the new analytic views in 12.2 also have a “dimension by” and a “measures” part. But the rules are missing (one can argue that attribute dimensions and hierarchies resemble the rules).

is {JSON}, or not is {JSON}, that is the question

Whether ’tis nobler in the mind to suffer
The [] and “” of outrageous json,
Or to take Arms against a Sea of troubles,
And by opposing {} end them: to select, to browse
No more; and by a browse, to say we end
the json array, and the thousand json objects
that Flesh is heir to? ‘Tis a consummation
devoutly to be wished. To select, to browse…

Intro

So I have this logging table where sometimes the message can be a json document. Most of the times it is an error message or some tracing information. But sometimes I want to show what data currently is inside a plsql collection. I wrote a small conversion function, that returns a clob which should contain JSON. This json ends up as a message in my logging table.

Inside an apex application I added the possibility to show this JSON as a modal page. I use the JSONView Plugin, however most browsers now have a default JSON display capability.

I detect if the message contains json data by using the 12.1 IS JSON operator.

In case it is json, I provide a link to the modal page and print the data there.

case 
  when LOG_MESSAGE is json 
  then
    '<a href="'||
    apex_util.prepare_url('f?p='||v('APP_ID')||':123:'||v('APP_SESSION')
    ||'::::P123_LOG_ID:'||to_char(LOG_ID))
    ||'" title="show JSON data">{JSON}</a>'
else 
  '<pre>'||LOG_MESSAGE||'</pre>'
end as log_message_enhanced

 
The modal page uses the application/json mime type.

The result looks similar to this:

 
modal_json_ok
 

How build such an apex page or how to do a conversion from a plsql collection to json is not part of this blog post. If your are interested in that, please leave a comment and I might consider to publish how to do so.

Problem

For certain documents the browser was not able to show the json. Instead it returned an error message and the json in text format. Here is an example.
modal_json_error

Sorry for the German Message, but I was not able to switch my Firefox to english mode easily. The error essentially says: “We could not parse the json, it seems the document does not comply to the specifications.”

So the database says it is json, but the browser says it is not!

What is going on? That is the question.
 

Solution

The reason for the behaviour is that Oracles IS JSON check uses the lax json syntax (by default). LAX json allows several things, among others it allows to have a list of objects with a trailing comma at the end. Exactly my issue.

Here is a basic SQL demonstration. Note the comma after the “Larry” inside the json object step.

select * from dual
where '[{"index":1,"name":"Larry",}]' 
      is json;
DUMMY
-----
X

So it is JSON. But only lax json.

Fortunatly we can also do a check for the more strict json interpretation.

select * from dual
where '[{"index":1,"name":"Larry",}]' 
      is json (STRICT);
No rows selected.

Can you spot the difference? The STRICT keyword including parenthesis tells the database that the document needs to confirm to the more strict specification.

This is of cause documented: About Strict and Lax JSON Syntax

The main differences are

  • STRICT: each JSON field and each string value must be enclosed in double quotation marks (“).
    LAX: An object literal can also be enclosed in single quotation marks (‘).
  • LAX: Case variations for keywords true, false, and null (for example, TRUE, True, TrUe, fALSe, NulL).
  • LAX: An extra comma (,) after the last element of an array or the last member of an object (for example, [a, b, c,], {a:b, c:d,}).
  • LAX: Numerals
    • with one or more leading zeros (for example, 0042.3).
    • Fractional numerals that lack 0 before the decimal point (for example, .14 instead of 0.14).
    • Numerals with no fractional part after the decimal point (for example, 342. or 1.e27).
    • A plus sign (+) preceding a numeral, meaning that the number is non-negative (for example, +1.3).

But there are more differences.

Using the (STRICT) keyword, solved my problem. Only those json documents where linked, that could be shown in the browser. The others were rendered as normal text.

Conclusion

 
Be aware that there are slightly different JSON specifications available.

If you want to show json inside a browser, then make sure the json document confirms to the strict json specification.

If you want to use json inside javascript, then probably the lax version is better suited.
 

 

10 Oracle plsql things you probably didn’t know

Many people enjoyed reading my last blog post “10 Oracle SQL features you probably didn’t know”. So I decided to spice it up a little more and do something similar for plsql.

I hope you like that one too.

With our further ado, let’s get started with the list.

10. The first Oracle version to feature plsql was Oracle DB version 6 (1988)

And no. Steven Feuerstein did NOT invent it.

At that time PLSQL did not have stored procedures nor did it have proper exception handling. But it already had embedded SQL.

I learned that from the great Lewis Cunningham. One of the godfathers of development with SQL and PLSQL.

Stored Procedures were added in Oracle 7 (1992). 7.3 was the version when I started to work with an Oracle Database. At that point plsql was in version 2.x. However there never was a version 3. Plsql versioning jumped to 8 when Oracle DB version 8 was introduced and plsql versioning was aligned with the db versions. So there are no plsql versions 3-7. But honestly? Nobody cares anymore that plsql does have its own versioning.

9. labels do not need to match

We can use <<labels>> in plsql. Mostly to increase readability of code. This is especially useful for loop constructs, but it also works for normal begin..end blocks.

begin
 <<mainloop>>
 loop
   <<check_some>>
   begin 
     <<dummyloop>>
     for r in (select * from dual) loop
       -- do nothing
       null;
     end loop dummyloop;
   end check_some;
   exit when 1=1;
 end loop mainloop;
end; 
/

As we can see there are several <<labels>>. And the usage of those labels at the “end” helps to distinguish which code part we are looking at [1].

But this is only as good as the programmer is!

Unfortunatly this works too:

begin
 <<mainloop>>
 loop
   <<check_some>>
   begin 
     <<dummyloop>>
     for r in (select * from dual) loop
       -- do nothing
       null;
     end loop mainloop;
   end check_sum;
   exit when 1=1;
 end loop dummyloop;
end; 
/

Here I mixed up the labels from the loops. And the “end check_sum” does not match the label at the beginning of the block. In fact the “label” at the end can be anything that is not a reserved word.

It runs identical to the previous code (still doing nothing). But it is way more confusing for the “future me” that has to maintain this mess.

8. pragma SERIALLY_REUSABLE

During the lifetime of a session, the package state (package variables, open cursors, etc.) are held in the UGA (user global area).  Subsequent calls in the same session to the same package profit from that by not needing to reinitialize the package state.

The pragma SERIALLY_REUSABLE is able to change this behaviour.

serially_reusable packages

After the work unit (server call) of a SERIALLY_REUSABLE package completes, Oracle Database does the following:

  • Closes any open cursors.
  • Frees some nonreusable memory (for example, memory for collection and long VARCHAR2 variables)
  • Returns the package instantiation to the pool of reusable instantiations kept for this package.

Essentially this means, that the package state exists only during the package call. Not for the whole session.

So far I never had the need to use this pragma. But I can imagine some very very special situations, where this might become interesting.

7. You can compile a package even while another session is running it

In general this is not possible. Assuming a package is currently running. Or to say it in technical terms: We have an active session (Session A) executing a packaged procedure.

A second session  (Session B) trying to do an “ALTER COMPILE PACKAGE (BODY)” would wait until session A finishes and will then afterwards try to compile the package. Upon success the first session A then will get a “package state has been discarded” error message as soon as it tries to run the same package once again. The second next try to run the package would succeed and will use the new package version. At that time the package state was discarded from the session and the new instantiation can be loaded.

This is all documented and well known behaviour.

We face three potential issues with this

  1. Session B needs to wait
  2. Session A might get an error
  3. Session B might wait so long that the developer decides to kill the client (closing SQL developer) thereby making everything 10 times worse, because the database compile call still is valid on the database session level – blocking all following attempts to run or compile the package.

EBR for the rescue!

Using Edition Based Redefinition (EBR) we can circumvent those issues.

Both sessions just need to use different editions. EBR allows us to store and run different code versions of the same plsql based object in the same database.

 

preparation

First create a package with a long running procedure. My example uses a procedure that runs for exactly 1 minute.


create or replace package myPck is
  procedure runMinute;
end myPck;
/

create or replace package body myPck is
   procedure runMinute is
   begin
     sys.dbms_lock.sleep(60);
   end runMinute;
end myPck;
/

 

Setup an edition DEV$ALPHA that is a child of the default edition (ORA$BASE).


create edition DEV$ALPHA;

 

You need an edition enabled schema to do this.

This is simple to do, but to explain EBR in more detail is beyond the scope of this blog post.

Example scenario

Lets run a few commands in two different sessions.

Session A resembles a USER/TESTER who currently executes the packaged function.

Session B resembles a DEVELOPER who wants to deploy a new version of the package.

Session A does this

set time on

alter session set edition=ORA$BASE;

execute myPck.runMinute;

Session B was started already and after the execute in Session A, run the following script in Session B.

alter session set edition=DEV$ALPHA;

— add a new procedure to the package

create or replace editionable package myPck …
/

create or replace editionable package body myPck …
/

— run the new procedure

exec myPck.run5secs;

Result is Session B finishes way before Session A does complete its 1 minute run.

See screenshotebr_run_sessions

q.e.d.

6. call a (pipelined) table function without the TABLE operator

It works only from 12.2 onwards. It is more of a SQL feature than a plsql one.

This will make a table function look indistinguishable from a parametrized view.

Example: split_string

First lets create a simple little table function. This one here just converts a delimited list into rows.

create or replace function split_string
 (p_str IN VARCHAR2
 ,p_delimiter IN VARCHAR2 default ','
 ) RETURN sys.odcivarchar2list PIPELINED 
IS
/** Function to split strings based upon delimiter
*
* @author Sven Weller
*
* @param p_str input string 
* @param p_delimiter delimiter string, default =, Delimiter should only be 1 char.
* @return list of strings
*
*/
 v_entry varchar2(4000);
 v_remaining_str varchar2(4000);
BEGIN
  -- input string needs to hold something to be able to split
  if p_str is not null then
     <<steps>>
    for i in 1..regexp_count(p_str,'\'||p_delimiter)+1 loop
      -- search + split
      v_entry := rtrim(regexp_substr(p_str,'[^\'||p_delimiter||']*('||p_delimiter||'|$)',1,i),p_delimiter);
      pipe row(v_entry);
    end loop steps; 
  else raise no_data_found; 
  end if; 

END split_string;
/

 

function created.

in 11g we call the function like this:

select * from TABLE(split_string('A:BB::CCC',':'));

in 12.2 we can now call it like that:

select * from split_string('A:BB::CCC',':');

As you can see the TABLE row source operator is gone. And it still works! The results of both statements are identical.

COLUMN_VALUE
A
BB
CCC

Want to test it? I made an example on livesql.com.

At the moment this is an undocumented 12.2 feature. So don’t use it for production code (yet). I quite like it. Less code is better! It might become some de-facto standard (similar to connect by level) and eventually will make it into the documentation.

5. dot notation for parameters

We can refer to parameters using the name of the module that declared them. This is useful when we need to distinguish a parameter from a column name.

example


create or replace function myFancyFunc (dummy in varchar2) return number
is
  ret number := 0;
begin
  begin
    select 1 into ret
    from dual
    where dummy = myFancyFunc.dummy
    and rownum = 1;
  exception
    when no_data_found then null;
  end;
  return ret;
end myFancyFunc;
/

Function MYFANCYFUNC compiled

select myFancyFunc('X') from dual;

1

select myFancyFunc('Y') from dual;

0

The function simply compares the value in the dummy column of the dual table to the value we input. If instead we would just compare dummy=dummy then we would get always 1 as a result. No matter what the input is. Even if we add an alias to the table and prefix the column with an alias, the non aliased “dummy” will still be interpreted as a column.

This behaviour is documented: Oracle Doc 12.1 – plsql name resolution

If a SQL statement references a name that belongs to both a column
and either a local variable or formal parameter,
then the column name takes precedence.

Interestingly we can also use labels on block level for specifying variables that are defined in this block.

set serveroutput on
<<main>>
declare
  dummy varchar2(10) := 'Y';
begin
  <<block1>>
  declare
    dummy varchar2(10) := 'X';
  begin
  <<block2>>
    declare
    dummy varchar2(10) := 'A';
    begin
      select dummy
      into dummy
      from dual
      where dummy = block1.dummy;

      dbms_output.put_line('MainBlock:'||main.dummy);
      dbms_output.put_line('Block1:'||block1.dummy);
      dbms_output.put_line('Block2:'||block2.dummy);
    end block2;
  end block1;
end main;
/

PL/SQL procedure successfully completed.

MainBlock:Y
Block1:X
Block2:X

Without dot notation the innermost variable (block2) is used – as we can see in the INTO part. And we can reference a different variable with the same name from a “higher” declaration by using the dot notation.

 

4. variable names be emojis

example

set serveroutput on
declare
  "💩"exception;
  pragma exception_init("💩",-20001);

  "⌚" timestamp := systimestamp;
  "🕑"interval day to second;
  "🎲"number;
  "💤"number := 2;
begin
  "🎲":= round(dbms_random.value(1,6));
  for "🔜"in 1.."🎲"loop
    dbms_lock.sleep("💤");
  end loop;
  "🕑":= systimestamp - "⌚" ;
  dbms_output.put_line('Slept for '|| "🕑");
exception
  when "💩"then
    dbms_output.put_line('Sorry something bad happend!');
    raise "💩";
end;
/

PL/SQL procedure successfully completed.

Slept for +00 00:00:08.049000

The source code looks a little bit different in sql developer. But trust me. I simply copy&pasted it from there to here.

emojicode

To make this work you need to use a font that supports emoijs/symbols, I used font “Segoe UI Symbol”. It is supposed to look better on windows 10[3].

If you are a hard core emoji lover then I suggest to have a look at emojicode.org

It is a emoji based programming language. Which did not make it into the esoteric programming languages list (yet).  Ook? Ook!

 

3. variables can be made mandatory (NOT NULL)

Check out the NOT NULL keyword during the variable declaration.

declare
  v_index number not null := 0;
begin
  v_index := 1;
  v_index := null;
end;
/

Error report –
ORA-06550: line 5, column 14: PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3: PL/SQL: Statement ignored

The error message is a bit vague about what happened, but it is very exact where it happened (line 5, column 14). And what do we see there? A NULL expression.

The expression is of wrong type, because we added a NOT NULL constraint to the number type that was used. For more complex cases we can create our own sub types and use them. But if we just want to make sure that we do not need to consider null cases during further variable calls, then this is a possible way.

Link to plsql documentation

Currently there are no such other constraints  that we can use.  I could imagine with the potential arrival of SQL assertions, this might become a hot topic in plsql too.

2. you can “hack” dbms_output

Warning! This is dangerous. It might break some (poorly written) code that resides in the same schema. Do it at your own risk! It is also hilariously funny to do on april fools day to your fellow coworkers. I mean they shouldn’t use dbms_output anyway. That will teach them!

I start the example by showing the behaviour first. Then the code to produce this result.

behaviour


create or replace procedure doSomething is
  v_dummy dual.dummy%type;
begin
  select dummy into v_dummy from dual where 1=2;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end doSomething;
/

Now we run the module a couple of times and want to see the output. We should expect a NO_DATA_FOUND error message.

set serveroutput on

execute doSomething;
execute doSomething;
execute doSomething;
execute doSomething;
execute doSomething;

Surprisingly instead of the error message we get something like this.

PL/SQL procedure successfully completed.

Wrong usage of DBMS_OUTPUT detected.

PL/SQL procedure successfully completed.

Make Databases Great Again!

PL/SQL procedure successfully completed.

ORA-01403: no data found

PL/SQL procedure successfully completed.

Make Databases Great Again!

PL/SQL procedure successfully completed.

So tell me what you want, what you really, really want

Omg! What is going on here?

Well here is the catch. We can “overload” dbms_output in out own schema. Then our package is called and not the original package from sys.

source code

create or replace package dbms_output
as
  procedure enable(BUFFER_SIZE number default null);
  procedure put_line(A in varchar2);
  procedure GET_LINE(LINE out VARCHAR2,STATUS out integer);
  procedure GET_LINES(LINES out sys.dbms_output.CHARARR,NUMLINES in out NUMBER);
  procedure GET_LINES(LINES out sys.DBMSOUTPUT_LINESARRAY,NUMLINES in out NUMBER);
end dbms_output ;
/

create or replace package body dbms_output
as
procedure enable(BUFFER_SIZE number default null) is
begin
  sys.dbms_output.enable(BUFFER_SIZE);
end;

function getRandomQuote (A in varchar2) return varchar2
is
  type quotes_t is table of varchar2(4000) index by binary_integer;
  v_quotes quotes_t;
  v_random binary_integer;
begin
  v_quotes(1) := 'You are hacked by the Chinese';
  v_quotes(2) := 'Wrong usage of DBMS_OUTPUT detected.';
  v_quotes(3) := 'System failure. Get away from keyboard';
  v_quotes(4) := 'Close all windows! NOW!';
  v_quotes(5) := 'Make Databases Great Again!';
  v_quotes(6) := A; -- sometimes return the correct text
  v_quotes(7) := A; -- sometimes return the correct text
  v_quotes(8) := 'So tell me what you want, what you really, really want';
  v_quotes(9) := 'None but ourselves can free our minds.';
  v_quotes(10) := 'Let there be light!';
  v_random := round(dbms_random.value(1,v_quotes.last));

  return v_quotes(v_random);

end getRandomQuote;

procedure put_line(A in varchar2) is
begin
  sys.dbms_output.put_line(getRandomQuote(A));
end;

procedure GET_LINE(LINE out VARCHAR2,STATUS out integer)
is
begin
  sys.dbms_output.GET_LINE(LINE,STATUS);
end;

procedure GET_LINES(LINES out sys.dbms_output.CHARARR,NUMLINES in out NUMBER)
is
begin
  sys.dbms_output.GET_LINES(LINES,NUMLINES);
end;

procedure GET_LINES(LINES out sys.DBMSOUTPUT_LINESARRAY,NUMLINES in out NUMBER)
is
begin
  sys.dbms_output.GET_LINES(LINES,NUMLINES);
end;
end dbms_output ;
/

How does it work?

Because how sql name resolution kicks in, the DBMS_OUTPUT package in our schema is used and not the public synonym for the DBMS_OUTPUT package from the sys schema.

The get_line functions then pushes the changed text to the normal buffer mechanism.

How can we avoid it?

Best is not to use DBMS_OUTPUT in real production code. It is a nice quick debugging tool. But not more than that.

Also if you prefix dbms_output always with the SYS schema, then it will call the original logic.
 

1. when others does not catch all exceptions

example

set serveroutput on 
declare
  e_cancelled exception;
  pragma exception_init(e_cancelled, -1013);
begin
  begin
    raise e_cancelled;
  exception
    when others then
      dbms_output.put_line('EXCEPTION OTHERS');
  end;
end;
/

ORA-01013: user requested cancel of current operation
ORA-06512: at line 6

We still see an exception, but not the dbms buffer output!

This needs some explanation.

There is a very limited set of exceptions that will not be captured by the WHEN OTHERS handler. We need to look closely and understand the exception itself to comprehend why this is a good thing.

Here the ORA-01013 is the “user requested cancel of current operation” exception. Essentially it means somebody pressed “CTRL+C” while running the code. In almost all environments this means: Stop doing whatever you do immediately! Or in more technical terms: It is an interrupt to the os process running your command. Same as executing “kill -2” (kill -SIGINT) in a nix environment (the-3-most-important-kill-signals-on-the-linux-unix-command-line). Even if the process is allowed to ignore the command, it shouldn’t do so by default.

ORA-01013 can sometimes also be the result of a timeout. Where the client is waiting for a response and after some time sends this as a timeout signal to the database session.

We are allowed to capture this exception and write a special handler for it.

set serveroutput on
declare
  e_cancelled exception;
  pragma exception_init(e_cancelled, -1013);
begin
  begin
    raise e_cancelled;
  exception
    when others then
      dbms_output.put_line('EXCEPTION OTHERS');
  end;
exception 
  when e_cancelled then
    dbms_output.put_line('OPERATION CANCELED');
END;
/

PL/SQL procedure successfully completed.

OPERATION CANCELED

Nothing to worry about. Just nice to know.

Please note: This example will behave differently in older outdated db versions. I think it was introduced as a fix for bug#12838063 in 11.2.0.4.

Other exceptions that are not handled include “ORA-03113: end-of-file on communication channel”.

But not “ORA-06508: PL/SQL: could not find program unit being called”. This was supposed to go through “when others” but testing on 12.2.0.1 revealed it is captured.

 

 

Footnotes


1. This is probably the only bug free code I ever wrote. It was meant to do nothing and it does that exceptionally well![2]
2. There might be room for some performance improvement. Allowing us to do nothing even faster.
3. On windows 10 💩 is supposed to look like 🔝💩.

 

 

 

10 Oracle SQL features you probably didn’t know

10 Oracle SQL features you probably didn’t know

I can not guarantee that you never heared of this. But the majority of developers doesn’t know or doesn’t seem to know. And honestly –  most of this information is fairly useless – and mostly harmless.

10. Why we have the CONCAT function

There is a concat function that can be used instead of the concat operator ||.

Nobody uses that! But there is a reason why it exists.

This is from the 8.1.7 Oracle documention

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.

So we do have this function, because in the past this || was not consistent over all the different platforms using different character sets. Since nobody seems to use IBM EBCDIC anymore, there is no real need to use the CONCAT function.

9. select UNIQUE

Instead of doing a SELECT DISTINCT you can do SELECT UNIQUE.

But you shouldn’t. None of them. Forget I mentioned this.

Ok here is an example.

Example

select unique e.deptno from scott.emp e;

DEPTNO
-----
30
20
10

It is also possible to do SELECT COUNT(UNIQUE …) . Not sure when that one was introduced, but it seems to work now.

Sven says: “SELECT DISTINCT|UNIQUE should be considered a bug in real production code.”

I have yet to find an example where SELECT DISTINCT is needed. More likely there is a bug in the data model or missing joins in the where clause. GROUP BY or sometimes EXISTS are the better long term alternatives.

Using SELECT DISTINCT is absolutly fine for developer ad-hoc queries.

I feel this whole concept is a bit inconsistent. 12c introduced the new approximation function APPROX_COUNT_DISTINCT, but there is no APPROX_COUNT_UNIQUE!

8. there is an ANSI datatype DATE

The ANSI datatype DATE does not hold any time information (no hours, minutes, seconds). That is what the ANSI datatype TIME is for. Oracle does not officially know these two datatypes.

However:

EXTRACT function

Purpose

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:

  • If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.
  • If DAY is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.
  • If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
  • If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

So essentially the doc says, that EXTRACT uses an ANSI DATE as an expression input.

Proof

select extract(day from sysdate) from dual;
21

select extract(second from sysdate) from dual;
ORA-30076: invalid extract field for extract source

select extract(second from systimestamp) from dual;
4.823

The error appears, because sysdate is kind of converted into a ANSI DATE and this does not hold time information.

 

Problem is: We just can not use this ANSI date type for normal activities.

Alternative for the ANSI date: TRUNC(datetime). In 12c as a virtual column.

Problem solved. ✔

 

7. group by ()

You can group by an empty parenthesis. Or let’s call it group by the FULL SET. This is in fact useful in some very specific cases.

It gives us the ability to get a NO_DATA_FOUND error if there is no data at all. Oracle guarantees that an aggregation select without a group by will always return a record. GROUP BY () can be added if you want to change this default behaviour on purpose.

Example

Let’s say we want to count how many employees are in one specific department.

select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'OPERATIONS';

EMP#
----
0

Since there are no employees in operations, this returns 0. This is correct and the information I want.

More specifically this query returns one row, but the e.empno is null and therefore is not counted. Count(*) would have returned 1!

But if we use a department name that does not even exists, then this will also return 0.

select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'FANCY_CLOUD_SPACE';

EMP#
----
0

Not correct! In this case I prefer to get a no data found error.

Grouping on the full set gives us this option.

select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'FANCY_CLOUD_SPACE'
group by ();

no rows found.

Btw: the SQL Developer syntax (pre-)parser identifies that () as an syntax error. But it will execute it successfully.

I have to admit, that in this case it would be much better to group by the department. But maybe it would be possible to get the count for multiple departments using an IN list. Then the group on department level would not help much.

 

Btw: GROUP BY NULL does the same thing, and might be less confusing for some developers. I personally prefere GROUP BY () because the () also is used in GROUPING_SETS.

 

6. + vs. –

In some edge cases using a different operator (+|-) leads to disturbing results.

Example: Substracting a tiny number from 1 does not equal 1. But adding a tiny number from 1 does equal 1!

select * from dual
where 1 = 1-0.000000000000000000000000000000000000001;

No data found!

This is expected behaviour…

select * from dual
where 1 = 1+0.000000000000000000000000000000000000001;

DUMMY
-----
X

Wat? Why do we get a result?!

The solution is simple. We overstepped the maximum precision of the number datatype when doing the addition. Max precision is 38 digits. The addition would require a precision of 39. Instead the result is rounded (or truncated) to a precision of 38 digits, which happens to equal 1.  The substraction result still is in the 38 precision range and therefore exact (very slightly less than 1).

Don’t confuse precision with maximum value! Precision is the number of different digits that we can store. This can then be moved by an exponent to the left or right for very large or very small values.

 

To put this into perspective

That precision is so extremly high, that we have major troubles visualizing it. So here is a comparsion. The comparison data comes from this hugely interesting site: http://money.visualcapitalist.com/all-of-the-worlds-money-and-markets-in-one-visualization/

The complete monetary assets in the whole word is estimated about 80 trillion $ (broad money=coins, banknotes, savings accounts, time deposits, …). That is a 80,000,000,000,000  or an 8 with 13 zeros at the end or 8e13 in scientific exponential notation.

select 8e13 from dual;

Considering all the national and private debts is even higher. Here we reach about  200 trillion $.

And if we consider derivates (=high risk gambles)  then the high estimations go as far as 1.2 quadrillion $.  That is a 1 followed by 24 more digits (mostly zeros) or 1.2e24.

Since all this is expressed in dollars, we can increase the number even further by chooseing a different currency.  For example the Nepalease Rupee is worth a little less than 0.01 dollar. So all the worlds derivatees could be expressed in Nepalease Rupees using a number as big as 1.2e26.

That means using a number with a precision of 38 we can easily express all the money in the world including derivates up to the very last Nepalease Rupee. And we still have only used 2/3rds of the maximum available precision. Or to say it differently. If we duplicate the planet Earth 1,000,000,000,000 (1 trillion) times – we could still give the exact amount of money on all planets in Napalease Rupees up to the very last Rupee using the number datatype.

That’s quite impressive!

Btw. The biggest number that can be expressed using the NUMBER datatype is 999…(38 9’s) x10125 . Which is way way bigger than anything we discussed so far.

 

5. instead of IN we can use = ANY

select 'TRUE'
 from dual
 where 1 = ANY (1,2,3,4,5)
TRUE

There are several similar unusual “replacement” options.

e.g.

  • instead of != we can also use ^=.
  • NOT IN is the same as  != ALL
  • SOME is a synonym for ANY – but only in sql!
  • <=LEAST | >=GREATEST can be replaced by <|>= ALL
  • >=LEAST | <=GREATEST can be replaced by >|<= SOME

I expect that SOME people do know about these operators, since questions about them are part of the SQL expert exam. So from ALL experts who took the exam at LEAST the few who passed should know SOMEthing about ANY.

The last replacement differs how NULL values are treated. In theory there could be cases where this comes in handy. I so far never had such an encounter.

Example

with testdata
as (select 1 a, 2 b, 3 c, 4 d from dual union all
   select 5 a, 4 b, 3 c, 2 d from dual union all
   select 1 a, null b, 2 c, null d from dual union all
   select null a, null b, null c, null d from dual)
select * from testdata
where 3 >= LEAST(a,b,c,d);

 

A B C D
-------
1 2 3 4
5 4 3 2

 

with testdata
as (select 1 a, 2 b, 3 c, 4 d from dual union all
    select 5 a, 4 b, 3 c, 2 d from dual union all
    select 1 a, null b, 2 c, null d from dual union all
    select null a, null b, null c, null d from dual)
select * from testdata
where 3 >= ANY(a,b,c,d);

 

A B C D
-------
1 2 3 4
5 4 3 2
1 - 2 -

4. external table on multiple files

External tables can load multiple files at once! As long as they have the same structure of cause. So here is an example.

CREATE TABLE EXT_DUMMY
(
    "RECORDTYPE" VARCHAR2(100 BYTE), 
    "COL1" VARCHAR2(100 BYTE), 
    "COL2" VARCHAR2(100 BYTE) 
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "IMPORT_BAD_FILE" 
    ACCESS PARAMETERS ( 
             records delimited BY newline 
             FIELDS TERMINATED BY ';' 
             MISSING FIELD VALUES ARE NULL 
               ( RECORDTYPE CHAR
               , COL1 CHAR 
               , COL2 CHAR 
               ) 
    ) 
    LOCATION ( 'Testfile1.txt, Testfile2.txt' )
)
    reject limit 10
;

When we then do a select on the external table, we will see the data from both files.

We can also switch the external table to a different file.

alter table EXT_DUMMY location ('Testfile3.txt' )

 

For the experts: We can use the ROWID to find out which file was used to load a specific record. Special thanks go to OTN-forum member Odie_63 who found this solution. More info in this old OTN forum thread.

with ext_loc as (
      select position-1 as pos
           , name as filename
      from sys.external_location$
      where obj# = ( select object_id
                     from user_objects
                     where object_name = 'EXT_DUMMY' )
    )
select x.filename,
       t.*
from EXT_DUMMY t
join ext_loc x 
on x.pos = to_number(regexp_substr(dump(t.rowid,10,9,1),'\d+$'))
;

It’s a very clever piece of software. Essentially it extracts the filenumber from the rowid, looks up the file number in the data dictionary and combines that with our data set.

This select was done in a 10g database. In 12c we can probably use dbms_rowid to do the same, instead of regexp_substr(dump(rowid)).

3. insert default values

We can insert into a table using the “default” keyword to force default behaviour for this column.

Example

-- setup
create table swe_default_test 
(col1 varchar2(10) not null,
 col2 varchar2(10) default 'TEST' not null
 );
Table SWE_DEFAULT_TEST created.

-- test
insert into swe_default_test (col1) values ('X');
1 row inserted.

insert into swe_default_test (col1,col2) values ('X',null);
ORA-01400: cannot insert NULL into ("MYUSER"."SWE_DEFAULT_TEST"."COL2")

insert into swe_default_test (col1,col2) values ('X',default);
1 row inserted.

-- double check
select * from swe_default_test;
COL1 COL2 
---------- ----------
X TEST 
X TEST 

-- cleanup
drop table swe_default_test;
Table SWE_DEFAULT_TEST dropped.

 

In 12c we now have a new and I think better option to do the same. The default column can be defined additionally with “ON NULL”. Which has the effect, that inserting a NULL value, will lead to using the default value instead. Of cause the “default” keyword still works too.

-- setup
create table swe_default_test12c
(col1 varchar2(10) not null,
 col2 varchar2(10) default on null 'TEST' not null
 );
Table SWE_DEFAULT_TEST12C created.

-- test
insert into swe_default_test12c (col1,col2) values ('X',null);
1 row inserted.

insert into swe_default_test12c (col1,col2) values ('X',default);
1 row inserted.

-- doublecheck
select * from swe_default_test12c;
COL1 COL2 
---------- ----------
X TEST 
X TEST 

-- cleanup
drop table swe_default_test12c;
Table SWE_DEFAULT_TEST12C dropped.

As we can see both cases now work. The one using a NULL value and also useing the DEFAULT keyword.

This new 12c “default on null” feature can be used to replace the typical BEFORE ROW INSERT trigger. More info how to do this in Sequence and Audit columns with Apex 5 and 12c

 

2. (1,2) = ((1,2))

We can compare expression lists using the = operator. But the right side of the comparison needs an extra set of parenthesis.

select 'TRUE' checked from dual
 where (1,2,3,4,5) = ((1,2,3,4,5));
CHECKED
-------
TRUE

This is just a shorthand form of

1=1 and 2=2 and 3=3 and 4=4 and 5=5

If we do not use the second set of parenthesis on the right hand side of the comparison, then we get an error.

select 'TRUE' checked from dual
 where (1,2,3,4,5) = (1,2,3,4,5) ;
ORA-00920: invalid relational operator

This is documented. Although a tiny bit difficult to read in the syntax diagrams.

See: SQL Reference – Simple Comparison

syntax_compare_lists_1syntax_compare_lists_2

 

1. DUAL is a real table

Although there are some special optimizations in various tools, dual is a real table residing in the sys schema.

select owner, table_name
 from dba_tables
 where table_name = 'DUAL';
OWNER TABLE_NAME
----------------
SYS DUAL

 

In older database versions it was possible to do an insert into the DUAL table.

Warning! This is extremly dangerous. It will probably break ALL applications in your database.

insert into dual values ('Y');
select mysequence.nextval into v_id from dual;

TOO_MANY_ROWS error!

And nobody captures the TOO_MANY_ROWS exception for selects from dual.

Btw: A working safety mechanism is to add rownum = 1 to your query.

select mysequence.nextval into v_id from dual where rownum = 1;

Fortunately manipulating DUAL does not work anymore in recent versions of the database. I think 12c introduced it. Not even DBAs have the privs anymore to do this.

ORA-01031: insufficient privileges
*Cause: An attempt was made to perform a database operation without
the necessary privileges.

0. The Oracle SQL Syntax is documented!

Bam! There you have it! You wouldn’t have thought that. Well, you are not alone. So many developers seem to have no clue that this exists or where it exists.

When googeling use “oracle 12c doc” or “oracle 12.2 SQL ref” in front of your search term. It helps to find the proper pages.

12.2 doc portal

12.2 SQL introduction

12.2 SQL Language reference

 

 

Special thanks go out to Stefanie R., who gave me the idea for this blog post!

 

sqldev alert - overwrite package body modifications

DBA/Dev quick tipp: recover lost package body under EBR

Problem

It can happen that SQL developer suggests to reload an old package body that you are currently working on. If you click the suggested “autoupdate” then you will loose all current changes that you made to the package body. This just happened to a collegue of mine.

sqldev alert - overwrite package body modifications

It is possible to recover the old code using the flashback feature of the oracle database. But one has to be quicker than the undo retention period of the database.

If the schema was edition enabled this complicates things a tiny bit further. We need to make sure to fetch the plsql code from the correct edition that we are interested in.

When we are quering normal data dictionary views like DBA_SOURCE this will always use the current edition. DBA_SOURCE_AE will show all code editions.

Flashback does not work well with data dictionary views. However we can use the base tables and run a flashback query there to see the old package code.

Action

Three simple statements will do it.

Set your dba session to use the correct edition (this might not be needed if the object id is found using a slightly different query). The edition needs to match the edition where the source code was originally compiled

ALTER SESSION SET EDITION="DEV$ALPHA";

Find the object id of the package body

Use the correct name of the package and your edition name

select obj#
from sys."_CURRENT_EDITION_OBJ"
where name = 'MYPACKAGE'
and type# = 11 -- package body
and namespace = 2
and defining_edition = 'DEV$ALPHA';

Flashback query to show the source code for the identified object

select *
from sys.source$
--versions between timestamp (systimestamp - interval '25' minute) and systimestamp
as of timestamp (systimestamp - interval '25' minute)
where obj# = 1906090;

 

Done.

 

Aftermath

The starting situation can be reproduced by changeing body and spec of the same package. Once the spec is recompiled, the body needs to be recompiled too. Then SQL developer suggests to load the new code (which is in fact the old code). If you click yes, it will overwrite the current changes.

Apex 5 – show Y/N columns as font awesome icons

apex5_YN_icons

The database column value is Y or N (J or N for German databases). In the past I rendered such columns using images that had a name, where the column value could be appended to, for example IconCheckbox_Y.png. In Apex 5 the same is possible using icon classes.

1) add an alias for Font-Awesome Icons

This little css will create two aliases for the fa-check and the fa-remove icons. And it will give them a default color.

.syn-check-J:before,.syn-check-Y:before {
  content: "\f00c";
  color: green;
}
.syn-check-N:before {
  content: "\f00d";
  color: #ff3a30; /* color: red; */
}

If you want to know the content code for a different icon you can search in the fontAwesome.min.css file or have a look at some “cheatsheet” pages like this one here: http://astronautweb.co/snippet/font-awesome/

If we would add another attribute:

  font-family:fontAwesome;

then we could use the icon even without the “leading” fa class.
I do not recommend this! Remember in Apex 5.1 we have two icon fonts to choose from – FontAwesome OR Font-Apex. Adding the font-family would prevent switching the icons.

2) html expression to set the class

And then use a normal text field with a html expression.
Html expressions are available for classic reports, but also for interactive reports and interactive grids.

<span class="fa fa-lg syn-check-#COL1#" alt="#COL1#"></span>

Our new classes still can be combined with other font-awesome styles. Here I enlarged them a little bit by adding the “fa-lg” class.

The good thing about an html expression is, that the field value still is the original value. So we can use the interactive report column filters on this value and an export to csv will not add any html tags. This solution is SOC conform. The database query will not bother with the representation of the values.

apex5_YN_icons_filter

Quick. Easy. Useful.

How does it work in 5.1 with Interactive grids?

IGs are slightly different. Especially if they are editable. We can not simply add the icon to a text column. Instead we have a new column type HTML EXPRESSION. In that we can reference a different column.

Here is an example from the Interactive Grid Sample Application (Page 30, Base Edit). The emp table has a column ONLEAVE and holds Y and N values. apex5_YN_icons_IG_column2

The substitution syntax for IG columns does not use # anymore. Instead the expression will look like below. The column value from ONLEAVE is substituted.

<span class="fa syn-check-&ONLEAVE.">&ONLEAVE.</span>

We now face an additional obstacle. The ONLEAVE column is a switch. The switch component will translate Y into Yes and N into No. Our new second column recieves that translated value. We could either add an additional hidden column to the select and referece that one or we can extend our css aliases. Because the switch component look pretty useful, it seems better to include its outcome to the css.

.syn-check-J:before,.syn-check-Y:before,.syn-check-Yes:before {
  content: "\f00c";
  color: green;
}
.syn-check-N:before,.syn-check-No:before {
  content: "\f00d";
  color: #ff3a30; /* color: red; */
}

And here is how the result looks like:
Apex5_YNIcon_IG_column

As you can see the icons look more delicate than before. This is because the sample application uses Font Apex and not Font Awesome anymore.

Example using images

And sometimes we still want an image. Maybe because there is no fitting icon in our font or maybe because the users insist on keeping the old images. This can also be done with css classes. However some more attributes are needed.

Here is an example without further explaination.


.syn-checkImg-J,.syn-checkImg-Y {
  background-image:url("https://upload.wikimedia.org/wikipedia/commons/thumb/0/03/Green_check.svg/240px-Green_check.svg.png");
  background-repeat: no-repeat;
  background-origin: padding-box;
  background-size: 20px 20px;
  padding:0 20px;
}
.syn-checkImg-N {
  background-image:url("https://upload.wikimedia.org/wikipedia/commons/thumb/a/a2/X_mark.svg/210px-X_mark.svg.png");
  background-repeat: no-repeat;
  background-origin: padding-box;
  background-size: 20px 20px;
  padding:0 20px;
}

It works with DIVs and SPAN tags.
And I created a jsfiddle where you can test it out for yourself.
https://jsfiddle.net/1n6s4y3j/

Apex 5.1 session cloning

Introduction

With Apex 5.1 we got a nifty new feature. It is now possible to clone an apex session, so that we can have two (or more) independent APEX sessions running in the same browser.

It only took me 2h to implement this from scratch. So it is really easy to do.

Joel Kallmann describes how to do it in this blog post http://joelkallman.blogspot.de/2016/07/apex-session-isolation-across-multiple.html

There are a few additional tweaks I would like to mention here.

Step 1) Enable session cloning on instance level

logon as sys (or any user with apex_administrator_role).

I first granted my normal DBA account the apex_administrator_role

grant apex_administrator_role to myDBA;

and then as DBA:

begin
apex_instance_admin.set_parameter(
p_parameter => 'CLONE_SESSION_ENABLED',
p_value     => 'Y');
end;
/

 

If you are in a CDB/PDB environment, with Apex installed in the PDB (recommended) then  make sure to run this on the matching PDB (especially when working with sys).

e.g.

alter session set container = PDBAPEX

Joels article also explains how to enable this for a single workspace. But I got an error message when trying to do so.

"ORA-20987: APEX - Instance parameter not found - Contact your application administrator."

Step 2) Add a new navigation bar entry

Of cause you are free to add this functionality everywhere you want. But you will need a link, that the user has to click on to get a new session. My preference was the navigation bar.

The url for the link is simple. Just add APEX_CLONE_SESSION as the request parameter.

f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APEX_CLONE_SESSION

create a navigation bar list entry with this link

  • Go to “Shared Components/Navigation Bar Lists/navigation bar” (the name of my list).
  • Choose any icon (fa-clone) and label text (“Session klonen”) that you want.
  • The target page needs to be “&APP_PAGE_ID.”. This will use the current page and add it into the link.
  • And most importantly the request needs to be APEX_CLONE_SESSION.

apex51_clone_session_navbar_entry

The entry is now ready and working. However it will replace the session in the current browser tab. But rightclick and “open in new tab” gives us two tabs with a different apex session.

If that is not enough then this next step is wanted too.

Step 3) Open the link in a new page

The goal is to add a TARGET attribute to our link, so that a new tab is always opened when we clone the session. Unfortunatly the navbar template has no option to include link atributes. We could do a kind of injection trick with the url, but I prefere a cleaner way.

To do so we need to modify three small parts.

First copy the navigation bar template as a new template “Navbar with Attributes”

And there we add the user defined property #A03# to our link. This needs to be done in all areas where we find an <a..> tag.At least for the “list template current” and “list template non-current”. I also added it for the sub list entries, even if my navbar doesn’t use sub lists.

apex51_clonesession_template

Don’t forget to add a description “Link Attributes” for the newly added attribute value in the “attribute description” section (scroll down a bit to see it).

Then enhance our existing navbar entry with target=”_blank”

apex51_clonesession_linkattributes

There is a tiny difference between using target=”_blank” or target=someothername.

target=”_blank” will always create a new tab.

target=”_someothername” will open a new tab on the first click. Consecutive clicks however will always reuse this same tab. This can be useful if you want to prevent your users to constantly cloning session after session after session.

 

And finally make sure that our application uses the new template for showing the navbar list

Shared Components/User Interface Attributes/Desktop/Navigation bar/List Template

apex51_clonesession_userinterface

 

Result

The navigation bar then could look similar to this here:

apex51_clonesession_navbar_de

Clicking on the “clone session” link will open a new tab in the same browser. In the url we will see that a different session id was created.

The new session will have all the same item values (page items, application items, etc.) as the privious session had. But from this point on, the two sessions will start to differ.

Both sessions will use the same browser cookie. For that reason if one session logs out, then the other session will be logged out too.

 

How to upgrade from Apex 5.0 to 5.1

Preparation

As a preparation I recommend several steps and checks to prepare the upgrade.

  • Make sure you have a working database backup and just in case a DBA at hand who would be able to restore your database, tablespace or schemas.
  • Workspace clean up – delete applications that are not needed anymore. Especially copies of others application, that were created to test a specific feature or do a proof of concept. Be careful whether you also want to run any included deinstallation scripts. For an Apex update in general you don’t want to delete the connected tables.
  • Software Download – Apex 5.1 can be downloaded from the OTN download page: www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
  • Backup Application – Export the application including all private saved reports
  • Export supporting objects like Theme, Static Application files
  • Backup Image folder – if your image folder is /i/ then make a copy and rename the copy to /i50/
  • Check the Apex 5.1 known issues page:  http://www.oracle.com/technetwork/developer-tools/apex/downloads/apex-51-known-issues-3408261.html

Apart from downloding the new Apex version, none of these steps is really required. But it gives a nice and cozy fealing to be sure to be able to go back.

In Apex 5.1 some features are deprecated and some options did change. It is possible to prepare your application to anticipate the effects of the upgrade. I will cover this in a separate blog post. More importantly read through the “changed behaviour”, “deprecated” and “desupported” sections of the installation manual (https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-8BDFB50B-4EC6-4051-A9B6-7D5805E00C4E ).

Here are some things to consider already in Apex 5.0.

  • Apex.Server plugin
    • does not return an jqXHR object anymore
    • async option deprecated
    • htmldb_getobject desupported => replace with apex.server
  • old Apex themes deprecated
  • check for CANCEL or BACK or PREVIOUS buttons (page redirect) with execute validations = YES. These will do client side validation in Apex 5.1. If that is not wanted, change it to NO.
  • jsTRee plugin deprecated
  • classic reports
    • hidden column type => hidden column
    • no enterable fields!
  • file browse storage => switch from WWV_FLOW_FILES to APEX_APPLICATION_TEMP_FILES
  • desupported attributes or types
    • page: body header, include standard javascript and css
    • region: svg charts, simple chart, classic tree
    • button: pre text, post text
    • item: start and stop grid layout, file browse storage type
  • Conditions deprecated: text= value, text != value, text is (not) contained in item
  • No more: Save state before branching
  • apex_plsql_job package desupported
  • check if you reference the internal hidden fields  (renamed in Apex 5.1): pPageChecksum => pPageItemsProtected, p_md5_checksum=>pPageItemsRowVersion
  • date picker (classic) deprecated
  • several updated javascript libraries

Decide about the upgrade path

Now consider whether you want to do a traditional upgrade (all steps in one go) or if you want to minimize the application downtime (several steps, not for CDB$ROOT installations). Or as oracle calls it: “maximize application uptime”.

To minimize downtime read this chapter in the documentation: https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-411DE0D8-59E1-4267-8751-FD56305ABE4E

Upgrade in one go

The only step needed to do:

Create database schemas and database objects (tables, packages) and do the application migrations.

@apexins.sql tablespace_apex tablespace_files tablespace_temp images

Upgrade with maximum Uptime

Instead of running a single script, we can do the upgrade in several steps. Only during the 3rd step, the end users need to be disconnected. This third step took only 1.01 seconds on my system.

The upgrade of an Application Express instance runs in four phases:

  1. Create database schemas and database objects (tables, packages).
    This essentially creates the Apex_051000 schema.
    -> no influence on running sessions
  2. Migrate application metadata.
    This copies the repository application data from Apex_050000 into Apex_051000.
    To help with that some upgrade triggers were previously installed.
    ->  developers can’t work anymore
  3. Migrate data that runtime applications modify and switch to the new version.
    -> downtime for all (developers and end users)
  4. Migrate additional log and summary data (this step starts automatically)
    -> no influence on running sessions

But we need zero downtime – is it possible?

I’m convinced it is possible to reach a downtimeless application upgrade using the EBR  (edition based redefinition) feature of the oracle database. I have extensive knowledge using EBR even inside Apex. However so far I didn’t have the time to do a proof of concept (POC) for the upgrade. Also this would be an unsupported action (currently). The change would include tweaking serveral non editioned objects (public synonyms, session contexts, registry data) in such a way that they show up differently when used inside an edition.

If any Germany based customer or the Oracle Apex Team itself is interested how to do this and is willing to pay for the time I need to invest in this, then please contact me.

do the upgrade

Unzip the Apex_5.1.zip file into an appropriate folder. And navigate to the apex_5.1/apex folder

If you decide for the “Maximum Uptime” upgrade path, then three scripts need to run. And ORDS needs to be stopped for script 3. To run the scripts we need to know the tablespace names and the image path.

Find the tablespace

The documentation gives examples useing the SYSAUX tablespace. I do not recommend that. Apex should have its own tablespace.

select username, default_tablespace, temporary_tablespace, profile, oracle_maintained
from dba_users
where regexp_like(username,'^(APEX_|ORDS_)');

This shows only the default setting. We can reuse the same tablespace. But it is also possible to install apex 5.1 into a new tablespace. If you want to do that, then this new tablespace needs to be created first.

Sometimes we want to see if the data is really in this default tablespace. Here is a select that will show the data distribution and also how much space is used.

select owner as schema, tablespace_name as data_tbs, nvl(segment_type,' - total -') segment_type, round(sum(bytes)/1024/1024,2) size_in_MB
from dba_extents
where regexp_like(owner,'^(APEX_|ORDS_)')
group by owner, tablespace_name, rollup(segment_type)
;

Example result

SCHEMA        DATA_TBS SEGMENT_TYPE SIZE_IN_MB
APEX_050000   APEX     INDEX        239,69
APEX_050000   APEX     TABLE        267,19
APEX_050000   APEX     LOBINDEX     12,75
APEX_050000   APEX     LOBSEGMENT   240,31
APEX_050000   APEX     - total -    759,94
ORDS_METADATA ORDS     INDEX        4,5
ORDS_METADATA ORDS     TABLE        1,63
ORDS_METADATA ORDS     LOBINDEX     0,19
ORDS_METADATA ORDS     LOBSEGMENT   0,38
ORDS_METADATA ORDS     - total -    6,69

This is a example of one of my apex environments. As you can see there is only one Apex tablespace “APEX” used. Approximatly the same amount of data is in tables, in indexes and in LOBs. LOBSEGMENT indicates that there had been some wwv_flow_files activities going on.

Run the scripts

The scripts are located in the apex subfolder. For example D:/product/apex/apex_5.1/apex.

Navigate to that folder and start an sqlplus session with sys as sysdba. If you are in a CDB/PDB environment connect to the PDB not to CDB$ROOT. To connect to the PDB, the service name needs to be provided. Because of that also a running listener and a matching tnsnames.ora file is needed.

Assuming the following settings:

  • tablespace for apex and apex files: APEX
  • temp tablespace: TEMP
  • image directory: /i/

Then those 3 scripts need to run.

@apexins1.sql APEX APEX TEMP /i/
@apexins2.sql APEX APEX TEMP /i/

stop ORDS

@apexins3.sql APEX APEX TEMP /i/

restart ORDS

Phase 4 is automatically started by running a dbms_scheduler job.

Check privs and synonyms

Sometimes we give extra access from the Apex Schema to our own custom schema. For example in one Application I extended the Apex Feedback functionality and did use the view APEX_TEAM_FEEDBACK. Such changes need to be moved to the new APEX_050100 schema.

ACLs

Here is just how to check if the Apex 5.0 schema has any network ACLs set.

SELECT *
FROM DBA_HOST_ACES xe
where principal = 'APEX_050000';

I created a script to duplicate all ACEs that exists for APEX_050000 to APEX_050100.

The script is shown and explained in a separate blog post.

 

Grants

Here is just how to check if the Apex 5.0 schema has any objects granted to other schemas.


select *
from all_tab_privs
where grantor like 'APEX\_%' escape '\'
and grantee not in ('PUBLIC','SYS','SYSTEM');

It will show objects like tables, views and packages that have privileges granted directly.

To see only the missing grants you can run the following statement. If it returns no rows, then you are fine.


select GRANTEE , TABLE_SCHEMA , TABLE_NAME , PRIVILEGE , GRANTABLE ,HIERARCHY
from all_tab_privs
where grantor in ('APEX_050000','APEX_050100')
and not regexp_like (grantee,'^(APEX_|ORDS_|SYSTEM$|PUBLIC$|SYS$)')
group by GRANTEE , TABLE_SCHEMA , TABLE_NAME , PRIVILEGE , GRANTABLE ,HIERARCHY
having count(*) = 1 and min(grantor) = 'APEX_050000';

Those grants probably need to be copied to the new apex_50100 schema.

Search Source code

You should search the complete application, if there are any references to APEX_050000. This should be done after the migration.

downgrade to 5.0

The section how to downgrade an application back to 5.0 is currently missing from the documentation. Here is a official blog post how to do it: http://jastraub.blogspot.de/2017/01/ooops-i-did-it-again.html

This is the sql script that Jason Straub published to do the downgrade:

alter session set current_schema = SYS;

@wwv_flow_val.sql
@wwv_flow_val.plb 

begin
 dbms_utility.compile_schema('APEX_050000');
end;
/ 

set define '^'
@validate_apex x x APEX_050000

begin
 for i in ( select owner, trigger_name
 from sys.dba_triggers
 where owner = 'APEX_050000'
 and trigger_name like 'WWV_FLOW_UPGRADE_%'
 order by 1 )
 loop
 sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
 execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
 end loop;
end;
/

ALTER SESSION SET CURRENT_SCHEMA = APEX_050000;
exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_050100','APEX_050000');

ALTER SESSION SET CURRENT_SCHEMA = SYS;
drop context sys.APEX$SESSION;
create context sys.APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
declare
 l_apex_version varchar2(30);
begin
 l_apex_version := apex_050000.wwv_flows_release;
 dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
 dbms_registry.downgraded('APEX',l_apex_version);
 validate_apex;
end;
/

select * from dba_tablespaces ;

select username, default_tablespace, temporary_tablespace, profile, oracle_maintained
from dba_users
where regexp_like(username,'^(APEX_|ORDS_)');

select owner as schema, tablespace_name as data_tablespace, nvl(segment_type,' - total -') segment_type, round(sum(bytes)/1024/1024,2) size_in_MB
from dba_extents
where regexp_like(owner,'^(APEX_|ORDS_)')
group by owner, tablespace_name, rollup(segment_type)
;