04 December 2013

Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he discussed the possibility to have invisible columns in a table, it triggered the question: What happens when a NOT NULL column is made invisible? To see what would happen, I set up a small test case on my Oracle 12c database.
SQL> select banner
  2    from v$version
  3  /

Oracle Database 12c Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production
First create a table, two columns - both NOT NULL, and some data.
SQL> create table t
  2  (id   number not null
  3  ,text varchar2(10) not null
  4  )
  5  /

Table created.

SQL> insert into t values (1, 'Hello')
  2  /

1 row created.

SQL> insert into t values (2, 'World')
  2  /

1 row created.

SQL> commit;

Commit complete.
Next to change the TEXT column to be invisible.
SQL> alter table t modify (text invisible)
  2  /

Table altered.

SQL> desc t
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------

SQL> select * from t
  2  /

The data that we entered is still there, or at least the ID values are still there.
So the column is now invisible, what if we try to add new data into it?
SQL> insert into t values (3)
  2  /
insert into t values (3)
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."TEXT")

Even though you can't see the column, the NOT NULL constraint is still enforced.
When you name the column explicitly, you can still do DML on it.
SQL> update t 
  2  set text = upper(text)
  3  /

2 rows updated.
But not like this
SQL> insert into t values (3, 'Goodbye');
insert into t values (3, 'Goodbye')
ERROR at line 1:
ORA-00913: too many values
This following is still valid, and again shows that naming the columns in an INSERT statement is a good practice.
SQL> insert into t (id, text) values (3, 'Goodbye');

1 row created.

20 November 2013

APEX Tree based on Pipelined Table Function

Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve that.
It is also possible to create a Pipelined Table Function which can be used a the basis of the APEX Tree. In this blogpost I will show you how to do that.

18 November 2013


The PIVOT and UNPIVOT operators were introduced in Oracle 11g and the other day I helped out a friend using these operators.
He wanted the data in the columns to be "shifted to the left". To explain a little more, take a look at the following data:
        ID V1    V2    V3    V4    V5
---------- ----- ----- ----- ----- -----
         1 a     b                 e
         2 aaa   bbbb  cccc
         3             a     b     e
         4 a           c           e
         5       b           d
Above is the data as it appeared in the table.
The requirement was to shift the data to the left like the data below:
        ID C1    C2    C3    C4    C5
---------- ----- ----- ----- ----- -----
         1 a     b     e
         2 aaa   bbbb  cccc
         3 a     b     e
         4 a     c     e
         5 b     d

The data in the columns is moved over one or more columns to the left.

15 November 2013

Tree with Checkboxes: Save the Data - JS Array to PL/SQL

In two previous blogposts I described a method to transform a regular APEX Tree component to a Tree component with checkboxes. The second blogpost described a way to retrieve the data from the database using JSON and AJAX and check the appropriate checkboxes. In this last part of the series on Tree with Checkboxes I will describe a method to save the checked values to the database.

25 September 2013

Oracle In-Memory Database

One of the big announcements of Oracle Open World 2013 is the Oracle Database In-Memory option. By simply "flipping the switch" the application will run faster than ever before.
From my understanding flipping the switch is making changes to some (not sure which) initialisation parameters.

Secret Sauce

What is the big deal with Oracle In-Memmory Database? Well, it is fast - really, really fast. DRAM is faster than Flash, and Flash is faster than disks.
Another big deal is the dual format in which the data is stored. The data is stored in a columnar and row fashion, both in memory. This means that analytical type queries can be answered by the columnar storage while other type of queries can be resolved using the row storage. And yes, the optimizer is aware of columnar storage as well.
Oracle takes care of storing the data in both formats, this is done completely transparent. It is simultaneously active and transactionally consistent. The secret sauce is the dual format storage, this is what it really makes it fast.
The columnar format of the data is memory only, the row format is stored in memory and persisted on disk, just as it is nowadays. In order to take full advantage of this you don't need to learn anything else - from a developer point of view.
During DML the affected data is marked as stale (in memory) thereby limiting the overhead. You can pick and choose which tables, partitions, columns you want to have in columnar format.

How does it differ from TimesTen?

