1 Oracle_SQL_Functions 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. 2 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).