Collections in 12c

0
0
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… Read more
0
0

Result Cache (Part 2)

0
1
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… Read more
0
1

Result Cache (Part 1)

0
1
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… Read more
0
1

Virtual Private Database (Part 2)

0
0
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… Read more
0
0

Virtual Private Database (Part 1)

0
0
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)… Read more
0
0

Views

0
0
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… Read more
0
0

PL/SQL Subtypes

0
1
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 databas… Read more
0
1

Custom Authentication in APEX

0
2
Trust is a great force multiplier. – Tom Ridge When you build a web application you have two choices for the pages: they can either be public or protected. Mostly you will build applications using a combination of the two. Some pages will be publicly available, like the landing page of your application, but there will also be pages whic… Read more
0
2

Checking for NULL with Oracle SQL

0
9
The art of doing mathematics consists in finding that special case which contains all the germs of generality. David Hilbert One of the most mindboggling values in the Oracle database is the NULL value. What is NULL? NULL is nothing. NULL is not even the same as NULL. NULL is undefined. But you need to work with NULL values (which are no… Read more
0
9

ANSI SQL

0
4
  if you can’t beat them, join them. Gregory Y. Titelman Since Oracle 9i, Oracle SQL supports the ANSI SQL syntax. It takes a bit of getting used to, especially when you are familiar with the Oracle syntax, but it is much more verbose, self-documenting, if you will. Syntax Part of the Select syntax is the joining of tables. To join… Read more
0
4

Kscope12

0
0
Just back from a full week in San Antonio for KScope12. I really had a good time. ODTUG provides us with the best conference for developers in the world. Seen some great sessions, met up with some old friends, made a lot of new ones, met in real life with some people I knew only from the internet.… Read more
0
0

Caching Part 2

0
0
Never trust a computer you can’t throw out of a window. Steve Wozniak Deterministic Function Caching A function is considered deterministic if the outcome is the same if the input values are the same. Another thing is that the program should have no side effects. All the program changes are the return value and any out parameters. It is becaus… Read more
0
0

Caching Part 1

0
0
While programming in the Oracle Database you interact a lot with the data in the tables. Retrieving this data from the database involves context switching between the PL/SQL Engine and the SQL engine. This takes up a relatively large amount of time. You may want to reduce these context switches by caching the retrieved data.… Read more
0
0

Invoker Rights Part 2

0
0
Kindness is the beginning of cruelty Frank Herbert Syntax If you check the syntax for the different program objects you can see that the entire program is defined either using definer or invoker rights. The default is definer rights, so if you don’t supply the clause, the program will be defined using definer rights. This means you don’t have to… Read more
0
0

Timing in PL/SQL

0
0
In my current project I wanted to add some timing to the logging the code already does. So I started my search for a good timing package. My first idea was to download PL/Vision and use the timer that is in there. Installing this was not as simple as I hoped it to be.… Read more
0
0

Invoker Rights Part 1

0
0
Truth suffers from too much analysis. Frank Herbert In the Oracle database you have a choice of two models for executing the PL/SQL code. The default model (and the only one available until Oracle 8i (8.1.7)) is the Definer Rights model. When using this model the program executes under the authority of the owner (or creator) of the program. T… Read more
0
0

Dynamic Show/Hide in APEX

0
0
Understanding is required before success is possible [Dune] old Orange Catholic Bible When you want to hide and display certain regions in an APEX based application based on the selection of the user, for instance when a button or a link is pressed then you can of course use the Region Display Selector, but this takes up a region on… Read more
0
0

Bulk Processing in Oracle Part 2

0
0
The process of learning requires not only hearing and applying but also forgetting and then remembering again. – John Gray Sending data back Using the bulk capabilities you can not only fetch data in one roundtrip to the database, but you can also use it to send data back to the database in a single roundtrip. Using the FORALL statement… Read more
0
0