Informix to Oracle Migration

admin

Download Oracle Migration Workbench 10.1.0.4.0

• Supported Source Platforms: Informix Dynamic Server: 7.3, 9.1, 9.2, 9.3, 9.4
• Oracle Databases: Oracle Database 9i and 10g
  

Informix to Oracle Migration ReferenceTechnical information on migration from Informix to Oracle.

• Database Objects and Concepts
• Identifiers
• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT Statement
• CREATE TABLE
• CREATE PROCEDURE
• Procedural SQL Statements
• SQL Statements
• Migration Validation
  

Database Objects and ConceptsMigrating database objects and specific constructs from Informix to Oracle:

	Informix		Oracle
1	SERIAL Datatype	Autoincrement column	Sequence and Trigger
2	Session-based Temporary Tables		Global Temporary Tables

IdentifiersConverting identifiers from Informix to Oracle:

		Informix	Oracle
1	First character	Letter or underscore (_)	Letter
2	Subsequent characters	Letter, digit, _ and $	Letter, digit, _, # and $
3	Quote character	” (double quotes)	” (double quotes)
4	Maximum length	128	30
5	Reserved words	Different reserved words

SQL Language ElementsConverting SQL language elements:

	Informix		Oracle
1	{ comment }		/* comment */
2	string[start, end]	Substring operator []	SUBSTR(string, start, end - start + 1)
	string[start]		SUBSTR(string, start, 1)
3	exp::datatype	Cast exp to datatype	CAST(exp AS datatype)
4	DATETIME(datetime) YEAR TO FRACTION	DATETIME literal	TIMESTAMP 'datetime'
	DATETIME(date) YEAR TO DAY		DATE 'date'
5	DBINFO('sqlca.sqlerrd1')	Get the last SERIAL value	table_seq.CURRVAL
6	DBINFO('sqlca.sqlerrd2')	Get the number of affected rows	SQL%ROWCOUNT
7	string MATCHES 'pattern'	Regular expression matching	REGEXP_LIKE(string, pattern)
8	ORDER BY	NULLs in ORDER BY	ORDER BY NULLS FIRST | LAST Different default order
9	num UNITS DAY	Interval in days	INTERVAL 'num' DAY

Data TypesConverting data types:

	Informix		Oracle
1	BIGINT	64-bit integer	NUMBER(19)
2	BIGSERIAL(s)	Auto-increment 64-bit integer	Sequence and trigger
3	BLOB	Binary large object, ⇐ 4T	BLOB
4	BOOLEAN	True, false or NULL	CHAR(1)
5	BYTE	Binary data, ⇐ 2G	BLOB
6	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 32767	CHAR(n), CHARACTER(n)
7	CHARACTER VARYING(n,r)	Variable-length string, 1 ⇐ n ⇐ 255	VARCHAR2(n)
8	CLOB	Character large object, ⇐ 4T	CLOB
9	DATE	Date (year, month and day)	DATE	Includes time part
10	DATETIME unit TO unit2	Date and time with fraction	TIMESTAMP
11	DECIMAL(p,s), DEC(p,s)	Fixed-point number	NUMBER(p,s)
12	DOUBLE PRECISION	Double-precision floating-point number	BINARY_DOUBLE
13	FLOAT(p)	Double-precision floating-point number	BINARY_DOUBLE
14	INTEGER, INT	32-bit integer	NUMBER(10)
15	INT8	64-bit integer	NUMBER(19)
16	INTERVAL unit TO unit	Date and time interval	NUMBER(5)
17	INTERVAL YEAR TO MONTH	Date interval	INTERVAL YEAR TO MONTH
18	INTERVAL DAY TO HOUR	Day and time interval	INTERVAL DAY(5) TO SECOND
19	INTERVAL DAY TO MINUTE	Day and time interval	INTERVAL DAY(5) TO SECOND
20	INTERVAL DAY TO SECOND	Day and time interval	INTERVAL DAY(5) TO SECOND
21	INTERVAL DAY TO FRACTION	Day and time interval	INTERVAL DAY(5) TO SECOND
22	INTERVAL HOUR TO MINUTE	Time interval	INTERVAL DAY(5) TO SECOND
23	INTERVAL HOUR TO SECOND	Time interval	INTERVAL DAY(5) TO SECOND
24	INTERVAL HOUR TO FRACTION	Time interval	INTERVAL DAY(5) TO SECOND
25	INTERVAL MINUTE TO SECOND	Time interval	INTERVAL DAY(5) TO SECOND
26	INTERVAL MINUTE TO FRACTION	Time interval	INTERVAL DAY(5) TO SECOND
27	INTERVAL SECOND TO FRACTION	Time interval	INTERVAL DAY(5) TO SECOND
28	LVARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 32739	VARCHAR2(n)
29	MONEY(p,s)	Currency amount	NUMBER(p,s)
30	NCHAR(n)	Fixed-length string, 1 ⇐ n ⇐ 32767	NCHAR(n)
31	NUMERIC(p,s)	Fixed-point number	NUMBER(p,s)
32	NVARCHAR(n,r)	Variable-length string, 1 ⇐ n ⇐ 255	NVARCHAR2(n)
33	REAL	Single-precision floating-point number	BINARY_FLOAT
34	SMALLFLOAT	Single-precision floating-point number	BINARY_FLOAT
35	SMALLINT	16-bit integer	NUMBER(5)
36	SERIAL(s)	Auto-increment 32-bit integer	Sequence and trigger
37	SERIAL8(s)	Auto-increment 64-bit integer	Sequence and trigger
38	TEXT	Character data, ⇐ 2G	CLOB
39	VARCHAR(n,r)	Variable-length string, 1 ⇐ n ⇐ 255	VARCHAR2(n)

