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?
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!
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?
Mine is PL/SQL Developer from allroundautomations.
Because it’s a PL/SQL development tool focused on PL/SQL development. Most of the other tools like TOAD or SQL Developer which I also used in the past, have great DBA/query functionality, but suck from a “state of the art” programming point of view.
Today I read an article on Grant Rolands blog about sending a SMS from Oracle Forms through a Web-Service.
I thought it would be nice to have the same in APEX and PL/SQL, too. For example if you have a system failure or get a new order in your shop…
A few days ago I came across an interesting posting on the AMIS blog about an XML(DB) presentation at the OOP 2007 conference.
The presentation done by Ken Atkins is also available online and is really worth reading it. It gives a comprehensive overview (with lot of examples) of the built-in XML processing features of the Oracle database.
Just read an interesting posting on Igor’s Oracle Lab about the planned PL/SQL enhancements in Oracle 11g. I really like some of the planned features. Want to get the full story? Check out the presentation done by Tom Kyte.