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:

Table 1. SQL functions and constants (Informix vs. Oracle)
Informix	ORACLE
today	trunc( sysdate )
current year to second	sysdate
day( value )	to_number( to_char( value, 'dd' ) )
extend( dtvalue, first to last )	to_date( nvl( to_char( dtvalue, 'fmt-mask' ), '19000101000000' ), 'fmt-mask' )
mdy(m,d,y)	to_date( to_char(m,'09') \|\| to_char(d,'09') \|\| to_char(y,'0009'), 'MMDDYYYY' )
month( date )	to_number( to_char( date, 'mm' ) )
weekday( date )	to_number( to_char( date, 'd' ) ) -1
year( date )	to_number( to_char( date, 'yyyy' ) )
date( "string" \| integer )	No equivalent - Depends from DBDATE in IFX
user	user ! Uppercase/lowercase: See The User Constant
trim( [leading \| trailing \| both "char" FROM] "string")	ltrim() and rtrim()
length( c )	length( c ) ! Different behavior: See The Length Function
pow(x,y)	power(x,y)

Solution

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