T-SQL v PL/SQL
How to Migrate T-SQL to Oracle PL/SQL
Oracle SQL Developer available for free on OTN provides a Migrate to Oracle feature.
Migrate to Oracle helps automate the task of migrating a SQL Server or Sybase database, their tables, data and their T-SQL objects (Views, Triggers, Functions, Procedures) to a corresponding Oracle database and PL/SQL Objects.
T-SQL v PL/SQL
Both are procedural languages which provide standard programming language features like control flow, variables, conditions and support embedded DML SQL (INSERT,UPDATE,DELETE,SELECT) statements.
Many of the structures found in T-SQL have a one to one mapping to similar structures in PL/SQL.
Sometimes there are differences in syntax and features.
Using SQL Developers Scratch Editor, T-SQL can be automatically converted to the corresponding Oracle PL/SQL. Typically procedures are migrated using the Migrate to Oracle feature, but the Scratch Editor provides a neat editor to type T-SQL on the left hand pane and have it automatically converted to Oracle PL/SQL on the right.
Some of the obvious differences are the T-SQL variables/parameters are denoted with '@' . This makes them distinct from column names. Oracle variables/parameters do not use this syntax.
Another obvious difference is the T-SQL query result set is passed back implicitly without the need for an explicit returned value. Oracles normal practice is to return the cursor explicitly through the procedures parameters.
In Oracle 12c, implicitly returned cursors are now supported through DBMS_SQL.RETURN_RESULT.
This makes migrating procedures a lot simpler as the procedure header does not change and therefore neither do its calls.
Many of the mappings between T-SQL and PL/SQL are handled automatically by Oracle SQL Developer.
Sometimes its not obvious why one construct is converted to another. The following outlines the some of the more common conversions.
Object Naming
SQL Server allow identifiers (table names, column names,..) to be up to 128 characters. Oracle has a limit of 30 bytes. SQL Developer will truncate and resolve collisions of objects names so that valid object names are referenced in the PL/SQL.
Embedded DDL
DDL , ex: CREATE TABLE, can be embedded and then referenced within T-SQL.
DDL cannot be directly embedded in PL/SQL but there are two choices.
1) Take the DDL out of the procedure and create it up front as a separate object.
2) Use dynamic sql (EXECUTE IMMEDIATE) to create the DDL from within the procedure. But then all references to it have to be withing dynamic sql as well.
Temporary Tables tend to be the most common DDL defined within T-SQL.
SQL Developer takes these definitions out of the procedures and convert them into Oracle Global Temporary Tables.
String Concatenation
T-SQL use the + operator for string concatenation and Oracle uses ||.
When converting from T-SQL to PL/SQL the expression data types need to known to choose the correct operator in oracle. SQL Developer can lookup the table definition to decide what data type the columns are.
UPDATE/DELETE FROM
T-SQL allows UPDATE and DELETE statements to have a FROM clause.
Oracle SQL does not allow a FROM clause in an UPDATE statement.
But the same functionality can be defined using a sub query or a MERGE clause.
SQL Developer will automatically convert these types of statements to a MERGE statement.
Empty String
Empty string literals '', means different things in different databases.
Sybase treats an empty string literal '' as a single space ' '.
SQL Server treats an empty string literal '' as an empty string literal ''
Oracle treats an empty string literal '' as a NULL.
During the conversion of T-SQL if an empty string literal is used , it is converted to a single space string literal.
Empty Strings are also converted to single spaces when moving data from SQL Server to Oracle.
Dynamic SQL
Dynamic SQL, or SQL defined within strings are not converted. Dynamic SQL typically isn't a complete statement, instead they tend to be strings built from constants, parameter values, column values ... , these substring of a SQL statement, are not translated automatically by SQL Developer. Instead they have to be reviewed and manually converted.
SQL Developer can be of some help though.
If you can manually put together an example of the complete T-SQL statement, the Scratch Editor can be used to convert it to PL/SQL.
Helping you manually convert the procedure.
CREATE OR REPLACE PROCEDURE proc1( v_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN v_cursor FOR 'select ' || ' * from t1 FETCH FIRST 2 ROWS ONLY';
END;
/
If the dynamic SQL is not directly available to translate, 12c provides a new feature SQL Translation Framework, which allows you to translate non Oracle SQL statements to Oracle on the fly.
It requires installing SQL Developers translator into the Oracle database and creating a new SQL Translation Profile. The procedure can then use dbms_sql to parse and execute the query and have the dynamic SQL translated by the translator on the fly.
create or replace PROCEDURE proc1( v_cursor OUT SYS_REFCURSOR)
AS
c integer;
n number;
v number;
BEGIN
c := dbms_sql.open_cursor();
BEGIN
execute immediate 'ALTER SESSION SET SQL_TRANSLATION_PROFILE=SQLSERVER_PROFILE';
dbms_sql.parse(c, 'select TOP 2 * from t1', dbms_sql.foreign_syntax);
n := dbms_sql.execute(c);
END;
v_cursor := DBMS_SQL.TO_REFCURSOR(c);
END;
Oracle SQL Developer available for free on OTN provides a Migrate to Oracle feature.
Migrate to Oracle helps automate the task of migrating a SQL Server or Sybase database, their tables, data and their T-SQL objects (Views, Triggers, Functions, Procedures) to a corresponding Oracle database and PL/SQL Objects.
T-SQL v PL/SQL
Both are procedural languages which provide standard programming language features like control flow, variables, conditions and support embedded DML SQL (INSERT,UPDATE,DELETE,SELECT) statements.
Many of the structures found in T-SQL have a one to one mapping to similar structures in PL/SQL.
Sometimes there are differences in syntax and features.
Using SQL Developers Scratch Editor, T-SQL can be automatically converted to the corresponding Oracle PL/SQL. Typically procedures are migrated using the Migrate to Oracle feature, but the Scratch Editor provides a neat editor to type T-SQL on the left hand pane and have it automatically converted to Oracle PL/SQL on the right.
Some of the obvious differences are the T-SQL variables/parameters are denoted with '@' . This makes them distinct from column names. Oracle variables/parameters do not use this syntax.
Another obvious difference is the T-SQL query result set is passed back implicitly without the need for an explicit returned value. Oracles normal practice is to return the cursor explicitly through the procedures parameters.
In Oracle 12c, implicitly returned cursors are now supported through DBMS_SQL.RETURN_RESULT.
This makes migrating procedures a lot simpler as the procedure header does not change and therefore neither do its calls.
Many of the mappings between T-SQL and PL/SQL are handled automatically by Oracle SQL Developer.
Sometimes its not obvious why one construct is converted to another. The following outlines the some of the more common conversions.
Object Naming
SQL Server allow identifiers (table names, column names,..) to be up to 128 characters. Oracle has a limit of 30 bytes. SQL Developer will truncate and resolve collisions of objects names so that valid object names are referenced in the PL/SQL.
Embedded DDL
DDL , ex: CREATE TABLE, can be embedded and then referenced within T-SQL.
DDL cannot be directly embedded in PL/SQL but there are two choices.
1) Take the DDL out of the procedure and create it up front as a separate object.
2) Use dynamic sql (EXECUTE IMMEDIATE) to create the DDL from within the procedure. But then all references to it have to be withing dynamic sql as well.
Temporary Tables tend to be the most common DDL defined within T-SQL.
SQL Developer takes these definitions out of the procedures and convert them into Oracle Global Temporary Tables.
String Concatenation
T-SQL use the + operator for string concatenation and Oracle uses ||.
When converting from T-SQL to PL/SQL the expression data types need to known to choose the correct operator in oracle. SQL Developer can lookup the table definition to decide what data type the columns are.
UPDATE/DELETE FROM
T-SQL allows UPDATE and DELETE statements to have a FROM clause.
Oracle SQL does not allow a FROM clause in an UPDATE statement.
But the same functionality can be defined using a sub query or a MERGE clause.
SQL Developer will automatically convert these types of statements to a MERGE statement.
Empty String
Empty string literals '', means different things in different databases.
Sybase treats an empty string literal '' as a single space ' '.
SQL Server treats an empty string literal '' as an empty string literal ''
Oracle treats an empty string literal '' as a NULL.
During the conversion of T-SQL if an empty string literal is used , it is converted to a single space string literal.
Dynamic SQL
Dynamic SQL, or SQL defined within strings are not converted. Dynamic SQL typically isn't a complete statement, instead they tend to be strings built from constants, parameter values, column values ... , these substring of a SQL statement, are not translated automatically by SQL Developer. Instead they have to be reviewed and manually converted.
SQL Developer can be of some help though.
If you can manually put together an example of the complete T-SQL statement, the Scratch Editor can be used to convert it to PL/SQL.
Helping you manually convert the procedure.
CREATE OR REPLACE PROCEDURE proc1( v_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN v_cursor FOR 'select ' || ' * from t1 FETCH FIRST 2 ROWS ONLY';
END;
/
If the dynamic SQL is not directly available to translate, 12c provides a new feature SQL Translation Framework, which allows you to translate non Oracle SQL statements to Oracle on the fly.
It requires installing SQL Developers translator into the Oracle database and creating a new SQL Translation Profile. The procedure can then use dbms_sql to parse and execute the query and have the dynamic SQL translated by the translator on the fly.
create or replace PROCEDURE proc1( v_cursor OUT SYS_REFCURSOR)
AS
c integer;
n number;
v number;
BEGIN
c := dbms_sql.open_cursor();
BEGIN
execute immediate 'ALTER SESSION SET SQL_TRANSLATION_PROFILE=SQLSERVER_PROFILE';
dbms_sql.parse(c, 'select TOP 2 * from t1', dbms_sql.foreign_syntax);
n := dbms_sql.execute(c);
END;
v_cursor := DBMS_SQL.TO_REFCURSOR(c);
END;