When I get the result of my PL/SQL quiz for the day, I’m pleased when I got it right, but if I got it wrong, I’m either annoyed or overjoyed:
- If I disagreed with the result, I’m annoyed.
- If I agreed with the result, I’m overjoyed – because I learned something new, or I was reminded of something I should have remembered.
Option #2 was my experience this morning – yesterday’s quiz featured the following code snippet:
...
EXECUTE IMMEDIATE 'update my_table set my_column = :value'
USING NULL;
...
This was one of four other, very similar, options – and I failed to notice that this version was binding NULL directly into the statement, instead of using a variable as any normal, reasonable, rational human being would. This snippet raises PLS-00457: expressions have to be of SQL types, which in this case is due to the fact that NULL is of no particular SQL type.
If one wanted to bind a literal NULL into a statement such as the one above, you don’t necessarily need a variable:
...
EXECUTE IMMEDIATE 'update my_table set my_column = :value'
USING '';
...
Proving that while ” is NULL, NULL is not ” – they are not always interchangeable.
P.S. please ignore the post title – I know it is incorrect to write ” = NULL or NULL != ” – but it wasn’t meant to be code, ok?

Today, grasshopper, you will learn the Way of the Template. The Templating Way is the path by which complex output is produced in a harmonious fashion.
The Templating Way does not cobble a string together from bits and pieces in linear fashion.
htp.p('<HTML><HEAD><TITLE>'||:title
||'</TITLE></HEAD><BODY>'
||:body||'</BODY></HTML>');
The Templating Way separates the Template from the Substitutions; by this division is harmony achieved.
DECLARE
template VARCHAR2(200)
:= q'[
<HTML>
<HEAD>
<TITLE> #TITLE# </TITLE>
</HEAD>
<BODY> #BODY# </BODY>
</HTML>
]';
BEGIN
htp.p(
REPLACE( REPLACE( template
,'#TITLE#', :title)
,'#BODY#', :body)
);
END;
It is efficient – each substitution expression is evaluated once and once only, even if required many times within the template.
The Templating Way makes dynamic SQL easy to write and debug. It makes bugs shallower.
SELECT REPLACE(REPLACE(REPLACE(q'[
CREATE OR REPLACE TRIGGER #OWNER#.#TABLE#_BI
BEFORE INSERT ON #OWNER#.#TABLE#
FOR EACH ROW
BEGIN
IF :NEW.#COLUMN# IS NULL THEN
SELECT #TABLE#_SEQ.NEXTVAL
INTO :NEW.#COLUMN#
FROM DUAL;
END IF;
END;
]', '#OWNER#', USER)
, '#TABLE#', cc.table_name)
, '#COLUMN#', cc.column_name) AS ddl
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_type = 'P'
AND c.constraint_name = cc.constraint_name
AND cc.column_name like '%NO';
The Templating Way is simple, but looks complex to the uninitiated. It is readable, and affords maintainability.
It rarely pays to answer too quickly.
Yesterday’s question at the PL/SQL Challenge was about aliases for expressions in cursor-based record types.
DECLARE
CURSOR profitable_internet_stocks
IS
SELECT SUM (share_price) FROM nasdaq_listings
WHERE profits > 0 AND sector = 'INTERNET';
few_and_far_in_between profitable_internet_stocks%ROWTYPE;
BEGIN
OPEN profitable_internet_stocks;
FETCH profitable_internet_stocks
INTO few_and_far_in_between;
CLOSE profitable_internet_stocks;
/*[display_statement]*/
END;
The question asked what code, inserted where the “display_statement” comment is, would display the result of the query. Without thinking too hard about it, I ticked the fourth option as correct:
DBMS_OUTPUT.PUT_LINE (few_and_far_in_between.sum_share_price);

I had this stupid idea that Oracle would step in and magically create an alias for the column*. A millisecond after clicking “Accept”, I realised I’d got it wrong. I’d assumed that at least one of the answers were likely to be correct, which is obviously not true.
* EDIT: Gary Myers has blogged about this too and explains that Oracle does, indeed, create an alias.

