Using an Oracle DB in E3.

1) Introduction

This articles discusses a few specificities of how Elipse E3 works with Oracle.

We do not show how to setup Oracle databases here. This should be conducted by a professional qualified in the installation and setup of Oracle. Still, some setup items are necessary for E3 to access the server. So it is fundamental for you to obtain basic information on the functioning of an Oracle database server in order to be successful in configuring project setups in the E3.

2) Connection setup

Communication with the Oracle DB is conducted in the E3 in a similar manner to other types of available servers: MS Access (which is a standard database) and MS SQL Server. Setup parameters are shown in in the figure below. These parameters should be received from the database administrator. Due to the complexity of the operation and management of Oracle servers, installation and basic setup are not covered in this document. Normally, choosing an Oracle (or even MS SQL) Server, there are professionals assigned to maintain and administer it.

The parameters shown above are:

• Connection: the connection name provided by the database administrator.
• User: the database user available for connection by the Elipse E3. Depending on the object type to be used in the project, the user should have different permissions. There is a table illustrating the needs of the object later in this document.
• Password: for the user who is intending to logon.

3) Error messages and restrictions

The connection to any database through the E3 can be monitored by creating log files. These logs are automatically created from the actions made by the E3 Server and do not cover the databases only. Therefore, the type of message logged makes clear the origin of the errors.

Some intentionally provoked error codes have been classified to facilitate the location of failures. Here are some error descriptions and possible causes (some restrictions of database use are contained within the errors).

Error exclusive restriction = ORA-01031: insufficient privileges
Most times, this message is generated when the user does not have enough privileges to create an object, such as tables, views, procedures, etc. 

Error = ORA-01017: invalid username/password; logon denied
Message generated in log when the password or the username are incorrect. Check if the user exists in the available connections.

Error = ORA-12154: TNS: It was not possible to solve service name
In the event the connection name is not found, this message will be displayed. Even though the database is correctly installed and working, the connection name may contain errors. For example, check that the correct combination of upper- and lower-case letters has been used in typing the connection name.

Error = ORA-02264: name already used by an existing restriction
The use of indexes or primary keys with the same name is not allowed and may cause tables creation errors as well as typing and consultation data errors. In order to prevent this, create and setup different names for indexes and primary keys in the E3.

Error = ORA-01403: data not found
When an error occurs, such as the use of indexes with the same name, the error normally appears above. Typically, this error is followed by another one providing better details of the type of problem that is occurring.

Error = ORA-00001: exclusive restriction (SYSTEM.E3INDEX) violated
Whenever there is an attempt to enter a record where the field defined as a primary key has a repeated value, it will be refused and the above message shown. It is interesting to note that the key name is shown (in this case, SYSTEM.E3INDEX) in the format Usuário.NomeChave (User.KeyName). Depending on the project this error is expected, but the majority of the time it is important to check if the best key has been used. Here are two cases to illustrate this:

• Power Tele-Measurement system: Designed to save the entire day’s data, the routines used for collection admit upper and lower margins in the time to be collected. Thus, some records will have to be typed again. In this case, the occurrence of this error is expected.
•  Alarms Maintenance: In this case, there can be more than one error per second in the system. If the field “E3TimeStamp” is used as a primary key, there can be a loss of some alarm occurrences, disrupting a post analysis of events. In this case, a proper solution may be to re-study the primary key.

4) E3 objects and permissions

It is necessary to setup the permissions in the DB for using E3 objects in an application. These permissions are listed below:

In Permissions, the expression Full Permission means that the user will have permission to:

• Create a table.
• Create indexes and keys.
• Delete existing tables.
• Input and delete data in the tables created.
• Consult the resulting tables.
• Create Stored Procedures.

Server administrator permissions (such as the creation of users, etc.) are not necessary for users available to E3.