How does it difer from TimesTen? Good Question. TimesTen is also an in memory database which has been around for a number of years. TimesTen is a more specialized approach to solve a particular business problem. The Oracle Database In-Memory Option does not tackle a particular problem, it works for the database as a whole.


Oracle Database In-Memory Option press release

17 September 2013

Tree with Checkboxes - Pre-Select Data with JSON

After you have modified the standard APEX tree to a tree with checkboxes (see this blogpost) you will probably need to find a way to load data from the database and show the values previously checked by the user.
In this blogpost I will show you how to load the data from the database using an AJAX-call and check the appropriate checkboxes in the tree.
The method described here retrieves the data uses the APEX_UTIL.JSON_FROM_SQL procedure and an AJAX call. This method can be used for many more things besides populating a Tree with checkboxes :)

22 July 2013

APEX: Tree with Checkboxes

The APEX Tree component is based on jsTree which is a jQuery Tree Plugin. However not all functionality is implemented in APEX. Knowing the component on which it is based, we can find the documentation and enhance the Tree in APEX. One of these enhancements is to have checkboxes in the APEX Tree component. In this blogpost I will show you how to change the default folders to checkboxes. I will assume that you already have an APEX Tree on one of your pages, and that this one needs to be ammended to have checkboxes instead of folders.

19 July 2013

APEX: Tree Based on Multiple Tables

When you have a table with a hierarchical relation it is very easy to create a Tree component in APEX. Simply write the hierarchical query using START WITH and CONNECT BY and you're good to go. When you have a Master-Detail relation between two tables, and you want to show a Tree component you would have to make up the hierarchy yourself in order to use START WITH and CONNECT BY clause. In this blogpost I will show you how to create the query which you can use to create a Tree component with multiple tables.

11 July 2013

Reading the Oracle Documentation the Easy Way

Personally I don't like reading documentation from my computer, I prefer to have a "real" book. So when Oracle 12c came out I wanted to read the New Features guide and the Concepts guide again, but the fact that I would have to read it off my computer screen stopped me from really reading it... Skimming the topics was as best as it got.
Of course I could download the PDF's and read it off my e-reader, but I never got around to that.
Yesterday Jeroen Evers told me an easier way (and thus getting rid of all my lame excuses) to read the documentation. The tip was hiding in plain sight, but I never noticed it.
Navigate to the Oracle Documentation Library (link at the bottom of this post) and choose the guide that you want to read, but choose the HTML version of it.
On the top of the page on the right-hand side there are several options, like PDF, Mobi and epub.

From my iPad I chose epub and the guide will be placed in iBooks, making it a lot easier to read:


All the Oracle Documentation
Oracle 12c Documentation Library

16 February 2013

APEX: Using the Application Date Format Mask

In your APEX application, you can define several default format mask to be used throughout the application, such as the Application Date Format or the Application Date Time Format. This eliminates the need to specify the format mask for each Date Picker item that you use. It helps in keeping the application at least consistent regarding the format mask used.

When you want to do some validation in your forms using the Dates entered by the enduser, you might have a need for the Application Date Format.
So instead of hard coding (using the same format mask as you entered):
to_date (:P1_DATE_PICKER, 'dd-mm-yyyy')
write it using the built-in Application Date Format mask. Problem is.... the built-in for the Application Date Format Mask is not documented. Thanks to a prompt reply by Joel Kallman via Twitter, use the following built-in:

04 January 2013

APEX: Highlight a Record in Report - with Dynamic Action

Yesterday I wrote a blog on how you can highlight a record in a report. That blogpost can be found here. Learco Brizzi posted a comment on a different way of accomplishing the same thing. And there are probably a number of other possibilities to implement the same requirement. In this blogpost I want to show you how to do the same with a Dynamic Action. Why would you want to choose for a Dynamic Action? This is a more efficient way because you don't need to refresh the page or the region.

03 January 2013

APEX: Highlight a Record in Report

Yesterday I read a question which was posted as a reply to an earlier blogpost. The orginal blogpost was about how to make a Report Row clickable, instead of just the Edit column. The question was: "How do I keep the clicked row highlighted so I can keep it as context information?" To see a working example of the requirement, check out this sample page In this blogpost I will provide a step-by-step way of doing just that.