2.3 Setting up SQL Translation Framework OBE for Database 12c Release 1

«« Previous
Next »»

1. Overview


Purpose

This tutorial shows you how to use have an environment ready to demo the new Oracle Database 12c feature, SQL Translation Framework, which is used to aid in the migration of 3rd party database and applications to the Oracle database. In this OBE, you will migrate a Sybase ASE database and its associate Java application to Oracle 12c Database. This tutorial will show how to migrate a Java Sybase Application to run against Oracle Database with minimal code changes using the SQL Translation Framework.

Time to Complete

Approximately 90 minutes

Introduction

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Sybase Migration Overview

Using Oracle SQL Developer, you can quickly migrate your third-party database to Oracle.

There are four main steps in the database migration process:


Capture the Source Database The first step is to capture a "snapshot" of the Sybase database. This can be done in two ways.

  1. Online Capture: This requires creating a connection in SQL Developer to a live Sybase database. Using JDBC, the Sybase database metadata can be accessed and the Capture Model created. 
  2. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Sybase database metadata to files. These files can then be "captured" by SQL Developer to create the Captured Model. The second method is what you will perform in this tutorial. 

Using SQL Developers Offline Capture feature, the pubs2 sample database has been extracted into offline data files. The files generated by the Capture tool contains the database schema information for the Sybase pubs2 database. Oracle SQL Developer uses these files as the basis for building a representation of the structure of the source Sybase database. This structure is called the Captured Model.
Convert the Captured Database Oracle SQL Developer uses the Captured Model to convert the captured objects to Oracle-format objects, building up a representation of the structure of the destination database. This structure is called the Converted Model.
Generate the Oracle Database Oracle SQL Developer generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Data The last step in the process is to migrate the data. You can do this in one of two ways.

  1. Online Data Move: You can create a connection from within Oracle SQL Developer to your Sybase source database and migrate the data. 
  2. Offline Data Move: You can export the data from Sybase. SQL Developer will create a series of BCP and sqlldr files that you can run from a batch file.

The second method is what you will perform in this tutorial.

SQL Translation Framework

SQL Translation Framework translates the SQL statements of a client application from a foreign (non-Oracle) SQL dialect into the SQL dialect used by the Oracle Database. In addition to translating non-Oracle SQL statements, the SQL Translation Framework can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue. In this way, you can address an application issue without patching the client application.The SQL Translation framework is installed as part of Oracle Database installation. However, it must be configured to recognize the non-Oracle SQL dialect of the application and you must install at least one translator to fully utilize the framework. Before using the SQL Translation Framework, you should migrate your data, schema, stored procedures, triggers, and views.

The SQL translation framework consists of the following two components:
The SQL Translator : The SQL Translator is a software component, provided by Oracle or third-party vendors, which can be installed in Oracle Database. It translates the SQL statements of a client application before they are processed by the Oracle Database SQL compiler. If an error results from translated SQL statement execution, then Oracle Database SQL compiler generates an Oracle error message. The SQL Translator automatically translates non-Oracle SQL to Oracle SQL, thereby enabling the existing client-side application code to run largely unchanged against an Oracle Database. This reduces the cost of migration to Oracle Database significantly. 

The SQL Translation Profile: The SQL Translation Profile is a database object that contains the set of captured non-Oracle SQL statements, and their translations and translation errors.The SQL Translation Profile is used to review, approve, and modify translations. A SQL Translation Profile is associated to a single SQL Translator. However, a translator can be used in one or more SQL Translation Profiles. Typically, there is one SQL Translation Profile per application, otherwise applications can share translated queries. You can export profiles among various databases. Inside the Database, the SQL statements are translated by the SQL Translator, registered with the SQL Translation Profile, to handle the translation for the non-Oracle client application. 

Hardware and Software Requirements (Optional)

The following is a list of hardware and software requirements:
  • Oracle Database 12c
  • SQL Developer 3.2.20_09 or later.
