Getting To Know Oracle Database

In this article we will address in a general way the main features of the Oracle database manager, in this way we will see the types of licensing it provides and thanks to this it has great popularity among companies. It is a robust manager with an object-relational data model, which means that it presents information in the form of objects, and of course it has a wide variety of data types, which allows it to be used optimally in certain scenarios.

En este artículo abordaremos de manera general las principales características del gestor de bases de datos de Oracle, de esta manera veremos los tipos de licenciamiento que provee ya que gracias a esto dispone de gran popularidad entre las empresas. Es un gestor robusto y con un modelo de datos objeto-relacional lo que significa que presenta la información en forma de objetos, y desde luego dispone de una gran variedad de tipos de datos lo que permite utilizarla de forma óptima en determinados escenarios.

DATABASE MANAGEMENT SYSTEM

A database management system (or DBMS) is essentially nothing more than a computerized data maintenance system. Users of the system are given facilities to perform various types of operations on that system, either for manipulation of the data in the database or for management of the database structure itself.

ORACLE DATABASE

Oracle is not a database in itself, but the means or the interface through which we will develop, assemble and work with them. In fact, to develop in Oracle, we use a special 5th generation programming language, known as PL/SQL (Procedural Language/Structured Query Language), an Oracle embedded variant of SQL (Structured Query Language), but which contains novel and special features such as the possibility of handling variables and developing flow control and decision making structures, among others.

Licensing

One of the biggest complications when choosing the type of licensing is a legal threat of being mislicensed on your platforms.

Today there are an innumerable variety of software deployment scenarios, so there can be a lot of doubt and confusion. Also part of the problem can arise because Oracle does not use software keys, or codes to activate licenses, so it is very easy to install and get into an under-licensing scenario. It will be up to us to license the software before using it as these licenses are not tied to the product version either.

The following are some ideas based on the official documentation dealing with licensing

Oracle Database Offerings

• Oracle Database Express Edition: It is a free community-supported edition of the Oracle Database family.

• Oracle Database Standard Edition 2: It includes the features needed to develop workgroup, departmental and web-based applications.

• Oracle Database Enterprise Edition: It provides performance, availability, scalability and security for the development of applications such as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses and demanding Internet applications.

• Oracle Database Enterprise Edition on Engineered Systems: Includes all Oracle Database components.

• Oracle Database Personal Edition: Includes all components included in Enterprise Edition, as well as all Oracle Database options, with the exception of the Oracle RAC One Node and Oracle Real Application Clusters options, which cannot be used with Personal Edition.

• Oracle Database Cloud Service Standard Edition: Includes Oracle Database Standard Edition 2 software.

• Oracle Database Cloud Service Enterprise Edition: Includes Oracle Database Enterprise Edition software.

• Oracle Database Cloud Service Enterprise Edition - High Performance: Includes Oracle Database Enterprise Edition software, plus many Oracle Database options and Oracle management packs.

• Oracle Database Cloud Service Enterprise Edition - Extreme Performance: Includes Oracle Database Enterprise Edition software plus all Oracle Database options and Oracle management packs suitable for use in Oracle Database Cloud Service.

• Oracle Database Exadata Cloud Service: These offerings include Oracle Database Enterprise Edition software plus all Oracle Database options and Oracle management packs that are suitable for use on Oracle Database Exadata Cloud Service or Oracle Database Exadata Cloud@Customer.

Data Type

It can be said that choosing the appropriate data type seems to be simple, and many times it is done incorrectly. Therefore, this bad decision can have a negative impact on the performance of the database. The choice of the data type to use is a very important task.

We will classify the data types supported by our DBMS as follows:

1. Character Data Types: CHAR (size): A fixed length character string with a maximum size of 2000 bytes.

• CHAR (size): A fixed length character string with a maximum size of 2000 bytes. If you insert a value less than the specified length, Oracle will fill in the blank. If you insert a value that is too long, Oracle will return an error. The size is the number of characters to store.

  Similar to CHAR (size) which contains formatted Unicode data.

• VARCHAR2 (size): A variable length character string with a maximum size of 4000 bytes.

