This is an old revision of the document!

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

Technical information on migration from Teradata to Oracle.

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

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
3	[NO] AFTER JOURNAL	Store before and after images of data	Clause removed
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;
3	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;
1	FOR var AS cur CURSOR FOR SELECT … DO stmts END FOR;	For each row loop	FOR var IN (SELECT …) LOOP stmts END LOOP;
2	IF condition THEN … END IF;	IF statement	IF condition THEN … END IF;
3	LEAVE label;	Leave a loop or block	EXIT label;
3	LEAVE outer_proc_label;	Leave the procedure	RETURN;
4	LOOP stmts END LOOP;	A loop statement	LOOP stmts END LOOP;
4	label: LOOP stmts END LOOP label;	A loop statement	<<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;	Conditional loop	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.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference