Skip navigation.


A good Rule of Thumb

Denes Kubicek - Fri, 2015-03-06 09:03
I like the newest blog post from Joel Kallman and especially his rule of thumb:

"My rule of thumb - when you're editing code in a text area/code editor in the Application Builder of APEX and you see the scroll bar, it's time to consider putting it into a PL/SQL package."

I would go further and say that even the smallest application you create should have at least one package for all the PL/SQL code you write.
Categories: Development

IR Scrolling - With a Little Help From My Friends

Denes Kubicek - Mon, 2015-03-02 04:02
If you are working with interactive reports you will for sure be faced with a problem of wide reports. If you are taking care of the page layout and eventually have more than just an interactive report on the page, you will want to limit it's size to something making sense. The first problem will appear if you limit the width by setting the region attribute to something like this


and you will not see some of the columns:

If you add a scrolling by wrapping the region in a div and adding the following to the region header:

<div style="width:810px;overflow-x:scroll">

and closing it in the footer by adding:


you will be able to scroll with two ugly side effects:

  • The action bar will be included in the scrolling as well and disappear as you scroll to the right.
  • The sort widgets for the columns will appear on the wrong position the more you scroll.

  • You can solve this problem in the following way:

  • Remove the scrolling DIV from the region header / footer.
  • Use this java script in the page Function and Global Variable Declaration:

    function onload_ir(p_width, p_report_id){

    $('<div id="scroll_me" style="width:' + p_width + 'px;overflow-x:auto;display:inline-block"></div>').insertBefore('#apexir_DATA_PANEL'); $("#apexir_DATA_PANEL").appendTo("#scroll_me"); $("#apexir_DATA_PANEL").show();

    var or_Finished_Loading = gReport._Finished_Loading; gReport._Finished_Loading = function(){ or_Finished_Loading(); if(gReport.current_control=='SORT_WIDGET'){

    var offset_pos = $("#" + p_report_id ).position().left; var pos_left = $('#apexir_rollover').css('left'); pos_left = pos_left.replace('px',''); if (pos_left>p_width-100) {new_pos = parseFloat(pos_left) + parseFloat(offset_pos) - 25; $('#apexir_rollover').css('left', new_pos+'px');} }; }; };

  • Create a Dynamic Action which runs After Refresh (fire on page load option should be turned on) of the IR region and execute this script there:

    onload_ir(810, 7990109002761687)

  • 810 is the widht of the scolling region, which is a bit less then the total width of the region.

  • 7990109002761687 is the id of the data grid of the interactive report. You can find this id if you use firebug and scroll to the point where the data grid is placed.

  • What this script does is:

  • It will wrap the data grid into an additional div and add a scroll bar to it.
  • It will overwrite the IR onload function and add a sort widget positioning function to it in order to reposition the widget according to the scrolling.
  • The important part of the overloading function was done by Tom Petrus, who is a big help when it comes to tricky stuff like this.

    Now, once you have done that, your report will show up properly once you scroll it.

    Categories: Development

    Who enjoys the feather display of a male peacock?

    FeuerThoughts - Tue, 2015-02-24 08:25

    Who appreciates the display of feathers by a male peacock?
    Female peacocks seem to get a kick out of them. They seem to play a role in mating rituals.
    Who else? Why, humans, of course!
    We know that humans greatly appreciate those displays, because of the aaahing and ooohing that goes on when we see them. We like those colors. We like the irridescence. We like the shapes and patterns.
    If one were to speculate on why a female peacock gets all worked up about a particular male's feather display, we would inevitably hear about instinctual responses, hard-wiring, genetic determinism, and so on.
    And if one were to speculate on why a human goes into raptures, we would then experience a major shift in explanation. 
    Time to talk about anything but a physiological, hard-wired sort of response.
    No, for humans, the attraction has to do with our big brains, our ability to create and appreciate "art". And that is most definitely not something other animals do, right?
    Oh, sure, right. Like these instinctive, hard-wired bowerbird mating nests:

    That clearly has nothing to do with an aesthetic sense or "art". Just instinct.
    Why? Because we humans say so. We just assert this "fact."
    Most convenient, eh?
    Categories: Development

    APEX 5.0: the way to use Theme Roller

    Dimitri Gielis - Mon, 2015-02-23 15:52
    Once you have your new application created using the Universal Theme it's time to customise your theme with Theme Roller.

    Run your application and click the Theme Roller link in the APEX Developer Toolbar:

    Theme Roller will open. I won't go in every section, but want to highlight the most important sections in Theme Roller in this post:
    1. Style: there're two styles that come with APEX 5.0: Blue and Gray. You can start from one of those and see how your application changes color. It will set predefined colors for the different parts of the templates.

    2. Color Wheel: when you want to quickly change your colors, an easy way to see different options is by using the Color Wheel. You've two modes: monochroom (2 points) and dual color (3 points - see screenshot). By changing one point it will map the other point to a complementary color. Next you can move the third point to play more with those colors.

    3. Global Colors: if the Color Wheel is not specific enough for what you need, you can start by customising the Global Colors. Those are the main colors of the Universal Theme and used to drive the specific components. You can still customise the different components e.g. the header by clicking further down in the list (see next screenshot).

    4. Containers etc. will allow you to define the specific components. A check icon will say it's the standard color coming with the selected style. An "x" means the color was changed and an "!" means the contrast is probably not great.
    Original with styleAfter changing colors

    This is just awesome... but what if you don't like the changes you did?

    Luckily you can Reset either your entire style or you can refresh the specific section by clicking the refresh icon. There's also an undo and redo button. But that is not all... for power users when you hit "ALT" when hovering a color you can just reset that color! (only that color will get a refresh icon in it and clicking it will reset it)

    Note that all changes you're making are locally stored on your computer in your browsers cache (HTML5 local storage), so you don't effect other users by playing with the different colors.

    Finally when you are done editing your color scheme you can hit the Save As button to save all colors to a new Style. When you close Theme Roller the style will go back how it was.
    The final step to apply the new style so everybody sees that version, is to go to User Interface Details (Shared Components) and set the style to the new one.

    Note that this blog post is written based on APEX 5.0 EA3, in the final version of APEX 5.0 (or 5.1) you might apply the new style from Theme Roller directly.

    Want to know more about Theme Roller and the Universal Theme - we're doing an APEX 5.0 UI Training May 12th in the Netherlands.
    Categories: Development

    How Do You Manage Your PL/SQL Code Base?

    Gerger Consulting - Mon, 2015-02-23 06:53
    Do you put your PL/SQL code in version control? If not, you are missing out on a great way to increase your productivity. Join our webinar and learn how you can use Gitora, the free version control tool for PL/SQL to manage your PL/SQL code base.

    With Gitora you can:
    • Revert to a previous state of your code base automatically.
    • Create branches of your PL/SQL code base, switch between them and merge them whenever you want automatically.
    • Clone your code base and have your team edit the same logical PL/SQL code units at the same time without overwriting each others updates.
    • Pull changes from other developers and merge them easily.

    Sign up for the Gitora webinar now.
    Categories: Development

    The most frequently abused feature in APEX

    Denes Kubicek - Wed, 2015-02-18 16:02
    Today I participated in the Scott's survey:

    and there was one really interesting question:

    "What do you think is most frequently abused? (and why)"

    If APEX has any week points than it is definitely the fact that you can place your code almost everywhere. Especially PL/SQL chunks of code. If I start counting and list all the places it will be a very long list:

  • Page Read Only and Cache Page Condition
  • Region Conditional Display and Read Only Condition
  • Buttons Conditional Display
  • Items Conditional Display
  • Items Source value or expression
  • Items Post Calculation Computation
  • Items Default value
  • Items Conditional Display and Read Only Condition
  • Computations
  • Processes on Submit, on Load, on Demand
  • Validations and Validation Conditions
  • ...and yes, Dynamic Actions as my favorite.

  • There is of course more but I will stop counting here. If you start working with APEX this is great - you will easily get on target and have an application up and running in no time. A little bit of code here, a little bit there and there we go.

    This problem becomes obvious if you application is more than just a small and temporary solution shared between a couple of people. As an application grows it will start suffering from performance issues. Furthermore it will be hard to maintain it. It will have a dozens of complex pages with many items, many computations and processes, conditional buttons, validations and dynamic actions. If you practice writing anonymous PL/SQL blocks and you paste those wherever it is possible, your code will become redundant and slow. You will probably repeat the same code many times on your page. This means, your pages will require more processing time than they should. I would even go so far and say this is almost as bad as putting your business logic into triggers. Have you ever had a chance to debug such applications (and you didn't know the code was placed there)?

    The possibility to have so many options is of course great and useful. But it doesn't mean you should use all of the options. The other thing important to know is that you should never write anonymous PL/SQL blocks in your applications. Never! Whenever you need to use PL/SQL, you should use packages and place your code there. The probability that you will then repeat your code is quite low and for sure your application will run much faster.

    My proposal for the future version of APEX would be to have a subtitle for each of those containers saying "Handle with care" and providing some explanations why. I am serious here. Really.

    Categories: Development

    Alert Function

    Denes Kubicek - Wed, 2015-02-18 10:51
    In APEX you will probably use javascript alert function in many cases by saying:

    alert ('some message');

    The popup box you get looks a bit strange and outdated. As an alternative to this you may use this function call:

    function get_alert (p_message,p_id) {$("<div/>", { "html":p_message}).attr({"title":"Error has occurred!"}).dialog({
    buttons:{"Go to Error":function(){$(this).dialog("close");
    }); }

    As you can see in this example, you can add additional functions to this alert box in order to do something after closing of the alert message. You can choose to display the alert as a modal window as well.

    Categories: Development

    APEX Jobs

    Denes Kubicek - Tue, 2015-02-17 11:44
    Alle, die eine Beschäftigung mit Oracle APEX suchen, sollten hier reinschauen:

    Joel's Blog

    Auch einige Stellenangebote aus Deutschland und Schweiz.

    Categories: Development

    EM 12C RACI and Maintenance Task

    Arun Bavera - Fri, 2015-02-13 10:10
    TASK RESPONSIBLE ACCOUNTABLE CONSULTED INFORMED Define Monitoring Requirements Target Owners, Infrastructure Teams, EM Admin EM Admin Installation planning and architecture EM Admin EM Admin Target Owners, Infrastructure Installation and configuration of EM EM Admin EM Admin Defining Agent deployment and patching procedures and processes EM Admin EM Admin Security and User Administration EM Admin/Security Admin EM Admin Admin Group Creation EM Admin EM Admin Target Owners Agent Deployment (can be performed by target owners) Target Owners Target Owners EM Admin Agent Patching (can be performed by target owners) Target Owners Target Owners EM Admin Target Configuration and Availability Target Owners Target Owners Agent Troubleshooting Target Owners, EM Admin EM Admin Target Troubleshooting Target Owners Target Owners EM Admin Weekly/Monthly/Quarterly Maintenance EM Admin EM Admin Target Owners OMS Patching EM Admin EM Admin Target OwnersRECOMMENDED MAINTENANCE TASKS TASK DAILY BIWEEKLY MONTHLY QUARTERLY Review critical EM component availability X Review events, incidents and problems for EM related infrastructure X Review overall health of the system including the job system, backlog, load, notifications and task performance X Review Agent issues for obvious problems (i.e. large percentage of agents with an unreachable status) X Review Agent issues (deeper /more detailed review of agents with consistent or continual problems) X Review metric trending for anything out of bounds X Evaluate database (performance, sizing, fragmentation) X Check for updates in Self Update (plug-ins, connectors, agents, etc.) Note that there is an out-of-box ruleset that will provide notification for the availability of new updates X Check for recommended patches XReferences:
    Categories: Development

    Easy quiz: rownum < NaN

    XTended Oracle SQL - Tue, 2015-02-10 15:25

    As you know, NaN is a “Not a Number”.
    How do you think, what would be the result of the following query? (0f/0 == NaN)

    select count(*) cnt from dual where rownum < 0f/0;

    SQL> select count(*) cnt, 0f/0 from dual where rownum < 0f/0;
           CNT       0F/0
    ---------- ----------
             1        Nan
    1 row selected.

    Ok, when you know the result, try to guess what will return this query:

    select count(*) cnt from dual where 1f/0 < 0f/0;
    SQL> select count(*) cnt from dual where 1f/0 < 0f/0;
    1 row selected.

    [collapse] PS

    1 * X < 0 * Y

    So we know now which non-negative values we should to substitute for X and Y in this expression, to make it true.

    Categories: Development

    select * from table where rownum=1

    XTended Oracle SQL - Sun, 2015-02-08 17:40

    I never thought I would have to optimize so simple query as

    select col1, col2, col4, col7 from table where rownum=1

    (even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)
    But a few days ago frequent executions of this query caused big problems on the one of our databases( because of adaptive serial direct path reads.

    I don’t know why, but I felt intuitively that full table scan with “First K rows” optimization (“_optimizer_rownum_pred_based_fkr“=true) should turn off adaptive serial direct path reads. It seems quite logical to me.

    PS. Unfortunately I had a little time, so I didn’t investigate what process and why it was doing that, I just created profile with “index full scan” access, and it completely solved the problem.

    Categories: Development

    Changing Target ownership after the discovery of Targets in EM12c

    Arun Bavera - Thu, 2015-02-05 16:29
    Building emcli commands to Change ownership of  Targets

    select 'emcli change_target_owner -target="'||target_name||':host" -owner="EM_ADMIN" -new_owner="WEB_ADMIN"' from mgmt$target where target_type='host' and target_name like 'mypattern%'


    Categories: Development

    Developing On-Device Java Mobile Apps for iOS...and Android Too

    Shay Shmeltzer - Wed, 2015-02-04 15:25

    At the last JavaOne conference I presented a session titled "Developing On-Device Java Mobile Apps for iOS...and Android Too"

    The recording of this session just became available, and I wanted to share it with you.

    This session should be a good introduction to how Oracle enables Java developers to take their skills to the mobile world.

    The first 28 minutes provide the overview, but if you are not into slides fast forward to minute 29 and start watching the extensive demo of developing an iOS application with Java and Eclipse. 

    &amp;amp;lt;br /&amp;amp;gt;

    Categories: Development

    INDEX FULL SCAN (MIN/MAX) with two identical MIN()

    XTended Oracle SQL - Wed, 2015-02-04 09:39

    I’ve just noticed an interesting thing:

    Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:

    SQL> explain plan for
      2  select
      3     min(ID)      as x
      4  from tab1
      5  where ID is not null;
    SQL> select * from table(dbms_xplan.display);
    Plan hash value: 4170136576
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    |   0 | SELECT STATEMENT            |         |     1 |     4 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
    |   2 |   FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
    Predicate Information (identified by operation id):
       3 - filter("ID" IS NOT NULL)
    Test tables
    create table tab1(id, x, padding)
       with gen as (select level n from dual connect by level<=1000)
       select g1.n, g2.n, rpad(rownum,10,'x')
       from gen g1,gen g2;
    create index ix_tab1 on tab1(id, x);
    exec dbms_stats.gather_table_stats('','TAB1');

    But look what will happen if we add one more “MIN(ID)”:

    SQL> explain plan for
      2  select
      3     min(ID)      as x
      4   , min(ID)+1000 as x1000
      5  from tab1
      6  where ID is not null;
    SQL> select * from table(dbms_xplan.display);
    Plan hash value: 3397888171
    | Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    |   0 | SELECT STATEMENT      |         |     1 |     4 |  3075  (17)| 00:00:02 |
    |   1 |  SORT AGGREGATE       |         |     1 |     4 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| IX_TAB1 |   999K|  3906K|  3075  (17)| 00:00:02 |
    Predicate Information (identified by operation id):
       2 - filter("ID" IS NOT NULL)

    At first I thought it was the old limitation from “MIN/MAX index access is not used if query has multiple MIN/MAX functions (Doc ID 316467.1)”, but we can get same plan by using hint:

    SQL> explain plan for
      2  select/*+ index(tab1) */
      3     min(ID)      as x
      4   , min(ID)+1000 as x1000
      5  from tab1
      6  where ID is not null;
    SQL> select * from table(dbms_xplan.display);
    Plan hash value: 4170136576
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    |   0 | SELECT STATEMENT            |         |     1 |     4 |  3433  (22)| 00:00:02 |
    |   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
    |   2 |   FIRST ROW                 |         |   999K|  3906K|  3433  (22)| 00:00:02 |
    |*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |   999K|  3906K|  3433  (22)| 00:00:02 |
    Predicate Information (identified by operation id):
       3 - filter("ID" IS NOT NULL)

    So, we can see that the real problem lies in the wrong cardinality calculation.

    Of course, we can avoid this problem using the old method – with simple inner view:

    SQL> explain plan for
      2  select x, x+1000
      3  from (
      4        select
      5           min(ID)      as x
      6        from tab1
      7        where ID is not null
      8       );
    SQL> select * from table(dbms_xplan.display);
    Plan hash value: 2347179087
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    |   0 | SELECT STATEMENT             |         |     1 |    13 |     3   (0)| 00:00:01 |
    |   1 |  VIEW                        |         |     1 |    13 |     3   (0)| 00:00:01 |
    |   2 |   SORT AGGREGATE             |         |     1 |     4 |            |          |
    |   3 |    FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
    |*  4 |     INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
    Predicate Information (identified by operation id):
       4 - filter("ID" IS NOT NULL)
    Categories: Development

    New Continuous Integration tutorial published

    Lynn Munsinger - Mon, 2012-07-02 09:44
    Hot off the press – a new continuous integration tutorial. It’s really not just about continuous integration, though! You’ll find it useful even if you aren’t using a continuous integration server like Hudson. It’s useful if you are doing any part of the scenario it documents: Setting up Team Productivity Center for your team and [...]

    Advanced ADF eCourse, Part Deux

    Lynn Munsinger - Tue, 2012-06-19 15:11
    In February, we published the first in a series of FREE(!) online advanced ADF training: The response to that course has been overwhelmingly positive as more and more people are moving past the evaluation/prototype stages with ADF and looking for more advanced topics. I’m pleased to relay the good news that the 2nd part [...]

    Fun with Hudson, Part 1.1

    Lynn Munsinger - Tue, 2012-06-05 09:19
    Earlier I posted that I had used the following zip command in the ‘execute shell’ action for my Hudson build job: zip -r $WORKSPACE/builds/$JOB_NAME-$BUILD_NUMBER * -x ‘*/.svn/*’ -x ‘*builds/*’ This zips up the content of the exported source, so that I can send it on to team members who need the source of each build [...]

    Hiring a Curriculum Developer

    Lynn Munsinger - Tue, 2012-05-15 09:34
    If you are an instructional designer with an eye for technologies like ADF, or if you are an ADF enthusiast and excel at creatively producing technical content, then ADF Product Management would like to hear from you. We’re looking for a curriculum developer to join our ADF Curriculum team, which is tasked with ensuring that [...]

    Hiring a Curriculum Developer

    Lynn Munsinger - Tue, 2012-05-15 09:34
    If you are an instructional designer with an eye for technologies like ADF, or if you are an ADF enthusiast and excel at creatively producing technical content, then ADF Product Management would like to hear from you. We’re looking for a curriculum developer to join our ADF Curriculum team, which is tasked with ensuring that [...]

    New ADF Insider on Layouts

    Lynn Munsinger - Mon, 2012-03-26 13:22
    I’ve published an ADF Insider session that helps de-mystify the ADF Faces components and how to work with them (and not against them), when building ADF applications. There’s also some great information on building ADF prototypes. Take a look here: