Precautions for migrating a program from MySQL to Oracle

ProgramConsiderations for migrating data from MySQL to Oracle

When migrating a program from MySQL to Oracle, You need to note the following:

4. Processing long strings

Oracle processing of long strings also has some special features. The maximum length of a string that can be operated during insert and update is less than or equal to 4000 single bytes. If you want to insert a longer string, use the clob type for the field to use the dbms_lob package that comes with Oracle. Before inserting a modification record, you must make a non-null and length judgment. If the field value cannot be blank or exceeds the length value, a warning should be given, and the last operation is returned.

5. Processing date fields

MySQL date fields are divided into Date and time. The Oracle Date field is only date, which contains information about the year, month, day, hour, minute, and second. the system time of the current database is sysdate, accurate to seconds, or use a string to convert to the date type function to_date ('1970-08-01 ', 'yyyy-MM-DD') year-month-day 24 hours: minute: Second format YYYY-MM-DD hh24: MI: SS to_date () has many date formats. For more information, see Oracle Doc. convert a datetime field to a string function to_char ('2017-08-01 ', 'yyyy-MM-DD hh24: MI: ss ')

The mathematical formulas for date fields vary greatly. MySQL uses date_field_name> subdate (now (), interval 7 day) to locate the current time seven days from the current time and uses date_field_name> sysdate-7;

6. Handling of null characters

Non-empty fields in MySQL are also empty. empty fields are not allowed in Oracle. The Oracle table structure is defined based on the not null value of MySQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the null character. If it is null or null, you need to change it to a space string.

7. Fuzzy comparison of strings

MySQL uses the field name like '% string %', and Oracle can also use the field name like '% string %'. However, this method cannot use indexes and is not fast, if you use a string to compare the instr function (field name, 'string') to 0, you will get more accurate search results.

8. After operating the database in programs and functions, pay attention to the release of the result set and pointer.

PDM Chinese website www.pdmcn.com