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

What is the foundation of Oracle Application Express (APEX)?

You are correct, it’s PL/SQL! I’m sure you knew that ;-)

So that means every improvement which is done for PL/SQL is good for us Oracle APEX developers, like the function result cache, the continue statement and much more which have been introduced in Oracle 11g.

Did you know that there is a new initiative by Steven Feuerstein and the Apex Evangelists guys to bring new enhancement requests to the attention of the Oracle PL/SQL product management?

Check out the new “I love PL/SQL, and …” web-site and help to improve PL/SQL!

Oracle 11g: Native Web Services

Just stumbled across an interesting paragraph in the New Features in Oracle XML DB for Oracle Database 11g Release 1 about Database Native Web Services.

The feature didn’t get that much attention yet on the Oracle 11g related blog postings, but according to the description it’s a real nice improvement compared to the 10g solution.

So what is it all about?

Continue reading