ODIORA001 - DATE and DATETIME data types

INFORMIX provides two data types to store date and time information:

• DATE = for year, month and day storage.
• DATETIME = for year to fraction(1-5) storage.

ORACLE provides only the following data types to store date and time data:

• DATE = for year, month, day, hour, min, second storage.
• TIMESTAMP (Oracle 9i) = for year, month, day, hour, min, second, fraction storage.

String representing date time information:

INFORMIX is able to convert quoted strings to DATE / DATETIME data if the string contains matching environment parameters (i.e. DBDATE, GL_DATETIME).

As in INFORMIX, ORACLE can convert quoted strings to DATE or TIMESTAMP data if the contents of the string matches the NLS date format parameters (NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT).  The TO_DATE( ) and TO_TIMESTAMP() SQL functions convert strings to dates or timestamps, according to a given format. The TO_CHAR( ) SQL function allows you to convert dates or timestamps to strings, according to a given format.

Date arithmetic:

• INFORMIX supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used, and an INTERVAL value if a DATETIME is used in the expression. In ORACLE, the result of an arithmetic expression involving DATE values is a NUMBER of days; the decimal part is the fraction of the day ( 0.5 = 12H00, 2.00694444 = (2 + (10/1440)) = 2 days and 10 minutes ) ) parens don't match???  The result of an expression involving Oracle TIMESTAMP data is of type INTERVAL. See Oracle documentation for more details.
• INFORMIX automatically converts an integer to a date when the integer is used to set a value of a date column. ORACLE does not support this automatic conversion.
• Complex DATETIME expressions ( involving INTERVAL values for example ) are INFORMIX specific and have no equivalent in ORACLE.
• To compare dates that have time data in ORACLE, you can use the ROUND() or TRUNC() SQL functions.

Solution :

The ORACLE DATE type is used for INFORMIX DATE data. The database interface automatically sets the time part to midnight (00:00:00) during input/output operations. You must be very careful since manual modifications of the database might set the time part, for example :
    UPDATE table SET date_col = SYSDATE
(SYSDATE is equivalent to CURRENT YEAR TO SECOND in INFORMIX).
After this kind of update, when columns have date values with a time part different from midnight, some SELECT statements might not return all the expected rows.

INFORMIX DATETIME data with any precision from YEAR to SECOND is stored in ORACLE DATE columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the ORACLE DATE value will be "1900-01-01 11:45:00".

When using ORACLE 9i, INFORMIX DATETIME YEAR TO FRACTION(n) data is stored in ORACLE TIMESTAMP columns. The TIMESTAMP data type can store up to 9 digits in the fractional part, and therefore can store all precisions of INFORMIX DATETIME.

Warning : Using integers (number of days since 1899/12/31) as dates is not supported by ORACLE. Check your code to detect where you are using integers with DATE columns.

Warning : Literal DATETIME expressions (i.e. DATETIME 1999-10-12 YEAR TO DAY) are not converted.

Warning : It is strongly recommended that you use BDL variables in dynamic SQL statements instead of quoted strings representing DATEs. For example :
   LET stmt = "SELECT ... FROM customer WHERE creat_date >'", adate,"'"
is not portable.  Use a question mark place holder instead and OPEN the cursor by USING adate :
   LET stmt = "SELECT ... FROM customer WHERE creat_date > ?"

Warning : Most arithmetic expressions involving dates ( for example, to add or remove a number of days from a date ) will produce the same result with ORACLE. But keep in mind that ORACLE evaluates date arithmetic expressions to NUMBERs ( <days>.<fraction> ) while INFORMIX evaluates to INTEGERs when only DATEs are used in the expression, or to INTERVALs if at least one DATETIME is used in the expression.

Warning : DATE arithmetic expressions using SQL parameters (USING variables) are not fully supported. For example: "SELECT ... WHERE datecol < ? + 1" generates an error at PREPARE time.

Warning : SQL Statements using expressions with TODAY / CURRENT /  EXTEND must be reviewed and adapted to the native syntax.

ODIORA003 - Reserved words

SQL object names like table and column names cannot be SQL reserved words in ORACLE.

 An example of a common word which is part of the ORACLE SQL grammar is 'level'.

Solution :

Table or column names which are ORACLE reserved words must be renamed.

ORACLE reserved keywords are listed in the ORACLE documentation, or Oracle 8i provides the V$RESERVED_WORDS view to track Oracle reserved words. All BDL application sources must be verified. To check if a given keyword is used in a source, you can use UNIX 'grep' or 'awk' tools. Most modifications can be done automatically with UNIX tools like 'sed' or 'awk'. 

ODIORA004 - ROWIDs

ORACLE provides ROWIDs but the data type is different: INFORMIX rowids are INTEGERs while ORACLE rowids are CHAR(18).

ORACLE ROWIDs are physical addresses of the rows. For example : AAAA8mAALAAAAQkAAA

Since ORACLE rowids are physical addresses, they cannot be used as permanent row identifiers ( After a DELETE, an INSERT statement might reuse the physical place of the deleted row, to store the new row ).

With INFORMIX, SQLCA.SQLERRD[6] contains the ROWID of the last INSERTed or UPDATEd row. This is not supported with ORACLE because ORACLE ROWID are not INTEGERs.

Solution :

If the BDL application uses INFORMIX rowids as primary keys, the program logic should be reviewed in order to use the real primary keys (usually, serials which can be supported) or ORACLE rowids as CHAR(18) ( INFORMIX rowids will fit in this char data type).

If you cannot avoid the use of rowids, you must change the type of the variables which hold ROWID values. Instead of using INTEGER, you must use CHAR(18). INFORMIX rowids (INTEGERs) will automatically fit into a CHAR(18) variable.

All references to SQLCA.SQLERRD[6] must be removed because this variable will not contain the ROWID of the last INSERTed or UPDATEd row when using the ORACLE interface.

ODIORA005 - SERIAL data type

INFORMIX SERIAL data type and automatic number production :

• The table column must be of type SERIAL.
• To generate a new serial, no value or zero value is given to the INSERT statement :
     INSERT INTO tab1 ( c ) VALUES ( 'aa' )
     INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
• After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2].

ORACLE sequences :

• Sequences are totally detached from tables.
• The purpose of sequences is to provide unique integer numbers.
• Sequences are identified by a sequence name.
• To create a sequence, you must use the CREATE SEQUENCE statement.
  Once a sequence is created, it is permanent (like a table).
• To get a new sequence value, you must use the nextval keyword, preceded by the name of the sequence.
  The <seqname>.nextval expression can be used in INSERT statements :
          INSERT INTO tab1 VALUES ( tab1_seq.nextval, ... )
