tag:blogger.com,1999:blog-29275570819257136272024-03-01T01:14:37.758-08:00Everything APEXBruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-2927557081925713627.post-33424668148365324142019-04-17T06:31:00.002-07:002019-04-17T06:31:20.326-07:00I've started a new weekly APEX newscastI am really excited to announce that I have started a new weekly newscast called APEX Now.<br />
<br />
The pppodcast presents the latest news and events in the world of Oracle Application Express (APEX).<br />
<br />
You can catch the very first episode at rebrand.ly/apexnow.<br />
<br />
Please feel free to submit any news items or events that you think ought to be highlighted in the newcast. Just send an email to podcast@applinks.co.za.<br />
<br />
To access the show notes for Episode 1 of Apex Now, go to www.applinks.co.za/eposide1.<br />
<br />
Join me each week for APEX Now.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com2tag:blogger.com,1999:blog-2927557081925713627.post-83119618150250558122019-02-03T03:18:00.001-08:002019-02-04T09:38:34.925-08:00My APEX Application Got Hacked : Change Passwords Into Passphrases<br />
<div class="MsoNormal">
While I will discuss APEX specifically later in this
article, the content here really applies to any web based application.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Most of us as developers fall into a false sense of security
when we establish our password policy. I too was guilty of this until I took the
time to look into the subject more thoroughly. <o:p></o:p></div>
<div class="MsoNormal">
Hopefully by the end of this article you will understand the
issues with your current password policy and how it can lead to
vulnerabilities. Remember that behind your application, there is a database
that holds a lot of organisational data as well as client specific information
that you are responsible to protect. <o:p></o:p><br />
<br /></div>
<div class="MsoNormal">
All the time in the news we hear of very large
corporations that have experienced password breaches resulting in critical
customer information being exposed. We are horrified by this and put the blame
squarely on the company that we have trusted with our personal information regardless of <span style="mso-spacerun: yes;"> </span>their privacy policy. If big corporations can
be hacked, then why can’t our simple user facing application be hacked.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
It is a known fact that most users create very bad passwords.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I am not going to insult you by pointing out that the
storage of passwords as a string in the database is a monumentally bad idea.
None of us I hope would ever do this. In a moment we’ll discuss how to do this
in a better way.<o:p></o:p></div>
<div class="MsoNormal">
<br />
Clearly you do need a password policy, but you do need to
think carefully about the components of that policy.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In terms of hacking passwords, I will really be discussing
offline file hacking as opposed to online directly against your application.
Hackers are very intelligent and sophisticated in their methods. The offline
file approach would mostly be the method they would use to access your data. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
An offline file simply means that your user passwords have
been accessed as a file that the hacker can find and use to crack passwords.
We'll look at some real examples of how this is done.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Obviously how you store the passwords in the database is a
key issue. You absolutely must encrypt the passwords as first line of defense.
You need to store the passwords as a hash value.<o:p></o:p></div>
<div class="MsoNormal">
<br />
There are two kinds of hashes – a “one way” <span style="mso-spacerun: yes;"> </span>hash and a “two way” hash.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
A two way hash means that you have some sort of key or
function that allows you to unhash the value stored in the database. Some
organisations use this to enable them to send an <span style="mso-spacerun: yes;"> </span>email to their user if the user has forgotten
their password. This is an unforgivable mistake. <span style="mso-spacerun: yes;"> </span><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
A one <span style="mso-spacerun: yes;"> </span>way hash means
that you can never your self<span style="mso-spacerun: yes;"> </span>access the
value in the database. The hash is created in the<span style="mso-spacerun: yes;"> </span>value of the hash can only be accessed by the
end user. You do this <span style="mso-spacerun: yes;"> </span>by hashing values
that only the user knows. One way of doing this is to hash the combination of
the username and the password <span style="mso-spacerun: yes;"> </span><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
So the point is here that you will always want to use a one
way hash to ensure that it cannot be unhashed by any database administrator or
developer. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Now that we have a good method to store the passwords in the
database we can move on to looking at our password policies.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
To assess the real strength of the passwords against an
attack, I will use the following site:<o:p></o:p></div>
<div class="MsoNormal">
<br />
http://www.howsecureis my password.net<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
These sorts of sites are not really accurate but it is at least a place to start.<br />
<br />
First let’s consider the situation where we have no password
policy at all. I’ll use the typical password below:<o:p></o:p></div>
<div class="MsoNormal">
<br />
Password1<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
When we type this in to the password analysis on the site,
this password can be cracked instantly. This is absolutely insecure.<o:p></o:p></div>
<div class="MsoNormal">
<br />
<br />
Now what if we require the user to have at least one number
and a special character. My password now will be:<o:p></o:p></div>
<div class="MsoNormal">
<br />
p@word1<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
When we type this in we can see that this password can be
cracked in only 2 seconds. Clearly we still have a huge risk.<o:p></o:p></div>
<div class="MsoNormal">
<br />
Well we can changed our policy to require a length of 7 <span style="mso-spacerun: yes;"> </span>and two numbers and one uppercase letter and
one special character. Here we go:<o:p></o:p></div>
<div class="MsoNormal">
<br />
Or@cl3!<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Probably looks familiar doesn’t it? A much more complex
policy has been used here. When we type this into the site we see that it can
be cracked in 7 minutes.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You might be very surprised that the complexity is not so
important as is the length of the password. This time my password will only
have lower case letters and nothing else. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Here we go:<o:p></o:p></div>
<div class="MsoNormal">
<br />
iliketodrinkwineatlunch<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
A pretty simple password,right? Type it into the site and
look at the result. This password will take 277 trillion years to crack. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
So with an 11 or more length requirement really protects us
much more and the vulnerability is significantly reduced.</div>
<div class="MsoNormal">
<br />
Now it is time to see the really scary stuff.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I have installed a program called Hashcat that is a popular
cracking program among hackers. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I will show you how a malicious hacker might actually crack
your passwords.<o:p></o:p></div>
<div class="MsoNormal">
<br />
I am going to assume that a list of our hashed passwords has
been exposed and the hacker got a hold of this file.This could happen in a lot
of ways such as sql injection against your application<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The password file I am using contains around 6,500 md5 hashed
passwords. You can create your own file by using one of the many online hash
generators.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
We will try and crack passwords using a number of different
attacks.Be aware that I am doing this on my PC with only one GPU. Hashcat uses
the GPUss to process. This should make things even a bit more scary.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The first attack we will perform is called a Brute Force
attack. It is probably the simplest of all attacks and will deal with passwords
that have letters and numbers and are of a length around eight digits. <o:p></o:p></div>
<div class="MsoNormal">
<br />
The attack starts looping through the hashes by first
guessing something like “aaaaaa: and then “aaaaaaab: and so on. It will iterate
all of the possible combinations and compare them to the hashed password.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
We call hashcat and give it the file.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<o:p><br /></o:p></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiu_aq2QwUMrnUCtA7kcpqIlmnITkNI0-AcaxNfhxLdUjFj4grP-qdaG1iMWuTos80VzS2-M8Sb-TrLd3lULAjrnoxnYyZL_g0Go6prrBkmLjMMFjzSV_um3s6pXvwOQfgooqN2sLUQXnY/s1600/firstattack_brut.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="535" data-original-width="1106" height="307" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiu_aq2QwUMrnUCtA7kcpqIlmnITkNI0-AcaxNfhxLdUjFj4grP-qdaG1iMWuTos80VzS2-M8Sb-TrLd3lULAjrnoxnYyZL_g0Go6prrBkmLjMMFjzSV_um3s6pXvwOQfgooqN2sLUQXnY/s640/firstattack_brut.jpg" width="640" /></a></div>
<o:p><br /></o:p></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;"><v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f">
<v:stroke joinstyle="miter">
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0">
<v:f eqn="sum @0 1 0">
<v:f eqn="sum 0 0 @1">
<v:f eqn="prod @2 1 2">
<v:f eqn="prod @3 21600 pixelWidth">
<v:f eqn="prod @3 21600 pixelHeight">
<v:f eqn="sum @0 0 1">
<v:f eqn="prod @6 1 2">
<v:f eqn="prod @7 21600 pixelWidth">
<v:f eqn="sum @8 21600 0">
<v:f eqn="prod @7 21600 pixelHeight">
<v:f eqn="sum @10 21600 0">
</v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas>
<v:path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f">
<o:lock aspectratio="t" v:ext="edit">
</o:lock></v:path></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_i1028" style="height: 218.15pt; mso-wrap-style: square; visibility: visible; width: 451.3pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/bruce/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png">
</v:imagedata></v:shape></span><o:p></o:p></div>
<div class="MsoNormal">
It recovered 1020 hashed passwords at a rate of 139 million
hashes attempts <span style="mso-spacerun: yes;"> </span>per second.<o:p></o:p></div>
<div class="MsoNormal">
Here is an example of the passwords it recovered:<o:p></o:p><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_q3vQ_ShYZb3wq4qJltPXkOxgEhZD75eYM_YkNhT7Jg0XgH1IHgSxlRze6f027ELo8GfLW7jibYwA425PO4pgIW8s55sYZ-LqkmEW9H2w5DAPPYBI5UU3AmHWG2BNtw8suFRpTHYockA/s1600/firstattack_pwd_example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="662" data-original-width="627" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_q3vQ_ShYZb3wq4qJltPXkOxgEhZD75eYM_YkNhT7Jg0XgH1IHgSxlRze6f027ELo8GfLW7jibYwA425PO4pgIW8s55sYZ-LqkmEW9H2w5DAPPYBI5UU3AmHWG2BNtw8suFRpTHYockA/s320/firstattack_pwd_example.jpg" width="303" /></a></div>
<br />
<br /></div>
<div class="MsoNormal">
The cracked passwords appear after the “:” on each line. You
can see that the passwords are not very long nor complex. But this is likely
the first attack type a hacker will attempt. The attack completed very quickly and you can see that we matched and cracked a fairly large number of the hashed
passwords.<br />
<br />
The attack results were pretty good but we still have a ways to go.</div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Now using the same file we will use a dictionary attack.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
A dictionary attack is much more sophisticated than the Brute Force attack<br />
<br /></div>
<div class="MsoNormal">
A dictionary may be a list or words in the English language
or it may be a list of passwords that have already been cracked through previous
breeches. Since hashes always are the same for a specific value, we can compare the hashes to previously <span style="mso-spacerun: yes;"> </span>cracked passwords against our
current hash file. These lists of previously cracked passwords come from other
very large hacks of large organisation. It is unlikely that a user’s password,
or part of it, has been used by another user for another system.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;">But we don’t want to just use
the dictionary. We want to also apply rules to the dictionary values. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;">For instance, we can toggle
between lowercase and uppercase values. Or try using symbols to replace
letters. Now the possible combinations greatly increases.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;">I am using a dictionary of
about 12 million words and a rule set of a lot of different rules and
combinations. Basically the attack tests each hash by comparing it to the
dictionary and then applies the rules . It has a fair bit of work to do so it will take a bit longer.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;">Let’s run the dictionary
attack against our hashed password file :<span style="mso-tab-count: 1;"> </span> <o:p></o:p></span><br />
<span style="mso-no-proof: yes;"><span style="mso-tab-count: 1;"><br /></span></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiccMUAKGYXMwccHnReeID-hq-aukXFziFrXJb9jh5RISmXYGuRIwWIr3QdAFiBQ_kCGn679MRBDKNmgvbs0MXBDLClmjvOpVLtVxQDZHIgdyhl6XsG7djChxh4eoS4aDcKI61WIFKjB_M/s1600/secondattack_dict.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="509" data-original-width="1279" height="252" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiccMUAKGYXMwccHnReeID-hq-aukXFziFrXJb9jh5RISmXYGuRIwWIr3QdAFiBQ_kCGn679MRBDKNmgvbs0MXBDLClmjvOpVLtVxQDZHIgdyhl6XsG7djChxh4eoS4aDcKI61WIFKjB_M/s640/secondattack_dict.jpg" width="640" /></a></div>
<span style="mso-no-proof: yes;"><span style="mso-tab-count: 1;"><br /></span></span></div>
<div class="MsoNormal">
This time it recovered another 2,299 hashed at a rate of 20
million hash attempts per second.</div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Here is an example of the passwords it cracked this time.<span style="mso-no-proof: yes;"> <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;"><v:shape id="Picture_x0020_3" o:spid="_x0000_i1025" style="height: 265.7pt; mso-wrap-style: square; visibility: visible; width: 303pt;" type="#_x0000_t75">
<v:imagedata o:title="" src="file:///C:/Users/bruce/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png">
</v:imagedata></v:shape></span><o:p></o:p></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkx4VxitKsq9cOS2WPj2Y28OcYvHzyRjslgsqh0W1mLOxp_OtSZYMPlV69LwdrTBKYnKfQ5vA_sVJHURgFlvKDWoNxQpv4zQNRzsHDqwXtGCK72Cid6hsSCjJ6sHwQS0OBWu6W7M6sbS0/s1600/secondattack_pwds.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="721" data-original-width="823" height="280" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkx4VxitKsq9cOS2WPj2Y28OcYvHzyRjslgsqh0W1mLOxp_OtSZYMPlV69LwdrTBKYnKfQ5vA_sVJHURgFlvKDWoNxQpv4zQNRzsHDqwXtGCK72Cid6hsSCjJ6sHwQS0OBWu6W7M6sbS0/s320/secondattack_pwds.jpg" width="320" /></a></div>
<br />
You can see that the passwords are a lot longer and
complicated.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I could keep going and use other dictionaries and rule sets.
In a normal attack the password file may be a lot larger and the passwords more
complicated.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Remember the speeds we are getting are based on only one
GPU.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Scared yet? <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
When you use a crack time estimator like the site we have
been using here, you should know that they are not entirely accurate. They
indeed do give us an indication of how long it might take a hacker to crack our
password, but it can only predict in a general way.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The estimators do not really consider the processing speed
that may be available to the hacker. Hackers use some powerful tools but they
all rely on GPUs or graphic cards. These GPUs are getting more powerful every
day. Usually a hacker will have a machine made up of several GPUs rather than
just one. The number of crack attempts could be up to several billion per
second. The crack time estimators have no way of determining the exact hardware
that a hacker might be using.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Another way that the estimators cannot really assess is the
sophistication of some of the dictionary attacks is they don’t know the hashing algorithm that the developer has used
to store the passwords.<br />
<br />
Generally you should understand that md5 is no longer
acceptable. There have been so many breeches based on md5 hashes, that it has become
very easy to crack them if the dictionary is long enough. It is now best to use
SHA256 or SHA512. Oracle 12c has support for using a SHA256 hash.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Here’s what I mean In the earlier dictionary attack we
recovered the password “energizer000000”. It was recovered in the first 11
minutes of the attack. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Going back to our estimator site and typing in the password
“energizer00000” the result is that it will take 175 thousand years. Not quite
as we have seen.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
If you consider the password “iliketodrinkredwine” it is
made up of the following words:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I like to drink red wine<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The phrase will not truly be that difficult to crack. There
are several words that appear to be standard phrases:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I like<span style="mso-spacerun: yes;"> </span>red wine<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In other words, these words would often follow each other in
the English language. The associated familiarity of the phrase makes the
dictionary attack easier.<o:p></o:p></div>
<div class="MsoNormal">
<br />
But consider the following password:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
drawerbufferturtlepalm<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
It consists of the following individual words:<o:p></o:p></div>
<div class="MsoNormal">
<br />
drawer buffer turtle palm</div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
These are not very common words and there is no real
connection between the words. A dictionary attack is much harder as it would have to
combine all four words in exactly this same order.<o:p></o:p></div>
<div class="MsoNormal">
<br />
Now, let’s at this minor change:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
draw@er!bufferturtlepalm<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
A slight change has been made here. Special characters have
been added to the phrase but note that they are not used to replace characters
but are inserted into the words. The dictionary attack becomes very difficult
as the individual words won’t be found exactly as they appear here.<o:p></o:p></div>
<div class="MsoNormal">
<br />
When you replace letters with special characters rather than
inserting them into the word, you are not really fooling the hacker. Users, for
instance, often replace “a” with @ or an “I” with 1. Hackers know these tricks
and they software they use already evaluates in this way. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
How then do I deal with things in an APEX Application?<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This is exactly the <span style="mso-spacerun: yes;"> </span>same for APEX since it accesses key data from
our Oracle database and uses public database connections to process data.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
When it comes to APEX, the security of passwords really has
to do with the authentication. The authentication we use is a way of
identifying the user and ensuring the password they use is correct.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
There are some preconfigured authentication methods
available to us. One of them is to authenticate the user by reference to LDAP.
This is a good method since as a user joins or leaves an organisation, the LDAP
information is updated. The only issue here is that the user will be able to
access all of your applications with the same credentials. You need to control this.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You can create your own custom authentication and this might
work best for you as it is flexible and can validate the authentication of the
user to address your specific needs including the method by which you encrypt
the password for storage in the database.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Now this might be another surprise. As of Apex 18.1, we have
another authentication method available to us. We can allow the user to be
identified by way of their social media account such as Google or Facebook. In
reality, this is a very secure method. In essence you are turning the issue
over to them. Each of the social media accounts have really strong password
policies and storage. They also, in some instances, use what is called “two factor verification".
This means that they have used two ways to validate the user. Often it may be
an OTP or an SMS with a verification code. Since only the user has this
information, the validation of the user is much more secure. Generally, most
social media accounts store their passwords in quite sophisticated ways and
thus may be a good choice for your application.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In addition to your authentication scheme in APEX, you must
also define an authorisation scheme. APEX does not require this as default, but
you must never overlook the importance of defining an authorisation scheme.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You can think of authorisation in APEX as defining<span style="mso-spacerun: yes;"> </span>responsabilities or roles. Think about it. If
you do not define specific authorisations, any user that is in LDAP account can access all of your APEX applications and view data or transact.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I usually use a custom authentication as it gives me more
control over the password processing as well as an ability to do some fine
grain authorisation.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
But, here is where my approach is somewhat different and is
based on all the information above. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Where I have a “change or reset password page”, I replace
the the placeholder of the password field to “Passphrase”.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The password policy I implement is really quite simple :<br />
<br />
<span style="text-indent: -18pt;">-- I</span><span style="text-indent: -18pt;">t must be at least 14 characters long</span><br />
<span style="text-indent: -18pt;">-- It cannot contain the user's first or last name</span><br />
<span style="text-indent: -18pt;">-- </span><span style="text-indent: -18pt;"><span style="mso-list: Ignore;"><span style="font-family: "times new roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">T</span></span></span><span style="text-indent: -18pt;">he same passphrase cannot be used twice in a
row </span><br />
<span style="text-indent: -18pt;">--</span><span style="text-indent: -18pt;">The passphrase will expire every 30 days.</span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
A
lot of web applications are starting to use the concept of a “PassPhrase”.
User’s need to be educated on what a passphrase is. So on the page I make sure
there is a link that will help the user to understand what it is and how to
select one.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
The
guidance I usually provide is something like this:<br />
<br />
-- <span style="text-indent: -18pt;">Your passphrase should be something that you
can easily remember and type</span><br />
<span style="text-indent: -18pt;">-- </span><span style="text-indent: -18pt;">Choose three or four words </span><br />
<span style="text-indent: -18pt;">-- </span><span style="text-indent: -18pt;">Choose words that are unusual and really don’t belong
together</span><br />
<span style="text-indent: -18pt;">-- </span><span style="text-indent: -18pt;">Y</span><span style="text-indent: -18pt;">ou might want to use your favourite band name or something like that
for one of the words </span><br />
<span style="text-indent: -18pt;">-- </span><span style="text-indent: -18pt;">You can use symbols as part of the words but
insert them rather than replacing letters with them </span><br />
<span style="text-indent: -18pt;">-- </span><span style="text-indent: -18pt;">Here are a few examples but you are not allowed
to use these exact ones:</span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
b <br />
eyelemonsink@level<br />
t<span style="text-indent: -18pt;">ornattackfridgelink</span><br />
<span style="text-indent: -18pt;"><br /></span>
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]--><span style="font-family: "symbol"; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> - </span></span></span><!--[endif]-->Do not use the same phrase for any other
application or web service<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
Next
I maintain a blacklist of words that cannot be used as part of any passphrase.
This list may include such words as:<o:p></o:p><br />
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
Password<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
Apex<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
Login<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
,,,
and so on.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
When
validating a user’s selected pass phrase, I first check that it does not
contain any of the blacklist words, and then make sure that it complies with
the policy.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
Passphrases, if done correctly are very secure and are less prone to any kind of
attack. They are very easy for the user to remember and less likely to be typed
with any errors. It will just take a bit of time to educate the user but in my
experience they really like it and have provided some very positive feedback on
the concept.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
Some
people recommend that users make use of some sort of password manager in to
manage the specific passwords for applications and only require the user to
remember one master password. Users can then create very random strings that
are difficult to crack. This is a pretty good idea but with APEX I do not use a
password manager.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br />
It
is my view that with APEX a password manager makes it very difficult, if not
impossible, to access a APEX Workspace or an Apex application from any other
machine than yours. I often do training and consultation where it is not always
possible to use my own machine. If an APEX application is truly a web
application, you should be able to access it from anywhere and from any
machine. <o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
I
hope that at at the very least this article has got you thinking and
reassessing how you store passwords in the database and how you set a policy
that will be secure now and going forward.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 0cm; mso-add-space: auto;">
<br />
Hackers
get more and more advanced in their techniques all the time. We owe it to our
users and our customers to protect their access security now and tomorrow. Secure
access to your application is a constant process of evaluating your current
measures against the current advances in password cracking across the internet.'<o:p></o:p><br />
<br />
Want to see if you have been hacked?<br />
<br />
Many applications set your email address as the username for your account. And, sadly, a lot of users tend to use the same password on more than one site.<br />
<br />
You can check if your email address has ever been subject to a password crack on any site by going to the following URL:<br />
<br />
https://haveibeenpwned.com/<br />
<br />
Just type in your email addressed and it will show you if any password associated with the email addressed has ever been compromised.</div>
<div class="MsoListParagraphCxSpLast" style="margin-left: 0cm; mso-add-space: auto;">
<br /></div>
Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com3tag:blogger.com,1999:blog-2927557081925713627.post-46340153099190785472019-01-14T23:11:00.000-08:002019-01-24T22:16:07.884-08:00Thanks Patrick! You lit up the Apex Page Designer for meAs some of you might know, but I suspect not many, I am visually impaired. In some jurisdictions I would be considered legally blind by legislation. I have been visually impaired all of my life. I have been an Apex consultant / developer for years. I have developed Apex application, trained Apex and mentored Apex for over 10 years. I am extremely passionate about Apex.<br />
<br />
Very recently Patrick Wolf did a presentation titled "Wow what a Page Designer". You can watch it on Youtube and I have placed the link at the bottom of the post.<br />
<br />
In his presentation, Patrick highlights many features of the Page Designer. In specific, he identifies many of the keyboard shortcuts available to move around the Apex workspace and the Page Designer. Some of the things he discusses m ay already been known to you, but for me many of them were new.<br />
<br />
The keyboard shortcuts are usually framed as a way to increase your productivity. For me they are an essential part of accessibility. I use a number of specific tools on my laptop to help me in other ways, but moving around the Page Designer with a mouse can sometimes be a bit challenging. The keyboard shortcuts and features like the search spotlight, really enable me to do my job in an easier way. I think that the Oracle Apex development team is doing a great job in ensuring that everyone can equally participate both in developing and using Apex applications.<br />
<br />
I am working currently on an article about how to ensure that your Apex application is fully accessible so that all developers can make this a fundamental part of their design and development process.<br />
<br />
Thanks very much Patrick for presenting some of these unique features of the Page Designer. While for some they may be nice to know, for me, they are absolutely critical.<br />
<br />
You can view Patrick's presentation <a href="https://www.youtube.com/watch?v=ehw_sbRynZI" target="_blank">here</a>.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com3tag:blogger.com,1999:blog-2927557081925713627.post-3218006316285609852019-01-13T00:59:00.000-08:002019-01-24T22:36:41.561-08:00An Interview With Joel R. KallmanAs an Apex developer you won't go very far without hearing the name Joel R. Kallman. Joel is the Oracle's Director of Software Development for Application Express. He is a long time contributor and friend of the Apex community and has participated in many conferences and workshops. Best of all, he is always willing to assist developer as a passionate evangelist of Apex.<br />
I recently had the opportunity to pose a number of questions to Joeal and wanted to share his responses with you. Joel, thank you so much for taking the time for this and for sharing some key information.<br />
<br />
Q : For those who may not know, what is your role as it relates to Oracle APEX?<br />
<br />
A: Good question. APEX is something that Michael Hichwa (Vice President, Database Tools) and I started back in 1999. Mike is the inventive and creative one, and I just happened to be employee #1. He architected the first framework, and I concurrently wrote the first application in this framework, proving and pushing the framework. Starting from a blank slate, in 3 months time, we developed and deployed a Web Calendar application for all of Oracle (used by 25K people, at the time). This framework became HTML DB and then ultimately Application Express (APEX), and is used by thousands of customers worldwide.<br />
<br />
Today, I am responsible for both the product development and product management of Oracle APEX. I’m blessed to have a very hard-working, focused, experienced and professional team, and all of us contribute to Oracle APEX.<br />
<br />
Q: What you consider some of the more exciting new features in Apex release 18.1?<br />
<br />
A: A lot of this is presented in our blog post announcement: <a href="https://blogs.oracle.com/apex/announcing-oracle-apex-181">https://blogs.oracle.com/apex/announcing-oracle-apex-181</a>. Probably the two biggest features are Application Features and the enhanced REST support.<br />
<br />
For many years, our team has developed and delivered productivity applications as part of Oracle APEX. Some examples include Group Calendar, Project Tracking, Survey Builder, etc. In the development of this “fleet” of applications, we found<br />
ourselves desiring the same features over and and over again – they were common features that someone would want in any APEX application. So we extended the concept of what you could create from the APEX application builder, and elevated it to higher-level features to be added to your application. It can consist of multiple pages and components which are employee in an app. Some good examples include user access control, activity monitoring, user-selectable UI (theme), and user feedback. Just by checking a box during application creation time, you can include very high-quality, polished application features which we use ourselves. These features can also be added to existing applications.<br />
<br />
APEX 18 is also a dramatic leap forward in the support for remote data sources over REST. APEX has always had support for REST Web services, but in many cases, the support was programmatic. In APEX 18, there is now a completely declarative interface to remote data sources over REST. You can create Web Source Modules (a new shared component type) in APEX 18, and Web Source Modules can be the data source for reports, charts and calendars. Anyone can use this functionality today on the free site <a href="https://apex.oracle.com/">https://apex.oracle.com</a>. I wrote a recent Oracle Magazine article which includes an interesting (and fun!) step-by-step tutorial. <a href="https://blogs.oracle.com/oraclemagazine/beyond-the-database">https://blogs.oracle.com/oraclemagazine/beyond-the-database</a>.<br />
<br />
APEX 18 also “unifies” the REST Services with Oracle REST Data Services (ORDS). There used to be a separate repository for the REST Services that you could create with APEX. ORDS, which is the next generation of REST Services for the database, had a separate repository. In APEX 18, these are now unified, and the REST Services that you can create in the REST Workshop of APEX 18 can also be edited via SQL Developer or even in the ORDS-supplied PL/SQL API<br />
<br />
Q: What has been the feedback from the Apex community on release 18.1?<br />
<br />
A: There has been tremendous uptake of APEX 18 by our customers. It has proven very reliable and stable, and there have been very few upgrade issues from prior versions of APEX. And in many cases, the applications themselves are faster too.<br />
<br />
Q: Release 18.2 followed quickly after 18.1. Is there anything key to release 18.2 that we ought to know about?<br />
<br />
A: The biggest thing you should take note of is the “cadence” we wish to achieve with APEX releases. Gone are the days of these very large, monolithic releases of APEX. Instead, we are opting for a release of APEX every 6 months. There are a number of more modest features that went into APEX 18.2, but it gets us and our customers in the process of this frequent release process. To accommodate this, we also made great strides in APEX 18.2 with the near-zero downtime APEX upgrades.<br />
<br />
Q: Some people were running applications in Apex 5.1 and using the compatibility mode to run applications created in older Apex versions. Will these still work after upgrading?<br />
<br />
A: We still support the same level of compatibility modes as we have done in the past. However, ideally, people should not rely upon these forever. The compatibility mode is an opportunity for you to have your applications run in a legacy mode, but there is a very good reason why we introduced a change in behaviour. With this said, there are many customers who directly upgraded from APEX 4.2 and APEX 5.0 to APEX 18 with literally zero issues.<br />
<br />
Q: I know that John Snyders has upgraded his IG Cookbook, but can you highlight some of the improvements and new features?<br />
<br />
A: There is new “Copy Don” support, which can be used to copy data from one row to other rows. There is integrated “Copy to Clipboard” support for a row or cell range selection. There are new Dynamic Action events which directly support Interactive Grid, namely Mode<br />
<br />
Q: Are JET Charts still the same in this release, or are there new features theras well?<br />
<br />
A: The version of Oracle JET which is included with APEX is updated with every release of APEX. This allows us to include new data visualizations in APEX, as well as remain on a supported JET release. In APEX 18, we upgraded to JET 4.2. And that had the additional effect of upgrading the version of jQuery and jQuery UI included with APEX 18 (jQuery 2.2.3 and jQuery UI 1.10.4). All native functionality of APEX has been upgraded to work with these newer libraries.<br />
In terms of new charts in APEX 18, we have introduced Gantt Charts, Pyramid and Box Plot.<br />
<br />
Q: Have customers encountered any issues with upgrading that might help us to know?<br />
<br />
A: Nothing really significant. We always keep the Known Issues of a release up to date, as customers report issues which we believe have broad relevance to the community. <a href="https://www.oracle.com/technetwork/developer-tools/apex/downloads/apex181-known-issues-4478237.html">https://www.oracle.com/technetwork/developer-tools/apex/downloads/apex181-known-issues-4478237.html</a><br />
<br />
<a href="https://www.oracle.com/technetwork/developer-tools/apex/downloads/apex181-known-issues-4478237.html"></a>Q: Why was there such a dramatic lead in release numbering from Apex 5 to Apex 18?<br />
<br />
A: This was solely to be consistent with the rest of the product naming and numbering with all other products & services from Oracle. Oracle provides a vast number of service offerings on the Oracle Cloud, and instead of having large, lengthy release cycles, there are releases every quarter, and in many cases, numerous times in a quarter. Oracle switched to this yearly product numbering for all Oracle Cloud Services. Other products available both on-premises and on cloud went to this same numbering (e.g., Oracle Database, Oracle REST Data Services, SQL Developer). We changed the release numbering of APEX to be consistent with this.<br />
<br />
Q: The Oracle Application Express Statement of Direction indicates that there will no longer be patch set releases such as 5.1.4. Does this mean that we will have to wait for the next major release to see issues resolved that developers have identified?<br />
<br />
A: Not particularly. Between cycles, we will release a cumulative “Patch Set Exception bundle” on My Oracle Support. We did this for APEX 18.1 and are doing so for APEX 18.2. We won’t include every bug fix in the patch set bundle, but we will include those which we believe impact a large number of customers. The upside to this frequent release cycle is you may see new features sooner. In the past, we could never alter translations or UI in a patch set. But we can do this in full releases. So this will ultimately result in us being able to deliver highly sought after functionality sooner.<br />
<br />
Q: So without playing all of your cards, what lies ahead for Apex?<br />
<br />
A: The APEX statement of direction for APEX 19.1 is available at <a href="https://apex.oracle.com/sod">https://apex.oracle.com/sod</a>. In general, though, things have never looked brighter for the APEX platform or community.<br />
<br />
By the way, if you have a moment, you may wish to listen to the recent APEX podcast with Executive Vice President Andy Mendelsohn (this podcast is recorded by the community, and not us). <a href="http://apex.press./">http://apex.press.</a><br />
<br />
<br />
Again, my thanks to Joel.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com0tag:blogger.com,1999:blog-2927557081925713627.post-77412664012627464212018-11-04T05:55:00.000-08:002018-11-12T23:18:04.947-08:005 Client Excuses For Not Upgrading ApexIn case you live under a rock, the latest release of Apex is Apex 18.2. This update was released on September 28, 2018. In case you missed it, you can find the release announcement <a href="https://blogs.oracle.com/apex/announcing-oracle-apex-182" target="_blank">here</a>. But even when there is a new Apex release, it seems that many Apex customers neither celebrate the release nor upgrade to it very quickly. Now I am in no way suggesting that you throw you production applications into Apex 18.2, but merely that you begin using it in your dev environment.<br />
<br />
I have been working with a lot of Apex clients. In some case they do not know that there is a new release. For others, there is a huge reluctance to update.<br />
<br />
As I work with my clients, I hear a lot of excuses that they repeatedly use for not upgrading to the latest release of Apex. In this post, I try to present the top 5 excuses I hear, and to respond to them as thoroughly as I can.<br />
<h2>
Excuse 1<br />I am waiting for the new release to be stable</h2>
I am sure you have heard this one before. It really has no merit. I don't know of any release of Apex to be so so buggy as to cause it to be pulled back or immediately followed by another major. Most often, I see the Oracle Apex team respond to any issues by releasing a patch release. The patch is released sometimes to address some unintended behaviour.<br />
<br />
Sometimes, Apex hosting companies will also give this same excuse. But, the excuse is not really the right reason for them not to upgrade. The more valid reason, I suppose, is to avoid a sudden change of the Apex hosting environment. They want to give their clients time to upgrade their own apps. Wait a minute! This is another important reason for those clients to upgrade.<br />
<br />
No matter how you slice it, this excuse is simply that - an invalid excuse.<br />
<h2>
Excuse 2<br />Eveything is working fine now</h2>
I am sure you've heard this one too. It is said over and over again.<br />
<br />
One of my recent larger clients were working in an Apex 5.1 environment. At least they upgraded to that version. Right? Wrong. While they upgraded to 5.1 most of their apps were built in 4.2, 4.0 and one even in 3.2. These apps were running in a 5.1 environment using the compatibility mode. Sure, the apps are working now, but for how much longer. While the Oracle Apex team is really good at supporting legacy versions, there will simply come a time that the version is so old that they surely break.<br />
<br />
Apex 5 was such a watershed release, it is crazy to have apps in any earlier version. Apex 5 significantly changed the performance of apps and introduce a whole new way of structuring applications. And, if you apps were built using Apex 5, it will be a snap to do the upgrade to release 18.2. So, to me, this excuse also has no validity.<br />
<h2>
Excuse 3<br />Our developers don't know the latest version</h2>
This excuse is pathetic. There is always a lot of good documentation, articles and blog posts that will help you as a developer to make the transition. But if your developers have never even transitioned to Apex 5 then that failure rests entirely on you.<br />
<br />
When you think of it, this excuse is quite insulting to the ability of your developers. If they have any skill at all, they surely won't find the transition difficult.<br />
<br />
Let's say you just got the latest iPhone. Would you just leave it sitting on the table until someone from Apple comes along to show you how to use it. No. You play, explore and discover for yourself. If something has changed so much that you just don't know how to use it, you check web articles or forums to get the information you need. Surely it is like that with a new release of Apex. As developers, we are used to experimenting and poking around. For us, it is fun to learn and to discover new answers to old questions.<br />
<br />
Stop blaming your developers. Any developer that can't learn new things are really not developers anyway.<br />
<br />
<h2>
Excuse 3<br />We don't need anything in version 18.2</h2>
Really? Do you even know what the new release has to offer?<br />
<br />
Usually those who use this excuse know very little about the new release They have not read the <a href="https://docs.oracle.com/database/apex-18.2/HTMRN/toc.htm#HTMRN-GUID-540B73CB-08A7-4422-B6BF-CC785EC47694" target="_blank">release notes</a>. They haven't looked at the <a href="https://apex.oracle.com/en/learn/release-notes/" target="_blank">new features</a>. And they haven't at least taken a cursory look at the version 18.2 <a href="https://docs.oracle.com/database/apex-18.2/" target="_blank">documentation</a>. Until you have done all of this, you simply can't use this excuse.<br />
<br />
<h2>
Excuse 4<br />We can't stop development to do an upgrade</h2>
This excuse is a bit like excuse 3. Sure, if you are in the middle of a large project, now may not be the right time. However, if are just thinking the upgrade will take hors and hours - you are simply wrong.<br />
<br />
You can start my upgrading your development environment. Check out the level of effort to do the upgrade. Usually, it's a breeze. If it causes issues, just rollback to your pervious version. But rolling back should be your last choice. Look at the issues and try to resolve. It is not likely that you have been the first to come across the issue. Head on to the <a href="https://community.oracle.com/community/groundbreakers/database/developer-tools/application_express" target="_blank">Apex forum</a>.<br />
<br />
If you need to continue with the active development, create a new 18.2 environment to check things out.<br />
<br />
<h2>
Excuse 5<br />We don't have anyone who knows how to do the upgrade</h2>
If you've ever upgraded an Apex environment, you'll know that the Oracle Apex team has made the job pretty straight forward. In other words, the upgrade is not rocket science. It is not as if you are upgrading the database! With proper planning, it won't take a DBA or upgrade expert to get the job done.<br />
<br />
Luckily there are a lot of good resources to help you. One of the best blogs on upgrading that I have come across is Dimitri Gielis's <a href="http://dgielis.blogspot.com/2018/05/safely-upgrading-to-oracle-apex-181.html">Safely uprade to Apex 18.1.</a><br />
<h2>
Conclusion</h2>
<div>
Okay. The decision to update is not one to take recklessly. It requires evaluation and planning. Nonetheless, it is unwise to remain on an older release indefinitely. It is even worse to continue to have applications that were built in very old versions of Apex. And, if it is not yet time to upgrade, remember that as developers we can <a href="https://apex.oracle.com/pls/apex/f?p=4700:2:106046438321969::NO:RP::" target="_blank">request a workspace</a>.</div>
<div>
<br /></div>
<div>
Just stop using excuses. They make you look silly.</div>
<div>
<br /></div>
<div>
Drop me a comment and let me know your thoughts on upgrading Apex.<br />
<br />
http://www.applinks.co.za</div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com3tag:blogger.com,1999:blog-2927557081925713627.post-56451161097430372612015-07-05T03:17:00.001-07:002015-07-05T03:17:34.909-07:00Apex 5.0 - Getting Down With Dialogs (Part 1)A part of any modern day web application is the use of dialogs. With the release of Apex 5.0, the development team has obviously put a great deal of work into this functionality and made it an integral part of Apex. Through the use of JQuery UI, the team has given us some feature-rich components and processes to use to support our need for dialogs.<br />
<br />
In their simplest terms, dialogs are interactive modal popups that can be used to give additional information to the user, assist them in processing selected steps, or guide them through a wizard of nested steps. They offer the user the sense of "staying" where they are and not having to navigate around to accomplish a task. Dialogs are immediate, dramatic and captivating.<br />
<br />
In previous versions of Apex, we had to use plug-ins or write considerable javascript to build these kind of dialogs into our applications. Now, with Apex 5.0, the full power of dialogs is rigt at our fingertips.<br />
<br />
There are several types of dialogs available in Apex 5.0. In this first post in the series, I will discuss only one of them - the inline dialog.<br />
<br />
Inline dialogs are really just regions on a page that function like a modal "popup" dialog. They are distinct from their more powerful cousin, the modal dialog page, in that they are simply a region and not a page. This, while it may seem obvious, is a key point. As a region on an existing page, the inline dialog does not need all of overhead involved in page rendering and processing. The region is in fact rendered as part of its containing page and therefore can be displayed very quickly. This lightweight "here I am" power of the inline dislog is also a bit of a weakness. As a region on a larger page, it lacks the validation and processing power of a separate page and is therefore not really suited to complex operations.<br />
<br />
Before we go into the details of how to create an inline dislog, we ought to understand what in fact it will do.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPnewD6meP-6M4gdstF6VD1c5KSDn3FbIN8-WUkDcXLsGd0i4QSEdB3nFCpm2vjsyhZsZsA4w5_ZU0_pAkrYAmWa-XUS3BciesDkemc8jX7ddDcLj_a1o_NbHegBIWQLIhUq0r9NEeH0s/s1600/message.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="318" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPnewD6meP-6M4gdstF6VD1c5KSDn3FbIN8-WUkDcXLsGd0i4QSEdB3nFCpm2vjsyhZsZsA4w5_ZU0_pAkrYAmWa-XUS3BciesDkemc8jX7ddDcLj_a1o_NbHegBIWQLIhUq0r9NEeH0s/s640/message.PNG" width="640" /></a></div>
<br />
As you can see from the above image, an inline dialog looks and feels much like a modal page. When the dialog is opened, the launching page is dimmed to highlight the modal region. The dialog awaits the users interaction before continuing and the underlying parent page is "frozen". Much like a modal page, it can have buttons to invoke actions, but it is important to understand that the whole page, not just the modal region, will be submitted when the button is clicked. More about that later.<br />
<br />
It may help if I take a moment to explain my specific use of the inline dialog here. My application tends to be very dynamic. I am always adding new functionality and need a way of highlighting the new features to users. So, what I have developed is a page messaging franework. I have a database table where I store the messages that I want to display referenced by page number. When a user launches a page in the application, a process checks to see if there is a message to be displayed to the user for that page. If the user has not viewed the message before, the inline dialog is displayed with the message and title from the database table. Once the user clicks the "Got It" button, I record that the user has viewed this message and it will not be displayed for the user again. The framework allows me to set messages for any page in my application in order to help users find and use new features.<br />
<br />
So, how do we create the inline dialog? It is really pretty simple. Create a new region on the page and set the region template to "inline dialog."<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSftjJmkcBajplZ01fJJpZyZRuvpdZ3gMEERM6VduJrzMkC67TdoUtFfVdWi8Ge-jGfxKr7xxI1InEn1MTuwPaqb0WeOL52z_VPY3XQx1gwkUtaW-neXq9reEKzUeEgseHkkT-ItX0iUI/s1600/region.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="311" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSftjJmkcBajplZ01fJJpZyZRuvpdZ3gMEERM6VduJrzMkC67TdoUtFfVdWi8Ge-jGfxKr7xxI1InEn1MTuwPaqb0WeOL52z_VPY3XQx1gwkUtaW-neXq9reEKzUeEgseHkkT-ItX0iUI/s320/region.PNG" width="320" /></a></div>
<br />
In my case, because I need the title and content to by dynamic, I have two hidden page items that I reference for both the region title and the content.<br />
<br />
Once you have selected the region template of "inline dialog, you can open the Template Options where there are a number of settings to further control and appearance of the region.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglks8wAra0RwyOWLsbLyTMKcl1ZzV-a6c-lvION_7_coBC6l7VagDj7v_njLWKR17jOAyrUsQShf0CacXjHmggavTBkIQoW9uUmhtYZD11NEQvlrM_aYrtSFkzHQXTrzc2RL4Umq0v3JM/s1600/options.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglks8wAra0RwyOWLsbLyTMKcl1ZzV-a6c-lvION_7_coBC6l7VagDj7v_njLWKR17jOAyrUsQShf0CacXjHmggavTBkIQoW9uUmhtYZD11NEQvlrM_aYrtSFkzHQXTrzc2RL4Umq0v3JM/s320/options.PNG" width="320" /></a></div>
<br />
Once you have the region the way you want it, it is time to trigger the display of the modal region. You need to determine under what circumstances the region will be displayed. Here, I want the region to be displayed on the loading of the page if there is a message to be displayed and the user has not viewed the message before. As I mentioned earlier, I have hidden page items for the title and content of the inline dialog message. These page items only get values assigned to them if there is a message to display for that specific page and if the user has not yet viewed the message. This then gives a mechanism for displaying the dialog. If the hidden items are not null, then I want to display the dialog. So, I have an "on change" dynamic action that references the hidden page item and fires if the item is not null:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7bFZBQF1ha71BV8OTL4cWu0i6poS_0tI0g9OumPND8YTGE6ICe2xdvBzn7_PGJ576yUksnnlgjVjEbPJfhTRwubkO6NBb1H5RloMs41QnNgvCRGwAbD6Sl9Y1TV3WRV-3q27OaZjjFqI/s1600/onchange.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7bFZBQF1ha71BV8OTL4cWu0i6poS_0tI0g9OumPND8YTGE6ICe2xdvBzn7_PGJ576yUksnnlgjVjEbPJfhTRwubkO6NBb1H5RloMs41QnNgvCRGwAbD6Sl9Y1TV3WRV-3q27OaZjjFqI/s320/onchange.PNG" width="320" /></a></div>
<br />
Then, as part of my dynamic action, I use a true action of "Execute Javascript Code" to open the dialog:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO-6OLcTzsqP2IMnSKY5ZR5CIl86ufDE2Ao06EYSddyoTbHwsdQXAzFqVFIxUnIpLa7byZTd4_9_GrffJTe_moEI98rnK4JZsXDa4nZv-FTFz3OJOaY6MqcEFWCf-wvGDOK-3wgbwYj3w/s1600/trueaction.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="141" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO-6OLcTzsqP2IMnSKY5ZR5CIl86ufDE2Ao06EYSddyoTbHwsdQXAzFqVFIxUnIpLa7byZTd4_9_GrffJTe_moEI98rnK4JZsXDa4nZv-FTFz3OJOaY6MqcEFWCf-wvGDOK-3wgbwYj3w/s320/trueaction.PNG" width="320" /></a></div>
<br />
Notice that I pass the static id for the region to the openModal function. And that's it! The inline dialog will be displayed. Of course, your triggering action may be quite different, but you get the idea.<br />
<br />
Let's return to my earlier point about the entire page being submitted. An inline dialog region is different from a "modal page" in that it is simply a component of the parent page. Consider my "Got It" button that is part of my dialog region. The button has the behaviour of "Submit Page." When it is clicked, because the inline dialog is part of the parent page, the entire page will be submitted with all of its items etc. Therefore, it is important that you control the processing and branching based only on the single button being clicked. There are several ways to do this, but the most common would be to use the "When button pressed" condition processes and branches.<br />
<br />
There are some other important issues associated with the fact that the inline dialog is simply a region of the parent page:<br />
<br />
1. It will display very quickly as it is rendered with the parent page.<br />
2. Unlike a modal page, it shares its processing with the parent page.<br />
3. It is best suited for static content and limited user interaction.<br />
<br />
There are lots of ways you can effectively use inline dialogs. You can use them for messages (as I hve), for alerts, confirmations or for quick popups that display additional information when the user opts to view it. They are very slick and can really contribute to a positive user experience.<br />
<br />
Modal regions have been a part of Apex for quite a while. But now, with Apex 5 and the built-in use of JQuery UI, they are easier than ever and offer the developer a great deal of options of customization and control.<br />
<br />
Add a comment to this blog and let me know how you are making use of inline dialogs. And watch for Part 2 in this series where I will discuss the more powerful and feature-rich modal dialog page.<br />
<br />
<br />
<br />Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com15tag:blogger.com,1999:blog-2927557081925713627.post-33992442741235682232015-06-28T06:37:00.002-07:002015-06-28T06:42:10.957-07:00Apex 5.0 after two monthsI have been using Apex 5.0 for two months now and figured it was high time I start blogging about it. So, here are some thoughts after eight weeks of exploration.<br />
<br />
At first I found the new builder features a little difficult. There had been so much hype about the new Page Designer. I guess I was just so comfortable with the old component layout that it would have been so easy to stay with what I know. But, yes, I stuck with Pagc Designer and now can't imagine a world without it. I love that you can change component attributes in bulk and that you only have to click save once for the entire page. It is so easy to drag regions and items where you want them. So, while Page Designer might intimidate you at first - stick with it. Your productivity will soar.<br />
<br />
I am thrilled with the new code editor. You can write code exactly as you would in any tool such as SQL Developer with all the feateres you need and can validate the code without leaving the editor. This is another huge productivity gain.<br />
<br />
Modal regions and modal pages are very powerful and easy to implement. I must say that I am using them more than I ever had in my applications and they contribute to a great user experience. I will try and post a blog just about modal dialogs soon.<br />
<br />
The Universal Theme and Themeroller are really inspiring and have a lot to offer. The user response has been good though many have suggested that the apps have adopted a "Microsoft" look - oh no!<br />
<br />
There are some things that took me a while to come right with and I will blog about those in the hope of saving others time and frustration.<br />
<br />
Most important of all, my apps in Apex 5.0 are running about 20% faster. Page and report refreshes are faster. Chart rendering Is faster and even database processing seems faster.<br />
<br />
Now for a few complaints. There have been long-standing issues with Tabular Forms and no work has been done in this area. Some of the IR customization capability seems to have been removed. Report layouts seem somewhat less flexible but I an still exploring here.<br />
<br />
So after all is said and done I love Apex 5 and feel renewed and excited as an Apex developer.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com1tag:blogger.com,1999:blog-2927557081925713627.post-3037655681222006172013-01-02T22:02:00.001-08:002013-01-02T22:54:03.203-08:00Email and Subscription IR Reports in Apex 4.2There has been a problem for some time with regard to email and subscriptions of IR reports. Prior to Apex 4.2, the "from" address for these reports when sent by email has always be the same as the "to" address entered by the user. In other words, if the email report was being send to john.smith@yahoo.com, it would have the from address set to john_smith@yahoo.com.<br />
<br />
Having the "from" address the same as the "to" address, often resulted in issues on mail servers. Some mail servers have policies set that do not allow external emails to come from an internal server address. Other times, mail servers will treat these sorts of a mails as junk or span and not direct them to the inbox. Further, it just looked weird!<br />
<br />
Now, Apex 4.2 allows you to explicitly set the "from" address either at the instance level, or at the report level itself.<br />
<br />
Navigate to a page that has an interactive report, and view the Report Attributes. Scroll down to Advanced Attributes. Here, you can specify the "Email From Address":<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKR11H1MlYH3jNs9o_QSH1o6Fui4xd1RlmxQNY8Sd3bpZFNPJij4Hx3ieJlKFbb2JiE4yXLMA2XB0-uaHSEESASRdJxHJ_tElCr3GamMGUeaCrhDHijmkqNnDu9j2K2Zlkc1NPOxc6zps/s1600/from_address.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="115" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKR11H1MlYH3jNs9o_QSH1o6Fui4xd1RlmxQNY8Sd3bpZFNPJij4Hx3ieJlKFbb2JiE4yXLMA2XB0-uaHSEESASRdJxHJ_tElCr3GamMGUeaCrhDHijmkqNnDu9j2K2Zlkc1NPOxc6zps/s320/from_address.jpg" width="320" /></a></div>
<br />
You can also set the from address at the application level. In Application Builder, edit and application and click the Edit Application Properties button. Scroll down to the Properties section, here you will find a property where you can special the application's "from emal address."<br />
<br />
Sometimes it is just the little things that make a big difference!Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com1tag:blogger.com,1999:blog-2927557081925713627.post-82539824970333522232013-01-01T22:10:00.000-08:002013-01-01T22:10:28.698-08:00A Response to Responsive Design in Apex 4.2I have been following with a lot of interest the discussions regarding implementing Responsive Design using Theme 25 in Apex 4.2. In case you are out of the loop, Responsive Design has to do with building applications that are fully functional across many different devices with various "viewport" sizes from the small screens of smartphones to the largest desktop screens.<br />
<br />
My journey begin by reading Shakeeb Rahman's blogs on Responsive design <a href="http://apex.shak.us/">here</a>. I downloaded the presentation slides and worked my way through them. To me, this was exciting stuff. I was beginning to explore new approaches to web applications and it was quite cool had it all worked. I then downloaded Apex 4..2 and began to play with them 25 to see how it all fit together.<br />
<br />
Not being convinced I had all the tools and information I needed, I purchased the book <i>Responsive Web Design with HTML5 and CSS3 </i>by Ben Frain that you can find <a href="http://www.amazon.com/gp/product/B007SVJA3M/ref=kinw_myk_ro_title">here</a>. It is a really great book and leads you through the whole implementation of a practical Responsive web application. I learned a tremendous amout about HTML5 and CSS3 and how to enhance what theme 25 already tries to accomplish. I built a couple of good test applications that worked very well on various deI vices.<br />
<br />
Now here is where my response comes in.....<br />
<br />
I have a large application that is used by many concurrent users. And, as you might expect, I thought "hey, why not make it responsive?" I set about to do just that. Not as simple as you might think. The big issue for me is that the application is primarily a series of real-time reports. Many of these reports are quite large and consist of up to fourteen columns. Some of the reports include drill-downs where the data is displayed on modal pages for ease of use and navigation. These reports simply are not suited for small screens - you can't avoid scrolling and the reports are almost useless in a small format. I just didn't make sense to try and make the application work all small viewports. Here is a case where it would be far more productive to have 2 separate user interaces, one for desktop and one for mobile (fully supported in Apex 4.2). The mobile application can present streamlined reports with only critical columns while the desktop version can support the full reports.<br />
<br />
There is, of course, a greater cost to having two separate interfaces (one for mobile and one for desktop). You need to support both versions of the application. The logic may in some places be duplicated. You may need to maintain separate CSS files and so on. But, in my case, no other approcah seems to make sense. I am open to comments from others that may suggest I am wrong. After all, I am new at Responsive Design. Please do feel free to offer you own feedback.<br />
<br />
I also did a bit of checking on my application. In Apex, you can monitor how your application is being accessed. You can monitor what OS is used and what browser. I found, dare I say it, over seventy percent of my users are accessing my application from IE (Yikes!!!!). This despite a message on the login page advising that the application is best used with Firefox. So, why spend oodles of development time (not to mention money) trying to make the application responsive.<br />
<br />
I am not suggesting that we ought not to learn about and use Responsive Desgin. It has a clear role in making our applications future-proof. After all, more and more users today access websites via a device other than a desktop. What I am suggesting, however, is that creating "responsive" applications is but one approach. There are instances when it will be appropriate and instances where is simply won't achieve great results.<br />
<br />
Perhaps the key lies in planning the design from the very start. If instread of simply following an approach and layout dictated by our client, or worse, by a graphic designer, we take the position from the start that it would be ideal to have a responsive application. In other words, we approach the whole project from a responsive point of view and try to desgin and build an application that can in fact be independent of the device on which it is viewed. To do this, you need to understand some of the concepts, techniques and tools involved in creating responsive web applications. There is no doubt this is the future. In my view, it is one more tool in our toolbox and not the solution to every situation. Thankfully, Apex 4.2 gives us a number of approaches to consider and to implement.<br />
<br />
<br />Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com26tag:blogger.com,1999:blog-2927557081925713627.post-86788123313191481222011-08-19T04:07:00.000-07:002011-08-19T04:07:50.157-07:00Enabling and Disabling Classic Report ColumnsSometimes a classic report with many columns takes up too much space on the screen. There are a number of options for dealing with this issue, but one that I like is to provide the user with the ability to turn on and off individual columns or groups of columns.<br />
<br />
I have seen several different ways of providing this functionality. For instance, Denes Kubicek has a solution in his sample application, but it does not handle column groups and is somewhat more awkward. So, for what it is worth, I thought I would share my solution.<br />
<br />
First, I create a series of checkbox items representing the columns (or column groups) that I want to allow the user to "turn on and off." I put these items in a hide and show region so that they are accessible but not necessarily displayed until the user wants to change the columns.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuXj_H0Vwi7i9-C0ziSanbDjqzq0BKF4tXPWQ-iW-bdBBfYUrHZxM7PeVcta2v2q0cASN21yDA657Nva1NsC5dNnB3Kz4PnRNjfgwuW2752-hYyh1E8IoiZbYJLsGm9LbtJcHO4GsyLOY/s1600/checkboxes.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="46" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuXj_H0Vwi7i9-C0ziSanbDjqzq0BKF4tXPWQ-iW-bdBBfYUrHZxM7PeVcta2v2q0cASN21yDA657Nva1NsC5dNnB3Kz4PnRNjfgwuW2752-hYyh1E8IoiZbYJLsGm9LbtJcHO4GsyLOY/s320/checkboxes.png" width="320" /></a></div><br />
<br />
Next, I modify the report. I set the Conditional Display attributes of each column in order to reference the related checkbox.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8cAreiBjb8VY4IrdebybafKsJVPRGowAwZv7JiQeItNrHPOIgq5H3HUlIUozqkRoAgYDeGwGPJQY0zcCOtww3PLrEoPI3dJv4msDUOLGwjlApXX43oy0A7z1tHo4meIcpkFqr0XH2F4Y/s1600/conditional.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="160" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8cAreiBjb8VY4IrdebybafKsJVPRGowAwZv7JiQeItNrHPOIgq5H3HUlIUozqkRoAgYDeGwGPJQY0zcCOtww3PLrEoPI3dJv4msDUOLGwjlApXX43oy0A7z1tHo4meIcpkFqr0XH2F4Y/s320/conditional.png" width="320" /></a></div><br />
In some cases, I have more than one column being controlled by a single checkbox. My report has several columns that display dates. I use a single "date" checkbox to turn off and on the entire group of columns.<br />
<br />
Next, I create a dynamic action for each of the checkbox items. The dynamic action is triggered by the "Change" event of the checkbox.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGIL-yv8qTtvXnBqdtcVvInTlL5MBfRIJ0ZyNKbch6QXVOwI1Otdc6c2A3TnHl1LYxOpeKDlyqYRLGzujmRmcO7TyelDJuglCNHZ4_aIe1Xj9uHl0sxlGbkoLVqdH76dAWsINuECEbo9g/s1600/dynamic.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="237" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGIL-yv8qTtvXnBqdtcVvInTlL5MBfRIJ0ZyNKbch6QXVOwI1Otdc6c2A3TnHl1LYxOpeKDlyqYRLGzujmRmcO7TyelDJuglCNHZ4_aIe1Xj9uHl0sxlGbkoLVqdH76dAWsINuECEbo9g/s320/dynamic.png" width="320" /></a></div><br />
There are two true actions. The first, Execute pl/sql Code simply serves to put the value of the checkbox item into session state.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsZE0uPjR4pbfLZxaiQkjQzBsUFBsDwb2ZpfiCLnDf3vSVbrXLQ3KeLPoSxnlUb60SEE1O39I0NcihGbSu2eORaeUTHkMqJR5pfhjgMh35AN0yicnm8aQdKAA3xxJAKswAxX9tmpDx0NM/s1600/action.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="218" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsZE0uPjR4pbfLZxaiQkjQzBsUFBsDwb2ZpfiCLnDf3vSVbrXLQ3KeLPoSxnlUb60SEE1O39I0NcihGbSu2eORaeUTHkMqJR5pfhjgMh35AN0yicnm8aQdKAA3xxJAKswAxX9tmpDx0NM/s320/action.png" width="320" /></a></div><br />
The second action simply refreshes the region containing the classic report.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibNm9Lh-WHldgE2n5bHIWjchHVWi35Ik9vk9DJewAGhyphenhyphenY6pCJFYILmu49f3A5UNlSEHPjSRaJsCm539ZsI5i-vCCANlTH4fogj6mSGseiRBi3apXxge76ePmME7zJkjsV01zOD2_FMIak/s1600/refresh.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibNm9Lh-WHldgE2n5bHIWjchHVWi35Ik9vk9DJewAGhyphenhyphenY6pCJFYILmu49f3A5UNlSEHPjSRaJsCm539ZsI5i-vCCANlTH4fogj6mSGseiRBi3apXxge76ePmME7zJkjsV01zOD2_FMIak/s320/refresh.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5FrWidLr9YTf1eayuw6FL84mzYhonqwkg4G9PtH3q_nVcmZdpiAsAvSTQHEjxH0St_ltYXm6vOyhg8s5sFK0crB5empnKWQ8i0X7T1QhDBu0cKDZ72V16_c9-SMtYy2nnS677KukKcjk/s1600/action.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div> You will need to repeat the above steps to create an dynamic action that will trigger on the change of each of the checkbox items.<br />
<br />
The result allows the user to quickly enable or disable columns. Due to the use of the dynamic actions, the report quickly responds to the user changes. You can set defaults for the checkbox items so that some of the columns will show on page load and others will wait for the user to select the column for display.<br />
<br />
<br />
<br />
<br />
<br />
Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com3tag:blogger.com,1999:blog-2927557081925713627.post-3289840851049953912011-07-29T02:11:00.000-07:002011-07-29T03:45:00.535-07:00Minimizing IR Filters On Default ReportI had a requirement for an IR report that specified that the background colour of the rows be set to specific colours based on certain data values. To accomplish this the easiest way possible and to allow the user to "turn on" and "turn off" the colours, I created a series of IR filters.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbETv2ZP3LFyagUcCXlMWC5I2FcZernNaIDDThCGQHnauobbxN7VM_B9638zDO52WzdJXZDiRsf9TXsMGK3DuHFk-BEGXaeVytfPq5Z90DcvZ5hbDWllO1GQE0F-ORl8zy2DKLDAjL48s/s1600/filters.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbETv2ZP3LFyagUcCXlMWC5I2FcZernNaIDDThCGQHnauobbxN7VM_B9638zDO52WzdJXZDiRsf9TXsMGK3DuHFk-BEGXaeVytfPq5Z90DcvZ5hbDWllO1GQE0F-ORl8zy2DKLDAjL48s/s1600/filters.png" /></a></div>Each filter is set to define the highlight colour based upon specified data:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu7c-EyHn1kWSrHNuBhoaUkbnkEwCbv4Fy2gH0uco-g1zcqIRJ8d6k8QuTufQNqQDZw6LfW2RASNPomREpifOFID3FmVd3vrAtuYjHUYIG9JDGi1kkh1bd89-sB1oYqe6JN-IzBDw2ljo/s1600/filter.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu7c-EyHn1kWSrHNuBhoaUkbnkEwCbv4Fy2gH0uco-g1zcqIRJ8d6k8QuTufQNqQDZw6LfW2RASNPomREpifOFID3FmVd3vrAtuYjHUYIG9JDGi1kkh1bd89-sB1oYqe6JN-IzBDw2ljo/s400/filter.png" width="400" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDs-AH208tkMNB9WqdTioqPbKv1DhABhJ2OtBLEWRi88mif0gmvY17Z7dW0dMjUr84wFBrsZa5NkM7vkMDW1rtJVC1Esjdsdbp1UL90dpDPySY8bP4lCKOLbdlQsLGvcbPOtVantxbqRE/s1600/desc.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;">l</a></div>The result is excellent. The rows are highlighted based on the specified criteria and the user can disable specific highlights if they wish. Obviously, these filters are saved as the default report.<br />
<br />
But, my mother taught me to keep things clean and tidy. And, when you first open the report you are faced with a list of filters that you may or may not wish to alter. It makes the report screen rather busy and I wanted to temporarily hide the filters when the page first loads. Hmmmm - dynamic actions?<br />
<br />
Here's the solution: <br />
<br />
1. Using the Advanced dynamic action wizard, create a new dynamic action and set the event to Page Load.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinoT036k08f4fM9BAQlahWwjqv0xZE06JNI2Io1n75f8KK16Z3ShhYKaPNQ2QxUu8R87fXKLTGUr1BtR1aj1wEK6oHTHxxszFJg7_paSA2GV6uuH3RHz8J7NBfnJYB6Gm5R90da4uiqOY/s1600/OnLoad.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="163" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinoT036k08f4fM9BAQlahWwjqv0xZE06JNI2Io1n75f8KK16Z3ShhYKaPNQ2QxUu8R87fXKLTGUr1BtR1aj1wEK6oHTHxxszFJg7_paSA2GV6uuH3RHz8J7NBfnJYB6Gm5R90da4uiqOY/s400/OnLoad.png" width="400" /></a></div><br />
2. Create a true action of type Execute Javascript Code.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDRvXDgvJ07Etfp9RxBoE2oTvhdVe2Xzcb5gKy0a6DAa061JaUhMcKyee_PXfub3N4ldIUC5KpHZGEdPGRxktrDDH3C0TB7rKGuCz1QvkROwLbpysiSlz1VyQUuUEiEAou432qJwX-9DU/s1600/action.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDRvXDgvJ07Etfp9RxBoE2oTvhdVe2Xzcb5gKy0a6DAa061JaUhMcKyee_PXfub3N4ldIUC5KpHZGEdPGRxktrDDH3C0TB7rKGuCz1QvkROwLbpysiSlz1VyQUuUEiEAou432qJwX-9DU/s400/action.png" width="400" /></a></div><br />
3. Enter the following for the javascript code:<br />
<br />
/* Minimize IRR filters on page load*/ <br />
if( $('#apexir_CONTROLS_IMAGE').attr("src") == '/i/minus.gif') { <br />
gReport.toggle_controls($x('apexir_CONTROL_PANEL_CONTROL')); <br />
} <br />
<br />
And, that's it! A result that even my mother would approve of - everything neat and tidy!<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj18v5IhCSaPznZN3Ntbg7qTCHRg7trEUnN_y70bCQFkKdKRf2J0Uz0EHSqC73V5Uds8C2PvQ_P9iL4rFgyGSV41z81R0BwXnNtZ4nXdanxbbzOgAZHIzcx3TtuKHBfeTQE-kTa6p1yoAo/s1600/closed.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="61" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj18v5IhCSaPznZN3Ntbg7qTCHRg7trEUnN_y70bCQFkKdKRf2J0Uz0EHSqC73V5Uds8C2PvQ_P9iL4rFgyGSV41z81R0BwXnNtZ4nXdanxbbzOgAZHIzcx3TtuKHBfeTQE-kTa6p1yoAo/s400/closed.png" width="400" /></a></div><br />
The users can still access the filters by clicking on the "plus" sign and can just as easily hide them again.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com7tag:blogger.com,1999:blog-2927557081925713627.post-31154790352016647002011-07-27T06:08:00.000-07:002011-07-27T06:25:26.249-07:00Expert Oracle Application ExpressIf you have not yet bought your copy of Expert Oracle Application Express - what are you waiting for? I got my copy in the mail a couple of weeks ago and was thrilled to read it. I am one of those developers who tries to read everything I can on Apex to stay at the top of what I do. And, for me, this book is now a "must read" for anyoune who aspires to be an Apex professional.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYEkNBVHHTUjiO-Q3ispRE4nN1P_HgRK3IOgq6E9xoGs7NH74t5AOfs2kAHD2fFrdVrcuHAeJFkWb5HGCWJnDcODnBSU98fawtvDnvQb8X_LOYylSdLT91O8GQ0g1WIS_zIF8inrmcZVQ/s1600/book.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYEkNBVHHTUjiO-Q3ispRE4nN1P_HgRK3IOgq6E9xoGs7NH74t5AOfs2kAHD2fFrdVrcuHAeJFkWb5HGCWJnDcODnBSU98fawtvDnvQb8X_LOYylSdLT91O8GQ0g1WIS_zIF8inrmcZVQ/s1600/book.png" /></a></div><br />
<br />
<br />
This book is written by a collection of authors - most of whom you will know from the forum or from their blogs. What I liked most about the content is that there is a focus on detailed information that you won't find elsewhere. It is not a book for the beginner who knows little about Apex, but rather, is aimed at the professional developer who is keen to know more.<br />
<br />
I have already put to use much of what I learned from the book. In particular, Dimitri Gielis's chapter on charts and Martin D'Souza's chapter on dynamic actions really helped to further my understanding on two key areas of importance in my current development. All of the other chapters were also fantastic.<br />
<br />
So, if you want to go beyond the boundaries of the basic documentation and move into the world of advanced knowledge - this book is a great place to start. I highly recommend it.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com3tag:blogger.com,1999:blog-2927557081925713627.post-56363862418206999412011-07-15T02:50:00.000-07:002011-07-15T02:56:09.322-07:00Tooltips On ChartsIf you don't know what a "tooltip" on a chart is, it is the little piece of text that pops up when you hover over a segment on the graph. In the Apex Chart Wizard, it is referred to as a hint. When you select the "Hints" checkbox in the wizard, the tooltips are enabled.<br />
<br />
While tooltips are available through the Apex Chart Wizard, very little of their real functionality is made available to you directly by the wizard. For example, my customer wanted the following:<br />
<br />
1. A specific font and text colour for the tooltip.<br />
2. The tooltip needed to show both the value and the percentage of the total.<br />
<br />
My first reaction was "that's not possible." But, a bit of research and effort determined that not only was it possible but that we can do all sorts of cool things with the tooltips.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQQ3tt9FDpK7LL0D5fWH9_y7X3_P6doBm9LAoDjd-vULmYMY3NZ9iRFVdxhFpvdzTnU8bWPCeCYoc-5UpbUOkacbIhVYD9Hkar1ZWAbr9xPyDv0IyEJ4PVR6bFcfapRzCZhsB9S37GYy8/s1600/graph.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="164" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQQ3tt9FDpK7LL0D5fWH9_y7X3_P6doBm9LAoDjd-vULmYMY3NZ9iRFVdxhFpvdzTnU8bWPCeCYoc-5UpbUOkacbIhVYD9Hkar1ZWAbr9xPyDv0IyEJ4PVR6bFcfapRzCZhsB9S37GYy8/s320/graph.png" width="320" /></a></div><br />
<br />
<br />
<br />
<br />
Like everything in Apex charts, it is handled through the XML. There is a tag for <tooltip_settings> and defines the tooltip functionality. Here is how my XML for the above tooltip looks:<br />
<br />
<br />
<tooltip_settings enabled="true"><br />
<format><![CDATA[{%SeriesName}{enabled:False} -<br />
{%Value}{numDecimals:0,decimalSeparator:.,thousandsSeparator:\,} -<br />
{%YPercentOfTotal}{numDecimals:0}%]]><br />
</format><br />
<font family="Arial" size="10" color="#808080" /><br />
<position anchor="Float" valign="Top" padding="10" /> <br />
</tooltip_settings><br />
<br />
As you can see, within the <tooltip_settings> tag there are a number of other tags such as <format>, <font> and <position>. There are also other tags as well that I have not used here.<br />
<br />
The format section specifies what information will appear in the tooltip and how it will be formatted. The values in the tooltip are specified by keywords (they have a % in front of them). Here is the information in my tooltip and below are the keywords associated with them:<br />
<br />
Air - 4 - 57%<br />
%SeriesName %Value %YPercentOfToal<br />
<br />
Typically, Apex will insert the %Value keyword for you when you specify Hints in the Wizard. But that is rather limited. There are a large number of keywords you can use to show other values. For example, I have used %YPercentOfTotal which displays the percentage of the total bar represented by %Value amount. Take a look at some of the cool keywords available to you:<br />
<br />
%YPercentOfCategory - the percentage of all the points with the same name<br />
%YPercentOfSeries - the percentage of the series represented by the point<br />
%High - the high value of the point (candlestick)<br />
%Low - the low value of the point (candlestick)<br />
%Name - the name of the point<br />
%Range - the range of the point <br />
%YPercentOfTotal - the percentage of all the series on the chart<br />
<br />
After each keyword is the format definition for that keyword. For example, {numDecimals:0} set the number of decimals displayed to zero. If you want no formatting at all, you can use {enabled:False} immediately after the keyword.<br />
<br />
While the wizard in Apex will get you started, by using "custom XML" for your chart, you can have complete control of the tooltips and make them even more meaningful for your users. Try it out for yourself.<br />
<br />
Here is a reference that will assist you in unlocking the power ot tooltips:<br />
<br />
<a href="http://www.anychart.com/products/anychart/docs/users-guide/tooltip-text-formatting.html">Tooltip settings and formatting</a>Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com8tag:blogger.com,1999:blog-2927557081925713627.post-39433326971436107752011-07-08T05:34:00.000-07:002011-07-08T06:02:34.672-07:00Custom Apex Maps - step by stepThe cool thing about receiving a complicated requirement is that you learn something new and it turns out to be pretty cool.<br />
<br />
In an application I needed to be able to display ocean ports on a map along with the number of shipments shipped from that port. Here is what the map looked like once I was done:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd70UHYTZffvC0jVDfJPsVF8GnAtNnVWXe6kJH7Q6BD6ylXiCpnJMSLa3BY8qcQNxlkMZ1q8gKoJQQfWlfjzdteEtzOnkLKK-fwOd0GR7XpzLVf2YBwBs3iOqEUXjEopxzruSowC3YXO8/s1600/port_map.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd70UHYTZffvC0jVDfJPsVF8GnAtNnVWXe6kJH7Q6BD6ylXiCpnJMSLa3BY8qcQNxlkMZ1q8gKoJQQfWlfjzdteEtzOnkLKK-fwOd0GR7XpzLVf2YBwBs3iOqEUXjEopxzruSowC3YXO8/s1600/port_map.png" /></a></div><br />
Well, that sounds easy enough! However, the maps that come with AnyChart's anymap in Apex 4, know nothing about ocean ports. The map like the one above has cities in the map information but those cities are not located exactly where the ports are located, nor do they have the same name as the port. The image of the map is exactly as I want it, but I need a set of custom locations to display on the map.<br />
<br />
Let's get started.....<br />
<br />
I decided to start by creating a collection on my page that would get the information I require from the customer's shipping data. I need the list of ports and the number of shipments per port. Here is my code to create the collection.<br />
<br />
declare<br />
l_query varchar2(4000);<br />
begin<br />
<br />
IF apex_collection.collection_exists(p_collection_name=>'CHINAMAP') then<br />
<br />
APEX_COLLECTION.DELETE_COLLECTION(<br />
p_collection_name => 'CHINAMAP' );<br />
<br />
END IF;<br />
<br />
<br />
l_query := q'(select null LINK, )';<br />
l_query := l_query || q'(port_of_load LABEL, )';<br />
l_query := l_query || q'(count(shipment_id) SHIPMENTS )';<br />
l_query := l_query || q'(from shipping_rep_sum )';<br />
l_query := l_query || q'(where export_country = 'China' )';<br />
l_query := l_query || q'(and main_carriage = 'OCEAN' )';<br />
l_query := l_query || q'(group by port_of_load)';<br />
<br />
apex_collection.create_collection_from_query (<br />
p_collection_name => 'CHINAMAP', <br />
p_query => l_query,<br />
p_generate_md5 => 'YES');<br />
<br />
end;<br />
<br />
And here is the resulting collection data:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAI7LGL3MLg8JeYwrrHWMMh4ldP9tMuZKvIGeXfG3KNfhZ80gduE3UH9hfn6tT3iKhZ5sC39ApHh7fn-KKXom6J6-DFjIC51cchiy8LkpmLL9ClgaWoEdfzkTw4PrWBNgMG57DqhNPQNo/s1600/collection.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAI7LGL3MLg8JeYwrrHWMMh4ldP9tMuZKvIGeXfG3KNfhZ80gduE3UH9hfn6tT3iKhZ5sC39ApHh7fn-KKXom6J6-DFjIC51cchiy8LkpmLL9ClgaWoEdfzkTw4PrWBNgMG57DqhNPQNo/s1600/collection.png" /></a></div><br />
If you were trying to display the above collection data on a map of China, it wouldn't work. The majority of the port names have no associated locations on the map.<br />
<br />
Next I created a table that stores the longitude and latitude of the ports (I looked up these figures on the internet. The table data looks like this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh27FiYMdV79-n5rC-6FOIj4D3x9glgPB05kx4a1_RU7LUuJWKtRrX8CrApGTcNXAyqSdGS9GuVEIWrxi5XFIWMUDMb4K9ScJnVZoS7CjXaez9-71nWfMATArM4Wx0dJIHut9No_bUsbcI/s1600/data.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="154" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh27FiYMdV79-n5rC-6FOIj4D3x9glgPB05kx4a1_RU7LUuJWKtRrX8CrApGTcNXAyqSdGS9GuVEIWrxi5XFIWMUDMb4K9ScJnVZoS7CjXaez9-71nWfMATArM4Wx0dJIHut9No_bUsbcI/s320/data.png" width="320" /></a></div><br />
So far....so good. I've got my customer data, and I have the coordinates for the locations. Now, I need to be able to supply the map with the data in the right format. The map uses xml to plot the locations. I need to pass data to the map in a format that will look like this:<br />
<br />
<data><br />
<series name="Ports" type="Marker" palette="Default" ><br />
<point name="Ningbo" x="121.55" y="29.88" ><br />
<attributes><br />
<attribute name="value1">23</attribute><br />
</attributes><br />
</point><br />
<point name="Qingdao" x="120.3575" y="36.093" ><br />
<attributes><br />
<attribute name="value1">45</attribute><br />
</attributes><br />
</point><br />
<point name="Shekou" x="112.90" y="21.48" ><br />
<attributes><br />
<attribute name="value1">2</attribute><br />
</attributes><br />
</point><br />
<point name="Nhava Sheva" x="72.97" y="18.95" ><br />
<attributes><br />
<attribute name="value1">2</attribute><br />
</attributes><br />
</point><br />
<point name="Shanghai" x="121.375" y="31.254" ><br />
<attributes><br />
<attribute name="value1">167</attribute><br />
</attributes><br />
</point><br />
<point name="Yantian" x="116.27" y="23.60" ><br />
<attributes><br />
<attribute name="value1">3</attribute><br />
</attributes><br />
</point><br />
<point name="Hong Kong" x="119.17" y="25.27" ><br />
<attributes><br />
<attribute name="value1">1</attribute><br />
</attributes><br />
</point><br />
</series><br />
</data><br />
<br />
I create a hidden text area on the page. In my case, I called it P6_XML_CHINA. Then I have an on load pl/sql process to generate the xml and populate the hidden item. Here's my process code:<br />
declare<br />
l_data varchar2(4000);<br />
v_xlocation varchar2(50);<br />
v_ylocation varchar2(50);<br />
v_port varchar2(100);<br />
<br />
cursor get_locations is select x_location<br />
,y_location<br />
from port_locations<br />
where port_name = v_port;<br />
begin<br />
l_data := '<data>'||chr(10);<br />
l_data := l_data ||'<series name='||chr(34)||'Ports'||chr(34)||' type='||chr(34)||'Marker'||chr(34)||' palette='||chr(34)||'Default'||chr(34)||' >'||chr(10);<br />
<br />
<br />
for c1 IN (select c002,c003<br />
from apex_collections<br />
where collection_name = 'CHINAMAP')<br />
<br />
<br />
loop<br />
v_port := c1.c002;<br />
v_xlocation := null;<br />
v_ylocation := null;<br />
open get_locations;<br />
fetch get_locations into v_xlocation, v_ylocation;<br />
close get_locations;<br />
<br />
if v_xlocation is not null<br />
then<br />
l_data := l_data ||'<point name='||chr(34)||c1.c002||chr(34)||' ';<br />
l_data := l_data ||'x='||chr(34)||v_xlocation||chr(34)||' ';<br />
l_data := l_data ||'y='||chr(34)||v_ylocation||chr(34)||' >'||chr(10);<br />
l_data := l_data ||' <attributes>'||chr(10);<br />
l_data := l_data ||' <attribute name='||chr(34)||'value1'||chr(34)||'>';<br />
l_data := l_data ||c1.c003||'</attribute>'||chr(10);<br />
l_data := l_data ||'</attributes>'||chr(10);<br />
l_data := l_data||'</point>'||chr(10); <br />
<br />
end if;<br />
<br />
end loop;<br />
<br />
l_data := l_data ||'</series>'||chr(10);<br />
l_data := l_data ||'</data>';<br />
<br />
:p6_xml_china := l_data;<br />
<br />
end;<br />
<br />
Next, I create a map region and select a map of China. I set all of the map attributes the way I want them and apply the changes. I then edit the map and select the "map xml" tab and set "Use customer xml" to 'Yes'. This displays the xml that the map will use. I need to edit the xml slightly. Scroll down until you find the reference #DATA# and replace it with a reference to the hidden item with &P6_XML_CHART. (remember the period at the end). This will tell the map to use the xml stored in the hidden item which will plot all of the custom points.<br />
<br />
You can plot anything on a map as long as you can supply the associated longitude and the latitude and prepare the required xml.<br />
<br />
Customer map done! Cool stuff learned!Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com9tag:blogger.com,1999:blog-2927557081925713627.post-22451167508290505002011-06-26T03:12:00.000-07:002011-06-26T07:19:26.017-07:00One dashboard background and many chartsOn a dashboard I have been developing, I wanted to have one large background image and have all of the charts displayed on that background. This turned out not to be as easy as I thought. Actually, the solution is easy, but finding details on exactly how to do it was not. So, I thought it would be useful to share how I ended up meeting the requirement.<br />
<br />
First, I selected a background image. In my case it is a map of the world and it is a dark blue image. Using a photo editor, I manipulated the image to be subtle and to achieve the effect I wanted. Then, I needed to load the image so I can use it. To make things easy, I just uploaded the image in Shared Components => Images. Alternatively, I could have uploaded to the /i/ directory.<br />
<br />
Next, I created my "main" region. This is an HTML region with nothing in the source. To the region header I added the following in order to display the image:<br />
<br />
<table width="100%" cellspacing="0" cellpadding="0" border="0" align="left"<br />
background = "#WORKSPACE_IMAGES#bkg_image.gif"> <br />
<tr height="1000px" align="left" valign="top"><br />
<td><br />
<br />
So, now I have my large background image on which to place my various dashboard charts.<br />
<br />
Next, I started to create my charts. In my case, I didn't want to use a chart region as it gave me a box look with a title etc. I wanted my charts to look like they were floating on the background. So, I created a chart and set the region type to "No Template". But there was still a problem. Every time I created a chart, even with "No Template" the chart had it own white background. I could change the background to a solid or gradient colour, but that is not what I wanted. I wanted the large background image to be the background and have my chart background to be transparent. In other words, the chart lines or bars sit on top of the background image.<br />
<br />
Here's what to do. Create the chart and get it working and looking exactly as you want. In my case I had to set the font colour for the labels and title to something like #AAAAAA because my background is dark.<br />
<br />
Once you have the chart created, do the following:<br />
<br />
1. Open the chart definition.<br />
2. Click on the Chart XML tab.<br />
3. Set "Use Custom XML" to Yes.<br />
4. The XML code will display so that you can edit it.<br />
5. In the XML you will find an empty tag that looks like this:<br />
<br />
<data_plot_background><br />
<br />
</data_plot_background><br />
<br />
It will be an empty tag if you have not selected a background for the chart.<br />
<br />
You want to edit this section. In may case, I changed it to be:<br />
<br />
<data_plot_background><br />
<fill type="Solid" color="0xffffff" opacity="0" /><br />
<border enabled="false"/><br />
</data_plot_background><br />
<br />
6. Apply Changes.<br />
<br />
Now, display the chart as a "child" region of the region that you created for your background image.<br />
<br />
The actual data area of the chart will now be transparent and your background image will show through. The chart will appear to be floating on your background. The labels, chart title and legend will also appear to be on top of your background image.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSGhIY6YAskdJdEP1mysDTcfyRM2ZH7PSjEQZ-l8QQ_SAqZqeuQHND2SCwusnqDu8z4BW8RJ_XD56HHl0ahxwgvVrBHP9bD6B_VKX1wTb2AYlkugaxCZF5bZB052kwbcU49EjKBdaXjDo/s1600/charts.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="142" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSGhIY6YAskdJdEP1mysDTcfyRM2ZH7PSjEQZ-l8QQ_SAqZqeuQHND2SCwusnqDu8z4BW8RJ_XD56HHl0ahxwgvVrBHP9bD6B_VKX1wTb2AYlkugaxCZF5bZB052kwbcU49EjKBdaXjDo/s320/charts.jpg" width="320" /></a></div><br />
<br />
I am not sure that the XML I have provied is the best way to do it.... but it works!<br />
<br />
The result is a dashboard with a single background image and charts that lay on top of that image.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com0tag:blogger.com,1999:blog-2927557081925713627.post-23853866739449713022011-06-26T02:38:00.000-07:002011-06-26T02:38:20.935-07:00Why bother to blog about Apex?For a long time I have been thinking about blogging about Apex. Some of my colleagues had asked when I was going to start blogging. I guess it always seemed to me that maintaining a blog would be quite a commitment and very time consuming. On this point, I am not wrong.<br />
<br />
But, there is another side to the story. As an Apex developer, I have used blogs extensively to solve problems, discover new ways of doing things and to learn about new functionality. Almost every day I read an Apex blog of some sort and learn something new. I have relied heavily on blogsaj and found them to be a critical resource.<br />
<br />
So, if I use blogs for learning, how can I sit back and not contribute to the community and share those things that I myself am learning.<br />
<br />
And, there is another reason I have started to blog. I am an Apex enthusiast and where I live and consult (South Africa), there is still limited use of Application Express. This is because there is a limited development community here that is using and promoting Apex. What better way to promote the tool than to become involved in sharing information about its capabilities.<br />
<br />
So, to blog or not to blog? The answer seems clear..... and here I am.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com0tag:blogger.com,1999:blog-2927557081925713627.post-29334353644648316472011-06-25T23:50:00.000-07:002011-06-25T23:57:03.953-07:00Complex Dashboard in ApexI have been spending time lately developing what has turned out to be a very complex dashboard in Apex. The dashboard has somewhere around twenty different dynamic flash charts on it, most of which have user-defined parameters.<br />
<br />
At first, it seemed simple enough. I developed the graphs and the user input fields. One by one the graphs were tested and worked. However, when it was all put together, I was not at all happy. The charts took quite a while to load, and randomly some of the charts would start to load but never render.<br />
<br />
After quite a bit of playing around and investigation, I discovered some things that I should have thought about from the start. Firstly, every time the page renders, the charts have to build their data set. The more complex the data and the more series involved, the longer it will take for the chart to render. Also, when there are user defined parameters associated with the data set, the chart will sometimes fail entirely.<br />
<br />
Now, to find a solution. I first deiced that it would be best to have less charts immediately render when the page is launched and allow the user to select what charts they want to see. To accomplish this, I used the parent / child region feature in Apex. I created several parent regions each based on a chart category. Next, I place "child" regions with charts (no template) inside each of the parent regions. To control the display, I created a checkbox list in the page sidebar. When the user selects the checkbox associated with the region, the charts are rendered. This was a great solution because the charts only render if the region is shown thus improving the overall performance. I even allowed the user to save their "dashboard settings" so that the dashboard would always first load using their "checkbox" settings. In a later blog I will wlak through all of the steps to do exactly this.<br />
<br />
Now, what about those pesky data sets? After quire a bit of investigation on the forum and elsewhere, I came to understand that if I could take the task of data generation away from the flash chart itself and make it as simple as possible, the faster the chart would load and the more manageable would be the user-defined paramets. Collections is the answer!<br />
<br />
For charts that required a larger amount of data with multiple series, I created a collection on page load. It is not rocket science. Generally the collection must end up have the same query format as the chart itself:<br />
<br />
select null link,<br />
<something> value<br />
from ....<br />
where....<br />
<br />
As long as you can successfully build the collection, the chart can then reference the collection rather than having to build its own data set:<br />
<br />
select c001 link,<br />
to_numcer(c002) value<br />
from apex_collections<br />
where collectin_name = <your collection><br />
and ....<br />
<br />
You will be amazed as to how quickly the chart will render. Bam! It is displayed!<br />
<br />
To make performance even better, I only load up my collections once on the initial page load. The data is available already for all subsequent loads. The collections have already been populated so there is not data fetch required.<br />
<br />
The final result is a very slick dashboard that renders very quickly and puts the user in control of what they see and when they see it. No matter how complicated the chart - the rendering is quick and slick.Bruce Clarkhttp://www.blogger.com/profile/15072068162337172673noreply@blogger.com0