Articles

2011-07-20

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 that you need to use the TABLE() operator to tell the SQL engine that it should treat whatever comes out of the function as if it were a normal table.

2011-08-11

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. In Oracle 8i they were renamed to Index By tables. Probably because of the index by clause in the syntax. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table did, but it can now be indexed by a VARCHAR2 as well.

2011-09-02

This article follows on from Collections in Oracle Part 1

2011-09-19

As you know (or may not know) there are two engines running in the Oracle database. The PL/SQL engine for executing all your PL/SQL code and the SQL engine for executing all you SQL statements. This means that every time your PL/SQL code needs data from the database, or writes to it control is passed from the PL/SQL runtime engine to the SQL. This process involves a lot of work which has to be done by the two engines, like writing the SQL statement in an area the SQL engine can access and having the SQL engine write the result in an area the PL/SQL engine can access. This is called a context-switch.

2011-09-27

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. You can provide the FORALL statement with one or more collections and it will send the data back to the database in one pass.

2011-10-31

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 your page and is not very customisable (yet). If you want more control over the look and feel and what happens when a user clicks an item, for instance show multiple regions or just use the hovering over an item to show something you need to build it yourself.

2011-12-08

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. The other model is the Invoker Rights model. When using this model the program executes under the authority of the invoker of the program. This means data and objects that are available to the user are also available to the program. When running the code using the Definer Rights model, the data and objects available to the definer of the code are available to the code. This might mean a user may have access to data he is not supposed to have access to. There are some differences between both models and your programs may run using a combination of the two.

2012-01-06

In my current project I wanted to add some timing to the logging the code already does.

2012-01-25

If you check the syntax for the different program objects you can see that the entire program is

2012-02-14

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. To do this Oracle supports a number of methods.

2012-02-23

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 because of this consistent behavior, Oracle can build a cache based on the functions inputs and output. The same input results in the same output and there are no side-effects. So the second time the function is called with the same parameters there is no need for it to be executed again to get to the result.

2012-07-18

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.

2012-08-30

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 actual values). Luckily Oracle provides us with a couple of functions to do the heavy lifting when it comes to checking for NULLs

2012-10-25

One of the ‘killer’ features of the Oracle Database version 11g Release 2 is probably Edition Based Redefinition. This functionality allows for near zero downtime when performing an upgrade for your application.

2012-11-06

In the previous article we saw how we could have different version of the code in different editions of the schema. But what about the tables? They are not editionable? This article will show you how to work ‘around’ that.

2013-01-09

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 which are protected. Users need to login to the application to use these pages.

2013-04-03

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 to store a maximum of four (4) digits of which we want to use two (2) as the decimal digits. This is how we, in real life, use our prices. In PL/SQL we can anchor our variables to this type using the <TABLE>.%TYPE syntax. But what if there is no column to anchor to? We can also use the NUMBER(6,2) syntax to constrain the values possible for a certain variable.

2013-06-05

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 can also limit the rows a user is able to see by adding a where clause to the view. The risk here is that the user changes the value of a column, making it invisible for him- or herself. If for instance the user only has access to records in the EMP table which belong to DEPTNO 20 then we could create a view like this:

2013-08-21

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) users. If you have an interface built to interact with the data, that would also mean you have to maintain multiple versions of these interfaces. Not a big problem if you only have a small number (like two or three) interfaces to maintain, but if the number of interfaces grows, chances are that you will forget to implement changes in one or more of them.

2013-09-04

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 update a value the Oracle server checks if the new record will be visible to you using your policy. If it is not visible, and this parameter was set to TRUE then it will not allow you to execute this command.