Oracle Recipe #1: How to generate an IDENTITY column using a sequence

Primary keys can be created using either numeric or character data types, with one exception: Oracle does not have the same concept of identity columns as MS SQL Server.

What is an Identity Column?

It is a property that automatically generates a unique sequential value when it is assigned to a numeric data type. Oracle handles this concept using a database object called sequence.

The Sequence database object enables you to generate a unique sequence number. Each user of the sequence can increment it and obtain numbers for their use. Because multiple users can obtain sequence numbers, there is no guarantee that the numbers you get will not have gaps.

A sequence does not have to be related to a single table, and therefore could be used to provide unique numbers to multiple tables. As a general rule, having one sequence per table or at least one for each major table results in easier diagnostics and a better overall experience.
The following examples show how you can add an identity column for two tables: categories and publishers.

Each table will use a IDENTITY column as PRIMARY KEY.
Fig 1. The categories and publishers catalog.

Fig 2. Script to create both tables.

The following code shows an example of how to select the NEXTVAL from a sequence. Upon executing this trigger, the sequence value is incremented by 1.

Fig 3. Script to create database objects: triggers and sequences.

Each time we add a new row, the trigger will automatically create a new unique number for that row.
With this concept, the first row ID would be 1, and the next ID would be last ID number plus one.

Fig 4. Inserting rows without primary key.

Fig 5.Querying the tables.

Download Scripts

C# Recipe 2: How to calculate the date of the Easter Sunday.

Easter is the celebration of Christ’s resurrection from the dead. It is celebrated on Sunday, and marks the end of Holy Week, the end of Lent, the last day of the Easter Triduum (Holy Thursday, Good Friday and Easter Sunday), and is the beginning of the Easter season of the liturgical year.

As we know from the Gospels, Jesus Christ rose from the dead on the third day following his crucifixion, which would be Sunday.
Since the early Middle Ages, all Christians have used the same method for determining the date of Easter, though they arrive at a different result.

The following code calculates the easter sunday for a given year:

Listing 1. The main program

Listing 2. The Util class

Fig 1. Running the sample, output 1

Fig 2. Running the sample, output 2

Fig 3. Running the sample, output 3

Download source code

C# Recipe 1: How to calculate a leap year.

The Gregorian Calendar is the most widely used calendar in the world today.It is a reform of the Julian calendar, proposed by Aloysius Lilius, and decreed by Pope Gregory XIII, from whom it was named, on 24 February 1582 by papal bull Inter gravissimas

The changes made by Gregory also corrected the drift in the civil calendar which arose because the mean Julian calendar year was slightly too long, causing the vernal equinox, and consequently the date on which Easter was being celebrated, to slowly drift forward in relation to the civil calendar and the seasons. The Gregorian Calendar system dealt with these problems by dropping 10 days to bring the calendar back into synchronization with the seasons, and adopting the following leap year rule:


Every year that is exactly divisible by four is a leap year, except for years that are exactly divisible by 100; the centurial years that are exactly divisible by 400 are still leap years. For example, the year 1800 is not a leap year; the year 1984 is a leap year and the year 2000 too.

Fig 1 The following example calculates a leap year.

 

 

Fig 2 Running the sample, output 1

 

 

Fig 3 Running the sample, output 2

 

 


Download source code