Loading...

Tuesday, July 22, 2014

An example of a user-defined aggregate function

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

May be user-defined aggregate functions are not among the most frequent hits in the life on a PL/SQL or APEX developer, but today I wanted to find an elegant solution for a problem submitted by a customer without reinventing the wheel:
given a report containing the labor expressed as hours and minutes in two separate column containing simple integers, how can I calculate the corresponding total?

The first thing that came off the top to my head was to convert this pair of values into a datatype that has been created exactly for this type of problems: interval day to second.

For instance, given two values representing 4 hours and 20 minutes, Oracle can represent this piece of information as a single value as follows:

select NUMTODSINTERVAL(4,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
from dual;

HM
-------------------
+00 04:20:00.000000
 


Of course the interesting property of Oracle's native time intervals lies in the fact that you can perform arithmetic operations with lesser effort than you would otherwise spend if you were doing it on your own (the famous wheel reinvented with square corners usually), so for instance, adding or subtracting two intervals is just a matter of performing '+' or '-' between two interval values.

In a perfect world, given these assumptions, it would mean that the built-in SUM aggregate function applied on interval values would return the total interval calculated on a bunch of rows, but in case you didn't notice up until now, we don't live in a perfect world, so how can we work around this?

It turns out that Oracle provides all the information necessary to create a user-defined aggregate function that with tons of imagination we are going to call SUMINTERVAL.
Below you can find the complete code for creating the function, which is very similar to the example given in the documentation and possibly more useful.


create or replace type SumIntervalImpl as object
(
  total INTERVAL DAY TO SECOND, -- store total interval
  static function ODCIAggregateInitialize(sctx IN OUT SumIntervalImpl) 
    return NUMBER,
  member function ODCIAggregateIterate(self IN OUT SumIntervalImpl, 
    value IN INTERVAL DAY TO SECOND) return NUMBER,
  member function ODCIAggregateTerminate(self IN SumIntervalImpl, 
    returnValue OUT INTERVAL DAY TO SECOND, flags IN NUMBER) return NUMBER,
  member function ODCIAggregateMerge(self IN OUT SumIntervalImpl, 
    ctx2 IN SumIntervalImpl) return NUMBER
);
/

create or replace type body SumIntervalImpl is 
  static function ODCIAggregateInitialize(
    sctx IN OUT SumIntervalImpl) 
    return NUMBER is 
  begin
    sctx := SumIntervalImpl(NUMTODSINTERVAL(0,'SECOND'));
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(
    self IN OUT SumIntervalImpl
  , value IN INTERVAL DAY TO SECOND) 
  return NUMBER is
  begin
    self.total := self.total + value;
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateTerminate(
      self IN SumIntervalImpl, 
      returnValue OUT INTERVAL DAY TO SECOND
    , flags IN NUMBER) 
      return NUMBER is
  begin
    returnValue := self.total;
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge(
      self IN OUT SumIntervalImpl
    , ctx2 IN SumIntervalImpl) 
      return NUMBER is
  begin
    self.total := self.total + ctx2.total;
    return ODCIConst.Success;
  end;
end;
/

create or replace function SumInterval (
  input INTERVAL DAY TO SECOND) 
  RETURN INTERVAL DAY TO SECOND 
 PARALLEL_ENABLE AGGREGATE USING SumIntervalImpl;
 
 
-- let's run our aggregate function for the first time 
select suminterval(hm) as total from (
  select NUMTODSINTERVAL(4,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
    from dual
  union
  select NUMTODSINTERVAL(3,'HOUR') + NUMTODSINTERVAL(50,'MINUTE') as hm
    from dual
);
TOTAL
-----------
0 8:10:0.0


Once I get the total labor in terms of days, hours and minute, I can easily pull out the single bits one at a time and do whatever I need to do. Indeed the function to accomplish this is called EXTRACT.

select extract(DAY from total) dt
     , extract(HOUR from total) ht
     , extract(MINUTE from total) mt 
from (
  select suminterval(hm) as total from (
    select NUMTODSINTERVAL(14,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
      from dual
    union
    select NUMTODSINTERVAL(13,'HOUR') + NUMTODSINTERVAL(50,'MINUTE') as hm
      from dual
  )
);

        DT         HT         MT
---------- ---------- ----------
         1          4         10


Now with plain integers to work with, it becomes really trivial to perform any further calculations if you prefer to represent the total labor as pure minutes or hours.

Everything ok?
Almost.

Interval datatypes come with variable precision but by default a datatype declared as follows allows for 2 digits in the DAY field and 6 digits in the SECONDS field.
While the number of seconds in the context of our aggregate function hold little importance, the number of digits for the days can quickly become insufficient if we process enough values.
Theoretically one can increase the precision up to 9 digits, but interestingly enough it seems there is no way to compile the various objects with non-default precision indicators and it's a glorious mistery why Oracle decided to set 2 as the default instead of 9 (the maximum allowed value).

When you hit this problem, you'll get the following error message:

ORA-01873: the leading precision of the interval is too small

So, how do we remedy to this further annoyance?
The best solution I could come up with was to use varchar2 strings instead of intervals and perform the conversions inside the code, which led me to modify the code above as follows:


create or replace type SumIntervalImpl as object
(
  total INTERVAL DAY(9) TO SECOND, -- store total interval
  static function ODCIAggregateInitialize(sctx IN OUT SumIntervalImpl) 
    return NUMBER,
  member function ODCIAggregateIterate(self IN OUT SumIntervalImpl, 
    value IN VARCHAR2) return NUMBER,
  member function ODCIAggregateTerminate(self IN SumIntervalImpl, 
    returnValue OUT VARCHAR2, flags IN NUMBER) return NUMBER,
  member function ODCIAggregateMerge(self IN OUT SumIntervalImpl, 
    ctx2 IN SumIntervalImpl) return NUMBER
);
/

create or replace type body SumIntervalImpl is 
  static function ODCIAggregateInitialize(
    sctx IN OUT SumIntervalImpl) 
    return NUMBER is 
  begin
    sctx := SumIntervalImpl('+000000000 00:00:00.000000000');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(
    self IN OUT SumIntervalImpl
  , value IN VARCHAR2) 
  return NUMBER is
  begin
    self.total := self.total + to_dsinterval(value);
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateTerminate(
      self IN SumIntervalImpl, 
      returnValue OUT VARCHAR2
    , flags IN NUMBER) 
      return NUMBER is
  begin
    returnValue := to_char(self.total);
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge(
      self IN OUT SumIntervalImpl
    , ctx2 IN SumIntervalImpl) 
      return NUMBER is
  begin
    self.total := self.total + to_dsinterval(ctx2.total);
    return ODCIConst.Success;
  end;
end;
/

create or replace FUNCTION SumInterval (input VARCHAR2) RETURN VARCHAR2 
PARALLEL_ENABLE AGGREGATE USING SumIntervalImpl; 

Now verify that the function can handle large figures.

select extract(DAY from total) dt
     , extract(HOUR from total) ht
     , extract(MINUTE from total) mt from (
 select to_dsinterval(suminterval(hm)) as total from (
   select NUMTODSINTERVAL(36000,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
     from dual
    union all
   select NUMTODSINTERVAL(36000,'HOUR') + NUMTODSINTERVAL(50,'MINUTE') as hm
     from dual
    union all
   select NUMTODSINTERVAL(36000,'HOUR') + NUMTODSINTERVAL(20,'MINUTE') as hm
     from dual
   )
 );

        DT         HT         MT
---------- ---------- ----------
      4500          1         30 


In conclusion, now I can easily compute total labor costs expressed as a function of days, hours and minutes, using plain SQL in my Apex reports without worrying about precision overflow.

See message translations for ORA-01873 and search additional resources.

Thursday, April 10, 2014

Oracle Application Express (APEX) 4.2.5 is now available for download

A new minor release of Oracle Application Express (APEX) has been announced yesterday.
Version 4.2.5.00.08 is now available for download.

Check out the accompanying documentation for new features and bugs fixed in this release.

You may also find interesting reading Joel's blog posting about Apex 4.2.5.

Thursday, April 03, 2014

On HTTP 401 Unauthorized (with Oracle EPG)

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It could happen that suddenly your Apex application that has been working for years starts asking for a username and password in order to access the XDB repository.
You hit the Cancel button and all you get is:
 
401 Unauthorized

No html, no images, nothing is returned, your app is blank.
What the hell happened with it?

How could this happen if I didn't change anything in a long time?
Where do I start looking first?

First of all, we need to assess what's gone awry.
If you are a Firefox user, Firebug comes in handy with its panels: check out the "Net" panel and see which component is giving troubles.

In a previous posting, I had problems with static files and images in the repository, but not with the main Apex component, the f function that is processing and returning the main HTML page.

If the page is just "garbled" and some components are missing, the problems are likely caused by permissions set at the XDB resource level (see the previous posting about this).

If HTTP 401 is returned by the main page itself instead, either there is problem with the XDB configuration file or what else?

In the former case, you should compare the current xdbconfig.xml with a working backup copy (some time ago I wrote a posting also about recovering a corrupted xdbconfig.xml, check it out).
It's absolutely necessary to keep backup copies of working configurations that will save your time in case of troubles like these, allowing you to make comparisons or quickly restore them if anything went wrong and it's much better than guessing its content without knowing if a certain option was set or unset when the system was running just fine.

Now, supposing the xdbconfig.xml is ok, still the same as before, where else should I turn my attention to?
Luckily enough, my sixth sense told me to look next at the database user status.
OK, great, but which one?

Depending on your configuration you may have a couple of Apex database users that have been created over time:
ANONYMOUS and APEX_PUBLIC_USER.
The good one is returned by the following query if the database is XE with EPG (Embedded PL/SQL Gateway) and the DAD is named APEX:

-- DBA user required
select dbms_epg.get_dad_attribute('APEX','database-username') d from dual;
 
D
--------- 
ANONYMOUS


The next step is to check what's the status of user ANONYMOUS.

select account_status, lock_date, expiry_date
  from dba_users
where username = 'ANONYMOUS';
 

ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE       
-------------------------------- ------------------- -------------------
LOCKED                           01-04-2014 18:37:26                    


Here is the answer. A locked account will make your Apex site look miserable in no time.
Just unlock the account ALTER USER ANONYMOUS ACCOUNT UNLOCK and everything will resume working immediately.
Thereafter you just need to find what or who made that account become locked suddenly, but that's another story.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles