Informix to Oracle Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Informix to Oracle.

• Informix 14.x, 12.x, 11.x, 10.x, 9.x and 7.x (Dynamic Server IDS and Extended Parallel Server XPS)
• Oracle 21c, 19c, 18c, 12c, 11g, 10g and 9i

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

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an Informix database use SQLines Data tool. You can use it Online.

Technical 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

Migrating database objects and specific constructs from Informix to Oracle:

Converting 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

Converting 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

Converting 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

Converting 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)

Converting 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;
5	OUTER clause	Outer join syntax	ANSI SQL OUTER JOIN clause

Converting table DDL from Informix to Oracle:

Table types:

Converting stored procedures from Informix to Oracle:

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

Converting 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;

Converting other SQL statements from Informix to Oracle:

• Data Transfer Validation