21 October 2014
21 October 2014

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
04 February 2014
04 February 2014

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
28 January 2014
28 January 2014

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
04 September 2013
04 September 2013

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
21 August 2013
21 August 2013

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
05 June 2013
05 June 2013

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
03 April 2013
03 April 2013

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
09 January 2013
09 January 2013

Custom Authentication in APEX

0
4
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
4
30 August 2012
30 August 2012

Checking for NULL with Oracle SQL

0
10
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
10
18 July 2012
18 July 2012

ANSI SQL

0
5
  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
5
02 July 2012
02 July 2012

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
23 February 2012
23 February 2012

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
14 February 2012
14 February 2012

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
25 January 2012
25 January 2012

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
06 January 2012
06 January 2012

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
08 December 2011
08 December 2011

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
31 October 2011
31 October 2011

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
27 September 2011
27 September 2011

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