One thing that bothers a lot of PL/SQL developers is when they have to select some data from a table into local variables. The problem is that the data may or may not exist (i.e. return 0..1 rows). They’re several ways to write this code, some taking longer than others which is the pain point for developers.
I’ve listed out various ways to do this in a blog post and hopefully it’ll help you find some easier ways to solve this issue.
The first example is the classic case of handling the 0..1 rows issue in PL/SQL. They’re a few issues (not problems) that I have with this. The first is that it adds some additional lines of code (highlighted) which can make the code harder to follow. The other issue is that instead of using “WHEN no_data_found” some developers tend to use “WHEN OTHERS” which is guaranteed to give you some false positives in the long run.
Read the examples in full on my blog.
2 Comments
Jan Leers
15/06/2012
I tend to look as this as a advantage instead of a disadvantage. If a function is called with a non-existing emp_id, I would like to know that this has happened, so I can investigate what is going wrong in my code.
However, if this is expected behaviour, I would like this to be clearly noticable in the code, either with NO_DATA_FOUND or by fetching from a cursor.
Kind regards,
Jan Leers
Martin Giffy D'Souza
21/06/2012
Hi Jan,
This article has spawned a lot of feedback from both this site and my blog. I think I'll have to write a follow up with other people's comments and fully explain how/when to use some of my logic that I described on my post as you need to know your data properly before using the SELECT INTO techniques that I described.
Thanks for your feedback,
Martin
No trackbacks yet.