News, views, tips and tricks on Oracle and other fun stuff

User Exceptions Gotcha, When an Error Appears as a Success

This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at

Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.

The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:

Continue reading…

3 Comments | Filed in Oracle, Tips | Tags: , , , , ,

Oracle DB 11g Password Expiry Gotcha ↗

Jeff Hunter:

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an “ORA-28002: the password will expire within X days” error message if you keep the default value.

To change the PASSWORD_LIFE_TIME, you:
ALTER PROFILE default LIMIT password_life_time UNLIMITED;

Read more about 11g new security related features here.

1 Comment | Filed in Links, Oracle, Tips | Tags: , , ,

Beware of Comments in SQL

In SQL and PL/SQL there are two comment styles you can use, single-line and multi-line:

  • Multi-line comments begin with a slash and an asterisk (/*) and end with an asterisk and a slash (*/). the comment can span multiple lines.

  • single-line comments begin with -- (two hyphens) and end with a line break. The comment text cannot extend to a new line. So, a double hyphen anywhere on a line turns the rest of the line into a comment.

The turns the rest of the line into a comment can sometimes cause unexpected behavior. My coworker was working on a Oracle form. He created an LOV based on a query similar to this (not the actual query, modified for simplicity):

select employee_id, first_name
from employees
where manager_id = 100 -- The king
and job_id = 'ST_MAN'

The above query returned 5 rows when executed in SQL*Plus. However, when bringing up the LOV in the form, there were 14 records. Where did the extra 9 rows come from? After turning trace on and analyzing the trace file, he noticed that somehow the above query was passed to Oracle like this:

select employee_id, first_name
from employees where manager_id = 100 -- The king and job_id = 'ST_MAN'

Which means that whatever is after the -- was treated as a comment and, as a result, was not executed. The where clause was effectively truncated which explains why he was getting 14 rows instead of 5. The fix was simple:

select employee_id, first_name
from employees
where manager_id = 100 /* The king */
and job_id = 'ST_MAN'

So, what have we learned? We learned to avoid commenting using two hyphens and instead use the slash and asterisk /* */ commenting style inside SQL statements.

14 Comments | Filed in Oracle, Tips | Tags: ,


Lewis Cunningham’s “mini” tip about the NO_DATA_FOUND exception in PL/SQL is interesting. He shows how this exception is not always raised when you expect it to be raised. Here is another NO_DATA_FOUND gotcha you should be aware of: Continue reading…

11 Comments | Filed in Oracle, Tips | Tags: ,

LOBs Gotcha in ColdFusion

A few weeks ago I wrote a post about how to Save/Download files to/from Oracle using ColdFusion. The example I used in the post was about saving Excel files as a BLOB in an Oracle table.

Today, I ran across an issue with file sizes greater than 244 KB. I was testing with a 7 MB Excel file. The file was correctly saved in Oracle and I could see that the BLOB size was indeed 7MB (using dbms_lob.getlength). However, when I tried to download it, it would not download more than 244 KB.

After some research, it turns out that, in the ColdFusion administrator (CFMX V7), if you go to Data Sources under Data & Services and edit the data source you used to retrieve the BLOB, there is a setting under the Advanced Settings to Enable binary large object retrieval (BLOB). In my case, it was unchecked. So, make sure to check this checkbox if you want to return the entire contents of any BLOB column in the database for your data source. If not checked, ColdFusion MX retrieves the amount specified in the BLOB Buffer setting, which, in my case, was 250000 bytes or approximately 244 KB. That explains it!

To summarize, if your application uses BLOBs or CLOBs to store/retrieve data from the database and you do not know (or do not want to enforce) what the maximum size of your BLOB or CLOB data is, make sure to check the Enable binary large object retrieval (BLOB) or Enable long text retrieval (CLOB) settings for your datasource in the ColdFusion administrator.

4 Comments | Filed in ColdFusion, Oracle | Tags: ,


While reading the oracle-l mailing list, someone asked a question about (auto)commiting transactions in SQL Plus. Consider: Continue reading…

2 Comments | Filed in Oracle | Tags: ,

Flash CFFORM Gotcha

I had a problem with a flash CFFORM. It would not show up in Internet Explorer. However, Firefox had no problem displaying it. The flash form was part of an application using the Mach-II framework (which I’m converting now to Model-Glue). I had a general layout template in which I included the body of the main page. My layout template was like this (simplified here for demonstration purposes): Continue reading…

2 Comments | Filed in ColdFusion, Tips | Tags: , ,

ArrayDeleteAt Gotcha

Be careful when using ArrayDeleteAt. When an element is deleted, ColdFusion recalculates index positions. For example, in an array that contains the months of the year, deleting the element at position 5 removes the entry for May. After this, to delete the entry for June, you would delete the element at position 5 (not 6). Here is an example to demonstrate this behavior: Continue reading…

2 Comments | Filed in ColdFusion | Tags: