August 2016

Quite obviously (well its obvious to me!) one of the areas I am very interested in is data loss / data theft / data security and of course specifically Oracle security. We spend a lot of time looking at customers....[Read More]

Posted by Pete On 31/08/16 At 08:17 PM

Eight in a series of posts in response to Tim Ford's #EntryLevel Challenge.

My Fear Nothing post last month introduced some of the trouble that nulls can cause by generating results that are counterintuitive to what you would expect from a superficial reading of a query. This month we’ll go deeper into the rabbit hole, because the situation with nulls is rather worse than you might think. 

Let’s imagine for a moment that we’re interested in answering this business question:

An invalid entry in the spfile may prevent the instance from starting up:

SQL> alter system set sga_target=500m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00821: Specified value of sga_target 512M is too small, needs to be at least 1392M

The instance doesn’t come up! This is easy to fix without having to restore the spfile from backup:

SQL> create pfile='/home/oracle/init.ora' from spfile;

File created.

SQL> host vi /home/oracle/init.ora

Now correct the value in the text file. I just removed the sga_target parameter from it here. Then

PL/SQL offers the entire suite of structured programming mechanisms, such as condition checking, loops, and subroutines, as shown in the following figure. (read more)