Prerequisites
  • Before starting this tutorial, you should: 
  • Download and install Oracle Database 12c.
  • Download and unzip the files.zip file into your working directory (i.e.wkdir)
  • Download and unzip the SQLApp.zip file into your desktop
  • Before starting the OBE, please perform the following:
    • Open the terminal 
    • Type :  . oraenv
    • To Make sure ORACLE_SID and ORACLE_HOME are set properly type: env | grep ORA
    • Start sqlplus
    • Enter username: sys as sysdba
    • Enter Password: oracle

2. Creating the mwrep User



SQL Developer persists information about the migration into a set of Oracle database tables called the migration repository. Its best to create a new user (mwrep) in the Oracle database to contain the migration repository. To create a new database user, perform the following steps:

Note:If you already have a noncdb_user connection and a mwrep user, you can skip these steps.

1. If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer session. If you do not have the icon located on your desktop, find the executable file (either named "sqldeveloper.exe" on Windows or "sqldeveloper.sh" on Linux), and run it.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Select View > Connections.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test. 
Connection Name: noncdb_user
User Name: sys
Password: <your_password>(Select Save Password) 
Role: SYSDBA
Hostname: localhost
SID: <your_own_SID>

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. 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 Save. 
Then click Connect. Close the window.

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1

7. Expand noncdb_user. 
Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

Setting up SQL Translation Framework OBE for Database 12c Release 1

8. Enter the following code in the SQL Worksheet to create a user for the migration repository 
CREATE USER MWREP IDENTIFIED BY mwrep

DEFAULT TABLESPACE USERS 

TEMPORARY TABLESPACE TEMP; 

GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

Setting up SQL Translation Framework OBE for Database 12c Release 1

9. Click Run Script.

Setting up SQL Translation Framework OBE for Database 12c Release 1

10. The mwrep user was created successfully.

Setting up SQL Translation Framework OBE for Database 12c Release 1

11. Grant unlimited quota to USERS tablespaces to MWREP using the following command: 
alter user mwrep quota unlimited on users ;

Setting up SQL Translation Framework OBE for Database 12c Release 1

Note: It's a new privilege in 12c required to write to tablespaces.


3. Creating the Migration Repository



To convert the Sybase database to Oracle, you need to create a repository to store the required repository tables and PL/SQL packages. To do this, perform the following steps: 
Note: If you already have a mwrep_noncdb connection and a migration repository for it, you can skip these steps.

1. 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.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test. 

Connection Name: mwrep_noncdb
User Name: mwrep 
Password: <your_password> (Select Save Password)
Hostname: localhost 
SID: <your_own_SID>

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. 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 Save. Then click Connect. Close the window.

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1

6. Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. 
A progress window appears. Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1


4. Capturing the Sybase Exported Files



The procedure for creating and running the Offline Capture Scripts  the Sybase database scripts has been completed for you and the files are available in the zip file provided in the prerequisites. To load the Offline Capture Scripts captured Sybase database scripts  into Oracle SQL Developer, perform the following steps:

Note: It is important that the password is saved with the connection, otherwise the installing of the SQL Translation Profile will not work.

1. Select Tools > Migration > Migrate.


Setting up SQL Translation Framework OBE for Database 12c Release 1

2. The migration wizard appears and it will walk you through the steps to convert third party databases to Oracle. Click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. In step 2, you need to select a connection for the migration repository. Select the mwrep_noncdb connection, and click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. In step 3, you need to provide the Project details. A Project is a container for the migration entities. Provide a Name to the project, and specify the Output Directory. Click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. In step 4, you need to provide the Source Database details. Select the mode as Offline. Click Browse to select the Offline Capture Source File.

Setting up SQL Translation Framework OBE for Database 12c Release 1

6. Select sybase15.ocp file from the files downloaded onto your working directory as part of the prerequisites and click Open.

Setting up SQL Translation Framework OBE for Database 12c Release 1

7. Click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

8. In step 5, you need to select the database to capture. Select the pubs2 database and check Proceed to Summary Page. Click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

9. Click Finish.

