1Orafce Documentation 2=== 3 4Orafce - Oracle's compatibility functions and packages 5--- 6 7This documentation describes the environment settings and functionality offered for features that are compatible with Oracle databases. 8 9 10Chapter 1 Overview 11--- 12 13Features compatible with Oracle databases are provided. 14These features enable you to easily migrate to PostgreSQL and reduce the costs of reconfiguring applications. 15 16The table below lists features compatible with Oracle databases. 17 18 19 20### 1.1 Features compatible with Oracle databases 21 22**Data type** 23 24|Item|Overview| 25|:---|:---| 26|VARCHAR2|Variable-length character data type| 27|NVARCHAR2|Variable-length national character data type| 28|DATE|Data type that stores date and time| 29 30**SQL Queries** 31 32|Item|Overview| 33|:---|:---| 34|DUAL table|Table provided by the system| 35 36 37**SQL Functions** 38 39 - Mathematical functions 40 41|Item|Overview| 42|:---|:---| 43|BITAND|Performs a bitwise AND operation| 44|COSH|Calculates the hyperbolic cosine of a number| 45|SINH|Calculates the hyperbolic sine of a number| 46|TANH|Calculates the hyperbolic tangent of a number| 47 48 49 - String functions 50 51|Item|Overview| 52|:---|:---| 53|INSTR|Returns the position of a substring in a string| 54|LENGTH|Returns the length of a string in number of characters| 55|LENGTHB|Returns the length of a string in number of bytes| 56|LPAD|Left-pads a string to a specified length with a sequence of characters| 57|LTRIM|Removes the specified characters from the beginning of a string| 58|NLSSORT|Returns a byte string used to sort strings in linguistic sort sequence based on locale| 59|RPAD|Right-pads a string to a specified length with a sequence of characters| 60|RTRIM|Removes the specified characters from the end of a string| 61|SUBSTR|Extracts part of a string using characters to specify position and length| 62|SUBSTRB|Extracts part of a string using bytes to specify position and length| 63 64 65 - Date/time functions 66 67|Item|Overview| 68|:---|:---| 69|ADD_MONTHS|Adds months to a date| 70|DBTIMEZONE|Returns the value of the database time zone| 71|LAST_DAY|Returns the last day of the month in which the specified date falls| 72|MONTHS_BETWEEN|Returns the number of months between two dates| 73|NEXT_DAY|Returns the date of the first instance of a particular day of the week that follows the specified date| 74|ROUND|Rounds a date| 75|SESSIONTIMEZONE|Returns the time zone of the session| 76|SYSDATE|Returns the system date| 77|TRUNC|Truncates a date| 78 79 80 - Data type formatting functions 81 82|Item|Overview| 83|:---|:---| 84|TO_CHAR|Converts a value to a string| 85|TO_DATE|Converts a string to a date in accordance with the specified format| 86|TO_MULTI_BYTE|Converts a single-byte string to a multibyte string| 87|TO_NUMBER|Converts a value to a number in accordance with the specified format| 88|TO_SINGLE_BYTE|Converts a multibyte string to a single-byte string| 89 90 91 - Conditional expressions 92 93|Item|Overview| 94|:---|:---| 95|DECODE|Compares values, and if they match, returns a corresponding value| 96|LNNVL|Evaluates if a value is false or unknown| 97|NANVL|Returns a substitute value when a value is not a number (NaN)| 98|NVL|Returns a substitute value when a value is NULL| 99|NVL2|Returns a substitute value based on whether a value is NULL or not NULL| 100 101 102 - Aggregate functions 103 104|Item|Overview| 105|:---|:---| 106|LISTAGG|Returns a concatenated, delimited list of string values| 107|MEDIAN|Calculates the median of a set of values| 108 109 - Functions that return internal information 110 111|Item|Overview| 112|:---|:---| 113|DUMP|Returns internal information of a value| 114 115 116 117**SQL Operators** 118 119|Item|Overview| 120|:---|:---| 121|Datetime operator|Datetime operator for the DATE type| 122 123 124**Packages** 125 126|Item|Overview| 127|:---|:---| 128|DBMS_ALERT|Sends alerts to multiple sessions| 129|DBMS_ASSERT|Validates the properties of an input value| 130|DBMS_OUTPUT|Sends messages to clients| 131|DBMS_PIPE|Creates a pipe for inter-session communication| 132|DBMS_RANDOM|Generates random numbers| 133|DBMS_UTILITY|Provides various utilities| 134|UTL_FILE|Enables text file operations| 135 136 137 138