PL/SQL

Category

Sometimes the best way to see the familiar is to go far from it – Wisdom of the desert In previous articles (collections I and collections II) I described the different types of collections, their possibilities and their limits. One of the limits was the possibility to use an Associative Array in a SQL statement. If you are building your… Continue Reading →

12c, All Things Oracle Full Articles, Database Development, Oracle Database, PL/SQL

When we examine an execution plan, we’re usually trying to work out the mechanical steps that Oracle took to produce a result set. When we do this, it’s important to remember that the text that Oracle optimized wasn’t necessarily the same as the statement we originally wrote. Oracle may have transformed our statement before passing it through the optimization engine…. Continue Reading →

All Things Oracle Full Articles, Database Administration, PL/SQL

A wise man once said, insanity is doing the same thing over and over again but expecting different results. If that is the case then no one’s saner than a computer programmer*, because programming is executing the same code over and over again and expecting the same results. We’ve already talked about bundling up statements in anonymous PL/SQL blocks; now… Continue Reading →

All Things Oracle Full Articles, Database Development, PL/SQL

A wise man* once said, all software programming is about hoops and loops. You take some variables, give them hoops to jump through and, depending on their success or failure, you give them some actions to loop through a number of times. Might sound basic, but that’s what all software code – from Angry Birds to the space shuttle –… Continue Reading →

All Things Oracle Full Articles, Database Administration, PL/SQL

If you have to tackle performance problems in an Oracle database one of the most important skills you need to acquire is the ability to interpret execution plans, and in this series of articles I’ll be telling you how to do just that. We’ll start with a couple of articles that look at some of the ways we can access… Continue Reading →

All Things Oracle Full Articles, Database Administration, PL/SQL

Insanity: doing the same thing over and over again and expecting different results. – Albert Einstein In part 1 we looked at the result cache for PL/SQL Code We can also use this same technique when our function depends on tables (or views). Let’s create a simple table, add some data and a simple, slow function: CREATE OR REPLACE FUNCTION… Continue Reading →

11g, 12c, All Things Oracle Full Articles, Database Development, Oracle Database, PL/SQL

First, solve the problem. Then, write the code. – John Johnson We are always looking for ways to do things faster. Sure you can use more CPU power or more memory in the database server, but there’s a limit to that approach. Be it the amount of money being spent, the limits of the [current] CPU power or other limits…. Continue Reading →

11g, 12c, All Things Oracle Full Articles, Database Development, Oracle Database, PL/SQL

Error Handling

Jan Leers on 06 December 2013 with 3 comments

Most programmers don’t like exceptions, but you should consider them as your closest friends. They are the ones that honestly say what is wrong with your program. We cannot foresee all possible problematic events, and even the best programmers write bugs. Exceptions There are three kinds of exceptions Internally defined: A system error, defined by Oracle, that occurs. Predefined: The… Continue Reading →

All Things Oracle Full Articles, Database Development, PL/SQL, Troubleshooting

Admit that your own private Mount Everest exists. That is half the battle. – Hugh Macleod Other options In the example we used pretty much the minimum number of parameters to make it work. There are more parameters and options available. For example UPDATE_CHECK. This does pretty much the same as WITH UPDATE CHECK in views. If you insert or… Continue Reading →

10g, 11g, 12c, 8i, 9i, All Things Oracle Full Articles, Database Development, Oracle Database, PL/SQL

Views When working with views you have several options to hide some of the data. In the article on views I have told you about the way to hide columns from the table. By adding a predicate (where clause) to the view, you can restrict the rows a user sees. This would imply creating different views for different (groups of)… Continue Reading →

10g, 11g, 12c, 8i, 9i, All Things Oracle Full Articles, Database Development, Oracle Database, PL/SQL