Caution when using PL/SQL functions in a SQL statement

Be cautious if you use PL/SQL functions/packages in your SQL statements, especially in the Where-Clause!

Why?

Because the function is probably called more often than you think it is! If you don't want to read the hole article, scroll down and check the APEX impact.


An example

Let's create the following simple function.

CREATE OR REPLACE FUNCTION getValue
( pValue IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  DBMS_Output.put_line
    ( 'called getValue with '||pValue||' at '||
      TO_CHAR(SYSDATE, 'HH24:MI:SS')
    );
  RETURN pValue;
END getValue;

And now look at the following SQL Statement where we call our new function with a constant value.

SELECT COUNT(*)
 FROM EMPLOYEES
WHERE SALARY = getValue(1)
;

How often do you think is the function called?

• One time because it always uses the same value as parameter?
• For each row which is checked by the query engine?
• Some number between one time and for all rows?

Check the DBMS_Output.

If you have bet your money on For each row which is checked by the query engine, then you are the winner! It's called 107 times! As often as we have rows in the table.

If we change the query to

SELECT COUNT(*)
 FROM EMPLOYEES
WHERE EMPLOYEE_ID > getValue(1)
;

How often to you think it is called? You have the same choice as before.

Check the DBMS_Output.

It's just called one time! What is the difference? In our second example the optimizer is using the EMP_EMP_ID_PK index and in that case as Tom Kyte explained me

The optimizer gets the start/stop range (the function might be called twice actually) and then it range scans with the constants.

So what's the deal all about it?

Keep in mind the performance issue when you have a function which is selecting against other tables and you use such a function in a where-clause, where 1.000's of records are processed!

Possible solutions?

You can change the query in the following way. The optimizer then knows that is ok when he executes the query just one time. So called scalar subquery caching, which works with all DB versions. Checkout AskTom about that term.

SELECT COUNT(*)
 FROM EMPLOYEES
WHERE SALARY = (SELECT getValue(1) FROM DUAL)
;

Or you take a look at the DETERMINISTIC clause which can specified when creating a function or a package functions. What does it do? It's a hint for the optimizer to indicate that the function returns the same result value whenever it is called with the same values for its arguments. But, as noted by Tom Kyte

10gr2 supports deterministic as an optimization in SQL for the first time. Prior to 10gr2 - deterministic was all about function based indexes only - the SQL engine ignored it.

So only if you have a 10.2.x database, using DETERMINISTIC will give you a speed up of your query. Use the above solution instead.

But let's change our function and see what we get now.

CREATE OR REPLACE FUNCTION getValue
( pValue IN NUMBER
)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
 DBMS_Output.put_line
   ( 'called getValue with '||pValue||' at '||
     TO_CHAR(SYSDATE, 'HH24:MI:SS')
   );
 RETURN pValue;
END getValue;

Run the first query again and you will see that the function is called just once!
Try the following SQL statement.

SELECT COUNT(*)
FROM EMPLOYEES
WHERE SALARY = getValue(SALARY)
;

How often do you think it is called? It's just called as often as there are different salaries in the table. With the old version of the function is called for each row again.

I know that the Oracle documentation about DETERMINISTIC says

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function.

Which I think is basically to see in relation to the function based index for which this "hint" has been introduced. From my observation, if you have such a function call in several SQL statements which are called after each other (eg in a anonymous pl/sql block), the function is executed again for each SQL statement. It's just a hint for the optimizer during execution of the current statement.

DON'T USE DETERMINISTIC if your function is changing some global variables (eg increase a counter) or doing DML and you expect that the function is called for each row!

And how is that all related to APEX?

Are you using the V or NV function in your SQL statements? In APEX 2.0 the V function isn't wrapped (in 2.2 it is) and I don't see that the DETERMINISTIC is used in the code... It's just memory processing they do in there, but if you call it for 1.000's of records...

Always use bind variables instead! Only use the V function in your packages, but never use them in your report SQL statements.

I have done a posting on OTN about speeding up the V function. Stay tuned.

UPDATE: See my posting Drop in replacement for V and NV function which contains a wrapper for the existing functions.

« ... Read full posting ... »

Referencing non existing APEX items

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!


It doesn't matter if you reference it with the bind variable- (eg :P8_TEST), V or NV function- (eg V('P8_TEST') ) or substitution- (eg. &P8_TEST.) syntax.

Just be aware of this behavior!

« ... Read full posting ... »

Find invalid PL/SQL code in your APEX application

Did you know that you can use the Database Object Dependencies report to find PL/SQL code in your application which doesn't compile? So that your users don't get a runtime error.

1. Open your application
2. In the Task sidebar click on View Application Reports
3. Update for 3.x: Click Shared Components and then in the Application section on Database Object Dependencies
4. For pre-3.0 installations: Click Application and then on Database Object Dependencies
5. Press the Compute Dependencies button
6. This will take some time...
7. Click onto the Parsing Errors link and you have all your invalid PL/SQL code snippets!
   

What does APEX/Oracle do in the background when you press the Compute Dependencies button?

It creates for each PL/SQL code a procedure, so that it can read the Oracle Dependency dictionary views to find references to other packages, tables, views, ...

Very clever! So they didn't have to write there own parser. And we can use the outcome to see which of the procedures didn't compile.

« ... Read full posting ... »

APEX items and implicit type conversion

Be aware when using bind variables or the V function to reference a 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!


So each time you reference an item with :YOUR_ITEM or V('YOUR_ITEM') the SQL/PL-Sql engine does a implicit data type conversion.

Don't rely on implicit data type conversion as the Oracle manual already says!

To ensure your program does exactly what you expect, use explicit conversions wherever possible.

Maybe it will work most of the time, but I would suggest to use the NV function to get the value of numeric items or use TO_NUMBER, TO_DATE, ... around your bind variables eg. TO_NUMBER(:YOUR_ITEM).

For details see the Oracle manuals:

• Data Conversion - Implicit and Explicit Data Conversion
• Converting PL/SQL Datatypes - Implicit Conversion
• Implicit Data Conversion Examples
  

« ... Read full posting ... »

Bored of setting the template property again and again?

If yes, continue reading. If not, continue doing it again and again, ... ;-)

For example you like the "Standard, Alternating Row Colors" template for reports or you don't want to write help texts (which developer does?) for your items, but the APEX builder always defaults the item to "Optional Label with Help".

You can change this defaults! And it will also help your team, so that everybody is using the same templates.

1. Go to Shared Components and then to Themes.
   
2. As View select Details and press Go.
3. Click onto the name of your current theme.
4. Change the Component and Region defaults as you want to have them.

« ... Read full posting ... »

Page rendering processing flow

Just came again across a feature of APEX which is worth mentioning it.

Have you ever had the problem, that the page processing flow of APEX was different then what you thought it should be? And it took you some time to figure out how APEX is processing the page?

Then maybe you should check out the "Event" view on the page definition page. It shows you a brief overview how the page is processed. It even shows you the page 0 stuff.

« ... Read full posting ... »

Update: Generic solution for depending select lists/lovs

Have updated the blog entry about "Generic solution for depending select lists/lovs". The description and the files cover now what do do in case of a version prior to 2.2.x.

Just curious, is somebody already using the solution in his APEX application?

« ... Read full posting ... »

Interesting APEX blog by Benjamin Wootton

Just found the APEX blog by Benjamin Wooton. Has some really interesting articles like the best practices for collaborative development or that you have to take care of the debug option when deploying your application. And different other tips and tricks.

Check it out!

« ... Read full posting ... »

Generic solution for cascading select lists/lovs

After implementing several times a select list/popup lov, which is depending on another item on the same page (see the AJAX example from Carl Backstrom), I thought that it was time to come up with a more generic solution where I don't have to implement javascript code and on-demand processes for each lov anymore.


I thought if the render engine of APEX is able to generate the SQL statements on the fly, I should be too ;-)

My goals where that I

• don't have to include any Javascript code or any other specific code onto the page and that I
  
• don't have to write an on-demand process for each select list/lov anymore.
• And of course I don't want to use selects-list with submit, I want that my application is "sexy" and uses AJAX for partial page reload!!! :-)
• So everything should be "out of the box" as APEX would already provide this feature.
  

My solution after thinking about it for a while

