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

Views

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

PL/SQL Subtypes

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

Custom Authentication in APEX

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

Checking for NULL with Oracle SQL

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

ANSI SQL

0
9
  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
9
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
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
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
1
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
1
02 September 2011
02 September 2011

Collections 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. This article follows on from Collections in Oracle Part 1 Varray The syntax for defining a varray in SQL is like this: CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type; The syntax for defining a varray in PL/SQL is lik… Read more
0
0
19 August 2011
19 August 2011

Collections in Oracle Part 1

0
1
“The beginning of knowledge is the discovery of something we do not understand.” Frank Herbert. Collections are single-dimensioned lists of information, very similar to 3GL arrays. Much like ‘in-memory’ tables. Maybe that’s why they started out as PL/SQL Tables. The PL/SQL only version got renamed a couple of times. When first introduced in Oracle 7 they were called PL/SQL tables… Read more
0
1
20 July 2011
20 July 2011

Working with Table Functions in PL/SQL

0
14
Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database table in the FROM clause of the query is… Read more
0
14