Setting up SQL Translation Framework OBE for Database 12c Release 1

10. The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1


5. Checking Conversion Preferences



It is important to review the conversion preferences at this point. To do so, perform the following steps:

1. Select Tools > Preferences.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Expand Migration and select Identifier Options. Make sure "Sybase: 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.

Setting up SQL Translation Framework OBE for Database 12c Release 1


6. Converting the Captured Model



To convert the captured model into the converted model, perform the following steps:

1. As part of the convert, you should first set the "Use all Oracle 12c features in Migration". Go to Tools->Preferences. Expand Migration and select Generation Options.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. In the Migration Projects tab, select and right-click the captured database. Click Convert.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. In the Migration Wizard, click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. You can review the default data type mappings. Check Proceed to Summary Page. Click Next. 

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. Click Finish.

Setting up SQL Translation Framework OBE for Database 12c Release 1

6. The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1

7. In the Migration Projects tab, expand Converted Database Objects. Expand pubs2 > dbo_pubs2 > Procedures. Here you will find a procedure "expectedToFail" that has a red "x" icon against it. This is a failed procedure.

Setting up SQL Translation Framework OBE for Database 12c Release 1

8. Select dbo_pubs2 tab. Select Conversion Issues. This will display a report of "Translation limitation" and "Errors".

Setting up SQL Translation Framework OBE for Database 12c Release 1


7. Resolving Stored Procedure Conversion Failures



An error represents the failure to convert an object. This generally only affects objects defined in T-SQL (Procedures, Triggers, Functions, and Views). These objects are available in the Converted Model after the conversion, but they remain defined in Sybase T-SQL and have not been converted to Oracle PL/SQL. Generally an object fails to convert because a part of the T-SQL is not recognized. Once this part of the T-SQL is identified, it can be worked around so that the majority of the translation can be performed automatically. Leaving only a small section of T-SQL to manually translate. In this tutorial, the sample database has been seeded with one procedure that fails to convert. The following steps outline how to go about identifying the issue and complete its conversion. The steps used here are the same for any type of conversion failure. To resolve the errors, perform the following steps:

1. Select the Converted Database Objects in the Migration Projects and expand dbo_pubs2.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Expand Procedures and select expectedToFail.

Setting up SQL Translation Framework OBE for Database 12c Release 1.

3. Copy the contents of the expectedToFail procedure.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. Select Tools > Migration > Translation Scratch Editor.

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. Paste the copied text from the expectedToFail procedure in the left side text box.

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1

7. Also, select the project model from the drop-down list.

Setting up SQL Translation Framework OBE for Database 12c Release 1

8. Click Translate

Setting up SQL Translation Framework OBE for Database 12c Release 1

9. You get an error as expected. Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1

10. Commenting out statements until the procedure translates will help you identify the problematic statement. In this case there is a new line between the bang and equals -"where lower(title) != @title". 
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. Workaround the issue and click Translate again. You can notice that the code has been successfully translated into PL/SQL on the right side textbox.

Setting up SQL Translation Framework OBE for Database 12c Release 1

11. Select the contents of the corrected expectedToFail procedure from the left side text box and copy it.

Setting up SQL Translation Framework OBE for Database 12c Release 1

12. In the Migration Projects navigator, click Captured Database Objects > pubs2 > dbo > Procedures.

Setting up SQL Translation Framework OBE for Database 12c Release 1

13. Select the expectedToFail procedure. Paste the copied content over the existing expectedToFail procedure.

Setting up SQL Translation Framework OBE for Database 12c Release 1

14. Click Save.

Setting up SQL Translation Framework OBE for Database 12c Release 1

15. Right-click expectedToFail procedure in the left-side navigation bar under Procedures. Select Reconvert.

Setting up SQL Translation Framework OBE for Database 12c Release 1

16. Click OK on the dialog box.

Setting up SQL Translation Framework OBE for Database 12c Release 1

17. The procedure "expectedToFail" in the Converted Database Objects is not erroneous after conversion.