• Create a generic Javascript library which handles the dependencies between the items. It should be able to clear and repopulate all depending items, if the value of an item changes.
• Write a process which fires on page 0, which reads the APEX data-dictionary to determine the relationship between the different items and generate the necessary calls to my generic Javascript library. But which has to be aware of existing Javascript code which may has already been registered for an onchange event on the item.
  
• Write an on-demand process who is able to execute the select statement of the lov definition and return the result to the Javascript library (see the existing AJAX examples) which will update the select item on the page.

And finally the result! :-)

So I have put together some Javascript and PL/SQL code and a How-To-Do, so that you are able to integrate it into your existing applications.

The solution has been tested with APEX 2.2.1 and Firefox and IE 6. But it should also work for all 2.x versions.

Integration into your application

Follow the steps in my How to integrate posting.

Just want to use the cascading lov? Then you only have to create

• ApexLib - include Javascript library
• ApexLib - Before footer with the following code

  ApexLib_Lov.generateBrowserData;
  ApexLib_Browser.flushJsBuffer;

• Application Item APEXLIB_REFERENCE_TYPE and APEXLIB_REFERENCE_ID
• Application Process ApexLib_getLovResult

That's it! All the rest is done automatically when you execute your application.

So what did we just do?

We created a two new regions on the page 0 (page 0 is executed for every page), the first will add the Javascript library into the html-page, and the second will call the PL/SQL package which will determine the lov dependencies for a page. It will generate the necessary javascript code on the fly. The Javascript code will call the generic on-demand process with all the necessary item values the lov query requires. The PL/SQL package which the on-demand process is calling, executes the defined lov query with the values entered on the html-page. The result is send back and used to repopulate the lov on the HTML page.

BTW, it's no problem when you have your own javascript code in an "onchange" event, the code generated by the library will be executed after your code.
If you want to disable the dependency checking for a lov, just write $APEXLIB_IGNORE_LOV$ into the "comments" of the page item.

Known restrictions

• Doesn't support lovs in a report region (multi-record)
• Doesn't support lovs where the select statement is returned by a function
• Doesn't support lovs of type radio group or checkbox
• Doesn't use the translation table for the result values if the property is set

But I will work on them as soon as I have some time...

Try it if this generic solution will also works for you. Feedback always welcome!

PS: I wish that some similar functionality will make it into APEX 3.0...

Example

On apex.oracle.com I put together a simple example which you can try. Afterwards the steps to create it.

1. Create a new region of type "HTML" and set region template to "Form Region"
2. Add a new item with the following settings
• Type: Select List
• Select list control type: Select List (you don't need Select List with Submit, ... anymore!)
• Name: P4_REGION_ID
• Display Null: Yes
• Null Text: - select region -
  
• List of Values Query

  
  SELECT REGION_NAME AS D, REGION_ID AS R
    FROM REGIONS
   ORDER BY REGION_NAME
  

• Label: Region
• Item Source: Always null


3. Add another item to this region
• Type: Select List
• Select list control type: Select List
• Name: P4_COUNTRY_ID
• Display Null Option: Yes
• Null Text: - select country -
  
• List of Values Query

  
  SELECT COUNTRY_NAME AS D, COUNTRY_ID AS R
    FROM COUNTRIES
   WHERE REGION_ID = :P4_REGION_ID
   ORDER BY COUNTRY_NAME
  

• Label: Country
  
• Item Source: Always null
4. Add another item to this region
• Type: Select List
• Select list control type: Select List
• Name: P4_LOCATION_ID
• Display Null Option: Yes
• Null Text: - select location -
  
• List of Values Query

  
  SELECT CITY AS D, LOCATION_ID AS R
    FROM LOCATIONS
   WHERE COUNTRY_ID = :P4_COUNTRY_ID
   ORDER BY CITY
  

• Label: Location
  
• Item Source: Always null

Run the page and you will see that if you change the region all details will be cleared and repopulated. Change the Country and the Location will be cleared and re-populated.



In case

if it doesn't work as expected, check if the error console of your browser (Firefox: Tools/Error Console) shows some errors. I would recommend that you get a Javascript debugger like FireBug, set a breakepoint at vResult = vAjaxRequest.get("XML"); , step into the get("XML") and check the results there. Maybe there is some problem with your SQL statement. The responseText will show what APEX is sending back.

« ... Read full posting ... »