• NVARCHAR2 (size): Similar to VARCHAR2 (size) containing formatted Unicode data.

• LONG: A data type for storing variable length character data up to 2 Gb in length (larger version than VARCHAR2).

• RAW: A variable length binary type for storing character data. There will be no character set conversion on the stored data, so it is considered a string of binary bytes of information. Stores up to 2,000 bytes.

• LONG RAW: This data type is similar to the LONG data type, so it is recommended that you use the CLOB or NCLOB data types.

2. The Numeric data types:

• NUMBER (p, s): A data type used to store numeric values. The data type has a precision number p and a scale s. The precision can range from 1 to 38. The scale can range from -84 to 127. Precision is the total number of digits allowed. Scale is the number of digits allowed to the right of the decimal point.

• BINARY_FLOAT: The native IEEE single precision floating point number. It is stored in 5 bytes: 4 fixed bytes for the floating point number and 1 byte length. It can store numbers in the range of ~ ± 1038.53 with 6 digits of precision.

• BINARY_DOUBLE: The native IEEE double precision floating point number. It is stored in 9 bytes: 8 fixed bytes for the floating point number and 1 byte in length. It can store numbers in the range of ± 10308.25 with 13 digits of precision.

3. The Date and Time Data Types

• DATE: These data types are used to store date and time values in a fixed-width 7-byte structure. It can handle date ranges from January 1, 4712 BCE to December 31, 9999.

  This is an extension of the DATE data type that can store date and time data (including fractions of a second). Up to 9 digits to the right of the decimal point can be retained. This data type takes 11 bytes of storage.

• TIMESTAMP WITH TIME ZONE: This is an extension of the TIMESTAMP data type that can additionally store time zone information, so the originally inserted time zone is retained with the data. This data type is stored in a fixed-width 13-byte structure.

• TIMESTATM WITH LOCAL TIME ZONE: This is a similar data type to the TIMESTAMP data type, however, it is time zone dependent. For example, if you insert a date/time value from a Web application using the US/Pacific time zone and the database time zone is US/Eastern, the final date/time value will be converted to US/Eastern time zone and stored as a timestamp value. When the information is requested again, the timestamp stored in the database is converted back to the Web application time zone (US/Pacific).

  First of all this data type is used to store a period of time / duration of time. As a consequence we can use this data type for date arithmetic in order to add or subtract a time period from a date or date and time data types.

There are two types of interval:

• INTERVAL YEAR TO MONTH: To store a difference in years and months. It is a fixed-width 5-byte data type.

• INTERVAL DAY TO SECOND: To store a difference in days, hours, minutes and seconds. It is a fixed-width 11-byte data type.

4. The data types Objects/Binary.

• BFILE: This data type allows to store a pointer to an object in the file system in a column of the database and to read the file directly from the file system. This allows you to access files available on the database server's file system as if they were stored in the database table itself.

  As a result this data type can hold up to 4 GB of data. BLOB is useful for storing binary documents (e.g. spreadsheet, word processing document, images, audio, video). Unlike the BFILE data type that stores the scanned information in the file system, the BLOB data type stores the scanned information directly in the database.

• CLOB: On the other hand this is a data type that can hold up to 4 GB of information and therefore contains information that is subject to character set conversion. It is also suitable for storing large plain text information. In short it is not suitable for storing plain text data that is 4,000 bytes or less, in this case it is recommended to use the VARCHAR2 data type.

• NCLOB: It works in the same way, as the CLOB data type, but the characters are stored in a NLS or in a multi-byte Unicode character set.

5. Rowid data type

• This data type usually uses a 10-byte address of a row in a database. Sufficient information is encoded in the ROWID to locate the row on disk, as well as identify the object to which the ROWID points.

ACID

Transactions

A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are all committed, which means they are applied to the database, or they are all rolled back, which means they are rolled back from the database. Oracle Database assigns each transaction a unique identifier called a transaction ID.

All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following:

Atomicity

All transactional data and changes are executed entirely as a single operation. No changes are made if that is not possible.

Consistency

Data must be consistent and valid at the beginning and end of a transaction.

