Obsessed with Oracle PL/SQL

A blog post about Oracle error codes? Curiosities about them, even? If you doubt that this might be possible or of interest, then answer these two questions: Can an error in Oracle have more than one error code? Are error codes positive or negative? If you answered "yes" for the first and "yes and no" for the second, you probably don't need to read this post. Oracle errors with more than one error code? Well that wouldn't be very normalized, would it? :-) But it is true that there at least one error that has two different error codes associated with it, and it's one of the most common "errors" you'll encounter in your code:  The NO_DATA_FOUND exception When I execute a SELECT-INTO statement, Oracle will raise NO_DATA_FOUND if no row is found for the query. It will raise TOO_MANY_ROWS if more than one row is found. So what error code is associated with NO_DATA_FOUND? The following code demonstrates this curiosity. I...

When more than one subprogram (procedure or function) in the same scope share the same name, the subprograms are said to be overloaded. PL/SQL supports the overloading of procedures and functions in the declaration section of a block (named or anonymous), package specifications and bodies, and object type definitions. Overloading is a very powerful feature, and you should exploit it fully to improve the usability of your software. Before exploring some of the details of overloading, a short quiz: Which of the following is another name for overloading? Dynamic Polymorphism Interface Inheritance Static Polymorphism Multiple Monomorphism In a poll I conducted on Twitter , we saw the following results: And I was very glad to see this, because Static Polymorphism is, indeed, another name for overloading, and here's why: With overloading, at the time your code is compiled, PL/SQL resolves all references to named elements, such as a function invocation. If there is more ...

I recently received this question in my In Box: Is FORALL faster with Associative Arrays or Nested Tables? Oracle 12.2 documentation  says : "The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause." And a blog post  claims  Associative Arrays with "indices of" option is fastest in 10.2. Just wondering if you have noticed any differences and if so, how much faster Associative Arrays are in 12.2 than Nested Tables? Quick Answer There is no significant difference I can see in the performance based on different collection types (with the limited tests I have run). Don't trust performance tests run on very old versions of Oracle Database (e.g,, 10.2). Use the documentation as a starting, not ending, point of your exploration. Try it yourself! Writing and running the code will teach you more than reading the doc or my blog post. If you would like to read fur...