Skip to main content


Showing posts from December, 2017

Implicit vs Explicit Cursor: Which should you use?

My post on Conditional Logic in PL/SQL generated some interesting discussion concerning whether to use an implicit cursor (SELECT-INTO) or an explicit cursor (OPEN-FETCH-CLOSE) when fetching a single row of data. "Hmmm," you might be saying to yourself, "a conversation about SQL queries in a post on conditional logic? That's strange." Ah, but maybe not so strange. Because as one person put it in the comments: Yes, this is true. You can use exception handling as a kind of conditional branching logic. But should you? Generally, no. It's a good idea to keep application logic out of exception sections. Developers don't expect or look for business logic in those sections. They are, after all, for dealing with exceptions - errors. So if you put lots of code in EXCEPTION WHEN clauses, they can be easily overlooked, making your code harder to debug and enhance. But that question - and the recommendation to avoid the exception section - then led to

PL/SQL 101: Nulls in PL/SQL

The Oracle Database supports a concept of a null value,  which means, essentially, that it has no value . The idea of having nulls in a relational database is controversial , but Oracle Database supports them and you need to know how they can impact your work in PL/SQL. First, and most important, remember that: Null is never equal to anything else, including null. And certainly 0. Null is never not equal to anything else, including null. DECLARE var INTEGER; BEGIN IF var = NULL THEN ... IF NULL = NULL THEN ... IF var <> NULL THEN ... IF NULL != NULL THEN ... END; you can rest assured that the code represented by "..." will never  be executed. Note: NULL in the above code is a literal value with a non-value of null. The same holds true for where clause predicates in a SQL statement. The following queries will never return any rows, regardless of the contents of the employees table. SELECT * FROM employees WHERE employee_id = NULL / SEL