I apologize, but for those of you who do stop by for the latest adventures here at Alydan Consulting each week, you noticed that last week there was no new tip. There were a number of reasons for that, but the primary one is this week's topic, which revisits a tip from last year, Parsing a CSV File Using PL/SQL, now in our ebook, Oracle Tips from the Real World...52 Weeks with Alydan Consulting. The journey back to that code hammered home the following points:

• The SQL*Navigator PL/SQL Debugger is a useful piece of software to have available.
• Beware of null values!
• Even year old code can have a problem!

I was using a process that included the routine to parse a CSV file while working on an unrelated request. All of a sudden, oracle.exe was taking 100% of the CPU. Using the SQL*Navigator PL/SQL Debugger, I found that the routine was reading the last record of the CSV with no problem, then reading the file one more time and spiking the CPU when entering the parse routine. I looked at the file and found an empty line at the end of the file. That led me to this line:

Unfortunately, the variable a_file_rec was null, which meant the result of the length function was null. So the above condition never evaluated to true, and the process never exited the loop. The simple correction was the following::

EXIT WHEN l_column_pos > NVL(LENGTH(a_file_rec), 0);

With that change, the process completed successfully, the lesson of handling null values properly was learned once again, and  Oracle Tips from the Real World...52 Weeks with Alydan Consulting.has been revised.

Note: This tip was tested using Oracle9i.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.