• To get the last generated number, ORACLE provides the currval keyword :
          SELECT <seqname>.currval FROM DUAL

Remark : In order to improve performance, ORACLE can handle a set of sequences in the cache (See CREATE SEQUENCE syntax in the ORACLE documentation).

INFORMIX allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERTs that are using a zero value :
    CREATE TABLE tab ( k SERIAL );  --> internal counter = 0
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 1
    INSERT INTO tab VALUES ( 10 );  --> internal counter = 10
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 11
    DELETE FROM tab;                --> internal counter = 11
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 12

Solution :

When using Oracle, the SERIAL data type can be emulated with INSERT TRIGGERs. In BDL programs, the SQLCA structure is filled as expected ( after an insert, sqlca.sqlerrd[2] holds the last generated serial value ).

The triggers can be created manually during the database creation procedure, or automatically from a BDL program: When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the Oracle interface automatically converts the SERIAL data type to NUMBER(10,0) and dynamically creates the trigger. For temporary tables, the trigger is dropped automatically after a "DROP TABLE temptab" or when the program disconnects from the database.

Warning : Users executing programs which create tables with SERIAL columns must have the CONNECT and RESOURCE roles assigned to create triggers and sequences.

In database creation scripts, all SERIAL[(n)] data types must be converted to NUMBER(10,0) data types and you must create the triggers (and the sequences when using sequence-based triggers). See below instructions for using the gsrl_ora tool from the Adaptation Kit.

Warning : With Oracle, INSERT statements using NULL for the SERIAL column will produce a new serial value, not a NULL as INFORMIX does :
   INSERT INTO tab ( col_serial, col_data ) VALUES ( NULL, 'data' )
This behavior is mandatory in order to support INSERT statements which do not use the serial column :
   INSERT INTO tab ( col_data ) VALUES ( 'data' )
Check whether your application uses tables with a SERIAL column that can contain a NULL value.

Warning : Since the INFORMIX SERIAL data type simulation is implemented in the ORACLE database, inserting rows with ORACLE tools like SQL*Plus or SQL*Loader will raise the INSERT triggers. When loading big tables, you can disable triggers with ALTER TRIGGER [ENABLE | DISABLE] (see ORACLE documentation for more details). After re-activation of the serial triggers, the SERIAL sequences must be re-initialized (use serialpkg.create_sequence('tab','col') or re-execute the PL/SQL script containing the sequence and trigger creation.

You are free to use SEQUENCE based insert triggers (1) or SERIALREG based insert triggers (2). The second solution needs the SERIALREG table to register serials.

With the following fglprofile entry, you define the technique to be used for SERIAL emulation :

   dbi.database.<dbname>.ifxemul.datatype.serial.emulation = {native|regtable}

The 'native' value defines the SEQUENCE-based technique and the 'regtable' defines the SERIALREG-based technique.

This entry must be used with :

   dbi.database.<dbname>.ifxemul.serial = {true|false}

If this entry is set to false, the emulation method specification entry is ignored.

When no entry is specified, the default is SERIAL emulation enabled with 'native' method (SEQUENCE-based).

1. Using SEQUENCES based triggers

Each table having a SERIAL column needs an INSERT TRIGGER and a SEQUENCE dedicated to SERIAL generation.

For manual trigger/sequence creation, use the gsrl_ora tool with the -gt1 option. This will generate the SEQUENCEs and the INSERT TRIGGERs, in accordance with a list of tables and columns provided with the -tl option.

2. Using SERIALREG based triggers

Each table having a SERIAL column needs an INSERT TRIGGER which uses the SERIALREG table dedicated to SERIAL registration.

You must prepare the database and create the SERIALREG table. You can generate the CREATE TABLE instruction with the gsrl_ora tool provided in the adaptation kit.

This table must exist in the database before creating the serial triggers.

For manual trigger creation, use the gsrl_ora tool with the -gt2 option. This will generate the INSERT TRIGGERs, in accordance with a list of tables and columns provided with the -tl option.

Warning : The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at start values and you might get duplicated values.

ODIORA006 - Outer joins

The syntax of OUTER joins is very different in INFORMIX and ORACLE :

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER keyword :

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c))
 WHERE a.key = b.akey
   AND b.key1 = c.bkey1
   AND b.key2 = c.bkey2 

ORACLE expects the (+) operator in the join condition. You must set a (+) after columns of the tables which must have NULL values when no record matches the condition :

SELECT ... FROM a, b
 WHERE a.key = b.key (+)

SELECT ... FROM a, b, c
 WHERE a.key = b.akey (+)
   AND b.key1 = c.bkey1 (+)
   AND b.key2 = c.bkey2 (+) 

When using additional conditions on outer tables, the (+) operator also has to be used. For example :

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey
   AND b.colx > 10

Must be converted to :

SELECT ... FROM a, b
 WHERE a.key = b.akey (+)
   AND b.colx (+) > 10

The ORACLE outer joins restriction :

In a query that performs outer joins of more than two pairs of tables, a single table can only be the NULL generated table for one other table. The following case is not allowed : WHERE a.col = b.col (+) AND b.col (+) = c.col

Solution :

The Oracle interface can convert most INFORMIX OUTER specifications to Oracle outer joins.

Prerequisites :

1. In the FROM clause, the main table must be the first item and the outer tables must figure from left to right in the order of outer levels.
      Example which does not work : "FROM OUTER(tab2), tab1 ".
2. The outer join in the WHERE part must use the table name as prefix.
      Example : "WHERE tab1.col1 = tab2.col2 ".

Restrictions :

1. Statements composed by 2 or more SELECT instructions are not supported.
     Example : "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Remarks :

1. Table aliases are detected in OUTER expressions.
      OUTER example with table alias : "OUTER( tab1 alias1)".
2. In the outer join, <outer table>.<col> can be placed on both right or left sides of the equal sign.
      OUTER join example with table on the left : "WHERE outertab.col1 = maintab.col2 ".
3. Table names detection is not case-sensitive.
      Example : "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".
4. Temporary tables are supported in OUTER specifications.

ODIORA007a - Database concepts

Most of BDL applications use only one database entity (in the meaning of INFORMIX). But the same BDL application can connect to different occurrences of the same database schema, allowing several users to connect to those different databases.

INFORMIX servers can handle multiple database entities, while ORACLE servers manage only one database. ORACLE can manage multiple schemas, but by default other users must give the owner name as prefix to the table name:

      SELECT * FROM stores.customer

Solution :

In an ORACLE database, each user can manage his own database schema. You can dedicate a database user to administer each occurrence of the application database.

Starting with version 8.1.5, any user can select the current database schema with the following SQL command :

      ALTER SESSION SET CURRENT_SCHEMA = "<schema>"

Using this instruction, any user can access the tables without giving the owner prefix as long as the table owner has granted the privileges to access the tables.

You can make the database interface select the current schema automatically with the following fglprofile entry :

       dbi.database.<dbname>.schema = "<schname>"

When using multiple database schemas, it is recommended that you create them in separated tablespaces to enable independent backups and keep logical sets of tables together. The simplest way is to define a default tablespace when creating the schema owner :

      CREATE USER <user> IDENTIFIED BY <pswd>
             DEFAULT TABLESPACE <tabspacename>
             TEMPORARY TABLESPACE <tmptabspace>

ODIORA008a - Data consistency and concurrency management

Data consistency involves readers that want to access data currently modified by writers and concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, ...).

INFORMIX

INFORMIX uses a locking mechanism to handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks according to the isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.

Control :

• Lock wait mode : SET LOCK MODE TO ...
• Isolation level : SET ISOLATION LEVEL TO ...
• Locking granularity : CREATE TABLE ... LOCK MODE {PAGE|ROW}
• Explicit exclusive lock : SELECT ... FOR UPDATE

Defaults :

• The default isolation level is read committed.
• The default lock wait mode is "not wait".
• The default locking granularity is page.

ORACLE

When data is modified, exclusive locks are set and held until the end of the transaction. For data consistency, ORACLE uses a multi-version consistency model: a copy of the original row is kept for readers before performing writer modifications. Readers do not have to wait for writers as in INFORMIX. The simplest way to think of Oracle's implementation of read consistency is to imagine each user accessing a private copy of the database, hence the multi-version consistency model. The lock wait mode cannot be changed session wide as in INFORMIX; the waiting behavior can be controlled with a SELECT FOR UPDATE NOWAIT only. Locks are set at the row level in ORACLE, and this cannot be changed.

Control :

• Lock wait mode (on SELECT only): SELECT ... FOR UPDATE NOWAIT
• Isolation level : SET TRANSACTION ISOLATION LEVEL TO ...
• Explicit exclusive lock : SELECT ... FOR UPDATE [NOWAIT]

Defaults :

• The default isolation level is Read Committed ( readers cannot see uncommitted data, no shared lock is set when reading data ).

The main difference between INFORMIX and ORACLE is that readers do not have to wait for writers in ORACLE.

Solution :

ORACLE does not provide a dirty read mode, the (session wide) lock wait mode cannot be changed and the locking precision is always at the row level. Based on this, it is recommended that you work with INFORMIX in the read committed isolation level (default), make processes wait for each other (lock mode wait), and use the default page-level locking granularity.

See INFORMIX and ORACLE documentation for more details about data consistency, concurrency and locking mechanisms.

ODIORA008b - SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to prevent several users editing the same rows at the same time.

  DECLARE cc CURSOR FOR
     SELECT ... FOR UPDATE [OF col-list]
  OPEN cc
  FETCH cc <-- lock is acquired
  CLOSE cc <-- lock is released

• The row must be fetched in order to set the lock.
• If the cursor is local to a transaction, the lock is released when the transaction ends.
  If the cursor is declared "WITH HOLD", the lock is released when the cursor is closed.

ORACLE allows individual and exclusive row locking with :

  SELECT ... FOR UPDATE [OF col-list]

• A lock is acquired for each selected row when the cursor is opened, before the first fetch.
• Cursors using SELECT ... FOR UPDATE are automatically closed when the transaction ends;
  Warning : Locks are not released when a cursor is closed.

ORACLE's locking granularity is at the row level.

To control the behavior of the program when locking rows, INFORMIX provides a specific instruction to set the wait mode :

   SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }

The default mode is NOT WAIT. This as an INFORMIX specific SQL statement.

In order to simulate the same behavior in ORACLE, your can use the NOWAIT keyword in the SELECT ... FOR UPDATE statement, as follows :

    SELECT ... FOR UPDATE [OF col-list] NOWAIT

With this option, ORACLE immediately returns an SQL error if the row is locked by another user.

Solution :

Warning : The database interface is based on an emulation of an Informix engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).

Warning : Cursors declared with SELECT ... FOR UPDATE using the "WITH HOLD" clause cannot be supported with ORACLE. See ODIORA031 and ODIORA032 for more details.

If your BDL application is using pessimistic locking with SELECT ... FOR UPDATE, you must review the program logic to OPEN cursor and CLOSE cursor statements inside transactions (BEGIN WORK + COMMIT WORK / ROLLBACK WORK).

ODIORA009a - Transactions handling

INFORMIX and ORACLE handle transactions differently. The differences in the transactional models can affect the program logic.

INFORMIX native mode (non ANSI) :

• DDL statements can be executed (and canceled) in transactions.
• Transactions must be started with BEGIN WORK. Statements executed outside of a transaction are automatically committed.

ORACLE :

• Beginnings of transactions are implicit; two transactions are delimited by COMMIT or ROLLBACK.
• The current transaction is automatically committed when a DDL statement is executed.

Transactions in stored procedures: avoid using transactions in stored procedures to allow the client applications to handle transactions, in accordance with the transaction model.

Solution :

Regarding transaction control instructions, BDL applications do not have to be modified in order to work with ORACLE. The INFORMIX behavior is simulated with an auto-commit mode in the ORACLE interface. A switch to the explicit commit mode is done when a BEGIN WORK is performed by the BDL program.

Warning : When executing a DDL statement inside a transaction, ORACLE automatically commits the transaction. Therefore, you must extract the DDL statements from transaction blocks.

See also ODIORA008b

ODIORA009b - Avoiding long transactions

Some BDL applications do not care about long transactions because they use an INFORMIX database without transaction logs (transactions are not stored in log files for potential rollbacks).

With ORACLE, using transactions is mandatory and every database modification is stored in rollback segments (to keep before-images of data) and redo logs (to keep transaction history).

BDL applications must prevent long transactions when connected to a database using logging.

For example, if a table holds hundreds of thousands of rows, a "DELETE FROM table" might produce a "snapshot too old" error in ORACLE if the rollback segments are too small.

Solution :

You must review the program logic in order to avoid long transactions.

An efficient way to delete all rows from a table in an ORACLE database is to use the "TRUNCATE TABLE" instruction instead of "DELETE FROM". However, the "TRUNCATE" statement does not save deleted rows in rollback segments and thus cannot be canceled with a ROLLBACK instruction.

ODIORA010 - Handling SQL errors when preparing statements

