Oracle Recipe #2: How to execute a query that returns a Scalar result with OracleCommand.

  1. Microsoft ADO.NET command objects have an ExecuteScalar method, which enables you to execute a query that returns a single result.
  2. Open a database connection.
  3. Create and initialize a command object.
  4. Call the ExecuteScalar method on the command object.
  5. Convert the return value from ExecuteScalar into an appropriate data type.
  6. Dispose the command object.
  7. Close the database connection.

The following example, show how to execute a query that determines the average salary from the table employees on the HR schema provided by Oracle Database XE.
The example assume that the query does not return a NULL result.

Fig 1. OracleCommand ExecuteScalar method code example.

Download source code

How to connect an Oracle Data Source by using ADO.NET

Step 1: Provide a connection string

To connect to a database, you must provide a connection string to identify the database. The following list describes several common parameters of connection strings.

  • Persist Security Info: if is false, the data source does not return security-sensitive if the connection is open.
  • User ID and Password: The data source login and password to use if you are not using integrated security.
  • Integrated Security or Trusted_Connection: if true, the data source uses the current account credentials for authentication, if false, you must specify the User ID and Password in the connection string.
  • Data Source: The name or network address of the data source instance.
  • Initial Catalog or Database: the name of the database.
  • Connection Timeout: The length of time in seconds to wait for a connection to the server before the data source terminates the attempt and returns an error. The default timeout is 15 seconds.
Fig 1. Provide a connection string.

Step 2: Retrieve a connection string from an application configuration file.

You can either store connection strings in an application configuration file or you can hard-code them directly in your application. If you store connection strings in the configuration file, you can modify them easily, without having to edit the source code and recompile the application.
Each connection string that is stored in an application configuration file has its own assigned name. In an application, you can access a connection string by its programmatic name.

Fig 2. Create and return an OracleConnection object.

Step 3: Handle connection events

ADO.NET connection objects have two events that you can use to retrieve informational messages from a data source or to determine if the state of a connection has changed.

  • InfoMessage: Occurs when a data source returns an informational message. Informational messages are messages from a data source that do not result in an exception being thrown.
  • StateChage: Occurs when a connection changes from the closed state to the open state or from the open state to the closed state.

If an error occurs at the data source, the data provider throws an exception. However, if the data source returns an informational message, the data provider raises an InfoMessage event instead.

Step 4: Handle connection exceptions

The .NET Data provider for Oracle throws an OracleException when it encounters an error or warning generated by an Oracle database. OracleException objects have a Code property that gets the code portion of the error as an integer and message property that gets a description of the error.

Fig 3. Step 3 and 4: Handle connection events and exceptions, you can see the example below.

The following code show how to test each step in C# code.

Fig 4. The code to test our classes.

Fig 5. We run the program and verify the events in the log.

Fig 6. The program wrote the events in the log.

Download 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

Regular Expressions en PL/SQL Oracle, parte II

En este post anterior mostré ejemplos de búsqueda con las funciones regexp_like y regexp_instr.

A continuación mostrare ejemplos de las funciones regexp_substr y regexp_replace respectivamente.

Como primer ejemplo de la función regexp_substr, obtendré dos subcadenas (substring), una de la columna MEMBERSHIP_NAME donde el carácter a se repita dos veces en cada registro y otra de la columna MEMBERSHIP_DUE en donde la cantidad comience con un dígito del 1 al 6 y después uno o más dígitos que se repitan.

Como segundo ejemplo de la función regexp_substr obtendré un substring de la columna MEMBERSHIP_DATE cuyos registros terminen con los dígitos del 1 al 6 repetidos una o dos veces.

Como primer ejemplo de la función regexp_replace buscaré dentro de la concatenación de las columnas MEMBERSHIP_NAME y MEMBERSHIP_LASTNAME los registros que tengan un carácter o repetido a partir de la posición 4 y lo sustituiré por la cadena (**found**).

Como último ejemplo ejecutamos la función regexp_replace en un texto y reemplazamos la palabra ‘fox’ por la palabra ‘SUPER CAT’ a partir de la posición 1.


Descarga el código PL/SQL

Regular Expressions en PL/SQL Oracle, parte I

Una expresión regular (regular expression) es un conjunto de caracteres (signos) conocido como patrón que al buscarse coincide una o más veces en una cantidad considerable de texto, estos patrones se construyen con una notación de caracteres ordinarios y meta caracteres, los cuales tienen un significado especial dentro de la expresión regular e indican las reglas a las que deben someterse los caracteres ordinarios para su interpretación estos bloques básicos de construcción son similares a una expresión algebraica o a un mini lenguaje de programación.

