Teradata to Oracle Migration

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, macros, queries, BTEQ and SQL scripts from Teradata to Oracle.

We also help convert embedded SQL statements in C/C++ (ODBC, ESQL/C), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl/PHP and SAS applications.

  • Teradata 15, 14, 13.x, 12, V2R6 and V2R5
  • Oracle 12c, 11g and 10g

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions, triggers and BTEQ scripts from Teradata to Oracle.

SQLines tool converts SQL scripts and standalone SQL statements.

Try SQLines Online or download Desktop version.

Teradata to Oracle Migration Reference

SQL Language Elements

Converting SQL language elements from Teradata to Oracle:

Teradata Oracle
1 ACTIVITY_COUNT Get the number of affected rows SQL%ROWCOUNT
2 exp MOD exp2 Modulo (remainder) operator MOD(exp, exp2)

Comparison operators:

Teradata Oracle
1 exp EQ exp2 Equal exp = exp2
2 exp LE exp2 Less than or equal exp <= exp2
3 exp LT exp2 Less than exp < exp2
4 exp NE exp2 Not equal exp <> exp2
5 exp GE exp2 Greater than or equal exp >= exp2
6 exp GT exp2 Greater than exp > exp2

Data Types

Data type mapping between Teradata and Oracle :

Numbers:

Teradata Oracle
1 BIGINT 64-bit integer NUMBER(19)
2 DECIMAL(p, s) DEC(p, s) Fixed-point number NUMBER(p, s)
3 DOUBLE PRECISION Double precision floating-point number NUMBER
4 FLOAT Double precision floating-point number NUMBER
5 INTEGER INT 32-bit integer NUMBER(10)
6 NUMERIC(p, s) Fixed-point number NUMBER(p, s)
7 REAL Single precision floating-point number NUMBER
8 SMALLINT 16-bit integer NUMBER(5)
9 BYTEINT 8-bit integer, -128 to 127 NUMBER(3)

Built-in SQL Functions

Converting functions from Teradata to Oracle:

Teradata Oracle
1 DATE Get the current date (year, month and day) SYSDATE
2 TIME Get the current time with fraction SYSTIMESTAMP
3 ZEROIFNULL(exp) Replace NULL with 0 NVL(exp, 0)

SELECT Statement

Converting SQL queries from Teradata to Oracle:

Teradata Oracle
1 SEL keyword Converted to SELECT
2 SELECT without FROM clause SELECT … FROM dual

QUALIFY clause conversion:

Teradata Oracle
SELECT c1
FROM t1
WHERE c1='A'
QUALIFY
ROW_NUMBER() OVER
(PARTITION by c1 ORDER BY c1) = 1
SELECT * FROM
(SELECT c1,
ROW_NUMBER() OVER (PARTITION by c1 ORDER BY c1) rn
FROM t1
WHERE c1='A'
) WHERE rn = 1

CREATE TABLE Statement

Converting CREATE TABLE statement from Teradata to Oracle:

Teradata Oracle
1 MULTISET Allows duplicate rows unless an unique
key defined
Keyword not required, removed
2 [NO] FALLBACK Store a row copy Clause removed
3 [NO] BEFORE JOURNAL Store before and after images of data Clause removed
[NO] AFTER JOURNAL
4 CHECKSUM = DEFAULT | val Calculate checksum Clause removed
5 DEFAULT MERGEBLOCKRATIO Combine small blocks Clause removed
6 PRIMARY INDEX (col, …) Hash partitioning PARTITION BY HASH (col, …)
7 UNIQUE PRIMARY INDEX Unique hash partitioning UNIQUE and PARTITION BY HASH

Column options and attributes:

Teradata Oracle
1 TITLE 'title' Column title Removed from CREATE TABLE, can be used as alias
in queries
2 FORMAT format' Display format Removed from CREATE TABLE
3 CHARACTER SET name Column character set Not supported, removed
4 CASESPECIFIC Case sensitive comparison Default, keyword removed
5 COMPRESS val | (val, …) Column values to compress Clause removed

Temporary tables:

Teradata Oracle
1 CREATE VOLATILE TABLE Temporary table that exists
until the end of session
CREATE GLOBAL TEMPORARY TEMPORARY TABLE
2 PRIMARY INDEX Temporal table can be partitioned Temporary tables cannot be partitioned
3 ON COMMIT DELETE | PRESERVE ROWS ON COMMIT DELETE | PRESERVE ROWS

