Table Management on Oracle

Table Existence Checks

Before accessing a table, Uniface performs a number of checks:

• It checks whether the table exists by parsing a SELECT query. The query is only parsed; it is not actually executed, and no data is fetched, but information is gathered for subsequent checks. If an expected table does not exist, it is created, with all columns, primary key and unique key constraints, indexes, and (optionally) the associated package specification and package body.
• It checks whether the Oracle storage formats of all columns are consistent with the information in the application model and connector options that change mapping behavior.

  If storage formats are incorrect, an Oracle connector error is generated. For example, assume you have an Oracle table called MYTABLE with a column called S_C, which has data type S and packing code C. This table was originally created with default connector options and now you attempt to access it with the connector option map fixed length to variable set. The connector generates the following error:

  Oracle connector Error [-80]:
  Column has incorrect Oracle storage format:
  Table MYTABLE, Column S_C,
  expected storage format VARCHAR2, actual storageformat is CHAR.
  

• For fields with a W packing code (Unicode), it checks whether the column's storage format is NCHAR (or NVARCHAR or NCLOB) or CHAR (or VARCHAR or CLOB), and sets the runtime data binding attributes accordingly. This prevents unnecessary data conversions and ensures optimum performance in some circumstances (for example, when fields with W packing codes are mapped to CHAR storage formats).
• When packages are used, it checks that the package version is compatible with the package version of the ORA connector.

To disable these checks, you can set the connector options disable checks or disable all checks.

Caution: Disabling these checks may improve initial table access and viewing performance, but in the case of some Unicode database configurations, it may degrade performance. For more information, see disable checks and disable all checks.

Overflow Tables

If Uniface variable-length techniques are used in an entity, both a base table and an overflow table are created. The name of the base table is the name of the entity in the application model (or the name assigned by means of entity assignment in the assignment file). The name of the overflow table is the name of the base table, prefixed by the character O. This means that when you are using Uniface variable-length techniques, you cannot define two entities where the name of one is the name of the other prefixed by the letter O.

Note:  The name of an overflow table is the name of the base table prefixed by an O. If, however, the entity name contains one or more dollar signs ($) or hash marks (#) (which is only possible via entity assignment, but not in the application model), the O is inserted directly after the last $ or #. For example, with the entity assignment MYENTITY.MYSCHEMA = $ORA:NEW$NAME, the base table is named NEW$NAME and the overflow table is named NEW$ONAME.

The primary key in the overflow table is made up of the primary key fields from the base table and a field identifying the overflow segment.

Location of Schema Objects

Uniface creates and accesses all objects in the Oracle schema of the user specified in the logon path, unless you specify otherwise. To access schema objects in another schema, you can set up public synonyms, or set up synonyms in the schemas which are accessed by Uniface.

Note:  These are the only ways that you can access schema objects in another schema when using Uniface.

Definition Dependency

The definitions of Oracle tables and indexes are taken from the definitions of entities in the Uniface application model. If you change the application model entity definitions after the Oracle tables and indexes have been created, or after the generation of the SQL script to create those tables and indexes, you may have to re-create or alter those tables and indexes. For this reason, you must keep track of the dependencies between the Uniface application model and the schema objects in Oracle.

Views

Uniface can access views in Oracle, if those views have been correctly described in the application model. You must use an appropriate combination of Uniface data type and packing code to describe the storage format of every column in the view. This is particularly important when a select list expression in a view is an SQL function.

For example, substrings returned from the SUBSTR() function are fixed-length strings returned with trailing spaces. Such a select list item is equivalent to a Char column, and not to a Varchar2 column. Alternatively, you can use the RTRIM() SQL function in the view to remove trailing spaces and describe the select list item as a Varchar2 column.

When you define a view in the application model, you must make sure that the fields you define have the same names as in the alias list of the Oracle view definition. See your Oracle documentation (the CREATE VIEW statement) for more information. If a view is not modifiable, you must mark the entity as non-modifiable in the application model.

Uniface uses ROWIDs with modifiable entities. If a view is defined as a join expression or it is defined using a set operator (for example, UNION), the ROWID pseudo column cannot be selected. In that case, you must mark the view as non-modifiable in the application model. This is not an extra limitation, because such views are not modifiable in Oracle. If you do not mark a view with a join expression or set operator as non-modifiable in the application model, Uniface attempts to use ROWIDs, and Oracle generates the following error:

ORA-1445: cannot select ROWID from view with more than one table

Uniface uses stored packages with modifiable entities (provided the entity meets a number of other requirements. For more information, see Oracle Stored Packages.)

If a view is modifiable, you can choose between the following alternatives:

• Create the associated package for the view. For this purpose, you must first describe the entity in the application model, set the connector option upgrade packages, and run the Create Table facility for the entity describing the view.
• Set the connector option ignore missing packages.
• Set the connector option disable packages.