What is the difference between oracle and sybase

The difference between oracle and sybase is: 1. The SQL syntax is inconsistent, 2. The post-processing mechanism for over-long fields is different. Sybase’s processing mechanism is automatic truncation, while Oracle does not have an automatic truncation mechanism when inputting over-long characters. 3 , Chinese field byte length is inconsistent, Sybase database 1 Chinese occupies 2 bytes, Oracle database 1 Chinese occupies 3 bytes, 4. Character type conversion, 5. Null value reading results are inconsistent, 6. Query statement output The default sorting is inconsistent, 7. The date format is inconsistent.

# Operating system for this tutorial: Windows 10 system, Dell G3 computer.

The difference between oracle and sybase is:

(1) Inconsistent SQL syntax

1. Different string processing functions: for example, Sybase is substring ( ), oracle is substr(); take the system time, Oracle is sysdate, and Sybase is getdate().

2. The syntax of multi-table joins is different.

(2) Different post-processing mechanisms for overlong fields

For inputs that exceed the field length defined by the database, the Sybase processing mechanism is automatically truncated. When overlong characters are entered, the transaction is successful; Oracle Since there is no automatic truncation mechanism when using characters, the transaction error will not be successful. This different overlong character input processing mechanism causes a large amount of historical abnormal data to succeed in Sybase and fail in Oracle transactions. The input sources of over-long fields include: (1) The input data from the host to the system database; (2) The front-end of the system's own page does not control the character length, and over-long data is input; (3) The system's own program has over-long function names, which are stored in The log table reports an error; (4) The associated system inputs an overlong field. The issues raised by this discrepancy are among the most widespread in this migration, involving hundreds of renovation transactions. Later, in response to the problem of overlong fields, after the project discussion, it was decided to reorganize the transaction-related fields, supplement test cases, conduct special tests, and compare and inspect the fields input by important external systems one by one.

(3) Chinese field byte length is inconsistent

1 Chinese character in Sybase database occupies 2 bytes; 1 Chinese character in Oracle database occupies 3 bytes. For example, some input fields are migrated in the database If the Chinese field length is not expanded before and after, there will be inconsistencies in actual business. For example: On an input page of the system, during the first version test, the development did not expand the field length of the custom remarks (Chinese can be entered), resulting in inconsistencies in the content of the custom remarks before and after database migration due to overwriting. . It is necessary to focus on sorting out the business fields and supported input types (whether Chinese is included).

(4) Character type conversion

Sybase and Oracle have different processing mechanisms for different character types; taking the char character type processing mechanism as an example, there is data of length 1 in the historical data in Sybase '6', after migrating to Oracle, the value will be automatically filled with spaces and changed to '6', causing program logic errors. At first, developers used trim to filter spaces in the program, and then uniformly changed the char type to varchar type.

(5) Null value reading results are inconsistent

Sybase and Oracle have different processing mechanisms for null values. For example: for a null value '' of length 0, Sybase stores it as a length of 1 Space: ''. When querying the message in this field later, this field will be a space with a length of 1; Oracle will store it as null. When querying the message in this field later, this field will be retrieved as NULL, which will result in one less field.

(6) The default order of query statement output is inconsistent

For example: for detailed transactions, the output order of the two databases is inconsistent; at the same time, the output order of the tasks to be processed is also inconsistent.

(7) Date format is inconsistent

The date processing formats of Sybase and Oracle are inconsistent. For example: Oracle datetime format is to_date('2020-05-1012:00:00','yyyy-mm-ddHH24:mi:ss'), and Sybaseddatetime format is '05/10/202012:00:00AM'.

The above is the detailed content of What is the difference between oracle and sybase. For more information, please follow other related articles on the PHP Chinese website!