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
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.
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!
Just came across a very strange behavior of the DBMS_LDAP.simple_bind_s and APEX_LDAP.authenticate procedure which I used to do a basic LDAP authentication against our MS Active Directory server.
I used the following simple test code
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
( pDepartmentId IN NUMBER
FOR rEMP IN
( SELECT EMPLOYEE_ID
WHERE SALARY > getAvgDeptSalary(pDepartmentId)
(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?
Oracle APEX 3.0 contains a nice new feature you should be aware of. There is a new substitution variable APEX_DML_LOCK_WAIT_TIME which you can use to define how the behavior of the “Automatic Row Processing (DML)” and ApplyMRU/D processes should be in case if the processed row is locked.
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
Did you know that when you reference a non existing APEX item in your SQL statements or PL/SQL code, that you are not getting a runtime error? APEX will just return NULL in such a case!
Be aware when using bind variables or the V function to reference an Oracle APEX item in your SQL statements or your PL/SQL code that APEX/Oracle always stores this values as VARCHAR2’s in its internal arrays!