The ORACLE interface  is implemented with the ORACLE Call Interface (OCI). This library does not provide a way to send SQL statements to the database server during the BDL PREPARE instruction, as in the INFORMIX interface. The statement is sent to the server only when opening the cursors or when executing the statement.

Therefore, when preparing an SQL statement with the BDL PREPARE instruction, no SQL errors can be returned if the statement has syntax errors, or if a column or a table name does not exist in the database. However, an SQL error will occur after the OPEN or EXECUTE instructions.

Solution :

Make sure your BDL programs do not test the STATUS or SQLCA.SQLCODE variable just after PREPARE instructions.

Change the program logic in order to handle the SQL errors when opening the cursors (OPEN) or when executing SQL statements (EXECUTE).

ODIORA011a - CHARACTER data types

INFORMIX provides the CHAR and VARCHAR data types to store characters. CHAR columns can store up to 32767 chars, and VARCHARs are limited to 255. Starting with IDS 2000, INFORMIX provides the LVARCHAR data type which is limited to 2K.

ORACLE provides the CHAR and VARCHAR2 data types. CHAR columns can have a length of 2000 and VARCHAR2 can have a length of 4000. VARCHAR is a synonym to VARCHAR2, but you should not use VARCHAR because the behavior may change in future server versions. See the ORACLE documentation for more details. 

Warning : When comparing VARCHAR2 values in ORACLE, the trailing blanks are significant; this is not the case when using INFORMIX VARCHARs. But comparison with columns of type CHAR is similar to INFORMIX. See blank-padded and non-padded comparison semantics in ORACLE documentation.

Warning : ORACLE treats empty strings like NULL values; INFORMIX doesn't. See issue ODIORA011c for more details.

Solution :

The database interface supports character string variables in SQL statements for input (BDL USING) and output (BDL INTO).

Warning : Based on the comparison semantics, we recommend that you use ORACLE CHARs for INFORMIX CHARs. Take care if you want to use ORACLE VARCHAR2, since the comparison of values having trailing blanks is different.

Warning : Check that your database schema does not use CHAR or VARCHAR types with a length exceeding the ORACLE limit.

ODIORA011b - The LENGTH( ) function

INFORMIX provides the LENGTH() function:

    SELECT LENGTH("aaa"), LENGTH(col1) FROM table

Oracle has a equivalent function with the same name, but there is some difference:

Informix does not count the trailing blanks neither for CHAR not for VARCHAR expressions, while Oracle counts the trailing blanks.

With the Oracle LENGTH function, when using a CHAR column, values are always blank padded, so the function returns the size of the CHAR column. When using a VAR CHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.

The INFORMIX LENGTH() function returns 0 when the given string is empty. That means, LENGTH('') is 0.

Since ORACLE handles empty strings ('') as NULL values, writing "LENGTH('')" is equivalent to "LENGTH(NULL)". In this case, the function returns NULL.

Solution :

The ORACLE database interface cannot simulate the behavior of the INFORMIX LENGTH() function.

You must check if the trailing blanks are significant when using the LENGTH() function.

If you want to count the number of character by ignoring the trailing blanks, you must use the RTRIM() function:

    SELECT LENGTH(RTRIM(col1)) FROM table

SQL conditions which verify that the result of LENGTH( ) is greater that a given number do not have to be changed, because the expression evaluates to false if the given string is empty (NULL>n) :
   SELECT * FROM x WHERE LENGTH(col)>0

Only SQL conditions that compare the result of LENGTH() to zero will not work if the column is NULL. You must check your BDL code for such conditions :
   SELECT * FROM x WHERE LENGTH(col)=0
In this case, you must add a test to verify if the column is null :
   SELECT * FROM x WHERE ( LENGTH(col)=0 OR col IS NULL )

In addition, when retrieving the result of a LENGTH( ) expression into a BDL variable, you must check that the variable is not NULL.

In ORACLE, you can use the NVL( ) function in order to get a non-null value :
      SELECT * FROM x WHERE NVL(LENGTH(c),0)=0
INFORMIX Dynamic Server 7.30 supports the NVL() function, as in ORACLE. So you can write the same SQL for both INFORMIX 7.30 and ORACLE 8, as shown in the above example.

If the INFORMIX version supports stored procedures, you can create the following stored procedure in the INFORMIX database in order to use NVL( ) expressions :

   create procedure nvl( val char(512), def char(512) )
          returning char(512);
      if val is null then
         return def;
      else
         return val;
      end if;
   end procedure;

With this stored procedure, you can write NVL( ) expressions like NVL(LENGTH(c),0). This should work in almost all cases and provides upward compatibility with INFORMIX Dynamic Server 7.30.

ODIORA011c - Empty character strings

INFORMIX SQL and ORACLE SQL handle empty quoted strings differently. ORACLE SQL does not distinguish between '' and NULL, while INFORMIX SQL treats '' ( or  "" ) as a string with a length of zero.

Warning : Using literal string values which are empty ('') for INSERT or UPDATE statements will result in the storage of NULLs with ORACLE, while INFORMIX would store the value as a string with a length of zero:

insert into tab1 ( col1, col2 ) values ( NULL, '' )

Warning : Using the comparison expression (col='') with ORACLE has no meaning because an empty string is equivalent to NULL;  (col=NULL) expressions will always evaluate to FALSE because this is not a correct expression: The expression should be (col IS NULL).

select * from tab1 where col2 = ''

Did you want this example to show the IS NULL syntax?

In Informix 4GL, when setting a variable with an empty string constant, it is automatically set to a NULL value. When using one or more space characters, the value is set to one space character :

define x char(10)
let x = ""
if x is null then -- evaluates to TRUE
let x = "    "
if x = " " then   -- evaluates to TRUE

Solution :

The ORACLE database interface cannot automatically convert comparison expressions like (col="") to (col IS NULL) because this would require an SQL grammar parser. The interface could convert expressions like (col=""), but it would do this for the whole SQL statement :

UPDATE tab1 SET col1 = "" WHERE col2 = ""

Would be converted to an incorrect SQL statement :

UPDATE tab1 SET col1 IS NULL WHERE col2 IS NULL

To increase portability, you should avoid the usage of literal string values with a length of zero in SQL statements; this would resolve storage and Boolean expressions evaluation differences between INFORMIX and ORACLE.

NULL or program variables can be used instead. Program variables set with empty strings (let x="") are automatically converted to NULL by BDL and therefore are stored as NULL when using both INFORMIX or ORACLE databases.

ODIORA012 - Constraints

Constraint naming syntax :

Both INFORMIX and ORACLE support primary key, unique, foreign key, default and check constraints, but the constraint naming syntax is different : ORACLE expects the "CONSTRAINT" keyword before the constraint specification and INFORMIX expects it after.

