Good-fast-cheap. Pick two. - I got invited to a meeting with the customer today. There was a problem in production. And the customer wanted answers. When it came time, I explained wha...
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.