Caution with new “Runtime Where Clause” in Oracle APEX 3.1

Found out the hard way that the new property “Runtime Where Clause” for processes (eg. Fetch, DML) has a serious bug. The value of the property gets exported and is contained in the export file of an application, but it isn’t set when you import an application!!!

Because most of us are not developing on the production system, that bug is a real show stopper for that feature, because you never get the identical application onto the production system or to the customer.

The problem has been reported and according to Scott from the APEX team, it’s getting fixed in 3.1.1

Note: The “Optional Where Clause” of “Get Next or Previous Primary Key Value” processes is not effected by that behavior, this property was already available before Oracle APEX 3.1

Caution: Apex_Item.Date_Popup and Oracle APEX 3.1

Just to let you know in case you are hitting the following problem or plan to upgrade to 3.1. The version of Apex_Item.Date_Popup which comes with Oracle APEX 3.1.0.00.32 has a “small” bug. The Date Picker will always write the return value into the first row, independent for which row you have opened the Date Picker.

Carl has already provided a workaround for the bug and I’m pretty sure it will be fixed in the first patchset of Oracle APEX 3.1.

Attention! Are you using the Drop in replacement for V and NV function?

If you are using my Drop in replacement for V and NV function and you are upgrading to Oracle APEX 3.1 then you have to alter the functions to reference the new schema FLOWS_030100!!!

If you are not doing this your applications will not work because the functions are trying to read the session state from the old FLOWS_030000 schema, but there no values can be found for the current session which are stored in FLOWS_030100.

Just to let you know that you are not searching to long why your application doesn’t work after the upgrade.

Note: You may have installed this drop in replacements as optional part of the ApexLib Framework!

SQL embedded into PL/SQL

A few days ago I spoke with another developer about SQL embedded into PL/SQL code and how function calls are handled in the WHERE clause of SQL statements. There is sometimes confusion who (PL/SQL engine or SQL engine) is executing it, I thought it’s a good idea to write a posting about it.

Last year I have already blogged about it in Caution when using PL/SQL functions in a SQL statement, but that was in the context of writing a stand alone SQL statement for a report, … but what actually happens if you have a SQL embedded into PL/SQL like the following example procedure code in a package

PROCEDURE processEmps
  ( pDepartmentId IN NUMBER
  )
IS
BEGIN
    FOR rEMP IN
      ( SELECT EMPLOYEE_ID
          FROM EMPLOYEES
         WHERE SALARY > getAvgDeptSalary(pDepartmentId)
      )
    LOOP
        NULL;
    END LOOP;
END processEmps;

(Note: getAvgDeptSalary isn’t a very good example, because you could also do that with SQL only, but it’s just an example)

Very simple. A function in the WHERE clause which uses the procedure parameter pDepartmentId. What would you expect how the code/SQL is executed?

Continue reading

Slightly different behavior of $x in Oracle APEX 3.0

Just noticed a slightly different behavior of $x in Oracle APEX 3.0.

Probably many of you just use it to pass the id-string of an element (eg. $x(“P4_TEST”)) to this function to get the object of this field/div/… id, but you can also call it with an object and in that case the function will just return the object which has been passed in.

You may ask when do you need that? Continue reading

Incomplete/Broken HTML output

A few days ago an ApexLib user from Norway contacted me and told me that the framework doesn’t work and he is getting JavaScript errors.

I looked at the provided HTML output and I was scratching my head, because the output looked really strange and I couldn’t really come of with a good reason why the JavaScript code was broken. Another wired thing was that it worked sometimes for the page and sometimes not, the error was depending on the data which was displayed! Continue reading