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 feature, you must migrate your data, schema, stored procedures, triggers, and views. Oracle implements database schema migration and data migration through Oracle SQL Developer functionality. Oracle SQL Developer simplifies the process of migrating a non-Oracle database to an Oracle Database through the use of Migration Wizard. The Migration wizard provides convenient and comprehensive guidance through the phases involved in migrating a database.

Oracle SQL Developer captures information from the source non-Oracle database and displays it in a captured model, which is a representation of the structure of the source database. This representation is stored in a migration repository, which is a collection of schema objects that Oracle SQL Developer uses to store migration information.

The information in the repository is used to generate the converted model, which is a representation of the structure of the destination database as it will be implemented in the Oracle database. You can then use the information in the captured model and the converted model to compare database objects, identify conflicts with Oracle reserved words, and manage the migration progress. When you are ready to migrate, generate the Oracle schema objects, and then migrate the data.

This section describes how to perform the subsequent tasks that enable automatic run-time migration. These examples use SQL Translator with a JDBC application that runs against a Sybase database; they can be easily adapted for other client/database configurations. Note that Oracle SQL Developer is shipped with an installed Sybase translator.

See Oracle SQL Developer User's Guide for more information.

Create a connection to the Database with the credentials as shown in Figure 3-2:

Figure 3-2 Creating an Oracle Database Connection

Description of "Figure 3-2 Creating an Oracle Database Connection"

You can use the following command to check the database you are connected to and the JDBC driver being used:

show jdbc

1. Select Preferences from the Tools menu.
2. Select Generation Options from Migration option on the left panel, as shown in Figure 3-3.

Figure 3-3 Setting Up Migration Preferences in Oracle SQL Developer

Description of "Figure 3-3 Setting Up Migration Preferences in Oracle SQL Developer"

1. Open Oracle SQL Developer.
2. From the Tools menu, select Migration, and then select Translation Scratch Editor.
   
   Description of the illustration sql_dev_01.gif
3. In the Scratch Editor toolbar, select Sybase T_SQL To PL/SQL option, which is the Sybase translator.
   
   Description of the illustration sql_dev_02.gif
4. In the left panel of the Scratch Editor, enter the following query in Sybase SQL dialect:

   select top 10 * from dual
   

5. Click the Translate icon.

   The translated query text is displayed in the right panel of the editor.

   
   Description of the illustration sql_dev_03.gif

1. Right-click the SYBASE_PROFILE node.
2. Select Open SQL Worksheet with Profile.
3. Enter a T-SQL statement that you want to translate.
   
   Description of the illustration sql_dev_17.gif
4. Click SYBASE_PROFILE and select the SQL Translation tab to inspect the profile and view the translated statement.
   
   Description of the illustration sql_dev_14.gif

   An alternative way to view the profile SQL in a better way when you double-click on it, the fingerprint and template open in a Translation Scratch Editor as shown in the following images:

   
   Description of the illustration sql_dev_15.gif

   
   Description of the illustration sql_dev_16.gif

1. Login as a system user.

   > sqlplus system/<password>
   

2. Grant create privileges to the standard user.

   This allows the standard user to create a SQL Translation Profile.

    SQL> grant create sql translation profile to <user>;
   

3. Login as a standard user.

   sqlplus <user>/<password>
   

4. Invoke the methods of DBMS_SQL_TRANSLATOR PL/SQL package to create and configure the translation profile.

   SQL> exec dbms_sql_translator.create_profile('sybase_profile')
   SQL> exec dbms_sql_translator.set_attribute('sybase_profile', 
        dbms_sql_translator.attr_translator, 
        'migration_repo.sybase_tsql_translator')
   

5. Grant all privileges for the SQL Translation Profile to Oracle Sybase translation schema.

   SQL> grant all on sql translation profile sybase_profile to migration_repo;

1. Login as a DBA
2. Issue the following commands to use the DBMS_SERVICE PL/SQL package to create and invoke the database service:

   SQL> declare
     params dbms_service.svc_parameter_array;
   begin
     params('SQL_TRANSLATION_PROFILE') := 'user.sybase_profile';
     dbms_service.create_service('sybase_service', 'network_name', params);
     dbms_service.start_service('sybase_service');
   end;
   /

1. Login as a standard user:

   sqlplus user/password
   

2. Specify the SQL Translation Profile at the SQL prompt:

   SQL> alter session set sql_translation_profile = sybase_profile;
   

3. Force the database to treat SQL*Plus as a foreign SQL application:

   SQL> alter session set events = '10601 trace name context forever, level 32';
   

4. Run a SQL query that uses Sybase SQL dialect. For example:

   select top 3 * from emp;
   

5. The query returns the following results:

   EMPNO   ENAME    JOB        MGR   HIREDATE       SAL    COMM    DEPTNO
   ----------------------------------------------------------------------
   7369    SMITH     CLERK     7902  17-DEC-80      800                20
   7499    ALLEN     SALESMAN  7698  20-FEB-81     1600     300        30
   7521    WARD      SALESMAN  7698  22-FEB-81     1250     500        30