A continuación algunos de los meta caracteres y su significado:

  • ^ coincide el patrón de búsqueda al inicio de una línea.
  • $ coincide el patrón de búsqueda al final de una línea.
  • . coincide cualquier carácter en cualquier lugar.
  • [] especifica un rango de caracteres
  • ? ubica un carácter opcional.
  • + ubica uno o más caracteres.
  • ubica cero o más caracteres.
  • {n} ubica un carácter que aparece n veces.
  • {n,} ubica un carácter que aparece n o más veces.
  • {n,m}ubica un carácter que aparece de n a m veces.
  • | disyunción o sea un or lógico entre caracteres.

Las expresiones regulares son ampliamente utilizadas en Linux o en otros lenguajes Open Source, además de plataformas como Java y.NET y en bases de datos como PostgreSQL y Oracle.
En Oracle las expresiones regulares son utilizadas cada vez que necesites operaciones de búsqueda demasiado complicadas en donde los comandos SELECT y LIKE no sean suficientes.
Oracle tiene las siguientes cuatro funciones para su utilización:

  • REGEXP_LIKE: es la versión de expresiones regulares del comando LIKE. Una función booleana que regresa TRUE,FALSE o NULL si en el texto existe una coincidencia con la expresión regular.
  • REGEXP_INSTR: esta función regresa la posición del caracter en el texto donde se encontró una coincidencia con la expresión regular.
  • REGEXP_SUBSTR: extrae una coincidencia de texto encontrada con la expresión regular.
  • REGEXP_REPLACE: ejecuta una operación de búsqueda y reemplazo si se encuentra una coincidencia en el texto.

Como ejemplo de su uso, creamos la siguiente tabla:

Después insertamos los siguientes registros para comenzar a utilizar las funciones.

Elizabeth  Bishop 36736-36738 976.063 02/08/1911
Charles Dickens 36734-5461 2244.789 07/02/1812
Jack London 5462-37314 898.127 12/01/1876
Joseph Conrad 37315-5463 1193.493 03/12/1857
Gustave Flaubert 37313-37316 1435.384 12/12/1821
John Milton 37317-37296 1348.582 09/12/1608
Samuel Taylor 37292-37318 207.449 21/10/1772
Virginia Wolf 37061-106 2077.947 25/01/1882
Walter  Scott 37319-37320 412.72 15/08/1771
Robert Louis  Stevenson 37945-37946 1033.54 13/11/1850
Joseph Rudyard  Kipling 37947-12556 382.41 30/12/1865
Arthur Conan Doyle 12557-10964 1844.945 22/05/1859
George  Orwell 54722-3236 2139.874 25/01/1903

Como primer ejemplo utilizamos la función REGEXP_LIKE para obtener de la columna MEMBERSHIP_LASTNAME
los regitros que comienzan con la letra D.La consulta es:

Ahora utilizamos la función REGEXP_LIKE para obtener de la columna MEMBERSHIP_DUE
los registros que terminan con el número 3.La consulta es:

Por último, utilizamos la función REGEXP_LIKE para obtener de la columna MEMBERSHIP_LASTNAME
los registros que tengan las letras de la A a la F. La consulta es:

Ahora ejemplos con la función REGEXP_INSTR. En el primer ejemplo
buscamos los registros que en la columna MEMBERSHIP_NAME
tengan de 1 a 2 veces la letra A y cuyo posición de coincidencia del texto sea mayor
a 0.

En este segundo ejemplo con REGEXP_INSTR buscamos todos los registros que en la columna
MEMBERSHIP_NAME comiencen con la letra J o la letra E.

Un último ejemplo con REGEXP_INSTR buscamos todos los registros que en la columna
MEMBERSHIP_DATE terminen en el penúltimo dígito del 0 al 9 y en el último dígito
del 1 al 2.

Download el código fuente PL/SQL

Obtener el primer día del mes en curso con una función first_day() con PL/SQL en Oracle.

En PL/SQL existe la función LAST_DAY() con la que se obtiene el último día del mes en curso, entonces si tenemos que cumplir un requerimiento en donde necesitamos un campo o una variable con el último día del mes en curso simplemente ejecutamos esta función:

Ahora bien, si el requerimiento a cumplir se trata de obtener el primer día del mes en curso, no existe en PL/SQL Oracle una función predeterminada, por lo que para llegar a ese resultado basta con restarle un mes a la fecha en curso con la función ADD_MONTHS(), le aplicamos la función LAST_DAY() para obtener el último día del mes anterior y sumarle un día.

Aquí otra forma alterna de llegar al mismo resultado.


Descarga el código PL/SQL