Data type attributes and options

	Informix	Oracle
1	column BYTE IN TABLE	column BLOB
2	column BYTE IN lob_space	column BLOB
3	column TEXT IN TABLE	column CLOB
4	column TEXT IN lob_space	column CLOB

Built-in SQL FunctionsConverting built-in SQL functions:

	Informix		Oracle
1	CURRENT	Get the current date and time	SYSTIMESTAMP
	CURRENT YEAR TO SECOND		SYSDATE
2	DBINFO('sqlca.sqlerrd1')	Get the last SERIAL value	table_seq.CURRVAL
	DBINFO('sqlca.sqlerrd2')	Get the number of affected rows	SQL%ROWCOUNT
3	DECODE(exp, when, then, …, else)	Evaluate condition	DECODE(exp, when, then, …, else)
4	MDY(month, day, year)	Build DATE from 3 integers	TO_DATE(TO_CHAR(month, 'FM09') || TO_CHAR(day, 'FM09') || TO_CHAR(year, 'FM0009'), 'MMDDYYYY')
5	SUBSTRING(str FROM start)	Get substring	SUBSTR(str, start)
	SUBSTRING(str FROM start FOR len)		SUBSTR(str, start, len)
6	MOD(dividend, divisor)	Get the remainder	MOD(dividend, divisor)
7	TO_CHAR(datetime, format)	Convert datetime to string	TO_CHAR(datetime, format)
8	TODAY	Get the current date	TRUNC(SYSDATE)

SELECT StatementConverting SQL queries from Informix to Oracle:

	Informix		Oracle
1	SELECT FIRST n …	Return n rows after sorting	ROWNUM <= n and subquery
2	SELECT UNIQUE | DISTINCT … FROM	Retrieve unique values	SELECT DISTINCT … FROM
3	select_stmt INTO RAW table;	Create a non-logged table and insert rows	CREATE table AS select_stmt;
4	select_stmt INTO TEMP | SCRATCH table;	Create temporary table and insert rows	INSERT INTO table select_stmt;
			CREATE GLOBAL TEMPORARY TABLE table ON COMMIT PRESERVE ROWS AS select_stmt;

CREATE TABLE StatementConverting table DDL from Informix to Oracle:

	Informix		Oracle