UNIQUE constraint example :

Primary keys :

Like INFORMIX, ORACLE creates an index to enforce PRIMARY KEY constraints (some RDBMS do not create indexes for constraints).  Using "CREATE UNIQUE INDEX"  to define unique constraints is obsolete(use primary keys or a secondary key instead).

Unique constraints :

Like INFORMIX, ORACLE creates an index to enforce UNIQUE constraints (some RDBMs do not create indexes for constraints). Warning: When using a unique constraint, INFORMIX allows only one row with a NULL value, while ORACLE allows several rows with NULL ! Using CREATE UNIQUE INDEX is obsolete.

Foreign keys :

Both INFORMIX and ORACLE support the ON DELETE CASCADE option. To defer constraint checking, INFORMIX provides the SET CONSTRAINT command while ORACLE provides the ENABLE and DISABLE clauses.

Check constraints :

Warning : The check condition may be any valid expression that can be evaluated to TRUE or FALSE, including functions and literals. You must verify that the expression is not INFORMIX specific.

Null constraints :

INFORMIX and ORACLE support not null constraints, but INFORMIX does not allow you to give a name to "NOT NULL" constraints.

Solution :

Constraint naming syntax :

The database interface does not convert constraint naming expressions when creating tables from BDL programs.

Review the database creation scripts to adapt the constraint naming clauses for ORACLE.

ODIORA013 - Triggers

INFORMIX and ORACLE provide triggers with similar features, but the trigger creation syntax and the programming languages are totally different.

INFORMIX triggers define the stored procedures to be called when a database event occurs (before | after  insert | update | delete ...), while ORACLE triggers can hold a procedural block.

In ORACLE, triggers can be created with 'CREATE OR REPLACE' to keep privileges settings. With INFORMIX, you must drop and create again.

ORACLE V8 provides an 'INSTEAD OF' option to completely replace the INSERT, UPDATE or DELETE statement. This is provided to implement complex storage operations, for example on views that are usually read-only ( you can attach triggers to views ).

Warning : ORACLE allows you to create multiple triggers on the same table for the same trigger event, but it does not guarantee the execution order.

Solution :

INFORMIX triggers must be converted to ORACLE triggers "by hand".

ODIORA014 - Stored procedures

Both INFORMIX and ORACLE support stored procedures, but the programming languages are totally different : SPL for INFORMIX versus PL/SQL for ORACLE.

In Oracle, stored procedures and functions can be implemented in packages (similar to BDL modules). This is a powerful feature which enables structured procedural programming in the database. ORACLE itself implements system tools with packages (dbms_sql, dbms_output, dbms_lock). Procedures, functions and packages can be created with 'CREATE OR REPLACE' to keep privileges settings.
With INFORMIX, you must drop and create again.

Warning : ORACLE uses a different privilege context when using dynamic SQL in PL/SQL; roles are not effective. Users must have direct privileges settings in order to perform DDL or DML operations inside dynamic SQL.

Solution :

INFORMIX stored procedures must be converted to ORACLE "by hand".

Try to use ORACLE packages in order to group stored procedures into modules.

ODIORA016a - Defining database users

INFORMIX users are defined at the operating system level, they must be members of the 'informix' group, and the database administrator must grant CONNECT, RESOURCE or DBA privileges to those users.

ORACLE users must be registered in the database. They are created by the database administrator with the following command :
   CREATE USER <username> IDENTIFIED BY <pswd>
Users defined at the operating system level can be declared as ORACLE users with the "IDENTIFIED EXTERNALLY" clause :
   CREATE USER OPS$<username> IDENTIFIED EXTERNALLY
In this case, ORACLE trusts the operating system, and users can connect to the database without giving any user name and password. The connection string is simply a slash ("/").

Warning : By default, database users authenticated by the operating systems have a name with the "OPS$" prefix. The 'OPS$' prefix can be changed with the OS_AUTHENT_PREFIX parameter (init<SID>.ora file). You can set this parameter to blank ("") in order to use the same user names in the system and in the ORACLE database. See ORACLE documentation ( "Server Administrators Guide", "User authentication" ) for more details.

Warning : For Windows NT operating system authentication to work, the SQLNET.AUTHENTICATION_SERVICES parameter must be set as follows in %ORACLE_HOME%\NETWORK\ADMIN\SQLNET.ORA :
       SQLNET.AUTHENTICATION_SERVICES = (NTS)

Solution :

Based on the application logic (is it a multi-user application ?), you must create one or several ORACLE users. As INFORMIX users are operating system users, we recommend that you use the OS authentication services offered by ORACLE. Do not forget to set the OS_AUTHENT_PREFIX parameter to get the same user names in the OS and in the database.

See also ODIORA047

ODIORA016b - Setting privileges

INFORMIX and ORACLE user privileges management are quite similar.

ORACLE provides roles to group privileges which then can be assigned to users. Starting with version 7.20, INFORMIX provides roles too. But users must execute the SET ROLE statement in order to enable a role. ORACLE users do not have to explicitly set a role, they are assigned to a default privilege domain (set of roles). More than one role can be enabled at a time with ORACLE.

INFORMIX users must have at least the CONNECT privilege to access the database :
    GRANT CONNECT TO (PUBLIC|user)

ORACLE users must have at least the CREATE SESSION privilege to access the database. This privilege is part of the CONNECT role.
    GRANT CONNECT TO (PUBLIC|user)

Warning : INFORMIX database privileges do NOT correspond exactly to ORACLE CONNECT, RESOURCE and DBA roles. However, roles can be created with equivalent privileges.

Solution :

Create a role which groups INFORMIX CONNECT privileges, and assign this role to the application users :

CREATE ROLE ifx_connect IDENTIFIED BY oracle;
GRANT CREATE SESSION, ALTER SESSION,
      CREATE ANY VIEW, ... TO ifx_connect;
GRANT ifx_connect TO user1;

ODIORA017 - Temporary tables

INFORMIX temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can also be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

Remark : BDL reports create a temporary table when the rows are not sorted externally (by the source SQL statement).

INFORMIX allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.

ORACLE does not support temporary tables as Informix does. ORACLE 8.1 provides GLOBAL TEMPORARY TABLEs which are shared among processes (only data is temporary and local to a SQL process). INFORMIX does not shared temp tables among SQL processes; each process can create its own temp table without table name conflicts.

Solution :

In accordance with some prerequisites, temporary tables creation in BDL programs can be supported by the database interface.

Warning : When creating a temporary table, you perform a Data Definition Language statement. Oracle automatically commits the current transaction when executing a DDL statement. Therefore, you must avoid temp table creation/destruction in transactions.

How does it work?

