Posts

Showing posts from August, 2018

Code You Should Never See in PL/SQL

Image
If you ever run across any of the following, apply the suggested cleanup, or contact the owner of the code, or run for the hills.

And I am pretty sure many of my readers will have suggestions for other code that should never appear in your PL/SQL programs. Let me know in the comments and I will add them to the post (giving me all the credit of course - no, just joking! YOUR NAME will be bright lights. :-) ).


Set default value to NULL

Whenever you declare a variable it is assigned a default value of NULL. So you should not explicitly provide NULL as a default value. It won't do any harm, but it will tell others who read your code that your understanding of PL/SQL is, shall we say, incomplete.

Bad Code

DECLARE l_number NUMBER := NULL; BEGIN ... END;
Cleaned Up

Just remove the assignment.

DECLARE l_number NUMBER; BEGIN ... END;
Select from DUAL for....just about anything

A long, long time ago, before PL/SQL was all grown up, it didn't have native implementations for some…

The PL/SQL Collection Resource Center

Image
Collections (Oracle PL/SQL's data structure to implement arrays, lists, stacks, queues, etc.) are not only handy in and of themselves, but are used for many key features of this powerful database programming language, including:
High performance querying with BULK COLLECTSuper-fast, bulk non-query DML operations with FORALLTable functions (functions that can be treated like a table in a SELECT's FROM clause) PL/SQL offers three types of collections - associative arrays, nested tables, and varrays - each with their own characteristics and ideal use cases.
If you are not already using collections on a regular basis in PL/SQL, you are really missing out.
Use this article as starting point for accessing a number of useful resources for getting up to speed on collections, and putting them to use in your programs.
Documentation
The PL/SQL User Guide offers detailed coverage of collection features here. It starts by reviewing the differences between collections types.


Articles

ORACLE-BAS…