Data Retrieval on Oracle

The read and selectdb ProcScript statements are supported, with some restrictions.

read

The following clauses of the read ProcScript statement are supported, with the indicated restrictions:

  • offset option—enables Oracle's data pagination functionality to be used instead of the Uniface hitlist mechanism. This is supported only by the ORA U7.0 connector or higher.

  • where—all text between the double quotation marks ("") is inserted literally as entered. For example, read where "EMPNO > 10" results in the where clause: 

    WHERE (EMPNO > 10) AND (...)

    Because the text is inserted literally as entered, values must be entered in a format acceptable to Oracle, taking National Language Support rules into account.

  • order by—supported using both ascending and descending order, but cannot be applied to Long, Long Raw, BLOB, and CLOB fields. Sorting is always performed by Oracle.

  • u_where—cannot be applied to Long, Long Raw, BLOB, and CLOB fields in Oracle.

The offset option to support native data paging is also supported.

selectdb

The selectdb ProcScript statement is supported but cannot be applied to Long and Long Raw fields in Oracle.

Retrieve Profiles

Uniface uses the ESCAPE clause with the LIKE operator to prevent Oracle wildcard characters in Uniface retrieve profiles from being interpreted. The backslash character (\) is used as the escape character. Some examples of Uniface retrieve profiles and their corresponding Oracle LIKE profile are shown in the table:

Uniface to Oracle Retrieve Profile Mpaaings

Uniface Retrieve profile

Oracle LIKE Profile

c_c GOLD *

c\_c%

c\_c GOLD *

c\\\_c%

c% GOLD *c

c\%%c

Retrieve profiles on character string fields behave differently on fields with the Char storage format than on fields with the Varchar2 storage format. For example, when the value Uniface is stored in a Char(10) and a Varchar2(10) field, respectively, the value matches the profile GOLD *FACE on the Varchar2 field, but not on the Char field. The reason for this is that the Char field stores trailing spaces, and contains 'Uniface' (three trailing spaces). The profile UNI GOLD * matches on both fields.

As a general rule, retrieve profiles on fixed-length Char fields only give the expected result if the last non-blank character is the GOLD * profile character, or if there is no GOLD * profile character in the retrieve profile.

Retrieve profiles can contain a maximum number of 400 operators. If a where clause exceeds this limit, error -88 Invalid WHERE clause expression requested is raised.

Related Topics