Mathias Magnusson

Subscribe to Mathias Magnusson feed
All Nerd, Complete geek, Never ordinary
Updated: 15 hours 39 min ago

Bug with prefactoring and nondeterministic SQL

Tue, 2020-03-03 07:00

Sometimes the results of a SQL is not at all what you expected. Most of the time it is a simple error you’ve made. Very rarely is it an issue that after hours of looking at it still looks like SQL does something wrong, But it still happens.

This post is about one such situation that occurred las week. If you read the last blog post, you’ll recognize some of this. It turned up when I looked at transforming that SQL to create the many million rows I needed.

with ds as (
 select level rad
   from dual
connect by level < 5
, a as (
select trunc(dbms_random.value(1900, 2020)) bs
  from dual
union all
select 2025 from dual
, b as
select, substr(, ds.rad, 1) dgt
  from a, ds
select, b.dgt
  from b
 where b.dgt >= 0
 order by

When I execute this it should get me eight rows, Four with a number somewhere between 1900 and 2020 and four with 2025. Those four then also has each digit in those number broken out as the second value in the result table.

An example would be like this:

1989 9
1989 9
1989 8
1989 1
2025 2
2025 5
2025 2
2025 0

The first four are 1989 with the digits 1,9,8,9 broken out and the second is 2025 with the digits 2,0,2,5 broken out.

Now the problem is that as written the SQL will return results that should not be possible. An example of the result it returns is.

1931 1
1989 8
2004 0
2008 8
2025 2
2025 5
2025 2
2025 0

Note the first four rows, for some reason it turned them into four different years. It should not be possible.

What happens seems to be a query transformation gone wrong. It does things that it should not when the execution of a SQL is not deterministic (due to the use of dbms_random).

The thing that triggers it to transform seems the be the very last condition in the SQL “where b.dgt >= 0”. That condition should never be fals with the data generated in the SQL, it is there just to showcase the bug. If you remove it you will get the expected result.

Same thing if you change “union all” to “union”, that also makes it produce a correct result. Note that as the query is written, there should be no difference in the result between union all and union.

An argument against it having anything to do with query transformation is that the hint NO_QUERY_TRANSFORMATION has no impact on the behavior.

I may of course have overlooked something here, but I have reviewed this behavior with two colleagues I enjoy debating SQL with.

This blogpost is largely as a reminder to myself and to anyone who may be interested in seeing the odd behavior non deterministic SQL in pre-factoring clauses seem to cause in some odd situations.



LUHNs algoritm – Three ways with SQL and PL/SQL

Tue, 2020-02-25 07:00

Have you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day.

It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Grace, and Israel to credit card numbers and IMEI numbers and misc other things.

It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the calculation used it is also referred to modulus 10.

The algorithm was invented by Hans Peter Luhn in 1954. It was meant to protect against most accidental errors, not against malicious attacks.

The algorithm

Each number in a sequence is alternating multiplied by 1 and 2 with the results added together. After that a modulus 10 is applied to the sum and the result is the check digit the function should return.

Let’s take an example, 374 is the input, we’re looking for the check digit that should be used. The calculation should be based on even number of digits in the input so we prepend with a zero, thus we use 0374 as our input. Now we’ll calculate 0*1, 3*2, 7*1, and 4*2 – multiply each digit in the input with 1 and 2 alternating.

0*1 = 0, 3*2 = 6, 7*1 = 7 and 4*2 = 8. Add them together and we have 0 + 6 + 7 + 8 = 21. Here is where we use modulo, 21 modulo 10 is 1. That however is not the check digit, the check digit is the reverse. That is, what do you need to add to this to make the result 10. In our case we arrived at one after the modulo operation so our check digit is 9.

There is a special case however. If a digit that is multiplied with two is between five and nine then the result will be two digits (5*2 = 10 and so on). That result is then added together resulting in all results being added together as single digits. This if we have 7 we get 7*2=14, 14 is now taken each digit and added so the result we get for the 7 is 5 (1+4). Instead of adding the digit like that, one can also subtract 9 from the result as it arrives at the same end result (14-9=5).

If you find my explanation above to rushed or hard to follow, take a look at wikipedia.

With that explanation out of the way, lets look at three ways to implement this. From slowest to fastest. If you only need this occasionally then it does not matter, they are all fast. I however faced a situation where I had to generate valid numbers that has correct check digits för a few hundred million rows.


Let’s first look at a basic implementation in PL/SQL. I say basic because it is probably the most straight forward implementation of the pseudo code version of the algorithm. It is in my experience the most common way to implement it. When this feature is needed it tends to end up in a traditional function/procedure in PL/SQL.

This code is “borrowed” from Daniel Ekberg, just changing some variable names to make it easier to understand for the rest of us.

create or replace function get_value_w_chk (inv in varchar2)
  return varchar
  in_val     varchar2 (30);
  pos        int;
  total      int := 0;
  multiplier int := 0;
  result_pos int := 0;
  in_len     int;
  chk_digit int;
  in_len := length (inv);
  in_val := inv;

  for i in 0 .. (in_len - 1)
    pos := in_len - i;
    multiplier := mod (multiplier + 1, 2);
    result_pos := to_number(substr(in_val, pos, 1), '0') 
                * (multiplier + 1);

    if result_pos > 9 then
     total := total + result_pos - 9;
     total := total + result_pos;
    end if;
  end loop;

  chk_digit := mod (1000 - total, 10);

  return in_val || chk_digit;
end get_value_w_chk;

The code essentially loops through a string of digits and makes the calculation for each digit, adds it to a total and finishes up with a modulo operation to end up with the check digit, it then returns the parameter concatenated with the check-digit.

PL/SQL recursive function

The cool kids are writing recursive functions all day long or so they would have you believe. This particular algorithm lent it self to it so I wanted to test doing that. Also, next time one of the cool kids claims it cannot be done in legacy languages like PL/SQL I can just point them to this blog post (hello future reader).

create or replace function get_chk(p_i_val in varchar2) 
       return varchar2 as
  v_inval varchar2(50) := 
                  when mod(length(p_i_val), 2) = 0 then p_i_val
                  else                                  '0' || p_i_val

  v_num number;
  v_result varchar2(51) := p_i_val;

  function recur_value( p_i_mult_with in number
                      , p_i_val       in varchar2) return number as
    v_num_char number(2);
    v_num_tot  number(2);
    v_sum      number(2);
    v_num_char := to_number(substr(p_i_val,1,1)) * p_i_mult_with;

    if v_num_char > 9 then
      v_num_tot := v_num_char - 9;
      v_num_tot := v_num_char;
    end if;

    if length(p_i_val) > 1 then
      v_sum := v_num_tot + recur_value( case 
                                          when p_i_mult_with = 1 then 2 
                                          else                        1 
                                      , substr(p_i_val, 2));
      v_sum := v_num_tot;
    end if;

    return v_sum;
  end recur_value; 

  return v_result 
      || (10 - mod( recur_value( p_i_mult_with => 1 
                               , p_i_val       => v_inval)
                  , 10)); 
end get_chk;

Side note: I have now spent an inordinate amount of time on what looks like a broken numbering of the code above. No idea what happens, but I need to find a better code formatter.

The code takes the inparameter and calls the recursive function which works by peeling of one digit from the parameter and calling itself with the rest. This call continues until a chain of calls has been done and the innermost invocation only has a single digit as the inparameter, then it returns it’s calculation and it starts summing each returned value with the calculation for the peel off digit eventually getting the total for all calculations returned to the outer function which performs the same kind of modulus operation as we saw in the first code block.

Pure SQL

The previous versions are what you probably want to start with if your value is already in PL/SQL, but what if it starts in SQL or you generate data with SQL? Then calling a PL/SQL function is not optimal.

In my case I had a need to get a correct check digit for an invented value. When you are to do it with a few hundred million rows you do not want to call a function for every row.

This is what I came up with to calculate the check digit in SQL.

with dl as
  select 1 rad from dual
  union all
  select 2 rad from dual
, a as
  select case
           when mod(length(:1), 2) = 0 then :1
           else                             '0' || :1
         end arg
    from dual
--select * from a;
, b as
select level rn, to_number(substr(arg, level, 1)) dgt
  from a
  connect by level <= length(a.arg)
--select * from b;
, c as
select b.rn
     , b.dgt
     , case
         when mod(b.rn, 2) = 0 then dgt * 2
         else                       dgt
       end calc
  from b
--select * from c;
select 10 - mod(sum(case
                      when dl.rad = 1 and c.calc <  10 then c.calc
                      when dl.rad = 1 and c.calc >= 10 then trunc(c.calc / 10)
                      when dl.rad = 2 and c.calc <  10 then null
                      when dl.rad = 2 and c.calc >= 10 then c.calc - 10
                    end), 10) chk
  from c, dl
where dl.rad = 1
    or(dl.rad = 2
   and c.calc > 9);

What is going on in this SQL? It looks long but it really is broken up into small chunks. We have four WITH-selects before we get down to the real one.

The first prefactoring SQL – dl – is just getting us a “table” with two rows. It is used later to handle the situation of the multiplication of a digit being > 9.

Next is “a”, all we do here is to fix the input in cases where it is of an uneven length. In such cases we prepend the input with the digit zero. An alternative would have been to change so we start multiplying the digits with 2 instead of 1, it achieves the same result.

After that comes “b” where we split the string into one row per digit in the input. The digits are also converted to numbers as we’re going to use them in multiplication in the next step.

In the next step “c” we calculate the value for each digit, The first is multiplied by 1, the second by two, the third by one again and so on.

After that the “real” SQL is executed. It takes the rows with sums by digits and creates two of them using “dl”. For rows where dl.rad is 1 we take the first digit (if calc is 15, we take 1), for situations where c.calc is over 9 we use the dl.rad rows where it is 2 to get the second digit (if calc is 15, we take 5). All those numbers are the added together and the usual modulus operation is performed returning the check digit.

To simplify following and troubleshooting I have the “select * from” rows left in the SQL. Uncomment one and run the SQL to see the result up to that point. It is by far the best way to understand what happens in the SQL, even better if you follow my explanation here and uncomment one to see the result I try to describe.

If this helps you please leave a comment or even more so if you have one variant of this that is simpler or faster.

Waiting for direct write temp – but what file?

Tue, 2020-01-28 07:00

This is essentially a not for myself for the next time I forget. It seems to be like once every other year I figure this out. It is not too easy to google for so it usually takes a bit too long.

So you have identified the event “direct path read temp” or “Direct path write temp” as your main culprit in a SQL. Great, now let’s just see what temp file it is.

That is how I’ve started a few times and I end up not finding it. Let’s take a look at why it can be confusing at first.

Your select runs long and you take a look at v$session_wait for your SID. If you see “Direct path read temp” as the event being the one your session waits on most of the time, you may want to know what file it is to understand more about why.

It is easy enough, the documentation states:

  • P1 = File number
  • P2 = First dba
  • P3 = block cnt

P1-P3 are columns found in views such as v$session_wait giving parameters the describes the details of different wait events. The names are generic due to different events having different parameters.

If you try to look up the file number in v$data_files or v$temp_files you may find that there is no such file. In my case I was looking for file number 1038, with the database having just a handful datafilee and even fewer temp files. Even looking for all files in the CDB, I only found files ranging from 1-650.

It turns out that both temp files and data files are numbered from 1, meaning the database can have a temp file 5 and a datafile 5, but wait events need to be able to distinguish between them with having just one parameter.

After a lot of searching for things like “file number over 1000”, “missing file number oracle” and so forth I usually strike gold with some odd search term and find my way back to this post where the first comment is by Jonathan Lewis and as usual in a few words clearly explains the issue.

For a temp file you have to subtract the initialization parameter db_files from the number found in wait events. After that you have a file number you can look up in v$temp_files.

This post was just to make a post about this to make it easier to find than what the existing ones seems to be when you do not know the reason for this. Maybe it will mostly be used by me next time I forget why tempfiles does not seem to exist with the numbers reported by the events.

BTB – Aggregation

Mon, 2019-06-10 08:00

It is time for aggregating data, using the GROUP BY keyword.

Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are ready,

Hint II: You can run the code in here by going through the little tutorial I created.

In here we will use the hr.employees table that lists data of employees including the department they work in. From this we want a small report showing the number of employees that works for each department.

For this we want to count each employee and sum it up by department ID.

Lets first use this to show all rows in the hr.employees table:

select * from hr.employees;

Now we’ll use the function count(*) to count up all the rows. Without anything else it would just give us a count of all rows in the table. Since we want to get the number of rows (employees) per department we use the keyword “group by” to specify that we want data to be grouped by department id.

Thus we’ll use this SQL:

select department_id
     , count(*)
  from hr.employees
 group by department_id;

When we run this we will get a report like this:

DEPARTMENT_ID COUNT(*) 50 45 40 1 110 2 90 3 30 6 70 1 – 1 10 1 20 2 60 5 100 6 80 34

Pretty nice, now we know how many people there are in each department. JUst having the ID is not all that nice, but it can be found by looking in the department table. In a future post, I’ll talk about how to combine the two tables in one SQL.

The observant reader has noticed that there is one employee that does not belong to any department, that row has an ID of “-“. Can you figure out who it is by applying the last episode of BTB?  It is actually not a dash in the column, but it is a NULL. That is database speak for no value has been given. That too will be a future post.

Back to basics

Tue, 2019-05-28 08:00

Let’s start with baby steps

We’re all very excited to read a new blog with a neat trick or a cool and complex feature or even a very useful undocumented function. However that is preaching to the choir. We do that and then we get together and complain that there is no new fresh talent in the database business. Well, it may be a bit hard to get into it if all we do are talking new features and really cool uses of analytical functions.

I think we need a bit of both, some stuff that are not advanced. In fact it does not have to be perfectly accurate or usable in all cases, the most important thing is that for someone new to the field it is understandable and usable for just getting something that works for their experience level.

My intent will be to challenge´myself to write things I feel is cringeworthy simple when read by anyone who has been around the block a time or two. If you find something to be “ridiculously basic, annoying that anyone would blog about such newbie content”, then I have achieved my goal.

Still reading? Then you must be looking for such material. I will end this post with a light overview of the mandatory keywords in a select, the stuff often referred to as vanilla SQL. This is the stuff I pretty much assume a reader will be aware of before looking for blogs on more.

Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. Just cut and paste the SQL below and runt it and you have the result displayed immediately. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are ready,

A SQL as basic as they come will look something like this:

select department_id
     , department_name
     , manager_id
     , location_id
  from hr.departments
 where department_id between 40 and 70;

There are three keywords to this SQL – Select, From and Where. Technically a SQL can do without the where clause but that rarely happens in practical SQL used for a purpose.


The select keyword is where you list the columns you want to select the data from.


The from keyword lets you list the table(s) you want to get data from.


The where keyword lists the conditions.

In plain english the SQL translates to “Get me the id and name of the department, the IDs for the manager and the location. I want this data to be fetched from the department table where the department ID is between 40 and 70. Not too far from the SQL itself.

Now, go run it on Live SQL. What is holding you back?

Getting t1001 in interactive grid PK

Tue, 2019-05-21 08:00

Have you been plagued with the issue of getting values like t1001 show up in columns you marked as primary key in your interactive grid? In case you do not feel it is a good interface for your end users you may have found yourself spending quality time with Google looking for clues. I have had a couple of people asking me about this in just the last week. Googling turns up very little and mostly about the T1000 processor from SUN.

What you can find are some discussions about how this is a way for APEX to manage records that have not yet been saved to the database. No matter what you select for default value of the column, it still shows t1001 or some such value.

First things first, is this a bug? No, I don’t think so. It is part of how interactive grid manages new rows, the question is how to design applications to met your requirements. Hopefully this post will help you do just that.

In the following walkthrough I have been using with version

Lets first show the problem using the DEPT table.
– Create a new page with an Interactive Grid based on this SQL.

select deptno
     , dname
     , loc
 from dept;

– Go into the attributes for the interactive grid and set it to be editable.

An editable interactive grid needs to have a primary key defined.
– Go into the attributes and select DEPTNO.
– Set it to be a primary key in the source section.

The report is now able to be used and you can add new departments. Run it and click on new row and see what happens.

Screen Shot IG t1000
The new line shows t1001 in the primary key column. It is used by APEX for processing new rows, but it is not always what an end user would like to see.

In many cases the primary key is not useful to display aand can simply just be hidden from view.

In this case it is a column the end user should (according to the user) see but not be allowed to set, it ought to show nothing for new rows and there ought to be a visual clue that it is a key. There are probably many ways to achieve this, but finding a straightforward solution can be hard even if the completed solution seems obvious in hindsight.

Here is my way to handle it. I like it because it is purely declarative, no-code, and it feels 100% logical – at least to me – once we accept that the primary key really is a data element APEX and the interactive grid controls.

What we do now is hide the primary key from view, but we  need the data to be shown.

Let’s begin with just hiding the primary key.
– Set DEPTNO to be of type hidden.

Now Let’s add a column that just shows the values but cannot be edited by the user
– Right click on the columns heading (under the IG in the rendering pane).
– Select “Create Column”
– Give it a good name. Something like “DEPTNO_SHOW”
– Set the label to “Deptno”
– In the “Source” section change Type to be “Database Column”
– Enter DEPT_NO in the “Database Column” field.
– Chose “Number” as the datatype.
– Set “Query Only” to Yes.

With this done we’re ready to take it out for a spin. Run the page and start with adding the column if it doesn’t show up. Drag it to the beginning of the line where it used to be.

The first thing to notice is that the “Deptno” column indicates that it cannot be edited by having a different background than editable columns. Now click on “Add Row” and lets see if it works.

Screen Shot 2019-05-19 at 21.51.21
Much better. Deptno cannot be edited and it does not show any data when a new row is created that can confuse an end user. It has a visual indication that it is a key or at least an uneditable field. When you save the data, DEPTNO is given a value and the column is updated in the report. It is done by a sequence and a trigger on the dept table assigns it a value when a row is inserted. You can of course use all the features for default value on the DEPTNO primary key column if you prefer to manage its value from your APEX-application.

Updating and deleting of course also works as it should. We have not messed with the APEX model for how to do this, only adjusted how the users sees the data. For cases when showing no value until it gets set during the insert is requested this will provide the end user with the interface they want while still letting the interactive grid do the heavy lifting.

Please let me know if this helped you or if something in this post was confusing.

Quicktip: Don’t grant access to a synonym

Wed, 2019-05-15 08:00

I’ve seen this in a few places lately. When I ask the database gays there about it, they think this DDL would never work or “it does nothing but does not return an error”.

Here is an example, All of the following is performed by a DBA user. We create a table, let’s call it TB. It is owned by user A. User B is granted select privilege to A.TB. A synonym is created for user B pointing to A.TB, let’s call it SN. Now we grant select on B.SN to user C.

User C => (user B) B.SN => (user A) A.TB

C can log on and do a select from B.SN and see the data from A.TB. Life is good. However, now it is decided to remove the synonym B.SN so that C can no longer see the data in A.TB. It always work to just drop a view so why not?

Well… A view is an object that has much more weight than a synonym. A synonym is essentially a lightweight pointer. Think a file link in Unix/Linux or a shortcut in Windows.

Same thing in the database, the privilege does not belong to a synonym the way it does for a view. After revoking the view, a crafty user can still select data from A.TB.

One client though that by removing their API-schema all access from another system was shut down. It was not an some accesses had by mistake been coded as direct (as if user C wrote a select from A.TB instead of from B.SN)

How can this be helped? Use views when creating objects that should carry privileges of their own. Use the user that owns objects to run all DDL making it harder to do this by mistake, B cannot grant access on A.TB to C. Catch grants on synonym in peer reviews and add reports showing grants going further than you intend. In the example, only user B which is the API-user should have access to objects user A has created. So if there are any other privileges then SQL can be used to find issues like this.

When views causes havoc

Wed, 2019-05-15 03:00

Views are great. They simplify design, makes code look more elegant and hides complexity. They also enables reuse by putting complex code in just one place instead of in every accessing piece of code.


There once was a large project that has been churning away for a long time. The performance of having more than one user on the system was horrendous. But it was chalked up to misconfiguration.

I am asked to spend a day giving said configuration a quick glance.

I start looking and am simultaneous asked to write code to make it possible to generate large volumes of data. These two efforts leads deep into their design.

It turns out a creative way to model the data was hidden in a layer of views. On top of those views there were other views. Something happened here with performance and it was painful beyond belief.

The data model had to be completely changed to allow SQL to properly navigate it in a relational fashion. But the views was a much bigger problem. It had ended up being an object oriented persons wet dream. Multiple things was subclassed that in the relational world would be one. Here is a completely made up example, the data was of a different kind but to make it less obvious for outsiders what I’m referring I’ve changed what was represented.

There was different kinds of people, lets say managers, project leaders and individual contributors. Each of these were in their own table. Those people had access to one or more vehicles, tractor, bicycles and cars. Each vehicle was placed in it’s own table.

These subclassed tables were essentially identical. It could have been one table. In fact the code needed to be able to deal with people as one entity and vehicles as another. To achieve this one view for each was created that put them back together via a union all + order by.

Next up was the access for these things together (what vehicles a certain person has), to achieve this a new view was created that did a join of the persons view with the vehicles view. Now this work just fine in development with some 30 persons and 30 vehicles.

When the data grows, it does not work at all. The reason being that a unioned dataset cannot be joined with another unioned dataset using indexes, especially when the data being unioned does not reside in the same table.

What ended up happening was that all views were fully materialized and sorted, then the join happened and out of it we pulled a single record. And then it was done again. This caused a massive performance issue and not even a big EXADATA helped the performance.

Of course to make matters worse, an ORM was put between actual code and the database. So what really happened was even more hidden. The developers did not understand the database and the database folks could not read and understand the SQL passed in by Hibernate.

My report stated that I saw no way for this to work and I could not see a way to fix it without doing a major redesign. It finished off with saying I realize it will not happen as it will be months upon months of rework for the whole project and that cost was not going to be accepted.

I come in the morning after and am told to drop everything. I get to redesign it the way I want and no limits are placed on the design. Nine months later the application has completed a rewrite for the data model and access model I have built. It is by far the best performing application at the site.

Did we learn from this to design data model before code. No, that is not the hip way to do it. What people took away even though I explained why this happened time after time is “Views are bad, you cannot get good performance with them.”.


Quick note on MT and applications II

Tue, 2019-05-14 03:00

Talking about Multi Tenant and applications, one has to talk about the different options for sharing. It is one of the great features of multi tenant.

It is a way to let one PDB – the application root –  have data that can be used from other PDBs.

There are three kinds that allow you to get different features of the sharing.

  1. Metadata
  2. Data
  3. Extended Data

Lets review each kind and why and how you’d use it.


This allows you to create a table in the application container and also add data to it. However upon syncing it to an application PDB, only the tables structure is copied. The data is local to each PDB.

This would be useful to install the same schema structure in many PDBs. Say that each PDB is used for a different customers data. Or that you in test want to quickly set up the schemas a new PDB needs. Then each schema is a different application in the application root and you just sync the ones you need.

In testing this may be one of the greatest features in the database to drive productivity in getting new PDBs ready for developers.


Data is the direct opposite of a metadata shared table. It makes it so the table can be found in an application PDB, but all data is stored in the table defined in the application root.

This is a great feature when you have a system that manages the data, but many other systems needs to read it. It becomes a master data of sorts without even copying the data to every target system. Each application can use it as if it was a table local to them and the data is always exactly what the source system has.

For production where one system created data that is used by many other systems. For example a system that holds all you products or all people that are or have been customers. Or in test it can be all the reference data your systems use. No need to copy it into every single PDB, set it up once and let every PDB just have read access.

Extended Data

Extended Data is a hybrid of Data and Metadata. It allows the data and table to exist in both the application root and in the application PDB. From the PDB, you see the data in the root as well as the data in the PDB. Data can be written and modified in the PDBs data.

This can be used in systems where all systems should have a base of information, but that new data can be created in each localized version for a customer (in their own PDB).

It can also be used in testing to get every tester a base of test data which they can add more data to.


Using the different versions of sharing for tables in a multi tenant setup can greatly enhance your ability to support developers and testers to be more productive as well as to deliver key features in production in a very nice way.

Used the right way, application containers in multi tenant is probably in itself worth the price of admission.

Quick note on MT and applications

Mon, 2019-05-13 08:00

Are you using multi tenant with multiple containers and you’re not familiar with application containers?

It allows you to set up PDBs that belong to a CDB-like PDB that “owns” them. With that you can install applications (database objects) in the container, and then decide into which PDB to install, upgrade or patch that particular application,

So you could have an application that is a set of schemas with objects that belong together in a logical sense. Once you have the application created in the application container, you can go to an PDB and sync that application there to get it to the latest version.

It is a very nice feature you should take a look at if you are licensed for the multi tenant option. I like to think of this feature as kind of “EBR light”. It is for sure not a full EBR, but it is a very neat feature to install the next version in the application container, verify it there before you sync it into your PDB where the system is actually running.

Start with the Oracle documentation.

Does recreating a view remove the privileges?

Mon, 2019-05-13 03:00

Short answer: No

This blog post is pretty much just to document what seems to have been hard to find online.

At work there was a big rework of a liquibase project due to the perception that “create or replace view” on an existing view would make the granted privileges be dropped. That was of course not what happened.

The issue was that using liquibase the new version of the view was installed. Then the rollback was triggered and it executed the manual version of undoing the create view. Unfortunately the way this was done was by issuing a “drop view”. After that the update via Liquibase was executed again.

This ended up giving people the impression that replacing a view results in privileges being lost. They are not lost and once the conclusion was questioned it was easily proven that the database does not lose privileges on a replaced view, it does however drop them if you drop the view.

Running a few google searches I found some bad information, primarily on some large forums not too trafficked by Oracle or even database experts.

I just wanted to put this out in case it may help someone struggling with this in the future and hopefully someone finds this and not the erroneous forum posts about how databases work. They do not implement replacing a view as drop view and create view thus losing privileges…

The Internet is a funny and scary place.

Your very own development environment

Sun, 2019-05-12 13:02

Everyone should have their own local development environment. No matter if you write Java and that just means IDE and JDG, or if you work with databases. Not having it reduces your learning a lot. I have yet to meet a really sharp database expert who does not have a local personal environment. It also goes the other way around, you can usually guess if a person has it or not.

Yes, you can get a database by just downloading a VM or use or hack in a database your employer provides. But to be able to extend and really do all the things that should be possible, you need your very own.

I have before made one documented setup available to friends and colleagues. However with docker and automation being in vogue it was time to make the jump from VirtualBox and just a textual description to a scripted setup for docker.

Fortunately Gerald Venzl has done the heavy lifting and led work to get both Oracle XE updated as well as getting docker files for all kinds of good things published. Using that I got the database and Java installed in different docker containers. I then managed to extend the Having that readydatabase to include an APEX-installation and the Java-installation to extend with ORDS and make them play nicely. I rounded it off with a utility-container for SQLcl.

So with that it builds a complete development environment with the database (which of course includes SQL*Plus), APEX, ORDS, and SQLcl. All that is needed is to run a couple of scripts to get it going. Having a development environment that can be rebuilt in a few minutes is a great feeling. I knew I had to get to that point after my premade environment went belly up at DOAG in 2018. It went completely corrupt minutes before my presentation, making it for the most stressful presentation to date. Never again! Famous last words…

Anyway, take a look at my development environment setup on github.

When you have tested that, you can of course activate the REST-features as you have a complete connected ORDS-config.

ODC Appreciation Day: Old Blog Posts

Thu, 2018-10-11 15:17

FIrst of all, kudos to  Tim Hall. This is one of the great times of the year when all these thankful blogs are posted in a day. If you read nothing else in the blogs all year, picking some of them would not be a bad choice.

Speaking of which, I will talk about being thankful for blogs in general. No, not about the new ones explaining all the new features we get in new versions. While that is very fascinating and motivates me to play with new tech, it is not the great thing with blogs.

What I think is great with blogs is old blogs. Yes, there may be few things as unsexy as old forgotten blog posts.

When there is an issue to resolve or a concept to get up to speed on, there are few things better than the blogs written  a year or ten ago. But are not everyone experts on everything when some time has passed. Nope, not even one person is a specialist on every facet of Oracle database technology.

Sometimes it is one thing one knows fairly well but needs a to understand edge cases, experiences, when not to use and so forth. Then a short google session generates a reading list as good as any book.

A few examples of blogs I have stumbled on that was not published this year but that provided great information and input follows. They are just things from the top of my mind when thinking back about such blogs.

Tobias Arnhold wrote about views provided by APEX. It shows a SQL listing all the views and shows the views hierarchical relationship. Not only was that useful, but a side effect was that I realized this was available as well as that the relationship could be found in metadata. It is a post over six years old, often thought of long forgotten. Still very applicable. In fact,  Tobias may even have forgotten the post himself, it is even more applicable today as there have been a lot of views added to the ones available back in 2012,

Another  great one is Connor McDonalds@connor_mc_d –post from early 2016 on auditing row changes. It is a great collection of all the things provided to uyou to avoid auditing columns, tables, triggers and so forth. Including a complete test case and demo from start to finish. Still two years old, but when needed it is a great start.

Then there are of course classics like Toon Koppelaars blog The Helsinki Declaration (IT-Version) that starts with the IT-version of it. If you have not read the four posts it consists of, you owe it to yourself to do so. Go back to the beginning. It should be mandatory reading for how to build database intensive applications.

I leave it at those to not extend this post more than necessary. But it shows the value of the combined effort of the community to write down small and big things as we come across them. There is immense value in those posts long after they have been written. Some are even more valuable a few years later when the world and the technology has matured enough to make it mainstream.




Customized help text in APEX

Wed, 2018-05-09 07:00

Yes, I’m finally back. The last post was written right before a complete period of offline before it was even scheduled to be published. That followed with slowly getting back. I have been fully recovered for a while but not gotten back enough to find time to blog. That changes now. So what causes almost a half years time to recover. Going out with the trash. Yes, it really is dangerous and should be avoided at all costs. What was really dangerous was the part of falling and acquiring a serious concussion. That was not made any better when I fell again a week later to get another severe concussion. I strongly advise against acquiring even mild concussions.

I’ll finish up this series about help texts with customized help text. The dedicated, in-line, modal, and non modal variants I’ve covered has all been declarative. Now we’ll take a look at what can be done if we want to not just modify with how it is shown to the user, but how it also looks.

For this blog I used version of APEX on

As I’ve stated before, my preferred way to deal with help text in APEX applications is to do it with inline help as the this blog post discussed. I have honestly never had to use this custom version in any real application. I imagine a lot can be done with just customizing CSS to make the help text show as one ants. But I guess for a customer that really want to control this, this option may come in handy.

There is a procedure provided with APEX for this. It is APEX_APPLICATION.HELP. it has this signature (taken from the linked documentation).

p_request              IN VARCHAR2 DEFAULT NULL,
p_flow_id              IN VARCHAR2 DEFAULT NULL,
p_flow_step_id         IN VARCHAR2 DEFAULT NULL,
p_show_item_help       IN VARCHAR2 DEFAULT 'YES',
p_show_regions         IN VARCHAR2 DEFAULT 'YES',
p_before_page_html     IN VARCHAR2 DEFAULT '<p>',
p_after_page_html      IN VARCHAR2 DEFAULT NULL,
p_before_region_html   IN VARCHAR2 DEFAULT NULL,
p_after_region_html    IN VARCHAR2 DEFAULT'</td></tr></table></p>',
p_before_prompt_html   IN VARCHAR2 DEFAULT '<p><b>',
p_after_prompt_html    IN VARCHAR2 DEFAULT '</b></p>:&nbsp;',
p_before_item_html     IN VARCHAR2 DEFAULT NULL, 
p_after_item_html      IN VARCHAR2 DEFAULT NULL

p_request is not used.

p_flow_id and p_step_id is application id and page id respectively.

p_show_item_help controls if help about individual items should be shown.

P_show_regions controls if information about each regions should be shown.

The remaining parameters have one before and one after parameter of each kind. The control what html we want to have injected before or after a certain element on the page.

p_xxxxxx_page_html controls what is put before the page info and what is put after the whole help text (the page info is considered to include regions and items).

The remaining parameters are conditional, such that the content is ignored when one of p_show_item_info and p_show_regions is set to something other than ‘YES’.

p_show_reqions has to be YES for these to be considered:

  • p_before_region_html
  • p_after_region_html

They control what HTML to inject before and after each region help text.

p_show_item_help has top be yes for these to be considered:

  • p_before_promt_html
  • p_after_prompt_html
  • p_before_item_html
  • p_after_item_html

The prompt ones controls what is injected before and after the label for each page item. The item ones control what is injected before and after the actual help text for each item.

Rather than describing how to build this, I recommend that you look at it on my demo application. The demo application allows you to play with all the above parameters and see the effect it has.

Watch it live

Take a minute and check out this live in my demo application to see for yourself how this can be used. Log in with demo/demo.

Help text as a modal dialog

Wed, 2018-01-03 04:00

To continue on the theme of help text in APEX, lets take a look at showing it in a modal popup dialog in this post and then finish off the subject in next post with cusomizing how help can be shown.

Screen Shot 2017-12-30 at 22.16.47

For completeness I’ll include a short section towards the end about how it works in non modal too.

In the last post I show how to show help text inline on a page and on the one before that how to set up a specific page to be the landing page for showing help text for any page in the application. In this post I’ll show how to get help text displayed in a modal dialog.

For this blog I used version of APEX on

As I’ve stated before, my preferred way to deal with help text in APEX applications is to do it with inline help as the last blog post discussed. But sometimes a dedicated page is preferable and sometimes a modal popup dialog is better. I think those cases might be preferred in cases where the help includes a lot of text and sdeeing the actual page at the same time is not needed. For example if the text more explains a concept than how to fill out the fields in the page.

We will set up another page with just a help region. We could make a copy of the page we created in the first post in this series, but to make this post more self contained we’ll create the page from scratch.

Creating the modal help page

Create a new blank page (I’ll use 4 in this example). Chose modal page type.

  • Add a “Help Text” region to the content body.
  • Name = Help
  •  If you did not get the page created as modal, go to the page attributes.
    • Find The Appearence group.
    • Page type = Modal Dialog

This is identical to the page 2 created in the post about help on a dedicated page with just the page type changed to be modal.

Create a button to show the modal help

We’ll first create a button just to show the machanics just because it is the easiest way to test it. After that we’ll finish off the access to help with a navigation bar entry, just as we did in the previous two posts.

Open the page from which you want to open the help. If you’ve followed along on the previous posts, it would be page 3. Any page for which yiou have some help text defined on the page attributes will do. That is needed just so we can see it work.

  • Add a text button to any region you have on the page.
  • Set the page to redirect to a page in this application
  • Set the page to be page 4
  • Open the advanced group in the popup
  • Request = &APP_PAGE_ID.

Test the page and see that when you click the button you get a modal popup page with the helptext for the page you button is on.

With this working we know the mechanics of this works, now we just need a navigation bar set up for it so it becomes available across the whole application.

Create a navigation bar entry

As before, we’ll finish off this version too with a navigation bar entry. It is by far the most natural way to provide a natural way for a user to request help on a page and for it to be available verywhere in an application.

Since the difference against the navigation bar entry for the dediczated page is largely that the page that is linked is defined as a normal or modal page, this pretty much a repeat of that.

Go to shared components and click on Navigation Bar List, and then on Desktop Navigation Bar. Click Create Entry and set:

  • Sequence = 50
  • List Entry Label = Modal Help
  • Target Type = Page in this Application
  • Page = Your modal help page (4)
  • Request = &APP_PAGE_ID.

If you run the application now you will see “Modal Help” up in the navigation bar, and clicking it takes has the same exact result as the button. It shows the help in a modal dialog by pulling up page 4 as a popup over the page your requested help from (3).

Now any new page you create for which you write help text will let the user click on the navigation bar and get the help text with no extra effort from you.

What about non modal?

Well, there is of course the option of a non modal page type too. The result of it is a stand alone window you can move around and keep referencing while navigating in the application.

To set it up we’ll just copy the page, button and navigationb bar entries.

Copy the page we created here (4) to a new one (5) and give it the name “Non Modal Help”, accept all other attributes and just click forward and create the page.

In page attributes:

  • Change the page type to  “Non-Modal Dialog”.
  • Set width to 700
  • Set height to 600

Go to your page in the application (3 if you have followed along) and copy the button “Modal”.

  • Name = “NonModal”.
  • Sequence = 80.
  • Target->Page = 5.

Test it to see your button creating a new window with the helptext.

Go to the shared components -> Navigation Bar List -> Desktop Navigation Bar

  • Click copy icon on the right side for “Modal Help”
  • Display Sequence = 60
  • New List Entry Label = Non Modal Help
  • Click “Copy List Entry”
  • Click “Non Modal Help”
  • Set Target->Page = 5

Now you’ve got a non modal help option in the navigation bar too. Try it out.

Watch it live

Take a minute and check out this live in my demo application to see for your self the effect of it before you build it yourself. Log in with demo/demo.

Help text in APEX in-line with page

Wed, 2017-12-27 04:00

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In the last post I show how to set up a specific page to be the landing page for showing help text for any page in the application. In this post I’ll show how to get help text displayed inline with a page the user is on.

For this blog I used version of APEX on The following has been the same since at least release 3.2 of Apex while where and how you enter the needed properties may not be identical in previous and future releases. I don’t think it has changed much over the years.

Show help in-line

It is often preferable to be able to see the help on the page you want to know how it works. If you have it on another page like I showed in the last post, you often end up going there reading a bit and then go back to look only to repeat that a number of times.

What if you could see the help on the page you’re on? Sometimes that is much better.

Setting up the global page

The basis for this is using the global page to have a halp region be available on every page in the application (unless you restrict it).

Create a global page if you do not have one in your application. Then add a “help text region” to the Content body of the global page. Name it “Info” and a sequence of 0 to make sure it is the very first region in the content body.

If you run the application now, every page shows the help text for it’s page. So it is already working, but we want the user to select when it is to be displayed.

We will use a page item to define when the help is shown and when it is not.

Creating the page item

Lets create a page item “AI_SHOW_HELP” to let us control when help is shown.

Go to the shared objects:

  • Click on “Shared Components”.
  • Click on “Application Items”.
  • Click Create
  • Name = AI_SHOW_HELP
  • Session State Protection = Checksum Required – Session Level
  • Click Create Applöication Item
Set default value for AI_SHOW_HELP

To make sure the application shows with help not being displayed, we’ll set up an application computation to default the application item to N.

  • Click on “Shared Components”.
  • Click on “Application Computations”.
  • Click Create
  • Computation Item = AI_SHOW_HELP
  • Computation Point = On New Instance (New Session)
  • Computation Type = Static Assignment
  • Computation = N
  • Click Create Computation
Set a condition for when to show the help text region

With the Application Item in place and dafaulting it to not show help, we just need to set the condition on the help text region on the global page to only show when AI_SHOW_HELP has a value different from N. To make sure it is only shown when we have requrésted it, we check for it being Y rather than just not being N.

  • Open the global page in the page designer (editing the page)
  • Click on the help text region (named Info above)
  • Scroll down to  Server Side Condition group among the regions properties
  • Type = “Item = Value”
  • Item = AI_SHOW_HELP
  • Value = Y
  • Click Save

If you now run the application, the help will no longer show on any of your pages. The reason is of course that we default AI_SHOW_HELP to “N” while only showing the help text when it is set to “Y” without any means to set it to “Y”.

What we need now is a way for the user to toggle AI_SHOW_HELP between “Y” and “N”

Navigation bar entry to toggle help on or off

The way to create a toggle in the navigation bar is to have two entries and only show the one the reverses the current selection, i.e. if AI_SHOW_HELP is “Y” then let it be “N” and vice versa.

Head back to shared components and click on “Navigation Bar List”, and click on “Navigation Bar List” in the report to go to editing the entries in the navigation bar.

  • Click “Create Entry”
  • Sequence = 30
  • List Entry Label = Show Help
  • Target Type = Page in this Application
  • Target = &APP_PAGE_ID.
  • Set these items = AI_SHOW_HELP
  • With these values = Y
  • Conditions = Value of Item/Column in Expression 1 is != Expression 2
  • Expression 1 = AI_SHOW_HELP
  • Expression 2 = Y
  • Click Create List Entry

Now we have a navigation bar entry (high up right where the logout link is) that toggles help on and that is only shown when help is not shown.

Run the application and click “Show Help” in the navigation bar to see the inline help being visible again. There is then – yet – no means to turn off the help.

Now that it works we need to create one more entry that does the reverse. It is shown when help is visible to allow the user to hide the help again.

Return to the edit window and lets set up a reverse navigation bar entry of the one we just created.

The easy way to do it is to click the copy icon on the right side on the row for “Show Help”. Do that and enter the following values.

  • Sequence = 40
  • New List Entry Label = Hide Help
  • Click Copy List Entry

Now lets edit the few things we need.

  • Click “Hide Help” in the report over navigation bar entries.
  • In Target section, set “With these values” = N
  • In Conditions, set “Expression 2” = N
  • Click Apply Changes

That is it, the application now has a toggle between Show Help and Hide Help to toggle showing help about pages inline in the application. Note that it is an application wide setting, so once on it remains active until the user turns it off. Thus, if you turn it on and move around in the application, the help will show on every page until you decide to turn it off and not show help anymore.

Watch it live

As I said in the last post I have set up a demo-app I’ll use to show the effects when I blog about things APEX where it makes sense to have a an app to show the feature. For the above, take a look at it. Log in with demo/demo.


Help text in APEX on a dedicated page

Wed, 2017-12-20 04:00

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In this post I’ll show how to set up a specific page to be the landing page for showing help text for any page in the application. It is the precursor to the next post where I’ll take it a step further and show how to get help text displayed inline with a page the user is on.

For this blog I used version of APEX on The following has been the same since at least release 3.2 of Apex while where and how you enter the needed properties may not be identical in previous and future releases. I don’t think it has changed much over the years.

Show help using another page

The following information is to a large extent a click stream version of Oracles official documentation.

The number in the parenthesis are example page numbers just to make sure there is no confusion of what page is referenced. It is the page numbers you’d end up with in a brand new application

Set up two pages

Create a blank page (2) and then add a help text region to the content body of the page. This is the page that will be used to display the halp for any page in the application.

Create another blank page (3) and scroll down to the bottom and fill in help text about the page in the “Help Text” property.

Help text location

Now we have everything needed in place, we just have to add any means of navigation to get the help page (2) to be loaded with the helptext of this page (3). Typically this is done with a link in the navigation bar as we’ll see later, but it is often easier to just try out navigation with a plain button.

Add Navigation

Add a button and label it OtherPage. Set the target to be the page number of the help page you just created (2). Set the request (in 5.1 under the advanced catagory) to “&APP_PAGE_ID.”.

Run the page (3) and click the button. You will be sent to your help page (2) where the helptext you entered for the page (3).

If you add items on your page (3) the help text for those will also be shown on your help page. You will however not want to create a help button on every page in your application. It would both wast real estate on your page as well as time to set it up on every single page.

Navigation bar

To make the help for every page in your application be displayed with no additional work per page other than writing the help text, let’s set up an entry on the navigation bar.

Go to shared components and clock on Navigation Bar List, and then on Desktop Navigation Bar. Click Create Entry and set:

  • Sequence = 20
  • List Entry Label = Help Page
  • Target Type = Page in this Application
  • Page = Your help page (2)
  • Request = &APP_PAGE_ID.

Editing navbar for help page

If you run the application now you will see “Help Page” up in the navigation bar, and clicking it takes has the same exact result as the button. It navigates to he help page (3) and shows the help text for your page (2).

Now any new page you create for which you write help text will let the user clock on the navigation bar and get the help text with no extra effort from you.

Watch it live

I have just set up a demo-app I’ll use to show the effects when I blog about things APEX where it makes sense to have a an app to show the feature. For the above, take a look at it. Log in with demo/demo.


Database is the marquee feature again

Sun, 2017-10-01 17:37

So every year before and during Oracle Open World we all complain about how data and database is brushed to the side.

This year it is the feature. It is the one thing the big sign on Moscone West screams. “The Autonomous Database”

Here is a picture from showing it. It is all about database.

Every year there is talk about how Oracle needs to return to data and databases. Now that they do, I think we should be very happy even if we suspect the actual feature isn’t what we would have requested.

Any day Oracle talks about database is a day they’re not spending on forgetting it.

Spam killed

Sun, 2017-05-21 16:51

After I moved my blog to self-hosting, it has attracted more and more spam. It started slow and very manageable. After my last blog post it got really bad. What started as a few spam messages a week, culminated with 30-40 spam comments per day. At that point I had completely lost any ability to sort through it.

‘I was so disheartened by this that blogging didn’t happen as I knew I had to solve this first. I thought solving it required paying for Akismet and that annoyed me. I pay for my hosting and domain and so forth, but paying just to get rid of comments meant to abuse my blog felt wrong especially as the blog does not have that much traffic that it felt reasonable to have to filter spam.

I kn ew I had to do something this weekend and after just a little bit of googling I had seen a few recommendations for Anti-Spam by Webvitaly. After reading up it seemed like a very promising option. Since spam engines does not do JavaScript, it places a hidden field on  the comment form and the populates it with the correct answer when someone submits a comment, but the spam engines puts no or bad data in it so the comment is just ignored.

After installing it 36 hours ago, it has filtered out 60 comments and not let through a single spam comment. I say it does the trick for what I need. I can go back to blogging about Oracle and other technologies I fancy, instead of being forced to deal with spam comments.

If you need to filter spam and be able to not even review them, based on my experience this far I’d definitely recommend you to look at this plug-in.

Speaking at DOAG and RMOUG rocked

Tue, 2017-03-14 04:50

As I wrote a while back I was accepted to speak both at the User Group Leader summit at DOAG16 and at RMOUG Training Days.

The first one was a short presentation where I talked about a large bug in Oracle security and the need to patch and upgrade to not have that exposure. It was great fun as it was limited to a four-minute talk. I learned a lot from preparing for it as that short time allows for no questions and no spur of the moment comments. Each slide has to be carefully timed to make sure the time is enough for all slides.

The last one was about a customer case where a severe performance issue was handled where I talk about all the assumptions we challenged in the process of resolving it. The job took 36 hours and it could only use 8 and soon the amount of work was expected to double. It ended up taking just a few minutes when we were done. Part regular tuning and part using the “magic” of the EXADATA.

While the talk ended up having few attendees – competing with Maria Colgan and Graham Wood is tough – it was a great experience. I have not presented at a conference this big before. Training Days is also a conference that scares me to present at. I lived in Denver and my respect for the conference, the presenters and the quality expected is almost at an unhealthy level. So being there to present was a way to slay a dragon of mine. I had a great time at the conference and I enjoyed presenting. Even though I did not have an oversubscribed room, those who came seems to have enjoyed the session as I was rewarded with a 9.0 rating for the talk.

If you’re thinking about maybe going to Training Days next year, my advice is to do it. It is a great conference and it is extremely well-organized. It is small enough to know the layout and the rooms fast, while still being big enough to have a lot of great talks to choose from every session. There were several where I wanted to go to three and I still regret having missed those where 2-3 fantastic sessions were held at the same time.

I really liked the effort made to make the biggest names available and approachable by everyone by having them have their own tables at lunch time and letting people sit at the table where one of the persons they respect the most sits. I really enjoyed my lunch at Cary Millsap’s table. It was a great group and a very inspiring discussion about performance and discussing old battles in the field.

It is far away, but I’ll be back. I had a blast.