SQL functions have been added to the OpenVMS Oracle Rdb SQL
interface for convergence with Oracle SQL. Complete descriptions
of these functions can be found in the Oracle Server SQL Language
Reference Manual.
1 – Optional Oracle SQL Functions
Optionally, you can install the functions listed in Optional Oracle
SQL Functions in your database from interactive SQL as shown in the
following examples.
The file is named SQL_FUNCTIONSnn.SQL, where "nn" is the version
number. For example, use the following statement:
SQL> ATTACH 'FILENAME mydatabase';
SQL> @SYS$LIBRARY:SQL_FUNCTIONS72.SQL
If you wish to use a character set other than DEC_MCS with the
installable functions, you must first define the RDB$ORACLE_
SQLFUNC_VCHAR_DOM domain as a character type using the desired
character set before executing the preceding statements.
Similarly, if you wish to use a date data type other than DATE
VMS with the installable functions, you must first define the
RDB$ORACLE_SQLFUNC_DATE_DOM domain as a date data type before
executing the preceding statements.
For example,
SQL> ATTACH 'FILENAME mydatabase';
SQL> CREATE DOMAIN RDB$ORACLE_SQLFUNC_VCHAR_DOM VARCHAR(2000)
cont> CHARACTER SET KANJI;
SQL> CREATE DOMAIN RDB$ORACLE_SQLFUNC_DATE_DOM DATE ANSI;
SQL> @SYS$LIBRARY:SQL_FUNCTIONS72.SQL
If you choose, you may remove the installable functions from your
database at a later time. However, you must release any dynamic
SQL statements and disconnect any sessions that reference any
of these functions before you can remove the functions. Use the
following statements from interactive SQL if you wish to remove
the installable functions from your database:
SQL> ATTACH 'FILENAME mydatabase';
SQL> @SYS$LIBRARY:SQL_FUNCTIONS_DROP72.SQL
The file SYS$LIBRARY:SQL_FUNCTIONS_DROPnn.SQL, where "nn" is the
version number.
Optional Oracle SQL Functions gives a brief description of
each of the functions that you can optionally install in your
database.
Table 1-12 Optional Oracle SQL Functions
Function Name Description Restrictions
ADD_MONTHS Returns the d must be of the same date data
(d,n) date d plus n type as the RDB$ORACLE_SQLFUNC_
months. DATE_DOM domain, which is bound
when you install the Oracle SQL
functions.
ACOS (n) Returns the n must be in the range of -1 to
arc cosine of 1, and the function returns a
n. DOUBLE PRECISION value in the
range of 0 to pi, expressed
in radians. If the passed
expression results in NULL then
the result of ACOS will be NULL.
The following example returns the arc cosine of .3:
SQL> SELECT ACOS(.3) "Arc_Cosine" FROM Rdb$DATABASE;
Arc_Cosine
1.266103672779499E+000
1 row selected
ACOSH (n) Returns the n must be equal to or greater
hyperbolic arc than 1. The function returns
cosine of n. a DOUBLE PRECISION value. If
either passed expression results
in NULL then the result of ACOSH
will be NULL.
SQL> SELECT ACOSH(1.0) "Hyperbolic Arc Cosine" FROM Rdb$DATABASE;
Hyperbolic Arc Cosine
0.000000000000000E+000
1 row selected
ASCII (str) Returns str must be of the same
the decimal character set as the RDB$ORACLE_
representation SQLFUNC_VCHAR_DOM domain, which
of the first is bound when you install the
character of Oracle SQL functions.
its argument.
ASIN (n) Returns the n must be in the range of -
arc sine of n. 1 to 1. The function returns
a DOUBLE PRECISION value in
the range of -pi/2 to pi/2,
expressed in radians. If the
passed expression results in
NULL then the result of ASIN
will be NULL.
The following example returns the arc sine of .3:
SQL> SELECT ASIN(.3) "Arc_Sine" FROM Rdb$DATABASE;
Arc_Sine
3.046926540153975E-001
1 row selected
ASINH (n) Returns the The function returns a DOUBLE
hyperbolic arc PRECISION value. If either
sine of n. passed expression results in
NULL then the result of ASINH
will be NULL.
SQL> SELECT ASINH (-
90.0) "Hyperbolic Arc Sine" FROM Rdb$DATABASE;
Hyperbolic Arc Sine
-5.192987713658941E+000
1 row selected
ATAN (n) Returns the n can be in an unbounded range
arc tangent of and returns a value in the range
n. of -pi/2 to pi/2, expressed
in radians. If the passed
expression results in NULL then
the result of ATAN will be NULL.
The following example returns the arc tangent of .3:
SQL> SELECT ATAN(.3) "Arc_Tangent" FROM Rdb$DATABASE;
Arc_Tangent
2.914567944778671E-001
1 row selected
ATANH (n) Returns the n must be in the range of -
hyperbolic arc 1 to 1. The function returns
tangent of n a DOUBLE PRECISION value. If
(in radians). either passed expression results
in NULL then the result of ATANH
will be NULL.
SQL> SELECT ATANH(0.905148254) "Hyperbolic Arc Tangent" FROM Rdb$DATABASE;
Hyperbolic Arc Tangent
1.500000001965249E+000
1 row selected
ATAN2 (n,m) Returns the n can be in an unbounded range
arc tangent of and returns a value in the range
n and m. of -pi to pi, depending on the
signs of n and m, expressed in
radians. ATAN2(n,m) is the same
as ATAN(n/m). If either passed
expression results in NULL then
the result of ATAN2 will be
NULL.
SQL> SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM Rdb$DATABASE;
Arc_Tangent2
9.827937232473291E-001
1 row selected
BITAND BITAND This function is commonly used
(expr1,expr2) computes an with the DECODE function. If
AND operation the passed expression results
on the bits in NULL then the result of the
of expr1 and BITAND will be null.
expr2, both
of which must
resolve to
integers, and
returns an
integer.
CEIL (n) Returns the
smallest
integer
greater than
or equal to n.
CHR (n) Returns the The returned value is of type
character RDB$ORACLE_SQLFUNC_VCHAR_DOM,
having the character set of which
the binary is bound when you install
equivalent to the Oracle SQL functions. In
n. addition, only 1 octet (byte) of
data is encoded.
COS (n) Returns the
cosine of n
(an angle
expressed in
radians).
COSH (n) Returns the
hyperbolic
cosine of n
(an angle
expressed in
radians).
COT (n) COT returns The function returns a DOUBLE
the cotangent PRECISION value. If either
of n. passed expression results in
NULL then the result of COT will
be NULL.
SQL> SELECT COT (3.14159265358979/4) "Cotangent" FROM Rdb$DATABASE;
Cotangent
1.000000000000002E+000
1 row selected
EXP (n) Returns e
raised to
the nth power
(e=2.71828183 . . .
).
FLOOR (n) Returns
the largest
integer equal
to or less
than n.
HEXTORAW (str) Converts its str must be of the same
argument character set as the RDB$ORACLE_
containing SQLFUNC_VCHAR_DOM domain, which
hexadecimal is bound when you install the
digits to a Oracle SQL functions. The value
raw character returned is of type RDB$ORACLE_
value. SQLFUNC_VCHAR_DOM.
INITCAP (str) Returns str must be of the same
the string character set as the RDB$ORACLE_
argument, SQLFUNC_VCHAR_DOM domain, which
with the first is bound when you install the
letter of Oracle SQL functions. The value
each word in returned is of type RDB$ORACLE_
uppercase, all SQLFUNC_VCHAR_DOM.
other letters
in lowercase.
Words are
delimited
by non-
alphanumeric
characters.
INSTR Searches s1 s1 and s2 must be of the same
(s1,s2[,n[,m]]) beginning character set as the RDB$ORACLE_
with its nth SQLFUNC_VCHAR_DOM domain, which
character and is bound when you install the
returns the Oracle SQL functions. If either
character n or m is omitted, they default
position to 1.
of the mth
occurrence
of s2 or 0 if
s2 does not
occur m times.
If n < 0, the
search starts
at the end of
s1.
INSTRB Searches s1 s1 and s2 must be of the same
(s1,s2[,n[,m]]) beginning character set as the RDB$ORACLE_
with its nth SQLFUNC_VCHAR_DOM domain, which
octet and is bound when you install the
returns the Oracle SQL functions. If either
octet position n or m is omitted, they default
of the mth to 1.
occurrence
of s2 or 0 if
s2 does not
occur m times.
If n < 0, the
search starts
at the end of
s1.
LAST_DAY (d) Returns the d must be of the same date data
last day of type as the RDB$ORACLE_SQLFUNC_
the month that DATE_DOM domain, which is bound
contains d. when you install the Oracle SQL
functions. The value returned is
of type RDB$ORACLE_SQLFUNC_DATE_
DOM.
LN (n) Returns
the natural
logarithm of
n where n is
greater than
0.
LOG (m,n) Returns the
logarithm base
m of n. The
base m can be
any positive
number other
than 0 or 1
and n can be
any positive
number.
LPAD (s,l,p) Returns s s and p must be of the same
left-padded to character set as the RDB$ORACLE_
length l with SQLFUNC_VCHAR_DOM domain, which
the sequence is bound when you install the
of characters Oracle SQL functions. The value
in p. If s is returned is of type RDB$ORACLE_
longer than l, SQLFUNC_VCHAR_DOM. There is no
this function default for p as with Oracle.
returns that
portion of s
that fits in
l.
LTRIM Removes s1 and s2 must be of the same
(s1[,s2]) characters character set as the RDB$ORACLE_
from the SQLFUNC_VCHAR_DOM domain, which
left of s1, is bound when you install the
with initial Oracle SQL functions. The value
characters returned is of type RDB$ORACLE_
removed up SQLFUNC_VCHAR_DOM. If omitted,s2
to the first defaults to space.
character not
in s2.
MOD (m,n) Returns the
remainder of m
divided by n.
Returns m if n
is 0.
MONTHS_BETWEEN Returns the d1 and d2 must be of the
(d1,d2) number of same date data type as the
months between RDB$ORACLE_SQLFUNC_DATE_DOM
dates d1 and domain, which is bound when
d2. you install the Oracle SQL
functions.
NEW_TIME Returns the d1 must be of the same date data
(d1,z1,z2) date and time type as the RDB$ORACLE_SQLFUNC_
in time zone DATE_DOM domain, which is bound
z2 when the when you install the Oracle SQL
date and time functions. z1 and z2 must be
in time zone of the same character set as
z1 is d. Time the RDB$ORACLE_SQLFUNC_VCHAR_
zones z1 and DOM domain, which is also bound
z2 can be: when you install the Oracle SQL
AST, ADT, BST, functions. The value returned is
BDT, CST, CDT, of type RDB$ORACLE_SQLFUNC_DATE_
EST, EDT, GMT, DOM.
HST, HDT, MST,
MDT, NST, PST,
PDT, YST, or
YDT.
NEXT_DAY Returns the d must be of the same date data
(d,dayname) date of the type as the RDB$ORACLE_SQLFUNC_
first weekday DATE_DOM domain, which is bound
named by when you install the Oracle SQL
dayname that functions. dayname must be of
is later than the same character set as the
the date d. RDB$ORACLE_SQLFUNC_VCHAR_DOM
domain, which is also bound
when you install the Oracle SQL
functions. The value returned is
of type RDB$ORACLE_SQLFUNC_DATE_
DOM.
POWER (m,n) Returns m
raised to the
nth power.
The base m and
the exponent
n can be any
number but if
m is negative,
n must be an
integer.
RAWTOHEX (str) Converts its str must be of the same
raw argument character set as the RDB$ORACLE_
to a character SQLFUNC_VCHAR_DOM domain, which
value is also bound when you install
containing its the Oracle SQL functions.
hexadecimal The value returned is of type
equivalent. RDB$ORACLE_SQLFUNC_VCHAR_DOM.
REPLACE Returns s1 s1, s2, and s3 must be of the
(s1[,s2[,s3]]) with every same character set as the
occurrence of RDB$ORACLE_SQLFUNC_VCHAR_DOM
s2 replaced by domain, which is bound when
s3. you install the Oracle SQL
functions. The value returned
is of type RDB$ORACLE_SQLFUNC_
VCHAR_DOM. If omitted, s2 and s3
default to an empty string.
RPAD Returns s s and p must be of the same
(s[,l[,p]]) left-padded to character set as the RDB$ORACLE_
length l with SQLFUNC_VCHAR_DOM domain, which
the sequence is also bound when you install
of characters the Oracle SQL functions. The
in p. If s is value returned is of type
longer than l, RDB$ORACLE_SQLFUNC_VCHAR_DOM.
this function If omitted, p defaults to a
returns that space.
portion of s
that fits in
l.
RTRIM Returns s2 s1 and s2 must be of the same
(s1[,s2]) with final character set as the RDB$ORACLE_
characters SQLFUNC_VCHAR_DOM domain, which
after the last is also bound when you install
character not the Oracle SQL functions. The
in s2. value returned is of type
RDB$ORACLE_SQLFUNC_VCHAR_DOM.
If omitted, s2 defaults to a
space.
SIN (n) Returns the
sine of n
(an angle
expressed in
radians).
SINH (n) Returns the
hyperbolic
sine of n
(an angle
expressed in
radians).
SQRT (n) Returns the
square root of
n. The value
of n cannot
be negative.
SQRT returns
a double
precision
result.
SUBSTR Returns a s must be of the same character
(s[,p[,l]]) portion of s, set as the RDB$ORACLE_SQLFUNC_
l characters VCHAR_DOM domain, which is
long, also bound when you install the
beginning Oracle SQL functions. The value
at character returned is of type RDB$ORACLE_
position p. If SQLFUNC_VCHAR_DOM. If omitted, l
p is negative, defaults to zero (0).
SUBSTR counts
backward from
the end of s.
SUBSTRB Same as s must be of the same character
(s[,p[,l]]) SUBSTR, except set as the RDB$ORACLE_SQLFUNC_
p and l are VCHAR_DOM domain, which is
expressed in also bound when you install the
octets (bytes) Oracle SQL functions. The value
rather than returned is of type RDB$ORACLE_
characters. SQLFUNC_VCHAR_DOM. If omitted, l
defaults to zero (0).
TAN (n) Returns the
tangent of
n (an angle
expressed in
radians).
TANH (n) Returns the
hyperbolic
tangent of
n (an angle
expressed in
radians).