1	IN dbspace	Dbspace name	TABLESPACE tablespace
2	EXTENT SIZE num	First extent size in KB	Removed
3	NEXT SIZE num	Subsequent extent size in KB	Removed
4	LOCK MODE PAGE | ROW | TABLE	Locking granularity	Removed

Table types:

	Informix		Oracle
1	CREATE EXTERNAL TABLE	Table outside the database	CREATE TABLE … ORGANIZATION EXTERNAL
2	CREATE RAW TABLE	Non-logged table	CREATE TABLE
3	CREATE SCRATCH TABLE	Non-logged temporary table (XPS only)	CREATE GLOBAL TEMPORARY TABLE

CREATE PROCEDURE StatementConverting stored procedures from Informix to Oracle:

	Informix		Oracle
1	CREATE PROCEDURE name		CREATE OR REPLACE PROCEDURE name
2	name()	When without parameters	name
3	OUT | INOUT param datatype(len) DEFAULT default		param IN | OUT | IN OUT datatype DEFAULT default
4	RETURNING | RETURNS d1	Scalar return value	Converted to CREATE FUNCTION
	RETURNING | RETURNS d1, d2, …	Multiple return values	List of OUT parameters is added
	RETURN WITH RESUME	Multiple rows returned	Converted to PIPELINED function
5	No AS keyword before the statements block		AS is added
6	No BEGIN keyword to start the statements block		BEGIN is added after DECLARE section
7	END PROCEDURE;	End of procedure block	END; /

For more information, see Conversion of Procedural SQL Statements from Informix to Oracle.

Procedural SQL StatementsConverting procedural SQL statements (SPL) used in stored procedures, functions and triggers from Informix to Oracle:

	Informix		Oracle
1	BEGIN [WORK];	Start a transaction	Commented
2	DECLARE cur CURSOR FOR stmt_id;	Declare a cursor	Linked with OPEN cur FOR select;
3	DEFINE var datatype(len);	Variable declaration	var datatype(len);
	DEFINE var, var2, … datatype(len);		var datatype(len); var2 datatype(len); …
4	EXIT FOR | FOREACH | LOOP | WHILE [WHEN condition];	Exit a loop	EXIT [WHEN condition];
5	FOREACH cur FOR select INTO vars stmt END FOREACH	Query loop	cur FOR select; FOR rec IN cur LOOP stmt END LOOP;
	FOREACH select INTO vars stmt END FOREACH		FOR rec IN (select) LOOP stmt END LOOP;
6	FREE stmt_id | cursor	Free statement/cursor	Not required, removed
7	LET var = value;	Assignment statement	var := value;
	LET var, var2, … = value, value2, …;		var := value; var2 := value2; …
	LET var, var2 = (SELECT c1, c2 FROM …)		SELECT c1, c2 INTO var, var2 FROM …
8	ON EXCEPTION stmt END EXCEPTION;	Exception handler	EXCEPTION WHEN OTHERS THEN stmt
	ON EXCEPTION SET var …	Set error code	var := SQLCODE;
9	PREPARE stmt_id FROM select;	Prepare SELECT statement	Linked with OPEN cur FOR select;
10	RAISE EXCEPTION code, isam, text;	Raise an exception	RAISE_APPLICATION_ERROR(code, text);
11	RETURN val1;	Return scalar value	RETURN val1;
	RETURN val1, val2, …	Return multiple values	Assign values to OUT parameters
	RETURN val1, val2, ... WITH RESUME;	Return multiple rows	PIPE ROW in Pipelined function
12	SYSTEM cmd	Execute OS command	DBMS_SCHEDULER.CREATE_JOB(job_action => cmd)
13	WHILE condition stmts END WHILE	A loop statement	WHILE condition LOOP stmts END LOOP;

SQL StatementsConverting other SQL statements from Informix to Oracle:

	Informix		Oracle
1	DROP TABLE temp_table;	Drop temporary table	TRUNCATE TABLE temp_table;
2	SET EXPLAIN FILE TO 'filename'	SQL trace file	Commented
3	SET ISOLATION TO DIRTY READ;	Set transaction isolation level	Commented
4	UPDATE STATISTICS FOR TABLE name	Collect statistics	Commented