CREATE PROCEDURE Statement

Converting stored procedures from Teradata to Oracle:

Teradata Oracle
1 CREATE PROCEDURE | REPLACE PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 name() When without parameters name
3 IN | OUT | INOUT param datatype(length) param IN | OUT | IN OUT datatype
4 DYNAMIC RESULT SETS num Number of returned result sets Clause not required, removed
5 No AS keyword before outer BEGIN END block IS keyword added
6 Declarations are inside BEGIN END block Declarations are before BEGIN END block

For more information, see Conversion of Procedural SQL Statements.

Procedural SQL Statements

Converting procedural SQL statements used in stored procedures, functions and triggers from Teradata to Oracle:

Variable declaration and assignment:

Teradata Oracle
1 DECLARE var datatype(len) [DEFAULT value]; Variable declaration var datatype(len) [DEFAULT value];
2 SET var = value; Assignment statement var := value;

Condition handlers:

Teradata Oracle
1 DECLARE EXIT HANDLER FOR SQLEXCEPTION SQL exception handler EXCEPTION WHEN OTHERS
2 DECLARE CONTINUE HANDLER FOR SQLSTATE
VALUE '23505'
Unique key violation EXCEPTION WHEN DUP_VAL_ON_INDEX

Cursor declarations and operations:

Teradata Oracle
1 DECLARE cur CURSOR FOR SELECT … Cursor declaration CURSOR cur IS SELECT …
DECLARE cur CURSOR FOR stmt_id; Cursor for prepared statement cur SYS_REFCURSOR;
DECLARE cur CURSOR WITH RETURN Result set cur OUT SYS_REFCURSOR
2 PREPARE stmt_id FROM sql_str; Prepare dynamic SQL statement Linked with OPEN cur FOR sql_str;
3 OPEN cur; Open a cursor OPEN cur;
OPEN cur [USING var, …]; Open cursor for prepared statement OPEN cur FOR sql_str [USING var, …];
4 FETCH cur INTO var, …; Fetch a cursor FETCH cur INTO var, …;
5 CLOSE cur; Close a cursor CLOSE cur;

Executing dynamic SQL statements:

Teradata Oracle
1 PREPARE stmt_id FROM sql_str; Prepare dynamic SQL statement Linked with EXECUTE IMMEDIATE sql_str;
2 EXECUTE stmt_id [USING var, …]; Execute prepared dynamic SQL EXECUTE IMMEDIATE sql_str [USING var, …];
3 EXECUTE IMMEDIATE sql_str; Execute dynamic SQL EXECUTE IMMEDIATE sql_str;

Flow-of-control statements:

Teradata Oracle
1 FOR var AS SELECT … DO stmts END FOR; For each row loop FOR var IN (SELECT …) LOOP stmts
END LOOP;
FOR var AS cur CURSOR FOR SELECT …
DO stmts END FOR;
2 IF condition THEN … END IF; IF statement IF condition THEN … END IF;
3 LEAVE label; Leave a loop or block EXIT label;
LEAVE outer_proc_label; Leave the procedure RETURN;
4 LOOP stmts END LOOP; A loop statement LOOP stmts END LOOP;
label: LOOP stmts END LOOP label; <<label>> LOOP stmts END LOOP label;
5 REPEAT stmts UNTIL condition END REPEAT; Conditional loop LOOP stmts EXIT WHEN condition; END LOOP;
6 WHILE condition DO stmts END WHILE; WHILE condition LOOP stmts END LOOP;

Other statements and procedural language elements;

Teradata Oracle
1 CALL proc(param, …) Call a procedure proc(param, …)
2 label: Label declaration <<label>>

Other SQL Statements

Converting other SQL statements from Teradata to Oracle:

Teradata Oracle
1 HELP STATISTICS table_name Show number of unique values for columns Commented
2 INS | INSERT INTO table_name Insert a row INSERT INTO table_name

Error Codes and Messagges

Mapping error codes and messages from Teradata to Oracle:

Teradata Oracle
1 SQLSTATE = '02000' Row not found cur%NOTFOUND for FETCH statement
SQL%NOTFOUND for UPDATE, DELETE statements
SQLCODE = 100 in an exeption handler for SELECT INTO

Note: In Oracle you can use the SQLCODE variable in an exception handler only.