Isolation

Transactions are executed synchronously, without any competition. They act as if they were happening consecutively.

Durability

Once a transaction completes, its connected data is permanent and cannot be modified.

PROGRAMMING

According to (Jérôme GABILLAUD) the following points are defined:

PL/SQL

PL/SQL is a combination of SQL with the procedural features of programming languages. The interest of the PL/SQL language lies in being able to mix the power of SQL statements with the flexibility of a procedural language in the same program.

These programs can be executed directly by ORACLE tools (anonymous blocks) or from database objects (stored procedures and packages).

Stored Procedures

A stored procedure is a nominated block of PL/SQL code, stored in the database and that can be executed from applications or other stored procedures. In a PL/SQL block, simply refer to the procedure by name to execute it. In SQL*Plus, the EXECUTE statement can be used.

Stored Functions

Like procedures, a function is a piece of PL/SQL code, but the function returns a value. These stored functions are used like ORACLE functions.

Databases Triggers

A trigger is a PL/SQL block associated with a table. This block will be executed when a DML (INSERT, UPDATE, DELETE) statement is applied to the table.

Triggers provide a procedural solution for defining complex constraints or constraints that take into account data from multiple rows or multiple tables, for example, to ensure that a customer cannot have more than two unpaid orders. However, triggers should not be used when it is possible to establish a completeness constraint.

Packages

A package is a schema object that logically groups related PL/SQL elements such as data types, functions, procedures and cursors.

The packages offer numerous advantages:

• Modularity: Logically grouping related PL/SQL elements together makes it easier to understand the different elements of the package and their use is greatly simplified.

• Simplification of development: During the process of defining an application, packages make it possible to define at the first design stage only the package header and thus perform compilations. The package body will only be needed to run the application.

• Hidden data: With a package it is possible to make certain elements not visible to the user of the package. This makes it possible to create elements that can only be used within the package and therefore.

Standalone Transactions

A transaction is a set of SQL commands that constitutes a logical unit of processing.

A standalone transaction is an independent transaction that is executed after another transaction, the main transaction. During the execution of the standalone transaction, the execution of the main transaction is stopped.

Dynamic SQL

A technique that allows creating SQL statements dynamically during PL/SQL code execution. Dynamic SQL allows creating more flexible applications, since the object names used by a PL/SQL block may be unknown at compile time. For example, a procedure may use a table whose name is unknown before executing the procedure.

Collections and Records

By saving all data in collection format directly in the PL/SQL block, all processing can be performed by the PL/SQL engine. By limiting SQL queries, accesses to the database are limited, which speeds up the processing time of the PL/SQL block, but also limits the occupancy of the SQL engine and, consequently, other users' queries can be processed more quickly.

Block Data Copy

With block copying, SQL statements can be applied to the entire collection and not just to individual elements in succession.

Functions and Row Sets

It is now possible to define functions in PL/SQL that accept input parameters and/or return not a single value, but a set of rows. The advantage of these functions is that it is no longer necessary to store the data in a temporary table before calling the function to execute.

The Wrap Utility

It is a program that allows to encrypt the PL/SQL source code. In this way, it is possible to distribute PL/SQL code without users being able to access the source code.

Wrap allows to mask the algorithm used, but in no case character strings, numbers, variable names, columns and tables are encrypted. Therefore, this utility does not allow hiding passwords or table names.

Regular Expressions

Oracle supports a set of common metacharacters used in regular expressions. The behavior of supported metacharacters and related functions is described in "Regular Expression Metacharacters in Oracle Database".

CONCLUSIONS

• Oracle is one of the most widely used database managers because it provides many tools and services that streamline the process of data creation and administration, in addition to offering different editions of its database manager that allows to adapt to the needs of each client, thus allowing to cover a wider market.

• The best service offered by Oracle is its automation of processes that make database administration workloads easier for large companies.

• Oracle supports all the features a server expects: a very complete database design language (PL/SQL) that allows "active" design through triggers and stored procedures, with a fairly strong declarative referential integrity.

• As discussed in this article, the Oracle database manager is undoubtedly an excellent choice to manage large amounts of data quickly and efficiently, as it provides a wide stack of tools that make it widely used, both in its free and paid versions.

• Due to everything studied above we can also conclude that the analysis of a DBMS is important when preparing a project of any magnitude, since we must visualize the advantages, disadvantages, facilities and features that a DBMS can offer us; studying this particular DBMS we realize that it provides many benefits to the customer, structuring their data and protecting information, as well as in terms of the cost benefit it offers according to its various types of editions that we propose in the licensing offers.

ACKNOWLEDGEMENTS

Constantino Sorto, for his support with the development of the necessary faculties to be able to elaborate this type of documents and quality in education.

REFERENCES

1. Gongloor, P., Minhas, M., Natarajan, A., Robinson, J., & Tsai-Smith, J. (2022, 6 enero). Licensing Information. Oracle Help Center. Recuperado 4 de febrero de 2022, de https://docs.oracle.com/en/database/oracle/oracle-database/21/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4
2. Ashdown, L., & Kyte, T. (2015). Introduction to Oracle Database. Oracle Help Center. Recuperado 4 de febrero de 2022, de https://docs.oracle.com/cd/E11882_01/server.112/e40540/intro.htm#CNCPT001
3. V. (2021, mayo 21). ¿Qué es Oracle y para qué funciona? Blog Emagister. Recuperado 5 de febrero de 2022, de https://www.emagister.com/blog/que-es-oracle-y-para-que-funciona/
4. What is a database management system? (2010). © Copyright IBM Corporation 2010. Recuperado 4 de febrero de 2022, de https://www.ibm.com/docs/en/zos-basic-skills?topic=zos-what-is-database-management-system
5. Oracle Corporation. (s. f.). Transactions. Docs.Oracle. Recuperado 5 de febrero de 2022, de https://docs.oracle.com/cd/E11882_01/server.112/e40540/transact.htm#CNCPT117
6. Oracle Corporation. (s. f.). database. Oracle. Recuperado 5 de febrero de 2022, de https://www.oracle.com/es/database/what-is-database/
7. GeeksforGeeks. (2020, 30 octubre). Difference between RDBMS and ORDBMS. https://www.geeksforgeeks.org/difference-between-rdbms-and-ordbms/
8. Granados, J. (2017, 9 abril). Tipos de datos en la base de datos Oracle. Código Lite. https://codigolite.com/tipos-de-datos-en-la-base-de-datos-oracle/
9. T. (2019, 16 abril). Formación en Oracle: Historia y Características. Formatalent Business School. https://formatalent.com/formacion-en-oracle-historia-y-caracteristicas/
10. Licenciamiento-oracle. (s. f.). neuronet. Recuperado 5 de febrero de 2022, de https://neuronet.cl/licenciamiento-oracle/
11. PL/SQL Tutorial. (s. f.). Tutorialspoint. Recuperado 5 de febrero de 2022, de https://www.tutorialspoint.com/plsql/index.htm
12. Gabillaud, J. (2010). Oracle 11g - SQL, PL/SQL, SQL*Plus. Ediciones Eni. https://books.google.hn/books?id=dGm6ppeU1-oC&printsec=copyright&hl=es#v=onepage&q&f=false
13. Oracle Corporation. (s. f.). Uso de Oracle Data Safe. Oracle Help Center. Recuperado 5 de febrero de 2022, de https://docs.oracle.com/es-ww/iaas/data-safe/doc/regular-expressions.html
14. Zengotitabengoa, A. (2019, 8 febrero). Expresiones regulares en Oracle. BNB. https://www.bnetbuilders.com/expresiones-regulares-en-oracle/
15. Oracle 11g - SQL, PL/SQL y SQL*plus - título, autor. . . | editiones ENI. (s. f.). Ediciones-Eni. Recuperado 5 de febrero de 2022, de https://www.ediciones-eni.com/open/mediabook.aspx?idR=30aa6f4c2a3140cb0454a936f8895bbe

AUTHORS

1. Gabriel Barrientos
2. Andy Avelar
3. Francisco Cáceres
4. Jorge Orellana
5. Reyner Rodriguez