Consultor Eletrônico

Status: Unverified

GOAL:

Why isn't EBCDIC sort with ORACLE DataServer supported?

FIX:

Oracle's NLS_SORT specifies the collating sequence for queries. If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).

If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. When NLS_SORT is set to EBCDIC in init.ora, ORACLE can perfom sorting according to EBCDIC (Extended Binary Coded Decimal Interchange Code)standard.

Following are reasons we do not support EBCDIC sort with ORACLE DataServer:

The DataServer almost always leaves the sorting to ORACLE. There will be problems if the sort order is different from the collating sequence used by the client. The Progress client will in many cases do selection on the result set provided by ORACLE. If the "name > 'foo'" test is true for EBCDIC, but false for the client, the row will be removed by the client.

The DataServer also generates where clauses using < and > for find statements that reposition each other and in response to a query index reposition. The DataServer will also use the ORACLE UPPER function to
perform case insensitive selection on non-indexed columns (For indexed columns, the DataServer maintains a separate column populated with the the client believes (based on code page, etc) to be the uppercase
equivalent of the original column. It is this shadow column that is indexed and used in the where and order by clauses.

1. Use run stored procedure send-sql-statement
("alter session ...") to send requests directly to ORACLE.

2. Depending on how the application is written and the data, customer can try run sample queries to see whether it works, chances are greater if ORACLE and Progress both have the same collating sequence, and map lower case to upper case the same way.