Setting up SQL Translation Framework OBE for Database 12c Release 1


8. Generate Target Database for Database Creation




To generate target database, perform the following steps:

1. In the Migration Projects tab, right-click Converted Database Objects. Select Generate Target.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. In the Migration Wizard, click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. This step provides you the Target Database details. Select the mode as Offline. You can select to drop the target objects here. Note down the Generated Script Directory location. Check Proceed to Summary Page. Click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. Expand Generate Script. Click Finish.

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1


9. Executing the Script to Create the Oracle Database Objects



To generate the SQL script with DDL statements that will be executed to create the objects in an Oracle Database, perform the following steps:

1. On the SQL Worksheet Toolbar, select noncdb_user from the drop-down list on the right.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Click Run Script.

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test. 
Connection Name: dbo_pubs2_noncdb
User Name: dbo_pubs2 
Password: <your_password> (Select Save Password)
Hostname: localhost 
SID: <your_own_SID>

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. 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.

Setting up SQL Translation Framework OBE for Database 12c Release 1

6. Expand dbo_pubs2_noncdb.

Setting up SQL Translation Framework OBE for Database 12c Release 1

7. Expand Tables.

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1

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

Setting up SQL Translation Framework OBE for Database 12c Release 1


10. Checking Offline Data Move Preferences



A date format mask can be specified in the preferences so that the Offline Data Move scripts (in particular, the Oracle SQL*Loader control files) can reference the correct format. To do so, perform the following steps:

1. Select Tool > Preferences.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Expand Migration and select Data Move Options.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3.  Make sure the Generic Date Mask format matches the following 
Mon dd yyyy HH:mi:ssAM

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. Make sure the Generic Timestamp Mask matches the following. 
Mon dd yyyy HH:mi:ss:ff3AM 
Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1



11. Create the Offline Data Move Scripts



To move data to the target database, perform the following steps:

1. In the Migration Projects tab, right-click Converted Database Objects. Select Move Data.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. In the Migration Wizard, click Next.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. This step provides details about the Data Move . Select Offline. Note down the location of Data Move Script Directory. Click Next.


Setting up SQL Translation Framework OBE for Database 12c Release 1

4. Expand Move Data. Click Finish.

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1



12. Migrating the Data



The data has already been exported generated from Sybase.To import the data using the scripts provided, perform the following steps:

1. Open a terminal and execute the following commands: 

<prompt> cd <location of the unzipped files\files\DataMove\sybase_obe\2011-02-01_09-25-25>

<prompt> oracle_ctl.sh <username> <password>@<connection>
Here we used: ./oracle_ctl.sh system oracle@noncdb

 Note: To make oracle_ctl.sh executable run the following command.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Note: oracle_ctl is a sh file that contains statements to load the data. It uses sqlldr to load the data.

Setting up SQL Translation Framework OBE for Database 12c Release 1


2. Switch to Oracle SQL Developer and click Refresh.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. The data for the table has been loaded successfully.

Setting up SQL Translation Framework OBE for Database 12c Release 1


13. Setup SQL Translation Framework



Perform the following steps to setup the SQL Translation Framework

1. Log into the database using sys privileges.Using the SQL Worksheet, enter the following commands:

GRANT CONNECT, RESOURCE, CREATE VIEW TO DBO_PUBS2;
ALTER USER DBO_PUBS2 QUOTA UNLIMITED ON USERS;


Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Grant the following permission to mwrep user  to load a SQL Translator (java).

BEGIN
DBMS_JAVA.GRANT_PERMISSION(UPPER('MWREP'),'SYS:java.lang.RuntimePermission','getClassLoader','');
END;

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. Using the SQL Developer migration repository schema owner, create a DBA connection in SQL Developer (using the DBA panel).

From the View menu, select DBA.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. In the DBA Navigator, right-click Connections and select Add Connection.

Setting up SQL Translation Framework OBE for Database 12c Release 1

5. In the Select Connection box, select the mwrep_noncdb connection and click OK. Again, repeat the same step and select dbo_pubs2_nondcb connection and click OK.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1

