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?
From my long time experience with Oracle, I noticed that a lot of people have problems with Outer Joins. Especially if the Outer Join also contains an additional restriction on the outer join table.
A few days ago Lucas Jellema from AMIS posted a real nice SQL statement to generate a character based Gantt-chart.
I think it’s a good demonstration of the possibilities and the power of SQL. Have a look!
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 a nice SQL puzzle on the AMIS blog called Writing a pure SQL based String Tokenizer and because Lucas wrote
Please share with me your thoughts on this – but I will share my attempt with you anyway
That’s why I thought I have to train my brain a little bit with a nice SQL statement… and here is my result using CONNECT BY instead of CUBE.
Be cautious if you use PL/SQL functions/packages in your SQL statements, especially in the Where-Clause!
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.