• INFORMIX specific statements involving temporary table creation are automatically converted to ORACLE "CREATE TABLE" statements and BDL program table names are converted to unique table names.
  
• The "temporary tables" are created in the schema of the current user. A "DROP TABLE" is performed automatically before the table creation, in order to remove existing tables which have not been removed due to a program crash.
  
• The data is stored in a dedicated tablespace : "TEMPTABS". Storing temporary table data in a separated tablespace allows you to use a physical device which can be different from the disk drive used for real data storage. Additionally, backups can be performed without the data of temporary tables.
  
• Once the temporary table has been created, all other SQL statements performed in the current SQL session are parsed to convert the original table name to the corresponding unique table name.
  
• When the BDL program disconnects from the database (for example, when it ends or when a CLOSE DATABASE instruction is executed), "temporary tables" which have not been removed with an explicit "DROP TABLE" are automatically removed by the database interface. However, if the program crashes, the "temporary tables" are not removed.

Prerequisites :

• Application users must have sufficient privileges to create database tables in their own schema (usually, "CONNECT" and "RESOURCE" roles).
  
• You must create a tablespace dedicated to temporary tables. The name of this tablespace must be "TEMPTABS".
  Warning : This tablespace must be of type "permanent". Make sure it is big enough to hold all the data, and check for automatic extension.
  For more details, see "CREATE TABLESPACE" in the Oracle documentation.
  

Limitations :

• Tokens matching the original table names are converted to unique names in all SQL statements. Make sure you are not using the temp table name for other database objects, like columns. The following example illustrates this limitation :
    CREATE TEMP TABLE tmp1 ( col1 INTEGER, col2 CHAR(20) )
    SELECT tmp1 FROM table_x WHERE ...
  
• You are not allowed to use the unique table name format in your own database schema. Make sure you are not using table or column names with the following format :
            tt_<identifier>_<number>
  
• In ORACLE, Data Definition Language statements (create table, drop table) automatically commit the current transaction. Make sure you are not creating or dropping temporary tables within a transaction.

Maintenance :

• If you want to list the tables created by specific user, do the following :
    SELECT * FROM ALL_TABLES WHERE OWNER = '<user_name>'
  Remark : as with other database object names, the user name is stored in uppercase letters if it has been created without using double quotes (create user scott ... = stored name is "SCOTT" ).

Creating indexes on temporary tables :

• Indexes created on temporary tables must have unique names also. The database interface detects CREATE INDEX statements which are using temporary tables and converts the index name to unique names.

• DROP INDEX statements are also detected to replace the original index name by the real name.

SERIALs in temporary table creation :

• You can use the SERIAL data type when creating a temporary table. See issue about SERIALs for more details.

ODIORA018 - Substrings in SQL

INFORMIX SQL statements can use subscripts on columns defined with the character data type :
    SELECT ... FROM tab1 WHERE col1[2,3] = 'RO'
    SELECT ... FROM tab1 WHERE col1[10]  = 'R'   -- Same as col1[10,10]
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
    SELECT ... FROM tab1 ORDER BY col1[1,3]

.. while ORACLE provides the SUBSTR( ) function, to extract a sub-string from a string expression :
    SELECT .... FROM tab1 WHERE SUBSTR(col1,2,2) = 'RO'
    SELECT SUBSTR('Some text',6,3) FROM DUAL       -- Gives 'tex'

Solution :

You must replace all Informix col[x,y] expressions by SUBSTR(col,x,y-x+1).

Warning : In UPDATE instructions, setting column values through subscripts will produce an error with ORACLE :
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
is converted to :
    UPDATE tab1 SET SUBSTR(col1,2,3-2+1) = 'RO' WHERE ...

ODIORA019 - Name resolution of SQL objects

INFORMIX uses the following form to identify an SQL object :
  [database[@dbservername]:][{owner|"owner"}.](identifier|"identifier")

The ANSI convention is to use double-quotes for identifier delimiters (For example : "tabname"."colname").

Warning : When using double quoted identifiers, both INFORMIX and ORACLE become case sensitive. Unlike INFORMIX, ORACLE database object names are stored in UPPERCASE in system catalogs. That means that SELECT "col1" FROM "tab1" will produce an error because those objects are identified by "COL1" and "TAB1" in ORACLE system catalogs.

Remark : in INFORMIX ANSI compliant databases :

• The table name must include "owner", unless the connected user is the owner of the database object.
• The database server shifts the owner name to uppercase letters before the statement executes, unless the owner name is enclosed in double quotes.

With ORACLE, an object name takes the following form :
   [(schema|"schema").](identifier|"identifier")[@database-link]

Remark : ORACLE has separate namespaces for different classes of objects (tables, views, triggers, indexes, clusters).

Object names are limited to 30 chars in ORACLE.

An ORACLE database schema is owned by a user (usually, the application administrator) and this user must create PUBLIC SYNONYMS to provide a global scope for his table names. PUBLIC SYNONYMS can have the same name as the schema objects they point to.

Solution :

Check that you do not use single or double quoted table names or column names in your source. Those quotes must be removed because the database interface automatically converts double quotes to single quotes, and ORACLE does not allow single quotes as database object name delimiters.

See also issue ODIORA007a

ODIORA020 - String delimiters and object names

The ANSI string delimiter character is the single quote ( 'string' ). Double quotes are used to delimit database object names ("object-name").

Example : WHERE "tabname"."colname" = 'a string value'

INFORMIX allows double quotes as string delimiters, but ORACLE doesn't. This is important, since many BDL programs use that character to delimit the strings in SQL commands.

Remark : this problem concerns only double quotes within SQL statements. Double quotes used in pure BDL string expressions are not subject to SQL compatibility problems.

Solution :

The ORACLE database interface can automatically replace all double quotes by single quotes.

Escaped string delimiters can be used inside strings like the following :

     'This is a single quote : '''
     'This is a single quote : \''
     "This is a double quote : """
     "This is a double quote : \""

Warning : Database object names cannot be delimited by double quotes because the database interface cannot determine the difference between a database object name and a quoted string !

For example, if the program executes the SQL statement :
   WHERE "tabname"."colname" = "a string value"
replacing all double quotes by single quotes would produce :
   WHERE 'tabname'.'colname' = 'a string value'
This would produce an error since 'tabname'.'colname' is not allowed by ORACLE.

Although double quotes are replaced automatically in SQL statements, you should use only single quotes to enforce portability.

ODIORA021 - NUMERIC data types

INFORMIX supports several data types to store numbers :

ORACLE supports only one data type to store numbers :

ANSI types like SMALLINT, INTEGER, FLOAT are supported by ORACLE but will be respectively converted to ORACLE native data types NUMBER(38), NUMBER(38) and NUMBER.