6. Grant the following privileges to the mwrep and dbo_pubs2 user using ADMIN privileges to allow the users to explicitly alter the session to use a profile.

Setting up SQL Translation Framework OBE for Database 12c Release 1

7. Increase the size of the SGA (required to load the java into memory when invoking the translator.
Select the noncdb_user connection and open a SQL worksheet Run the following commands using F5. 
alter system set memory_max_target=1000M  scope=spfile;
alter system set memory_target=1000M scope=spfile;

Setting up SQL Translation Framework OBE for Database 12c Release 1

8. Grant the following privilege to allow dbo_pubs2 to create profiles.

Setting up SQL Translation Framework OBE for Database 12c Release 1

9.  Expand mwrep_noncdb.

Setting up SQL Translation Framework OBE for Database 12c Release 1

10. Expand SQL Translation Framework. Right-click SQL Translators and select Install SQL Translator.

Setting up SQL Translation Framework OBE for Database 12c Release 1

11. Along with installing the SQL Translator, the Install SQL Translator dialog box also enables you to create a translation profile by performing the following steps: 
  1. From the SQL Translator drop-down box, select Sybase SQL Translator.
  2. Check Create New Profile.
  3. Enter Sybase_profile in Profile Name field.
  4. In Profile Schema, select dbo_pubs2_noncdb.
  5. Click Apply.

Note: Be sure to install the Profile into the same connection/schema as the dbo_pubs2 user. 

Setting up SQL Translation Framework OBE for Database 12c Release 1

12. Verify that the user has sufficient privileges to run the translation profile. When prompted, supply SYS password to grant LoadJava privileges to dbo_pubs2 user.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1

13. Install Translator.

Setting up SQL Translation Framework OBE for Database 12c Release 1

Setting up SQL Translation Framework OBE for Database 12c Release 1

14. Grant the following permission using the dbo_pubs2 user to the mwrep user to allow the translator to reference the Profile.

GRANT ALL ON SQL TRANSLATION PROFILE SYBASE_PROFILE TO MWREP;

Setting up SQL Translation Framework OBE for Database 12c Release 1

15. Grant the following privileges using the and mwrep user to the dbo_pubs2 user to allow the profile to reference the Translator.

GRANT EXECUTE ON SYBASE_TSQL_TRANSLATOR TO DBO_PUBS2;

Setting up SQL Translation Framework OBE for Database 12c Release 1

16. Close all connections at this point to ensure the privileges are granted.

17. To ensure that both the Profile and Translator are properly installed, verify whether the appropriate package and Java class files are present or not in the Connections pane.

Setting up SQL Translation Framework OBE for Database 12c Release 1



14. Test Sybase Translator


You can test the SQL Translation Profile by using SQL worksheet in the following way:

1. Right click on the SQL Translation Profile in the DBA tree and select Open SQL Worksheet with Profile.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Run some basic sybase queries.  For example, SELECT 'A' + 'B';

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. You should get the results as follows:

SELECT 'A' || 'B' with AB → results

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. Open the SQL Translation Profile and confirm the contents of the SQL Translations Page.

Setting up SQL Translation Framework OBE for Database 12c Release 1


15. Convert the Sybase Java application to Oracle



This section shows you a Java application running Sybase SQL against Oracle. Perform the following steps to run the java application which converts a Sybase Java application to Oracle.

1. Change the Sybase connection to connect to Oracle (using the Oracle JDBC driver and Oracle connection details).

Note: All the changes are already done for you. The below screenshot just shows you the changes made to this application.

Setting up SQL Translation Framework OBE for Database 12c Release 1

2. Set the SQL Translator Profile for the Oracle connection.

Setting up SQL Translation Framework OBE for Database 12c Release 1

3. Build and run the application.

Setting up SQL Translation Framework OBE for Database 12c Release 1

4. The result is shown below:

Setting up SQL Translation Framework OBE for Database 12c Release 1

«« Previous
Next »»