System converted from Oracle version to SQL Server version

Oracle|server|sqlserver WATERXP from Oracle version to SQL Server version

1, System arrangement


For both Oracle and SQL Server versions to be very convenient to transform, but also for two versions can be synchronized, especially the business logic layer. The decision is as follows:

A, two versions of the business logic layer are placed in the source directory. There are two directories in this directory:

SQL and Ora. The two directories have three files:

COMMON.PBL, WATER_MODI.PBL,DW_VERSION.PBL.

The vast majority of these three files are data windows, mainly because of differences in syntax between SQL Server and Oracle. If simply because the data window has double quotes in SQL Server, remove the field quotes from the select syntax of the Data window, because a SELECT statement without quotes is available under SQL Server and Oracle. Note the Update property during the modification process.

B, different databases will use different directories.


2, the establishment of the system environment
The following disk mappings are established on each machine:

P points to \\oraservr\p237

V is pointing to \\oraservr ql237 or \\oraserver\ora237.

The source code is inside the \\oraserver\code\water237 ource.

The P disk is certain to have, and the V disk is determined by what version is used.
3, the source code changes
The changes to the business layer are as far as possible at the source, as this modification allows two versions to be modified at the same time.

P disk is a class library that can be modified without modification.

The Data window in the V disk needs to be changed.

Change the sql237 inside the data window, to change and to note the place:

Method of substitution

Oracle uses SQL Server inside

To_char (readingdate, ' yyyymm ') convert (char (6), readingdate,111)

To_char (readingdate, ' yyyy/mm ') convert (char (7), readingdate,112)

Decode (,,,,) case when then or IsNull (x,0)

Left outer JOIN (+)

Notice the Update property of the data window during the modification process.
4. Work plan
4,1 first modifies the syntax of the Data window in the sql237 directory under three PBL. To take care of the update properties of the data window, it is recommended that you use the method of edit source, and that the quotation marks for the Select syntax field are removed in the SQL Server version. Use the PB replace function.

4,2 modifies the syntax of Embedded SQL for some data windows. Because some embedded SQL also uses Decode (), or to_char (), these syntaxes must also be replaced in SQL Server.

To modify the method:

If gs_database = ' ORACLE ' Then

... decode () ......................

Else

... when then ... when the case is ......... when the case is in the end ...;

End If

4,3 last job is to test. This is the most tedious and important. In the process of testing you will find that there are some data windows that are not available in SQL Server: The modification method is to remove the quotation marks from the fields or move them to the DW_VERSION.PBL file inside the SQL and Ora directory.

4,4 The main data tables have been migrated, like names, maybe in SQL Server there are some tables in the fields not enough so please re-import again. The main stored procedures have been translated and the names are different. In the process of testing, you will find that some views do not exist, then copy the syntax from Oracle and generate them in SQL Server Query Analyzer.