PL/SQL

Category

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 5 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

I try to use subtypes as much as I can and after reading the “All Things Oracle” article of Patrick Barel on PL/SQL subtypes, I want to add something to his article about the use of subtypes. Booleans are a PL/SQL datatype and doesn’t exist in SQL. This means that when your PL/SQL function returns a boolean, you can’t use… Continue Reading →

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

Views

Patrick Barel on 05 June 2013 with 1 comments

Success always occurs in private and failure in full public view. – Unknown When working with views you have several options to hide some of the data. Of course there is the possibility not to select certain columns of data in the view. This way, the user of the view doesn’t even have to know this column even exists. You… Continue Reading →

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

Prerequisites A local database, for development SQL Developer version 3.2.20.09.87 or higher (Download) Wallet Manager Getting started To get started, we first need an account on the Oracle cloud. So go to Oracle Cloud, login with your OTN account and request a trial period. You will need to enter a credit card for verification, but it will not get charged… Continue Reading →

All Things Oracle Full Articles, APEX, Application Development, Oracle Cloud, Oracle Database, PL/SQL

Article #3 in my ongoing series covering SQL statistic functions in Oracle Database is now up. The topic is the median: MEDIAN: For When You Don’t Really Mean It Median is useful in typifying a data set when the data might be skewed, or in the presence of extreme outliers. For example, the U.S. Census Bureau reports median household income… Continue Reading →

Application Development, Database Administration, Database Development, Oracle Database, PL/SQL, Uncategorized

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand – Putt’s Law We are always working with data and their datatypes. In the tables we constrain the data by their datatype. For instance, we create a price column of type NUMBER(4,2) telling the database… Continue Reading →

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