A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL. A lot of discussion followed which I'm not going to repeat. Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.
In a database table the activity, start time and the number of repetitions is stored, but for the report this needs to be expanded. The number of repetitions dictates the number of rows per activity, each incremented by five minutes from the start time.
To get things going a simplified table is created, as well as some sample data.
create table activities (activity varchar2(10) ,activity_date date ,no_of_reps number );
Having a Page Alias shown on the page can make communication with your end users a little bit easier. Instead of directing them to the URL and asking them for the second value shown after the "f?p", it is a little bit simpler to direct them to the location of the Page Alias, e.g. bottom left corner of your screen. For my projects I tend to use the Page Alias as a link between my APEX front-end and my database code back-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 a single column.
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".
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.
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 use Temporal Validity to get the correct rows?"
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.
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.
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 difference should be added (or subtracted) on the last row.
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 bottom of this article.
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.
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 to add to those two articles on the subject, here is a third one combining the first two articles.
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).
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.
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.
Recently someone asked me: "How did you do that? When I include an APEX page in a Page Group, the correct tab is automatically highlighted" When I setup an application, I usually use Dimitri Gielis' method, so instead of using "real tabs", I use a List and display that list as Tabs.
One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!
In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that blog by following this link.
Almost two years ago, I wrote a little blog on how to trigger a Dynamic Action from a report. You can find that blog right here. Things have changed with APEX5 (which is currently still in "early adopter 2") which allow you to do this in a more clean way (or at least I think so). No need to create a "fake link" so the user know that the icon is clickable. No need to upload your own images, use the already shipped Font Awesome library.
A common requirement is to show a Master-Detail Report where both the Master as well as the Detail Report show multiple records. When you click on one of the Master records, the connected detail records are shown as well. This is very easy to accomplish with a Hidden item and a Dynamic Action. First the Master Report, for this example I am using the DEPT table:
APEX 5 is currently in Early Adaptor 2, so the exact implementation of this blogpost might change when APEX 5 goes GA. Font Awesome is standard included with APEX 5 and you can use the icons on buttons, there is a special property for that. I was playing around the other day and I wanted to include some Font Awesome icons in my report. First create a report (classic or interactive, whatever you want) and use the following query:
Last week, at the yearly ODTUG Kscope Conference, I did my presentation "Getting Started with APEX Plugin Development". After the session Patrick Wolf, Principal Member of Technical Staff for APEX, pointed out an improvement that could be made. In the presentation I point out the need to escape the input that you get from a user of the plugin in order to protect the plugin from unwanted use, like SQL Injection, Cross Site Scripting and the like.
The morning started nice, ODTUG organized breakfast with country themes. There were tables for Australia, Canada and The Netherlands. If you are Dutch you might have expected to have hagelslag or cheese, this was not the case. It was a nice and hearty American style breakfast, eggs, bacon, potatoes. The first session of the day that I went to was by Nathan Catlow on Oracle APEX Security, an interesting topic.
Mark Drake, the product manager for XMLDB and the new JSON features in the database, started Tuesday with a session on flexible storage. After a short history of the XMLDB ("more than just a LOB Store"), he went to the heart of the presentation: flexible storage.
Monday morning started with the opening keynote. At the same time the Dutch soccer team played Chile (which the Dutch won with 2-0). A small Dutch delegation went to watch the game, all dressed in orange, I was one of them. During the keynote the location of Kscope 2015 was announced: Hollywood, Florida. Lunch was special, there were lunch and learn. There were "reserved" tables were you could sit on and talk tech with ACE Directors. We had some nice discussions at the table that I sat on, including the soccer game.
The ODTUG Kscope conference always starts with a full day symposium. There are several specialized tracks going on, and I attended the APEX track (mainly). The room was packed, I would estimate around a 175 delegates. Joel Kallman started the day with an overview of the history of APEX, including a video of Steve Balmer being very enthousiastic about APEX. He also told a bit about the background and how some features evolved throughout the years, like Themes and lessons learned with the packaged applications.
Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.
A few weeks ago Packt Publishing asked me if I would give a book review for the recently released Oracle APEX Cookbook, the second edition by Marcel van der Plas and Michel van Zoest. In exchange I would get the e-book for free, so this can be considered a sponsored blogpost. Even though it is sponsored I will give my honest opinion about it.
On one of the pages there was a need to refresh a report region when a node in an APEX Tree was clicked. As far as I know there is no built-in method to create a Dynamic Action to a node in an APEX Tree, so this might call for a Custom Event. If there is a built-in method, please leave a comment :)
Trigger Custom Event
To trigger a Custom Event, change the "link" column in the APEX Tree Query to something like the following:
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.
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. The first that we will need are some Type definitions, starting with the Object Type:
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:
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. In this blogpost I will assume that you created the APEX tree as described in the other two blogposts (the links to these blogpost are at the bottom).
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.
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 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.