A project just a while ago is to port the database of SQL Server to Oracle. The requirement is to modify the original code so that the application can run on two different databases at the same time. This is my first attempt to operate Oracle using C. I 've taken a lot of detours, but now I want to take a few detours.
1. in Oracle, the table name, field name, stored procedure name, and variable name cannot exceed 30 string lengths.
2. Top syntax is not supported in Oracle. Use where rownum <n instead. However, you need to note that if there is a sorting operation before top, you need to first sort the operation and get it using rownum.
3. The usage of optical standards in Oracle is quite different from that in SQL Server, mainly in terms of loop control.
4. If a column in Oracle is of the date type, the default format is yyyy-mm-dd.
Where coldate = '2017-06-08 17:14:57 'will result in an error, '2017-06-08' Can. If you need to accurately compare the time (hours, minutes, seconds), you can use the following methods: to_date ('2017-6-8 17:14:57 ', 'yyyy-mm-dd hh24: MI: SS '). Since Oracle and sqlserver have different implementation methods for SQL standard expansion on datetime, the same
SQL implementation.
5. The timestamp (timestamp) in Oracle differs greatly from that in SQL Server. Timestamp in sqlserver is stored in the database in binary format. You can set this field type in dataset to base64binary. Timestamp in Oracle is stored in time format. Therefore, you cannot use the same dataset to load both SQL Server Timestamp and Oracle timestamp. Because this value is rarely used in applications, you can not retrieve this field.
6. The timestamp in Oracle cannot be automatically filled when SQL Server imports data. You can set the default value of systimestamp to implement similar functions.
7. Oracle does not have the Boolean field type. You can use intger or Char to replace the bit type in SQL Server.
8. Oracle will treat "null strings with multiple null values. That is to say, when an application inserts an empty string into the database, the actual database will try to insert a null string. If this field is of the notnull type, an error occurs. In this case, you can use another default value to replace the Null String. When importing data from sqlserver to Oracle, an empty string in the notnull column will cause the export operation to fail. In this case, you can manually modify the SQL script in the export wizard and set the null string to a special value.
9. When using oledbcommand to call the Oracle stored procedure, you should note that the parameters passed in by the application and stored procedure match in sequence rather than by name, which is essentially different from SQL Server. If you encounter errors such as e_fail during debugging, you can check the program in the following sequence.
(1) check whether the number of input parameters of the application is consistent with the number of parameters in the stored procedure.
(2) It is important to check whether the order of input parameters of an application is the same as that of stored procedure parameters. mismatch may easily lead to various seemingly strange problems. We recommend that you write an automatic generation tool for a relatively simple Stored Procedure (adding, deleting, and modifying a single table) to complete this operation.
(3) check whether the stored procedure is correct.
10. Pay special attention to using oledbdatareader objects. In SQL Server, we can use a SELECT statement to return a datareader In the stored procedure. However, we cannot do this in Oracle. We must use ORACLE ref cursor to return the query result. However, you must use the oraclecommand. executereader () method instead of the oledbcommand object. Therefore, to use datareader in Oracle, you must use the for ORACLE data provide class package provided by Microsoft. This framework1.0 does not exist by default and needs to be downloaded. It exists by default in framework11.1. For details, refer:
Http://msdn.microsoft.com/library/chs/default.asp? Url =/library/CHS/cpguide/html/cpconusingadonetproviderstoaccessdata. asp