Teradata to Oracle Migration

SQLines provides services to help you 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, Python, Perl/PHP, SAS, Bash shell scripts and applications.

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

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

NULL values related functions:

Teradata Oracle
1 NULLIFZERO(exp) Replace 0 with NULL NULLIF(exp, 0)
2 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
3 SELECT … FROM table AS t AS keyword to define an alias SELECT … FROM table t AS keyword removed, not allowed

Alias references in the same SELECT expression list:

Teradata Oracle
1 SELECT
func(col1 + col2) AS alias1,
alias1 + value AS alias2
FROM …
SELECT
func(col1 + col2) AS alias1,
func(col1 + col2) + value AS alias2
FROM …

GROUP BY clause:

Teradata Oracle
1 SELECT exp1, exp2, exp3, …
FROM table
GROUP BY 1, 2, 3
Positional
reference
SELECT exp1, exp2, exp3, …
FROM table
GROUP BY exp1, exp2, exp3
1, 2, 3 are treated as
constants, so expressions
have to be used

QUALIFY clause conversion:

Teradata Oracle
1 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

UPDATE Statement

Converting UPDATE statement from Teradata to Oracle:

Teradata Oracle
1 UPDATE table a1
FROM (SELECT … FROM t2 ) a2
SET col = …, col2 = …,
WHERE condition
Update from SELECT
from other table(s)
MERGE INTO table a1
USING (SELECT … FROM t2 ) a2
ON (condition)
WHEN MATCHED THEN UPDATE
SET col = …, col2 = …,

CREATE TABLE Statement

Converting CREATE TABLE statement from Teradata to Oracle:

Teradata Oracle
1 MULTISET Allows duplicate rows unless an unique
key defined
This is default, keyword is removed
2 SET Does not allow duplicate rows Keyword removed, unique constraint is required
3 [NO] FALLBACK Store a row copy Clause removed
4 [NO] LOG Log operations Clause removed
5 [NO] BEFORE JOURNAL Store before and after images of data Clause removed
[NO] AFTER JOURNAL
6 CHECKSUM = DEFAULT | val Calculate checksum Clause removed
7 DEFAULT MERGEBLOCKRATIO Combine small blocks Clause removed
8 PRIMARY INDEX (col, …) Hash partitioning PARTITION BY HASH (col, …)
9 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

Creating tables using AS SELECT:

Teradata Oracle
1 WITH DATA Copy data during creation Option removed, this is default

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, …) from a PL/SQL block
CALL proc(param, …) from a SQL*Plus, use CALL proc()
if there are no parameters
2 label: Label declaration <<label>>

SET Statements

Converting SET statements from Teradata to Oracle:

Teradata Oracle
1 SET QUERY_BAND … FOR SESSION | TRANSACTION Define query banding Commented

Other SQL Statements

Converting other SQL statements from Teradata to Oracle:

Teradata Oracle
1 BEGIN TRANSACTION | BT Start a transaction explicitly Commented, not required
2 DEL | DELETE FROM table ALL | WHERE … Delete rows from a table DELETE FROM table WHERE …
3 END TRANSACTION | ET End the current transaction COMMIT
4 HELP STATISTICS table_name Show number of unique values for columns Commented
5 INS | INSERT INTO table Insert a row INSERT INTO table

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.