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.
Technical information on migration from Teradata to Oracle.
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 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) |
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) |
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:
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 |
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 = …, |
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:
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 |
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.
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>> |
Converting SET statements from Teradata to Oracle:
Teradata | Oracle | ||
1 | SET QUERY_BAND … FOR SESSION | TRANSACTION | Define query banding | Commented |
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 … |
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.