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

Are you already using the Caching feature of Oracle APEX 3.0?

In case if you don’t read the Oracle APEX OTN forum all the time. Recently there was an interesting thread about the new Page/Region Caching feature of Oracle APEX 3.0

The thread contains some insides from the APEX developers how to use this new feature. The provided information will help you to have a smooth start.

Check it out V3 Caching – any more info? for more details.

Drop in replacement for V and NV function

If you have read my previous posting about Caution when using PL/SQL functions in SQL statements and a related thread on the OTN forum, you know that using the V or NV function in your SQL-where clause can have a performance impact on your query.

I have created wrapper functions for V and NV which use the DETERMINISTIC optimizer hint, so that the optimizer/query engine just calls them once for the query and not for every row.
Continue reading