Check Your Subroutines - We are delivering our latest release to internal test today. Had a code review yesterday. Many issues were found. We are fixing the highest priority probl...
Back to Basics
For example, DUAL is actually a table owned by SYS. I just considered it a virtual scratch pad table area. But it is a real table. It only has one column called DUMMY of type VARCHAR2(1). Furthermore, that table has exactly one row with a value of "X".
I reviewed the syntax to create a package. There is no BEGIN keyword in declaring the specification. Just an END. The package body has an optional BEGIN-END pair. But that is for a one time initialization set of code. It executes once the first time your session accesses the package. I have actually used this feature a time or two.
Now a bit about types. I know that NUMBER is an Oracle type. INTEGER on the other hand, is an Oracle subtype of NUMBER. You can define your own subtypes. Not that I do that much.
There were a bunch of tips on getting better performance. Some were not intuitive. Try to do your operations in a single SQL statement.Avoid the use of implicit conversion. I am all behind that. Implicit conversion just feels like sloppy program to begin with. Do not define your variables as NOT NULL.
Huh? You would think limiting a variable to NOT NULL might help performance. Nope. For those variables, Oracle has to do extra work to keep checking if the thing is getting set to NULL. Use PLS_INTEGER for math operations. And of course, use BULK COLLECT and FORALL when dealing with collections.