Developing Portable Applications : B. Function Mapping : Mapping OpenSQL Functions to Host Functions : Oracle Gateway
 
Share this page                  
Oracle Gateway
The following table contains a list of the OpenSQL functions and the support level for the Oracle gateway:
Function Name
Support
Comments
ABS
COMPAT
None
ATAN
COMPAT
None
AVG
COMPAT
None
BIGINT
RESTRICT
None
BIT_ADD
NO
None
BIT_AND
NO
None
BIT_NOT
NO
None
BIT_OR
NO
None
BIT_XOR
NO
None
BYTE
NO
None
C
NO
None
CHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
CHAREXTRACT
RESTRICT
Results will not match Ingres if the second parameter is less than 1 or greater than the length of the first parameter.
If CHAREXTRACT is used in a SELECT target list and the result is a space, the gateway will perform regular empty string processing, that is, an empty string will be sent to the client. An exception to this behavior occurs if c1 is a column of varchar(6) and it contains 'Aa Bb', then length(charextract(c1, 3)) returns 1 (not 0).
If CHAREXTRACT is used in a WHERE clause, the result matches Ingres behavior.
CONCAT
COMPAT
None
CORR
COMPAT
None
COS
COMPAT
None
COUNT
TRANS
None
COVAR_POP
COMPAT
None
COVAR_SAMP
COMPAT
None
DATE
TRANS
None
DATE_GMT
RESTRICT
Parameter must be a literal. Return value contains both date and time.
DATE_PART
RESTRICT
Units must be a literal. The unit 'week' is not supported. If the date is not in a host-acceptable format, it must be inside a DATE typecast.
DATE_TRUNC
RESTRICT
Units must be a literal. Second parameter cannot be a host variable when unit = week. Note that if the date is not in a host-acceptable format, it must be inside a DATE typecast.
DBMSINFO
RESTRICT
Parameter must be a literal. May only be used as the inner most scalar(). Nesting dbmsinfo() is not supported. Valid request_name values are:
_bintim
_bio_cnt
_cpu_ms
_et_sec
_version
autocommit_state
charset
database
dba
query_language
server_class
terminal
transaction_state
username
DECIMAL
RESTRICT
The optional precision and scale are not passed on to Oracle. Function does not restrict input value to range of data type. If function is in a SELECT target list, the result is a float8.
DOW
TRANS
None
EXP
COMPAT
None
FLOAT4
RESTRICT
Function does not restrict input value to range of data type.
FLOAT8
RESTRICT
Function does not restrict input value to range of data type.
GMT_TIMESTAMP
RESTRICT
Parameter must be a literal.
HEX
TRANS
None
IFNULL
TRANS
None
INT1
RESTRICT
Function does not restrict input value to range of data type.
INT2
RESTRICT
Function does not restrict input value to range of data type.
INT4
RESTRICT
Function does not restrict input value to range of data type.
INT8
RESTRICT
None
INTERVAL
NO
None
INTEXTRACT
RESTRICT
The Oracle gateway does not support the BYTE data type; the first parameter must be char or varchar; the string must be a 7-bit ASCII. Results are undefined for multi-byte or extended character sets. Return type is float8 instead of integer. Results may not match Ingres if the second parameter is less than 1 or greater than the length of the first parameter.
LEFT
COMPAT
None
LENGTH
TRANS
None
LN
COMPAT
None
LOCATE
TRANS
None
LOG
TRANS
None
LONG_BYTE
NO
None
LONG_VARCHAR
RESTRICT
Deprecated Oracle types such as LONG and LONG RAW are not supported with the LONG_VARCHAR function.
LOWER
COMPAT
None
LOWERCASE
TRANS
None
MAX
COMPAT
None
MIN
COMPAT
None
MOD
COMPAT
None
MONEY
RESTRICT
Function does not restrict input value to range of data type. If function is in a SELECT target list, the result is a float8.
NCHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
NOTRIM
NO
None
NVARCHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
OBJECT_KEY
NO
None
PAD
NO
None
POWER
COMPAT
If the first parameter is negative, the second parameter must be positive; this is an Oracle limitation.
RANDOM
TRANS
None
RANDOM(L,H)
RESTRICT
The first parameter must be a literal.
RANDOMF
TRANS
None
RANDOMF(L,H)
RESTRICT
The first parameter must be a literal. Return type is ALWAYS float8, whereas the Ingres return type varies depending on the type of the input parameters.
REGR_AVGX
COMPAT
None
REGR_AVGY
COMPAT
None
REGR_COUNT
COMPAT
None
REGR_INTERCEPT
COMPAT
None
REGR_R2
COMPAT
None
REGR_SLOPE
COMPAT
None
REGR_SXX
COMPAT
None
REGR_SXY
COMPAT
None
REGR_SYY
COMPAT
None
RIGHT
TRANS
None
SHIFT
RESTRICT
The first parameter cannot be a host variable. Length must be a literal. Results will not match Ingres when LENGTH(PARM1) + PARM2 exceed the original storage length of PARM1.
SIN
COMPAT
None
SIZE
NO
None
SOUNDEX
COMPAT
None
SQRT
COMPAT
None
SQUEEZE
RESTRICT
This function may be used to compare two similar strings without regard to whitespace. It is not 100% compatible with the Ingres SQUEEZE definition in that the result may not be the same length as the parameter.
STDDEV_POP
COMPAT
None
STDDEV_SAMP
COMPAT
None
SUBSTRING
TRANS
None
SUM
COMPAT
None
TABLE_KEY
NO
None
TEXT
NO
None
TRIM
TRANS
None
UNHEX
TRANS
None
UPPER
COMPAT
None
UPPERCASE
TRANS
None
VARBYTE
NO
None
VARCHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
VAR_POP
COMPAT
None
VAR_SAMP
COMPAT
None
_DATE
RESTRICT
Parameter must be a literal.
_DATE4
RESTRICT
Parameter must be a literal.
_TIME
RESTRICT
Parameter must be a literal.