Oracle Recipe #4 How to retrieve the list of schema objects with Oracle Data Provider for .NET (ODP.NET)

In this post, I am going to introduce you one of the sample schemas that Oracle provides as we learn Oracle database: The HR Schema.
But before I introduce it specifically, we need to understand what is a schema.

I’ve found two definitions for the same term, a schema basically can be:

  • A logical container for data structures
  • A collection of objects associated with the database.

Oracle draws the distinction between logical and physical structures: structures that are visible at a disk level or operating system level such as data files, control files and redo log files are considered physical structures, on the contrary, objects like tablespaces, schemas, tables, views , and any database objects are considered logical structures.
A container in this context means that a single schema name can contain many different objects, these logical objects are known as schema objects, and they are made up of structures such as:

  • Table: A table is the basic logical storage unit in the Oracle database; composed of rows and columns.
  • Cluster: A cluster is a set of tables physically stored together as one table.
  • Index: An index is a structure created to help retrieve data more quickly and efficiently.
  • View: Logically represents subsets of data from one or more tables.
  • Store procedure: Stored procedures are predefined SQL queries stored in the data dictionary designed to allow more efficient queries.
  • Sequence: Numeric value generator.
  • Package: Named PL/SQL modules that group related stored procedures, functions, and identifiers.
  • Synonyms: Gives alternative names to objects.

The HR schema

The HR schema is a sample schema that Oracle makes available for learning purposes.
You can install sample schemas using DBCA (DataBase Configuration Assistant) or you can get it from the following link:

Fig 1. Entity Relationship Diagram for HR Schema.

Schemas present a layer of abstraction for your data structure and it helps to avoid a problem called name collision. Let me show you an example: if we don’t use schemas a user called Bob can create a table called Employees, and then another user called Alice cannot create a table called Employees on the same schema that Bob, but Alice can create a table in a different schema. Other users can access or execute objects within a user’s schema once the schema owner grants privileges.

List schema objects using .NET

The following code example uses Oracle Developer Tools for Visual Studio (ODT) to retrieve the list of schema objects that are available and then displaying them to the console.

Fig 2. Create and return an open OracleConnection
Fig 3. The utilities class
Fig 4. The main program
Fig 5. Running the program
Fig 6. Retrieving the list of schema objects of hr user.
Fig 7. Retrieving the list of schema objects of system user.
  • Note 1: You will find in many Oracle’s texts that some people using schema and user indistinctly.
  • Note 2: Oracle validates that the users have permissions to use the schema objects being accessed by theirs.

Download example source code.

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