Warning : When dividing INTEGERs or SMALLINTs, INFORMIX rounds the result ( 7 / 2 = 3 ), while ORACLE doesn't, because it does not have a native integer data type ( 7 / 2 = 3.5 )

Solution :

We recommend that you use the following conversion rules:

Avoid dividing integers in SQL statements.  If you do divide an integer, use the TRUNC() function with ORACLE.

ODIORA022 - Getting one row with SELECT

With INFORMIX, you must use the system table with a condition on the table id :

   SELECT user FROM systables WHERE tabid=1

Oracle provides the DUAL table to generate one row only.

   SELECT user FROM DUAL 

Solution :

Check the BDL sources for "FROM systables WHERE tabid=1" and use dynamic SQL to resolve this problem.

ODIORA024 - MATCHES and LIKE in SQL conditions

INFORMIX supports MATCHES and LIKE in SQL statements, while ORACLE supports the LIKE statement only.

MATCHES allows you to use brackets to specify a set of matching characters at a given position :
   ( col MATCHES '[Pp]aris' ).
   ( col MATCHES '[0-9][a-z]*' ).
In this case, the LIKE statement has no equivalent feature.

The following substitutions must be done to convert a MATCHES condition to a LIKE condition :

• MATCHES keyword must be replaced by LIKE.
• All '*' characters must be replaced by '%'.
• All '?' characters must be replaced by '_'.
• Remove all brackets expressions.

Solution :

Warning : SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.

ORACLE provides the TRANSLATE function which can be used to replace MATCHES in specific cases. The TRANSLATE function replaces all occurrences of characters listed in a 'from' set, with the corresponding character defined in a 'to' set.
INFORMIX : WHERE col MATCHES '[0-9][0-9][0-9]'
ORACLE   : WHERE TRANSLATE(col,'0123456789','9999999999')='999'

ODIORA025 - INFORMIX specific SQL statements in BDL

The BDL compiler supports several INFORMIX specific SQL statements that have no meaning when using ORACLE:

deleted the next sentence as not necessary

• CREATE DATABASE
• DROP DATABASE
• START DATABASE (SE only)
• ROLLFORWARD DATABASE
• SET [BUFFERED] LOG
• CREATE TABLE with special options (storage, lock mode, etc.)

Solution :

Review your BDL source and remove all static SQL statements which are INFORMIX specific.

ODIORA028 - INSERT cursors

INFORMIX supports insert cursors. An "insert cursor" is a special BDL cursor declared with an INSERT statement instead of a SELECT statement. When this kind of cursor is open, you can use the PUT instruction to add rows and the FLUSH instruction to insert the records into the database.

For INFORMIX database with transactions, OPEN, PUT and FLUSH instructions must be executed within a transaction.

ORACLE does not support insert cursors.

Solution :

Insert cursors are emulated by the ORACLE database interface.

ODIORA029 - SQL functions and constants

Almost all INFORMIX functions and SQL constants have a different name or behavior in ORACLE.

Here is a comparison list of functions and constants  :

Solution :

Warning: You must review the SQL statements using TODAY / CURRENT / EXTEND expressions.

You can define stored functions in the ORACLE database, to simulate INFORMIX functions. This works only for functions that are not already provided by ORACLE :

  create or replace function month( adate in date )
  return number
  is
    v_month number;
  begin
    v_month := to_number( to_char( adate, 'mm' ) );
    return (v_month);
  end month;

You can find the scripts to create those stored functions in the ifxf_ora.sql script provided in the Adaptation Kit.

ODIORA030 - Very large data types

INFORMIX uses the TEXT and BYTE data types to store very large texts or images. ORACLE 8 provides CLOB, BLOB, and BFILE data types. Columns of these types store a kind of pointer ( lob locator ). This technique allows you to use more than one CLOB / BLOB / BFILE column per a table.

Solution :

The ORACLE database interface can convert BDL TEXT data to CLOB and BYTE data to BLOB.

Warning : ORACLE BFILEs are not supported.

ODIORA031 - Cursors WITH HOLD

INFORMIX closes opened cursors automatically when a transaction ends unless the WITH HOLD option is used in the DECLARE instruction. In ORACLE, opened cursors using SELECT statements without a FOR UPDATE clause are not closed when a transaction ends. Actually, all ORACLE cursors are 'WITH HOLD' cursors unless the FOR UPDATE clause is used in the SELECT statement.

Solution :

BDL cursors that are not declared "WITH HOLD" are automatically closed by the database interface when a COMMIT WORK or ROLLBACK WORK is performed.

Warning : Since ORACLE automatically closes  FOR UPDATE cursors when the transaction ends, opening cursors declared FOR UPDATE and WITH HOLD results in an SQL error that does not normally appear with INFORMIX, in the same conditions. Review the program logic in order to find another way to set locks.

ODIORA032 - UPDATE/DELETE WHERE CURRENT OF <cursor>

INFORMIX allows positioned UPDATEs and DELETEs with the "WHERE CURRENT OF <cursor>" clause, if the cursor has been DECLARED with a SELECT ... FOR UPDATE statement.

Warning : UPDATE/DELETE ... WHERE CURRENT OF <cursor> is not support by the Oracle database API. However, ROWIDs can be used for positioned updates/deletes.

Solution :

UPDATE/DELETE ... WHERE CURRENT OF instructions are managed by the ORACLE database interface. The ORACLE database interface replaces "WHERE CURRENT OF <cursor>"  by  "WHERE ROWID=:rid" and sets the value of the ROWID returned by the last FETCH done with the given cursor..

ODIORA033 - Querying system catalog tables

As in INFORMIX, ORACLE provides system catalog tables (actually, system views). But the table names and their structure are quite different.

Solution :

Warning : No automatic conversion of INFORMIX system tables is provided by the database interface.

ODIORA034 - Syntax of UPDATE statements

INFORMIX allows a specific syntax for UPDATE statements :

    UPDATE table SET ( <col-list> ) = ( <val-list> )

or

Solution :

Static UPDATE statements using the above syntax are converted by the compiler to the standard form :

    UPDATE table SET column=value [,...]

ODIORA036 - INTERVAL data type

INFORMIX's INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes : year-month intervals and day-time intervals.

ORACLE 8i does not provide a data type similar to Informix INTERVAL.

ORACLE 9i supports the INTERVAL data type similar to INFORMIX, with two classes (YEAR TO MONTH and DAY TO SECOND), but Oracle's INTERVAL cannot be defined with a precision different from these two classes (for example, you cannot define an INTERVAL HOUR TO MINUTE in Oracle). The class DAY TO SECOND(n) is equivalent to the INFORMIX INTERVAL class DAY TO FRACTION(n).

