OTN Appreciation Day: Analytic Functions

This is my contribution to the OTN Appreciation Day, which was initiated by Tim Hall.

One of my favorite features of the Oracle Database are Analytic Functions. They were introduced with Oracle Database 8.1.6 Enterprise Edition, and have been in the Standard Edition since version 9.

With analytic functions you can add inter-row calculations, aggegrate over multiple dimensions, rank assignments based on values. All this without a GROUP BY clause.
The syntax might (more...)

Iconic Breadcrumbs in APEX

Normally when you use a Breadcrumb in an APEX application you enter the data and that's it. The breadcrumb will look something like this:

Or when you reduce the screen, the Breadcrumb will look slightly different:

It's trivial to replace the text that you enter in the Breadcrumb with a little bit of markup and show a Font Awesome (or with APEX 5.1 Font APEX) icon.


<span class="fa fa-home"></span>
Now the text will be (more...)

Celebrate Batman Day with SQL

Because today is Batman Day, it calls for a celebration of the SQL type:

SQL> select listagg (b)
2 within group (order by null) ||' Batman'
3 from (select 0f/0 b
4 from dual
5 connect by level <= 14
6 );

LISTAGG(B)WITHINGROUP(ORDERBYNULL)||'BATMAN'
--------------------------------------------------------------------------------
NanNanNanNanNanNanNanNanNanNanNanNanNanNan Batman

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

Temporal validity, multiple end dates

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)

APEX 5.1: features shown at DOAG

On the last day of the DOAG conference, in the last time slot Patrick Wolf from the APEX development team did a session on the next release of APEX.
For the developer the most significant change in the Page Designer is the component view. This will be a tab in the center pane instead of a completely separate page.

Most time was spent showing the new Interactive Grid. The Interactive Grid will be a, loosly (more...)

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Indentity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );

Table created.

(more...)

Busy October

October is going to be a busy month, and it already started.
The First one is the Slovenian User Group immediately followed by the Kroatian User Group.

For the Sloveninan User Group (SIOUG) I will do the following presentations:

    • Oracle 12c for Developers
      Analytic Functions: Unleash the Power
      APEX, Node.js and HTML5: Magic!
  • On top of that I was asked to join the "Ask the Experts" panel on APEX.

    In Kroatia (for the HrOUG) (more...)

    Rounding Amounts, the missing cent: with the MODEL clause

    Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
    Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

    To create an example, first let's create a table with only three records in it.


    SQL> create table t
    2 as
    3 select rownum + 42 id
    4 from dual
    5 connect (more...)

    Rounding Amounts, the missing cent

    Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
    Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
    Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
    To solve this cent-problem, we decide that the (more...)

    Object Type with Optional Attribute: Extra Constructor Function

    When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
    The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
    If you are unfamiliair with this technique, there are some links at the (more...)

    Conditional Compilation and Static Boolean

    One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
    This post is not about Logger, but some dealings with Conditional Compilation.

    With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
    The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)

    Deadlock with a Virtual Column

    Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
    In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
    While using the Virtual Columns, we ran into a little oddity with them.

    First of all let's start with the version of the database that I tested this on. Yes, I know (more...)

    LoggerUtil: Create a Custom Template

    Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
    If you haven't read that blog about my pet project, here's the synopsis:

    I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)

    Splitting a comma delimited string the RegExp way, Part Three

    The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
    On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
    Links to both articles are included at the bottom of this article.
    It seems like there is a need for functionality like that frequently. And just (more...)

    Refresh Multiple Materialized Views in One Go: No Data Found

    To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
    When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).

    The procedure that I initially wrote was the following:

    create (more...)

    APEX 5: forgot the images?

    On my play environment I usually use Oracle APEX with the Embedded PL/SQL Gateway, just because it's so easy.
    When a new version of APEX is released, just like everybody else, I upgrade my play environment.
    After the apexins.sql script is run, I always want to start playing with it immediately. Usually it is at this point where I just see a blank page... scratching my head wondering why it is not running,... having (more...)

    Speed Up Development with Logger: Generate a Template

    Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger.
    Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster.
    What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments.
    (more...)