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

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.

Informix to Oracle Migration Reference

Database Objects and Concepts

Migrating 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

Identifiers

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

SQL Language Elements

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

Data Types

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

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 Functions

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)

SELECT Statement

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

CREATE TABLE Statement

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

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

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;

SQL Statements

Converting 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

Migration Validation