I just wanted to bring attention to some very interesting discussion (that’s been going on for years now) regarding Table APIs (TAPI) versus Transactional APIs (XAPI). Some very nice answers, as well as a bit of controversy 🙂
Steven Feuerstein lists seven excellent “Golden Rules” in his presentation (via Eddie Awad) and says “Don’t repeat anything. Aim for a Single Point of Definition for every aspect of your application – formulas, business rules, magic values, SQL statements.” giving the following code as exhibit A:
I’m guessing in his presentation he spoke about various things that could be done to improve this code, but they’re not in the PDF; so I’d like to give it a go myself and see how much we can improve the maintainability of this code by reducing hard-coding.
1. Type Declarations
Instead of declaring parameters and variables as NUMBER, VARCHAR2 etc, these should use the %TYPE operator so that they are automatically synchronized with the datatype from the table columns they represent:
PROCEDURE process_employee
(department_id_in IN employees.department_id%TYPE)
IS
l_id employees.employee_id%TYPE;
l_salary employees.salary%TYPE;
...
l_name, however, is not based on any table column we know of at this point; so there is no %TYPE we can use for it. But bear with me, we’ll fix this later.
2. Magic Values
This one’s a no-brainer: that “10000000” is obviously a magic value that some bean-counter decided was the correct threshold for the CEO’s salary. Whatever.
You might define this as a constant defined in a global package specification, e.g.
CREATE PACKAGE employee_constant AS
ceo_salary_threshold CONSTANT employees.salary%TYPE := 10000000;
END employee_constant;
Personally, I’d suspect that the business will review and revise this number from time to time, to keep up with inflation; so we might end up needing a database table to store the current threshold, plus a date range for which the threshold applies. I’d then add an interface on top of this table so that queries and procedures don’t need to know how to get the current threshold. We can retrofit this later by changing ceo_salary_threshold into a function instead of a constant. That’s a bit beyond the scope of this exercise, however.
3. Formatting Rules
The rule about formatting an employee name as “LAST,FIRST” is duplicated in a comment and in the SELECT statement; and chances are it will be required elsewhere in the application as well. My preferred method for creating a SPOD for this sort of business rule used to be to move the implementation into a view, e.g.:
CREATE VIEW formatted_employees AS
SELECT employees.*,
employees.last_name || ',' || employees.first_name
AS full_name
FROM employees;
COMMENT ON COLUMN formatted_employees.full_name
IS 'Full name: LAST COMMA FIRST (ReqDoc 123.A.47)';
This view is what I like to call a “formatting” view: it is only allowed to query one table, it contains no WHERE, GROUP BY or HAVING clauses, and it selects all the columns from the table. The view can be used almost anywhere the table may be used. It adds additional columns that format the data in various ways. If need be, we can even add INSTEAD OF triggers to handle inserts/updates on the derived columns – if the business rules make the conversion from derived-to-underlying-column well defined.
So, now we can redefine the cursor as:
CURSOR emps_in_dept_cur
IS
SELECT employee_id, salary, full_name AS lname
FROM formatted_employees
WHERE department_id = department_id_in;
Notice that I don’t call the column “last_comma_first” or anything like that – that would again be hard-coding the business rule, which would then be replicated throughout the application. In Oracle 11g, however, I think it might be better to create virtual columns on the table instead:
ALTER TABLE employees ADD (
full_name VARCHAR2(100)
GENERATED ALWAYS AS (last_name || ',' || first_name) VIRTUAL
);
COMMENT ON COLUMN employees.full_name
IS 'Full name: LAST COMMA FIRST (ReqDoc 123.A.47)';
CURSOR emps_in_dept_cur
IS
SELECT employee_id, salary, full_name AS lname
FROM employees
WHERE department_id = department_id_in;
The virtual column can have its own stats, and even an index if needed for querying.
Another option would be to create a function that does this formatting:
CREATE FUNCTION employee_full_name
(last_name IN employees.last_name%TYPE,
first_name IN employees.first_name%TYPE)
RETURN VARCHAR2 DETERMINISTIC IS
--Full name: LAST COMMA FIRST (ReqDoc 123.A.47)
BEGIN
RETURN last_name || ',' || first_name;
END employee_full_name;
We could call this function from the procedure or the view, but if we’re on 11g there’s no reason we can’t create a virtual column on it:
ALTER TABLE employees ADD (
full_name VARCHAR2(100)
GENERATED ALWAYS
AS (employee_full_name(last_name,first_name)) VIRTUAL
);
Another advantage to using the view or a virtual column is that we can now remove the “VARCHAR2 (100)” from the variable declaration, e.g.:
l_name employees.full_name%TYPE;
4. Cursor Parameter
The cursor refers directly to the parameter to the procedure, which is a no-no – this couples the cursor too much with the procedure, i.e. we can’t re-use it elsewhere unless we always define a variable “department_id_in”. Instead, we should use a cursor parameter:
CURSOR emps_in_dept_cur
(department_id_in IN employees.department_id%TYPE)
IS
SELECT employee_id, salary, full_name AS lname
FROM employees
WHERE department_id = emps_in_dept_cur.department_id_in;
The addition of the context “emps_in_dept_cur.” is not strictly necessary, but it is good practice to define the scope of all variables so that unrelated changes (e.g. the addition of a column called “department_id_in”) don’t change the code.
5. Cursor Row Type
What if we need to add 10 more columns to the cursor? At the moment we’re adding one more variable for each column of the cursor, and specifying it three times (variable declaration, cursor SELECT clause, and the FETCH INTO). We can reduce this to just once by declaring a cursor row type instead:
PROCEDURE process_employee
(department_id_in IN employees.department_id%TYPE)
IS
CURSOR emps_in_dept_cur
(department_id_in IN employees.department_id%TYPE)
IS
SELECT employee_id, salary, full_name lname
FROM employees
WHERE department_id = emps_in_dept_cur.department_id_in;
TYPE emps_in_dept_cur_type IS emps_in_dept_cur%ROWTYPE;
emp emps_in_dept_cur_type;
BEGIN
OPEN emps_in_dept_cur;
LOOP
FETCH emps_in_dept_cur
INTO emp;
...
6. Don’t COMMIT
Procedures should rarely COMMIT (there are very few exceptions to this rule, e.g. procedures declared as autonomous transactions). Transactional control should be left to the calling process – this process might need to be done along with a number of other changes elsewhere, and we would want to either COMMIT or ROLLBACK all the changes together as one transaction. What if the next procedure raised an error and we had to rollback? Our system would be left in an inconsistent state.
7. Error Package
That RAISE_APPLICATION_ERROR hard-codes an error code and an error message. What if we type the error number wrong somewhere? If the calling process handles ORA-20907 in some fashion, but we mistype it as -20908 in one procedure, the calling process will not handle it.
We could declare an exception instead, e.g. in a global package specification:
CREATE PACKAGE employee_exception AS
invalid_dept_id EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_dept_id, -20907);
END employee_exception;
Now, our exception handler can raise just the one exception:
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE employee_exception.invalid_dept_id;
However, we’ve now lost the error message. It would be better to create an error-handling package instead:
CREATE PACKAGE employee_error AS
invalid_error_no CONSTANT NUMBER := -20000;
invalid_error_no_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_error_no_exception, -20000);
invalid_dept_id CONSTANT NUMBER := -20907;
invalid_dept_id_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_dept_id_exception, -20907);
PROCEDURE raise_exception (error_no IN NUMBER);
END employee_exception;
CREATE PACKAGE BODY employee_error AS
PROCEDURE raise_exception (error_no IN NUMBER) IS
BEGIN
CASE error_no
WHEN invalid_dept_id
THEN RAISE_APPLICATION_ERROR(invalid_dept_id, 'Invalid department ID');
ELSE RAISE_APPLICATION_ERROR(invalid_error_no, 'Bug: invalid error number');
END;
END message;
END employee_exception;
EDIT: PRAGMA EXCEPTION_INIT only accepts literal numbers for its second parameter (or else you get PLS-00702 at compile time) – fixed accordingly
Now, our exception handler is nicely modular:
EXCEPTION WHEN NO_DATA_FOUND THEN
employee_error.raise_exception(employee_error.invalid_dept_id);
So now, our code looks like this:
PROCEDURE process_employee (department_id_in IN employees.department_id%TYPE)
IS
CURSOR emps_in_dept_cur (department_id_in IN employees.department_id%TYPE)
IS
SELECT employee_id, salary, full_name lname
FROM employees
WHERE department_id = emps_in_dept_cur.department_id_in;
TYPE emps_in_dept_cur_type IS emps_in_dept_cur%ROWTYPE;
emp emps_in_dept_cur_type;
BEGIN
OPEN emps_in_dept_cur;
LOOP
FETCH emps_in_dept_cur
INTO emp;
IF emp.salary > employee_constant.ceo_salary_threshold THEN adjust_comp_for_ceo (emp.salary);
ELSE analyze_compensation (emp.employee_id, emp.salary, employee_constant.ceo_salary_threshold); END IF;
EXIT WHEN emps_in_dept_cur%NOTFOUND;
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
employee_error.raise_exception(employee_error.invalid_dept_id);
END;
One final change that one might make here is to move the SQL query right out of the procedure and use a ref cursor instead, supplied by a central “employee_cursor” package.
There are probably plenty of other changes we could make to improve the maintainability of this code further.
What do you think?
My morning routine for the last few weeks has had a small addition – just a few minutes to log on The PL/SQL Challenge at 8am (WST) and answer a quiz question.
This is a great way to test the depth and breadth of your knowledge of Oracle PL/SQL. Whether you’re a beginner in the field or have years of experience, I’m certain you’ll occasionally learn something new – plus, it’s completely free!
Some of the questions are fairly general, others are trivial; while they’re never “trick” questions, they can still trip you up if you’re not observant!
I think I have an unfair advantage, being in this timezone – early morning is when I am most alert and ready to tackle any challenge.
A couple of things I’ve learned recently:
UTL_FILE.putf
can be used if you want to use C-style “printf” syntax, e.g. '\n'
SIGNTYPE
is a standard subtype that can only take values of NULL
, -1
, 0
or 1
(I’ve learned a few other things as well, but I’ve forgotten what they were :|)
DECLARE
lexists BOOLEAN;
lfile_len NUMBER;
lblocksize NUMBER;
BEGIN
UTL_FILE.fgetattr(
location => 'a',
filename => 'b',
exists => lexists,
file_length => lfile_len,
blocksize => lblocksize);
END;
I was trying to use this procedure in a 9i database and kept getting:
PLS-00103: Encountered the symbol ">"...
– complaining about line 8 (the “exists” parameter). If I removed the parameter names, it worked fine. Something was wrong with that “exists” parameter name.
In the 9i and 10g documentation:
UTL_FILE.FGETATTR(
location IN VARCHAR2,
filename IN VARCHAR2,
exists OUT BOOLEAN,
file_length OUT NUMBER,
blocksize OUT NUMBER);
In the 11g documentation:
UTL_FILE.FGETATTR(
location IN VARCHAR2,
filename IN VARCHAR2,
fexists OUT BOOLEAN,
file_length OUT NUMBER,
blocksize OUT BINARY_INTEGER);
Ah – the parameter was actually called “fexists”. Ok. Try again:
PLS-00306: wrong number or types of arguments in call to 'FGETATTR'
Aaarrgh. Time for more googling.
According to psoug:
utl_file.fgetattr(
location IN VARCHAR2,
filename IN VARCHAR2,
fexists OUT BOOLEAN,
file_length OUT NUMBER,
block_size OUT BINARY_INTEGER);
Thank goodness I’ve got access to more than just the Oracle docs!
If, by some great random cosmic chance, you are a reader of this blog, but not of Tom Kyte‘s, then you would have missed this post:
NO_DATA_NEEDED – something I learned recently
It appears to have been documented in the 9i documentation, complete with spelling error:
ORA-06548, 00000, "no more rows needed"
Cause: The caller of a pipelined function does not
need more rows to be produced by the pipelined
function.
Action: Catch the NO_DATA_NEEDED exception is an
exception handling block.
Mind you, it’s not all that obvious since if the pipelined function does not handle the exception, nothing goes wrong – the exception is never raised by the calling SQL statement. It’s not obvious when ORA-06548 would ever be raised.
EDIT:
Looks like ORA-06548 can appear in the error stack.
As Feuerstein says,
We all know that hard-coding is a bad thing in software. But most developers think of hard-coding simply as typing a literal value into your program whenever you need it.
…
So what’s wrong with doing this? Nothing – as long as the value is never going to change. But what’s the chance of that happening? In fact, what’s the chance of anything staying the same (never changing) in our application requirements and resulting code?
Almost nil.
I agree with most of what Steven says in this post, except for the “Almost nil” part. In my experience, developers who have slavishly converted all literal values in their applications to constants have made life much more difficult for subsequent maintenance and performance tuning.
I would contend that literal values should be hard-coded throughout the code when those values will not change, because they cannot change. The main example of this kind of value is that of hidden magical ID numbers, that crop up especially often in database designs featuring any kind of EAV pattern. These designs usually feature some kind of metadata table, e.g.:
PROPERTYTYPES (
propertyid,
code,
description,
datatype, length, etc.)
and sprinkled throughout the codebase, like sand in your toddler’s nappy after a trip to the beach, are statements like this:
SELECT value INTO custname
FROM propertyvalues
WHERE entityid = p1
AND propertyid = cNAME;
(where cNAME
is a constant that happens to be set to 30456 or something like that)
It gets worse when they need more than one property about an entity:
SELECT a.value, b.value, c.value
INTO custname, custphone, custaddress
FROM propertyvalues a, propertyvalues b, propertyvalues c
WHERE a.entityid = p1 AND b.entityid = p1 and c.entityid = p1
AND a.propertyid = cNAME
AND b.propertyid = cPHONE
AND c.propertyid = cADDRESS;
Someone might say, “that’s good, isn’t it? The hardcoded literal values have been stored once and once only in the constant declarations, and they can be used everywhere. If we want to change a property ID we can change it in one place and everything still works.”
Wrong – for two reasons.
Firstly, it’s not just one place – if you change the property ID, you have to also change the data in all the tables that point to that property. You also have to change code in that frontend UI that can’t read those constants, or in that external process that inexplicably was written with its own logic around those particular ID values (and search-and-replace won’t work because they’ve written some braindead code like this: if propertyid > 30453 & propertyid < 30458 { ... }
). The larger, more complex and widespread the codebase, the more it’s just not going to change.
Secondly, WHY? Why would you ever want to change these IDs? Much preferable to hard-code those ID values everywhere. If you stick to using constants where it makes sense (like the “maximum salary” that Steven had in his excellent example), then your hard-coded literals will tell future developers one important and life-preserving message:
“Do not change this code.”
Jeffrey Kemp
2 March 2010
PL/SQL /
Short-circuit evaluation!
Oracle Differences between NVL and COALESCE