Wednesday, March 29, 2017
Posted by Dimitri Gielis at 15:00
Thursday, February 16, 2017
When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.
Here’s an example:
Your SQL statement probably looks like this:
select CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1, CUST_CITY, CUST_STATE, CUST_POSTAL_CODE, CUST_EMAIL, CREDIT_LIMIT from DEMO_CUSTOMERS where CUSTOMER_ID = :P4_SEARCH
When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.
There’re a couple of options you have, I’ll list three below:
where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
where customer_id in to_number(( select regexp_substr(:P4_SEARCH,'[^,]+', 1, level) from dual connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null ))
where customer_id in (select * from table(apex_string.split(:P4_SEARCH,',')))
Which one to choose? It depends what you need… if you need readability, maybe you find APEX_STRING or INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).
The Explain Plan for the first SQL looks like this:
The Explain Plan for the last SQL looks like this:
The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:
The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:
where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0
Happy searching your Classic Report :)
Update 23-FEB after feedback from Martin and FaTechs.:
There are many other alternatives, but I do want to highlight one more if you’re using APEX 5.1 there’s a nice package called apex_string which can help too.
where customer_id in (select * from table(apex_string.split(:P4_SEARCH,',')))
Posted by Dimitri Gielis at 23:30
Monday, February 13, 2017
When reviewing Oracle APEX applications I often see hardcoded date or timestamp formats.
You can define your date formats in multiple places in your application. In your item or column attributes, as part of your code e.g.TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI’) or if you want to make it more reusable you might create a substitution string. That will all work, but you can make your life easier and for the ones looking or needing to maintain your code…
APEX itself provides in the Globalization attributes (Shared Components) a place where you can define your default date and format masks for your entire applications. I consider this a best practice to use those fields, as it’s defined in one logical place, so when you need to change your format, you do it once and you’re done. In your custom SQL and PL/SQL code you can also reference those format masks by predefined substitution strings:
e.g. TO_CHAR(sysdate, :APP_NLS_DATE_FORMAT)
Here’s a screenshot which shows which substitution string corresponds with which field:
You can define the format mask you want, or you can click the arrow to see most used format masks represented with an example. To make it a bit easier, I put the format mask (in red) next to it, so you see the underlying format mask more easily:
If you need to make the format mask dynamic, for example using different format masks for different language, APEX doesn’t allow you to translate that substitution string through Text Messages, but you can work around it by using your own substitution string and have that dynamically filled. In the Globalization Attributes you would add instead of a hardcoded format mask your own substitution string e.g. &MY_TRANSLATED_DATE.FORMAT.
Posted by Dimitri Gielis at 17:32
Tuesday, February 07, 2017
Today I got the question how to change the label of an item in Oracle Application Express (APEX) based on some condition. I actually had this requirement myself a couple of times, so maybe other people too.
Here’s an example; whenever we change the Source item, we want the Affected Item to change it’s label:
The first thing that comes to mind (if you already know a little bit of APEX); lets use a Dynamic Action: on change of the Source item we will fire (in this example we will only fire when the value is A):
Now which action should we use when the dynamic action fires?
Set Value will typically set the value of an Item, but what about the Label?
var newLabel = 'My new label for ' + $v('P2_SOURCE_ITEM'); $('#'+$(this.affectedElements).attr('id')+'_LABEL').html(newLabel);
This will set the label to "My new label for " and then the value of the item, at least if you select in the Affected Elements the item that needs the label change.
Whenever I think about writing custom code, my mind says “you should create a plugin for that”.
So I actually started to write an Oracle APEX Plug-in called “Set Label” (https://github.com/dgielis/orclapex-plugin-set-label)
While I was trying the plugin and writing up the things I needed to do, I guess something happend in my mind. I missed the obvious, it suddenly came to my mind there’s a much simpler solution to this…
You can actually use the Set Value action… just add after your item _LABEL, that’s it.
Here’s the result:
Sometimes developing is much more simple than initially thought, you just have to see it :)
Update 16-FEB: if you want to make your label dynamic, in the Set Values Dynamic Action, you can specify SQL / PL/SQL:
Posted by Dimitri Gielis at 23:37
Tuesday, January 10, 2017
I’m trying to write this blog post with classeur.io.
Just like Martin I’m also searching for alternative ways to write blog posts. I don’t want to completely migrate my blog to a new platform, so I’m searching for a way to write in Markdown and deploy to Blogger.
Since our development of APEX Office Print (AOP) we also use Markdown for our documentation as it makes including code samples easier, it can be version controlled and overall it’s pleasant to write in. Whenever we deploy a new version, we publish the markdown as HTML. For example you see the result of our documentation here. SSjj… we like Markdown so much that we are even looking into supporting Markdown to write your template in AOP, next to Word, Excel and Powerpoint, but more on that in the February timeframe. :)
So I’m giving classeur.io a try, it’s just another Markdown editor, but it can publish directly to Blogger. It allows me to include code samples like this:
declare l varchar2(100); begin l := 'hello world'; sys.htp.p(l); end;
Or if I want to reference somebody I can use a quote:
Oracle Application Express (APEX) changed my life. – Dimitri Gielis
And a list … for example the top 3 reasons I’m looking at a different way to blog:
- Faster to write a post
- Reuse my writings in different ways
- Easier to share code
So this post is really to try the different options of classeur.io which should show up in Blogger after I hit the publish button.
Here’s an image which I plan to use in my next post:
If you see this and the post looks ok, my test went well :)
Posted by Dimitri Gielis at 01:18
Saturday, December 17, 2016
This means that you can start developing your apps in APEX 5.1 from now on. Unlike the early adopter releases (apexea.oracle.com) you can develop your apps on apex.oracle.com and later export them and import in your own environment once the on-premise version of APEX 5.1 is available.
APEX 5.1 is again a major update behind the scenes. The page processing is completely different from before; where previously full page reloads were done, now there's much more lightweight traffic and only necessary data is send across.
The big features in this new release are the introduction of Interactive Grids, which is both a successor for Interactive Reports as for Tabular Forms. The other big feature is the integration of Oracle JET, which you see mostly in the data visualisation (charts) part of APEX, but more components will probably follow in future versions. Although those two features addresses the most common issues we previously had (outdated tabular forms and charts), APEX 5.1 brings much more than that. Equally important for me are the "smaller" improvements which makes us even more productive. Below you find some examples...
When creating a new application, the login page is immediately a great looking page:
Previously in APEX 5.0 you had to adapt the login page, see my blog post Pimping the Login Page.
APEX 5.1 has now a template option to display the Pre and Post text as a Block:
Or when you want an icon inside your item, there's an Icon CSS Class option selector which shows the gorgeous looking new handcrafted Font APEX icons:
You could do all the item customisations above in APEX 4.2 or 5.0 too, but it would require custom css and some code, whereas now it's declarative in APEX 5.1.
And there's so much more; ability to switch style by user, new packaged apps, warn on unsaved changes, no reload page on submit etc. features that haven't been talked about much yet, but which before you had to do with a plugin or a lot of custom code and now it's just there.
So those "smaller" features are actually not so small, they are an enormous timesaver and bring your apps in warp-speed to modern and great looking applications.
In the next blog posts I'll go in more detail on some specific features that will gain you at least an hour a day, but in the meantime, embrace APEX 5.1 and start earning those extra hours :)
Update: on December 21th APEX 5.1 was made available to download on OTN.
Posted by Dimitri Gielis at 12:19
Tuesday, October 11, 2016
If you're following some Oracle blogs or Twitter, you'll see many blog posts starting with "OTN Appreciation Day :
So I just have to write about a feature of the Oracle Database; but which one to pick? The way Oracle implemented SQL, or the programming language inside the database PL/SQL or the tools and options that make the database awesome?... I thought some time about it and for me personally next to the database itself, it was really APEX that changed my life, so I just have to write about it.
In this post I want to share why I love Oracle Application Express (APEX) and why I consider this the best feature of the Oracle Database *ever*.
The goal, I believe, of a database is to capture data and do something with it; either to get insight in your data or share it again in different formats with others... and Oracle Application Express is just the easiest way to do this! In no time you create a web application with some forms that capture data directly in your database. And in even less time you share and get insight in your data through beautiful reports and charts. You just need a browser... it's secure, fast, scalable and you can use the full power and features of the database - APEX is the window to your data!