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

Oracle 11g some more infos

Just noticed that Oracle has put some more information about Oracle 11g onto OTN.

There are a lot of whitepapers available on OTN. For example the Oracle Database 11g New Features Overview which even mentions Oracle APEX! Ok, they mention it on the last page, but at least! :-)

Another interesting read is Oracle Database 11g Application Development which gives an interesting overview of the new PL/SQL enhancements in Oracle 11g like
Continue reading

Oracle SQL Developer 1.2 released

Maybe you have already read somewhere else that Oracle SQL Developer 1.2 has been released. Maybe you have also read in one of my previous postings that PL/SQL Developer is my favorite development IDE.

But why could SQL Developer still be interesting if you are an Oracle APEX developer and are using another IDE?

Because the new release 1.2 has some interesting features, especially for Oracle APEX developers.

Give it a try and check it out.