Before you create the repository, you need to create a connection to the mwrep user. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

Note: If this tab is not visible, select View > Connections.

Enter mwrep_orcl in the Connection Name field (or any other name that identifies your connection), mwrep for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

The connection is saved and you can see it listed under Connections in the Connections tab.

Right-click the mwrep_orcl connection and select Migration Repository > Associate Migration Repository.

A progress window appears.

When the repository has been built, click Close.

Click OK.

Select Migration > Third Party Database Offline Capture > Load Database Capture Script Output.

Browse the Capture directory and select the sybase15.ocp file.

The objects are being captured. When done, click Close.

Sybase15 is listed in the Captured Models tab. Expand Sybase15.

Expand dbo to see the list of objects that were captured.

Select Tools > Preferences.

Expand Migration and select Identifier Options.

Make sure "Is Quoted Identifier On" is not selected. This is because the Sybase pubs2 database recognizes double quotes as String literals. If this is set incorrectly it can cause the conversion failure of procedures, triggers and views. Click OK.

Right-click the captured model Sybase15 and select Convert to Oracle Model.

The Set Data Map window appears, that shows you the Source Data Type and what it will be converted to in the Oracle Model. Click Apply.

The conversion is performed. When done, click Close.

Expand Converted:Sybase15 listed in the Converted Models tab.

Expand dbo_pubs2 to view the converted objects.

Select the Converted:Sybase15 model in the Converted Model navigator and expand dbo_pubs2.

Expand Procedures and select expectedToFail. The Migration Log - Log tab contains the list of errors and warnings that occurred during the conversion.

Right-click the Failed To Convert Stored Procedure error and select View Details.

In this example, the error message provides line details to help identify the problematic syntax. Not all errors provide this information. For the purposes of this tutorial this information will be ignored, but during your own migration this information is helpful. Click OK.

You will now fix these errors. Copy the contents of the expectedToFail file and select Migration > Translation Scratch Editor.

Double-click Scratch Editor to enlarge the editor.

Adjust the two text boxes.

Paste the copied text from the expectedToFail file in the Enter 3rd Party SQL: text box.

Click the drop-down list and select Sybase T-SQL To PL/SQL.

Click the Translate icon.

You get an error as expected. Click OK.

Comment out statements and clauses of the DDL and retranslate until the problematic statement or clause is identified. If your having difficulty identifying the issue, watch this.

The space between bang and equals was not recognized. A quick solution would be to remove the space, but a more generic approach for all issues would be to comment out the clause with a TODO comment. Then manually replace it once in Oracle.

Select the contents of the Generated PL/SQL: text box and copy it.

Double-click the Scratch Editor tab.

Click the expectedToFail file.

Click the Edit icon.

Paste the copied content in the new expectedToFail tab that opens.

Scroll down till the where clause you commented.

Resolve the TODO comment yourself. Remove the comments and change the where clause to:

where lower(title) != v_title;

Click the Save button.

Objects that fail to convert are not automatically placed in the generation script. This stops unconverted T-SQL from being run against an Oracle database. For the expecteToFail procedure to be added to the generation script the preference must be set. Select Tools > Preferences.

Expand Migration and select Generation Options.

Select the Generate Failed Objects check box and click OK.

In the Migration Log - Log tab, double-click the Multiple Limitations... message.

The converted procedure, expectLimitations, is opened at the line:

/*TODO:SET QUOTED_IDENTIFIER OFF*/

Click the Edit icon. A new expectLimitations tab opens.

This statement does not have an Oracle equivalent and its conversion is not required. Remove the TODO comment. Click Save.

Double Click on the "Translation Limitation 'VALID_NAME' encountered"

The converted procedure, expectLimitations, is opened at the line:

SELECT VALID_NAME(v_identifierName)

Click the Edit icon.

VALID_NAME is a system function in Sybase. The conversion Limitation informs us that this function has not be automatically converted. The converter can decided how to best approach the migration of the VALID_NAME function.

For the purposes of this tutorial, a function VALID_NAME will be created in the sybase_utilities package that can then be implemented.

