SQL Support on Oracle

Transaction Management

If a transaction manager is running, commit and rollback is managed by the transaction manager rather than the ORA connector. When you issue the commit and rollback ProcScript commands, they are not passed to the ORA connector but to the transaction manager connector. The commit and rollback instructions should not be used in the SQL Editor. For more information, see the documentation for your transaction manager.

Selected Data

All data that is selected, except for Long Raw or BLOB fields, is converted to the Varchar2 storage format by Oracle with a maximum length of 2000 characters.

Long Raw or BLOB data is converted by Uniface to a character string in hexadecimal format, with a maximum length of 2000 characters.

If data is truncated when it is returned to Uniface, no error is generated. When you use the sql ProcScript statement or the SQL Workbench to retrieve Long data, it is truncated at 42 bytes. To avoid this truncation, use a Uniface entity that maps to the table and do a normal retrieve of the entity.

SQL Editor

In the SQL Editor, the maximum length of one row of the formatted result is 8190 bytes. When this length is exceeded, the ORA connector generates the following error:

Oracle connector Error [-27]:
Selected data too large for SQL Workbench.

Data Formats

Data formats in the sql ProcScript statement and SQL Workbench are dependent on Oracle National Language Support parameters. This is the case both for input and output data.

It is possible to use Unicode characters in ProcScript and the SQL Workbench.

$result and $status

When you use the sql ProcScript statement, the connector returns values to $result and $status that depend on the SQL statement used. The following list describes a few different circumstances:

• If you use the SQL SELECT statement and it succeeds in selecting one or more rows, the first field of the last row is returned as a character string in $result. The conversion to character string occurs as described above. The number of rows selected is returned in $status.
• If the SQL statement is not a SELECT statement, or if the SELECT statement succeeds but selects zero rows, $status has a value of zero and $result is not modified.
• If the SQL statement fails, $status has a value less than zero and the value of $result is undefined.

In the SQL Workbench, the return status is shown in the message area.

Other Considerations

In the SQL Workbench, Uniface presents data using a fixed-column length format. To preserve this format, Uniface rearranges the order of the selected columns by moving all Long, Long Raw, CLOB, and BLOB fields to the end. In the sql ProcScript statement, the first field returned in $result is always the first field selected in the query, even if it is a Long or Long Raw field.

SQL statements must not be terminated with a semicolon and you cannot use embedded SQL.

Do not use the sql ProcScript statement to count rows in this way, as this is very inefficient. If you want to count rows, use the selectdb or lookup ProcScript commands.

Note:  If you are using ODBC to manipulate numeric data, and the data returned from Oracle contains more than 32 digits, the ORA connector truncates the returned data to 32 digits.

If you wish to use ROWID in a query, you must explicitly convert the ROWID to or from character format to the Oracle8 internal ROWID format. Thus, SELECT statements should select ROWIDTOCHAR(ROWID) rather than ROWID, and conditions should have the syntax ROWID=CHARTOROWID('RowID').

Special Considerations for PL/SQL

PL/SQL is supported in the sql ProcScript statement and SQL Workbench, provided that the Oracle server to which you connect has the Procedural option. Calling stored procedures and functions is possible.

PL/SQL statements and statement blocks are terminated by a semicolon. PL/SQL code must be included in an anonymous PL/SQL block. For example:

sql "begin raise_application_error(-20000, 'I goofed!'); end;", "ora"

When this statement is executed, $status returns a negative value and $dberror contains the error number which is generated. Oracle errors in $dberror are always positive; $dberror returns 20000 in this case.

As a special service for PL/SQL, Uniface allows you to pass one data item back to Uniface, provided that this data item is convertible to the Varchar2 storage format and has a maximum length of 2000 characters. To achieve this, Uniface binds a placeholder called :uresult when it is referenced in the PL/SQL block. (See your Oracle PL/SQL documentation for more information on placeholders.)

For example:

sql "begin select user into :uresult from dual; end;", "ora"

The value which is stored in :uresult by the PL/SQL block is available as a character string in the ProcScript variable $result. The conversion to a character string occurs as described above. When the PL/SQL block reads the value of :uresult, it is a Varchar2 (2000) variable which holds the NULL value.

The situations which can arise, and the values they return, are as follows:

• If a PL/SQL statement which does not reference the :uresult placeholder succeeds, $status has a value of zero and $result is not modified.
• If a PL/SQL statement references the :uresult placeholder, but either assigns no value or the NULL value to it, $status has a value of 1 and $result is the empty string.
• If a PL/SQL statement succeeds and assigns a non-NULL value to the :uresult placeholder, $status has a value of 1 and $result contains the value assigned to the :uresult placeholder converted to a character string.
• If the PL/SQL statement fails then $status returns a value less than zero and the value of $result is undefined.

In the SQL Workbench, the return status is shown in the message area.

The SQL Workbench also supports PL/SQL and the :uresult placeholder. This means that you can test PL/SQL blocks in the SQL Workbench.

Monitoring SQL in the Message Frame

When Uniface logs on to Oracle, the connector displays a message that identifies the connector version, the connector options in the USYS$ORA_PARAMS assignment, and the current major and minor package version numbers of the connector. For example, the following message appears:

U6.1 ORA connector
for Oracle 8.1.7.
connector options:
step size 20.
The current major
package version of the connector is: 1.
The current minor
package version of the connector is: 0.

Apart from this message, only SQL and PL/SQL issued by Uniface are displayed in the message frame.

Because of the way Uniface uses SQL and PL/SQL with Oracle, the information you can see in the message is restricted in the following ways:

• Uniface displays only those statements which are both parsed and executed. For example, the table existence check which is performed when a table is accessed for the first time is not shown, because it is only parsed and not executed.
• Uniface uses a statement cache, which means that it re-executes statements which have already been generated and parsed. When this occurs, no SQL is regenerated, and therefore no SQL appears in the message frame.
• Uniface uses placeholders and host language binding, instead of supplying actual data in the SQL statement. This means that you cannot see data in the message frame. Placeholders are identifiers, preceded by a colon. The placeholder names are generated by the ORA connector, and are generally not very meaningful. For example:

  SELECT "USER" FROM "DUAL" WHERE "DUMMY" = :"WPH1"

• Uniface implements commit and rollback using functions in the Oracle Call Interface, instead of using SQL statements. You cannot, therefore, see the commit and rollback statements in the message frame.
• Uniface implements multiple I/O requests by means of one stored procedure. You can see the procedure call as an anonymous PL/SQL block in the message frame, but you cannot see which I/O request is being called. When multiple I/O requests occur, you usually see the procedure call only once, as it is cached in the statement cache managed by the ORA connector.