Solution :

When using Oracle 8i

It is not recommended that you use the INTERVAL data type because Oracle 8i has no equivalent native data type. This would cause problems when doing INTERVAL arithmetic on the database server side. However, INTERVAL values can be stored in CHAR columns.

When using Oracle 9i

Informix INTERVAL YEAR(n) TO MONTH data is stored in Oracle INTERVAL YEAR(n) TO MONTH columns. These data types are equivalent.

Informix INTERVAL DAY(n) TO FRACTION(p) data is stored in Oracle INTERVAL DAY(n) TO SECOND(p) columns. These data types are equivalent.

Other Informix INTERVAL types must be stored in CHAR() columns as with Oracle 8i, because the high qualifier precision cannot be specified with Oracle INTERVALs. For example, Informix INTERVAL HOUR(5) TO MINUTE has no native equivalent in Oracle.

ODIORA039 - Data storage concepts

An attempt should be made to preserve as much of the storage specification as possible when converting from INFORMIX to ORACLE. Most important storage decisions made for INFORMIX database objects (like initial sizes and physical placement) can be reused for the ORACLE database.

Storage concepts are quite similar in INFORMIX and in ORACLE, but the names are different.

The following table compares INFORMIX storage concepts to ORACLE storage concepts :

ODIORA040 - National characters data types

INFORMIX : NCHAR & NVARCHAR
ORACLE : NCHAR & NVARCHAR2

- Only OCI V8 supports NCHAR datatype.
- String constants must be preceded by the character 'N'.

Solution :

Warning : National character data types are not supported yet.

ODIORA046 - The LOAD and UNLOAD instructions

INFORMIX provides two SQL instructions to export / import data from / into a database table: The UNLOAD instruction copies rows from a database table into a text file and the LOAD instructions insert rows from a text file into a database table.

ORACLE does not provide LOAD and UNLOAD instructions, but provides external tools like SQL*Plus and SQL*Loader.

Solution :

In 4gl programs, the LOAD and UNLOAD instructions are supported with ORACLE, with some limitations:

Warning : There is a difference when using ORACLE DATE columns.  DATE columns created in the ORACLE database are equivalent to INFORMIX DATETIME YEAR TO SECOND columns.  In LOAD and UNLOAD, all ORACLE DATE columns are treated as INFORMIX DATETIME YEAR TO SECOND columns and thus will be unloaded with the "YYYY-MM-DD hh:mm:ss"  format.
The same problem appears for INFORMIX INTEGER and SMALLINT values, which are stored in an ORACLE database as NUMBER(?) columns. Those values will be unloaded as INFORMIX DECIMAL(10) and DECIMAL(5) values, that is, with a trailing dot-zero ".0".

Warning : When using an INFORMIX database, simple dates are unloaded using the DBDATE format (ex: "23/12/1998"). Therefore, unloading from an INFORMIX database for loading into an ORACLE database is not supported.

ODIORA047 - The USER constant

Both INFORMIX and ORACLE provide the USER constant, which identifies the current user connected to the database server.

Example :

INFORMIX : SELECT USER FROM systables WHERE tabid=1
ORACLE : SELECT USER FROM DUAL

However, there is a difference :

• INFORMIX returns the user identifier as defined in the operating system, where it can be case sensitive (UNIX) or not (NT).
• ORACLE returns the user identifier which is stored in the database. By default ORACLE converts the user name to uppercase letters, if you do not put the user name in double quotes when creating it.

This is important if your application stores user names in database records (for example, to audit data modifications). You can, for example, connect to ORACLE with the name 'scott', and perform the following SQL operations :
     (1) INSERT INTO mytab ( creator, comment )
              VALUES ( USER, 'example' );
     (2) SELECT * FROM mytab
               WHERE creator = 'scott';
The first command inserts 'SCOTT' (in uppercase letters) in the author column. The second statement will not find the row.

Solution :

When creating a user in ORACLE, you can put double quotes around the user name in order to force ORACLE to store the given user identifier as is :

CREATE USER "scott" IDENTIFIED BY <pswd>

To verify the user names defined in the ORACLE database, connect as SYSTEM and list the records of the ALL_USERS table as follows :

    SELECT * FROM ALL_USERS

ODIORA051 - Setup database statistics

INFORMIX provides a special instruction to compute database statistics in order to help the optimizer to find the right query execution plan :

UPDATE STATISTICS ...

Oracle has the following instruction to compute database statistics :

ANALYZE ...

See Oracle documentation for more details.

Solution :

Centralize the optimization instruction in a function. See the ODILIB.BDL source as example.

ODIORA052 - The GROUP BY clause

INFORMIX allows you to use column numbers in the GROUP BY clause

     SELECT ord_date, sum(ord_amount) FROM order GROUP BY 1

Oracle does not support column numbers in the GROUP BY clause.

Solution :

Use column names instead.

ODIORA053 - The ALTER TABLE instruction

INFORMIX and ORACLE have different implementations of the ALTER TABLE instruction. For example, INFORMIX allows you to use multiple ADD clauses separated by commas. This is not supported by ORACLE :

INFORMIX :
     ALTER TABLE customer ADD(col1 INTEGER), ADD(col2 CHAR(20))

ORACLE :
     ALTER TABLE customer ADD(col1 INTEGER, col2 CHAR(20))

Solution :

Warning : No automatic conversion is done by the database interface. There is no real standard for this instruction ( that is, no common syntax for all database servers). Read the SQL documentation and review the SQL scripts or the BDL programs in order to use the database server specific syntax for ALTER TABLE.

ODIORA054 - The star (asterisk) in SELECT statements

Informix allows you to use the star character in the select list along with other expressions :

   SELECT col1, * FROM tab1 ...

Oracle does not support this. You must use the table name as a prefix to the star ::

   SELECT col1, tab1.* FROM tab1 ... 

Solution :

Always use the table name before the star.

ODIORA055 - NULLs in indexed columns

Oracle btree indexes do not store null values, while Informix btree indexes do. This means that if you index a single column and select all the rows where that column is null, Informix will do an indexed read to fetch just those rows, but Oracle will do a sequential scan of all rows to find them. Having an index unusable for "is null" criteria can also completely change the behavior and performance of more complicated selects without causing a sequential scan.

Solution :

Declare the indexed columns as NOT NULL with a default value and change the programmatic logic. If you do not want to change the programs, partitioning the table so that the nulls have a partition of their own will reduce the sequential scan to just the nulls (un-indexed) partition, which is relatively fast.

ODIORA100 - Data type conversion table