Add the text sybase_utilities. before VALID_NAME(v_identifierName).

Save the file. You will update the sybase_utilities package after it has been created in our Oracle database.

Right-click Converted:Sybase15 and select Generate.

The Oracle SQL is being generated. When done, click Close.

The SQL from the script is shown. Select system_orcl from the drop-down on the right. icon.

Click the Run Script (F5).

The results are display from the script execution.

Now that your scripts have run successfully, you can create a connection for the dbo_pubs2 user. Right-click Connection and select New Connection.

Enter dbo_pubs2_orcl in the Connection Name field (or any other name that identifies your connection), dbo_pubs2 for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

Expand the dbo_pubs2_orcl connection.

Expand Tables.

The database tables that were converted to Oracle are listed. Select the AUTHORS table.

Select the Data tab. Notice that currently there is no data in the table. You will migrate the data later in this tutorial.

Select Tool > Preferences.

Expand Migration and select Data Move Options.

Change the Date Mask to the following. This will be used on Sybase SMALLDATETIME columns

Mon dd yyyy HH:mi:ssAM

Change the Timestamp Mask to the following. This will be used on the Sybase DATETIME columns

Mon dd yyyy HH:mi:ss:ff3AM

Click OK.

Note: Although a TIMESTAMP may be used to recognize the Sybase DATETIME values, this will get implicitly converted to a DATE if required during the data load.

Open a DOS command prompt and execute the following commands:

<prompt> cd <location where files are>

<prompt> oracle_ctl

The files are located in the Data directory where you unzipped the files provided in the prerequisites section.

oracle_ctl is a bat file that contains statements to load the data. It uses sqlldr to load the data.

The command executes successfully.

Switch to Oracle SQL Developer and Refresh the connection. The data for the table has been loaded successfully.

In the Connections navigator, expand the Procedures node and select EXPECTLIMITATIONS.

Right-click EXPECTLIMITATIONS and select Compile.

Some errors are displayed in the Compiler - Log tab. VALID_NAME is not yet defined. You have to manually create it.

Under Packages, double-click the Sybase_Utilities node. It opens in a separate tab.

Add the following function:

FUNCTION valid_name(p_identifierName IN VARCHAR2) RETURN NUMBER;

Click the Compile icon.

Double-click the Sybase_Utilities Body node.

Click the Edit icon.

Add the following definition for the function:

FUNCTION valid_name(p_identifierName IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
/*TODO: write logic*/
RETURN 1;
END valid_name;

Click the Compile icon.

Double-click the EXPECTLIMITATIONS tab.

Click the Compile icon. The procedure gets complied successfully.

Under the Procedures node, select the DYNAMICSQL procedure.

The following T-SQL still resides within the converted procedure. The TOP n syntax is not valid in Oracle, so a conversion is required.

EXECUTE IMMEDIATE 'SELECT TOP 10 ' || v_selectList || ' FROM ' || v_tableName || ' WHERE ' || v_condition;

Copy the select statement and open the Translation Scratch Editor (Migration > Translation Scratch Editor).

Select Sybase T-SQL To PL/SQL from the drop-down list.

Paste the copied text in the Enter 3rd Party SQL: text box.

Modify the statement to create a valid "dummy" T-SQL statement. This has to be valid Sybase T-SQL. You can use the text given below. Click the Translate icon.

SELECT TOP 10 dummylist/* v_selectList*/
FROM dummyTableName /*' || v_tableName || '*/
WHERE dummyCondition = 1 /*' || v_condition;*/

The resulting PL/SQL in shown on the right hand side window. This helps define what the Sybase T-SQL TOP n clause can be emulated in Oracle using a ROWNUM condition.

Using the above translation, recreate the Dynamic SQL as follows:

' SELECT '|| v_selectList ||
' FROM ' || v_tableName ||
' WHERE ' || v_condition ||
' AND ROWNUM <= 10';
       

Click the Edit icon in the DYNAMICSQL tab.

Replace the copied text. Note that as this is a dynamic query, and a result set needs to be passed back. See below for the completed procedure. After making the correct modification click the Compile icon.

The procedure gets complied successfully.