F.38. orafce

F.38. orafce
Prev	Up	Appendix F. Additional Supplied Modules	Home	Next

F.38.1. Features Compatible with Oracle Databases
F.38.2. Notes on Using orafce

orafce - Oracle's compatibility functions and packages.

F.38.1. Features Compatible with Oracle Databases

Features compatible with Oracle databases are provided. These features enable you to easily migrate to LightDB and reduce the costs of reconfiguring applications. The table below lists features compatible with Oracle databases.

Table F.54. Data type

Item	Overview
VARCHAR2	Variable-length character data type
NVARCHAR2	Variable-length national character data type
DATE	Data type that stores date and time
NUMBER	NUMBER is an alias for NUMERIC
CLOB	stores single-byte and multibyte character data and has mostly same functionality with `text` data type.
BLOB	stores unstructured binary large objects and has mostly same functionality with 'bytea' data type.
RAW	Variable-length hex data type .
PLS_INTEGER	A PL/SQL data type used for storing signed integers.
BINARY_INTEGER	A data type used for storing signed integers. It is an alias for INT4.
BINARY_FLOAT	32-bit floating point number. This data type requires 4 bytes.
BINARY_DOUBLE	64-bit floating point number. This data type requires 8 bytes.
XMLType	Variable-length xml data type .
LONG	stores single-byte and multibyte character data and has mostly same functionality with `CLOB` data type.

varchar2 and nvarchar2 is config in priority between varchar and char, after create orafce extensin, the priority is :

numeric > double precision > real > bigint > integer > smallint > text > varchar > varchar2 > nvarchar2 > char

Table F.55. SQL Queries

Item	Overview
DUAL table	Table provided by the system

Table F.56. Mathematical Functions

Item	Overview	Parallel attribute
BIN_TO_NUM	Converts a bit vector to its equivalent number	Safe
BITAND	Performs a bitwise AND operation	Safe
BITOR	Performs a bitwise OR operation	Safe
BITXOR	Performs a bitwise XOR operation	Safe
COSH	Calculates the hyperbolic cosine of a number	Safe
MOD	Returns the remainder (modulus) of argument 1 divided by argument 2.	Safe
REMAINDER	Returns the remainder of n2 divided by n1	Safe
ROUND_TIES_TO_EVEN	Returns n rounded to integer places	Safe
SINH	Calculates the hyperbolic sine of a number	Safe
TANH	Calculates the hyperbolic tangent of a number	Safe
CEIL	Returns the smallest integer(type interval will extract the days to number first) that is greater than or equal to a number	Safe

Table F.57. String Functions

Item	Overview	Parallel attribute
INSTR	Returns the position of a substring in a string	Safe
INSTRB	Returns the position in the string that is the first byte of a specified occurrence of the substring	Safe
LENGTH	Returns the length of a string in number of characters	Safe
LENGTHB	Returns the length of a string in number of bytes	Safe
LPAD	Left-pads a string to a specified length with a sequence of characters	Safe
LTRIM	Removes the specified characters from the beginning of a string	Safe
NLSSORT	Returns a byte string used to sort strings in linguistic sort sequence based on locale	Safe
REGEXP_COUNT	searches a string for a regular expression, and returns a count of the matches	Safe
REGEXP_INSTR	returns the beginning or ending position within the string where the match for a pattern was located	Safe
REGEXP_LIKE	condition in the WHERE clause of a query, causing the query to return rows that match the given pattern	Safe
REGEXP_SUBSTR	returns the string that matches the pattern specified in the call to the function	Safe
REGEXP_REPLACE	returns the string that matches the pattern specified in the call to the function	Safe
REPLACE	REPLACE returns string with every occurrence of search_string replaced with replacement_string	Safe
RPAD	Right-pads a string to a specified length with a sequence of characters	Safe
RTRIM	Removes the specified characters from the end of a string	Safe
SOUNDEX	Returns a character string containing the phonetic representation of char.	Safe
SUBSTR	Extracts part of a string using characters to specify position and length	Safe
SUBSTRB	Extracts part of a string using bytes to specify position and length	Safe
GUID	Return UUID	Safe
CONCAT	concatenate two strings	Safe
UPPER	make all letters uppercase	Safe

Table F.58. Date/Time Functions

Item	Overview	Parallel attribute
ADD_MONTHS	Adds months to a date	Safe
DBTIMEZONE	Returns the value of the database time zone	Safe
LENGTHB	Returns the length of a string in number of bytes	Safe
LAST_DAY	Returns the last day of the month in which the specified date falls	Safe
LTRIM	Removes the specified characters from the beginning of a string	Safe
MONTHS_BETWEEN	Returns the number of months between two dates	Safe
NEXT_DAY	Returns the date of the first instance of a particular day of the week that follows the specified date	Safe
ROUND	Rounds a date	Safe
SESSIONTIMEZONE	Returns the time zone of the session	Safe
SYSDATE	Returns the system date	Safe
TRUNC	Truncates a date	Safe
TZ_OFFSET	Returns the time zone offset	Safe

Table F.59. Data Type Formatting Functions

Item	Overview	Parallel attribute
CONVERT	Converts a character string from one character set to another.	Safe
NUMTOYMINTERVAL	Converts number to an INTERVAL YEAR TO MONTH literal	Safe
TO_BLOB(raw)	Converts RAW values to BLOB values.	Safe
TO_CHAR	Converts a value to a string	Safe
TO_DATE	Converts a string to a date in accordance with the specified format	Safe
TO_MULTI_BYTE	Converts a single-byte string to a multibyte string	Safe
TO_NUMBER	Converts a value to a number in accordance with the specified format	Safe
TO_SINGLE_BYTE	Converts a multibyte string to a single-byte string	Safe
TO_CLOB	Converts a string or a number to clob

Table F.60. Conditional Expressions

Item	Overview	Parallel attribute
DECODE	Compares values, and if they match, returns a corresponding value	Safe
LNNVL	Evaluates if a value is false or unknown	Safe
NANVL	Returns a substitute value when a value is not a number (NaN)	Safe
NVL	Returns a substitute value when a value is NULL	Safe
NVL2	Returns a substitute value based on whether a value is NULL or not NULL	Safe

Table F.61. Aggregate Functions

Item	Overview	Parallel attribute
ANY_VALUE	Returns a single non-deterministic value of expr.	Unsafe
BIT_AND_AGG	Returns the result of a bitwise AND operation	Unsafe
BIT_OR_AGG	Returns the result of a bitwise OR operation	Unsafe
BIT_XOR_AGG	Returns the result of a bitwise XOR operation	Unsafe
KURTOSIS_POP	Used to determine the characteristics of outliers in a given distribution	Unsafe
KURTOSIS_SAMP	Used to determine the characteristics of outliers in a given distribution	Unsafe
LISTAGG	Returns a concatenated, delimited list of string values	Unsafe
MEDIAN	Calculates the median of a set of values	Unsafe
SKEWNESS_POP	Used to determine symmetry in a given distribution	Unsafe
SKEWNESS_SAMP	Used to determine symmetry in a given distribution	Unsafe
WY_CONCAT	Returns a concatenated, comma delimited list of string values	Unsafe

Table F.62. Functions that return internal information

Item	Overview	Parallel attribute
DUMP	Returns internal information of a value	Safe
NLS_CHARSET_ID	Returns the character set ID number corresponding to character set name string.	Safe
NLS_CHARSET_NAME	Returns the name of the character set corresponding to ID number number.	Safe
SYS_CONTEXT	Returns the value of parameter associated with the context namespace at the current instant	Safe
USERENV	Returns the value of parameter associated with the context 'USERENV' at the current session	Safe

Table F.63. SQL Operators

Item	Overview
Datetime operator	Datetime operator for the DATE type

Table F.64. Other functions

Item	Overview	Parallel attribute
EMPTY_CLOB	Return an empty CLOB	Safe
EMPTY_BLOB	Return an empty BLOB	Safe
ORA_HASH	ORA_HASH is a function that computes a hash value for a given expression	Safe
VSIZE	Returns the number of bytes in the internal representation of expr	Safe
DEPS_SAVE_AND_DROP_DEPENDENCIES	Save and drop dependencies for table	Unsafe
DEPS_RESTORE_DEPENDENCIES	Restore dependencies for table	Unsafe
PLVSTR.IS_PREFIX	Return true if `a` is the prefix of `b`.	Safe
TIMESTAMP_TO_SCN	Returns the transaction ID relative to a given timestamp.	safe

Table F.65. Packages

Item	Overview
DBMS_ALERT	Sends alerts to multiple sessions
DBMS_ASSERT	Validates the properties of an input value
DBMS_DATAPUMP	Provides a set of stored procedures and functions for moving data and metadata between databases
DBMS_LOB	Some functions compatible with DBMS_LOB package.
DBMS_OUTPUT	Sends messages to clients
DBMS_PIPE	Creates a pipe for inter-session communication
DBMS_RANDOM	Generates random numbers
DBMS_UTILITY	Provides various utilities
DBMS_JOB	Schedules and manages jobs in the lt_cron
DBMS_LOCK	Provides an interface to Lock Management services
DBMS_METADATA	Provides a way for you to retrieve metadata from the database dictionary
DBMS_OBFUSCATION_TOOLKIT	Enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms
DBMS_SNAPSHOT	Enables you to refresh snapshots(MVIEW)
DBMS_SQL	Provides a way to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL
DBMS_STATS	Enables you to collect optimizer statistics
UTL_FILE	Enables text file operations
UTL_RAW	Provides SQL functions for manipulating RAW datatypes
UTL_URL	Has two functions that provide escape and unescape mechanisms for URL characters
UTL_ENCODE	Provides functions that encode RAW data into a standard encoded format

Table F.66. System View

Item	Overview
DBA_SEQUENCES ALL_SEQUENCES USER_SEQUENCES	Describes sequences
DBA_SYNONYMS ALL_SYNONYMS USER_SYNONYMS	Describes the synonyms, empty now
DBA_TAB_COLS ALL_TAB_COLS USER_TAB_COLS COLS DBA_TAB_COLUMNS ALL_TAB_COLUMNS USER_TAB_COLUMNS	Describes the columns of tables, views
DBA_TAB_COL_STATISTICS ALL_TAB_COL_STATISTICS USER_TAB_COL_STATISTICS	Contains column statistics and histogram information extracted from "[DBA/ALL/USER]_TAB_COLUMNS"
DBA_OBJECTS ALL_OBJECTS USER_OBJECTS OBJ	Describes objects
DBA_CATALOG ALL_CATALOG USER_CATALOG	Lists tables, views, and sequences
DICTIONARY DICT	Contains descriptions of data dictionary tables and views.
DBA_DEPENDENCIES ALL_DEPENDENCIES USER_DEPENDENCIES	Describes dependencies between objects
DBA_SOURCE ALL_SOURCE USER_SOURCE	Describes the text source of stored objects
DBA_PROCEDURES ALL_PROCEDURES USER_PROCEDURES	Lists functions and procedures
DBA_TRIGGERS ALL_TRIGGERS USER_TRIGGERS	Describes triggers
DBA_TRIGGER_COLS ALL_TRIGGER_COLS USER_TRIGGER_COLS	Describes the use of columns in triggers
DBA_TYPES ALL_TYPES USER_TYPES	Describes object types
DBA_CONSTRAINTS ALL_CONSTRAINTS USER_CONSTRAINTS	Describes constraint definitions
DBA_CONS_COLUMNS ALL_CONS_COLUMNS USER_CONS_COLUMNS	Dscribes columns that are specified in constraints
DBA_VIEWS ALL_VIEWS USER_VIEWS	Describes views
DBA_TABLES ALL_TABLES USER_TABLES TABS DBA_ALL_TABLES ALL_ALL_TABLES USER_ALL_TABLES TAB	Describes object tables and relational tables
DBA_TAB_STATISTICS ALL_TAB_STATISTICS USER_TAB_STATISTICS	Displays optimizer statistics for tables
DBA_TAB_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS	Displays comments on tables and views
DBA_COL_COMMENTS ALL_COL_COMMENTS USER_COL_COMMENTS	Displays comments on the columns of tables and views
DBA_TAB_MODIFICATIONS ALL_TAB_MODIFICATIONS USER_TAB_MODIFICATIONS	Describes modifications to all tables
DBA_INDEXES ALL_INDEXES USER_INDEXES IND	Describes indexes
DBA_INDEX_USAGE ALL_INDEX_USAGE USER_INDEX_USAGE	Displays cumulative statistics for each index.
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS	Describes the columns of indexes on tables
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS USER_IND_EXPRESSIONS	Describes the expressions of function-based indexes
DBA_IND_STATISTICS ALL_IND_STATISTICS USER_IND_STATISTICS	Displays optimizer statistics for indexes
DBA_USERS ALL_USERS USER_USERS	Describes users
DBA_ROLES	Describes all roles in the database
DBA_ROLE_PRIVS USER_ROLE_PRIVS	Describes the roles granted to users and roles
PRODUCT_COMPONENT_VERSION	Contains version and status information for component products
PLAN_TABLE	PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users, empty now
DBA_DATA_FILES	Describes database files
DBA_JOBS ALL_JOBS USER_JOBS	Describes jobs
DBA_JOBS_RUNNING	Lists all jobs that are currently running in the instance
DBA_TABLESPACES USER_TABLESPACES	Describes tablespaces
NLS_DATABASE_PARAMETERS NLS_INSTANCE_PARAMETERS NLS_SESSION_PARAMETERS	Lists NLS parameters
DBA_SEGMENTS USER_SEGMENTS	Describes the storage allocated for segments
DBA_PART_TABLES ALL_PART_TABLES USER_PART_TABLES	Displays the object-level partitioning information for the partitioned tables
DBA_TAB_PARTITIONS ALL_TAB_PARTITIONS USER_TAB_PARTITIONS	Displays partition-level partitioning information, partition storage parameters, and partition statistics
DBA_TAB_SUBPARTITIONS ALL_TAB_SUBPARTITIONS USER_TAB_SUBPARTITIONS	Displays information for all subpartitions
DBA_PART_KEY_COLUMNS ALL_PART_KEY_COLUMNS USER_PART_KEY_COLUMNS	Describes the partitioning key columns for the partitioned objects
DBA_SUBPART_KEY_COLUMNS ALL_SUBPART_KEY_COLUMNS USER_SUBPART_KEY_COLUMNS	Displays subpartitioning key columns for composite-partitioned tables(and local indexes on composite-partitioned tables)
DBA_IND_PARTITIONS ALL_IND_PARTITIONS USER_IND_PARTITIONS	Describes index partitions
DBA_PART_INDEXES ALL_PART_INDEXES USER_PART_INDEXES	Displays the object-level partitioning information for the partitioned indexes
COL	Describes the columns of tables, views

Views starting with DBA show something in the database. Views starting with ALL is currently the same as Views starting with DBA, and no permission verification is done like oracle. now. Views starting with USER show something owned by the current user(actually is current schema. in oracle, User and schema are one-to-one correspondence).

Numeric implicit cast with Boolean

orafce supports type NUMERIC implicit cast to BOOL , and vice versa.

Example

CREATE TABLE test(v1 BOOLEAN, v2 NUMERIC);

-- numeric implicit cast to boolean
-- boolean implicit cast to numeric
INSERT INTO test(v1, v2) VALUES(1.1, true);
SELECT * FROM test WHERE v2 = true;

F.38.2. Notes on Using orafce

Orafce is defined as user-defined functions in the "public" schema created by default when database clusters are created, so they can be available for all users without the need for special settings. For this reason, ensure that "public" (without the double quotation marks) is included in the list of schema search paths specified in the search_path parameter.

The following features provided by orafce are implemented in LightDB and orafce using different external specifications. In the default configuration of LightDB, the standard features of LightDB take precedence.

Table F.67. Data type

Item	Standard feature of LightDB	Compatibility feature added by orafce
DATE	Stores date only.	Stores date and time.

Table F.68. Function

Item	Standard feature of LightDB	Compatibility feature added by orafce
LENGTH	If the string is CHAR type, trailing spaces are not included in the length.	If the string is CHAR type, trailing spaces are included in the length.
SUBSTR	If 0 or a negative value is specified for the start position, simply subtracting 1 from the start position, the position will be shifted to the left, from where extraction will start.	- If 0 is specified for the start position, extraction will start from the beginning of the string. - If a negative value is specified for the start position, extraction will start from the position counted from the end of the string.
LPAD RPAD	- If the string is CHAR type, trailing spaces are removed and then the value is padded. - The result length is handled as a number of characters.	- If the string is CHAR type, the value is padded without removing trailing spaces. - The result length is based on the width of the displayed string. Therefore, fullwidth characters are handled using a width of 2, and halfwidth characters are handled using a width of 1.
LTRIM RTRIM BTRIM (*1)	If the string is CHAR type, trailing spaces are removed and then the value is removed.	If the string is CHAR type, the value is removed without removing trailing spaces.
TO_DATE	The data type of the return value is DATE.	The data type of the return value is TIMESTAMP.

*1: BTRIM does not exist for Oracle databases, however, an external specification different to LightDB is implemented in orafce to align with the behavior of the TRIM functions.

Also, the following features cannot be used in the default configuration of LightDB.

Table F.69. Function

Feature
SYSDATE
DBTIMEZONE
SESSIONTIMEZONE
TO_CHAR (date/time value)

Table F.70. Operator

Feature
Datetime operator

To use these features, set "oracle" and "pg_catalog" in the "search_path" parameter of lightdb.conf. You must specify "oracle" before "pg_catalog" when doing this.

   search_path = '"$user", public, oracle, pg_catalog'

Information

The search_path parameter specifies the order in which schemas are searched. Each feature compatible with Oracle databases is defined in the oracle schema.
It is recommended to set search_path in lightdb.conf. In this case, it will be effective for each instance.
The configuration of search_path can be done at the user level or at the database level. Setting examples are shown below.
If the standard features of LightDB take precedence, and features that cannot be used with the default configuration of LightDB are not required, it is not necessary to change the settings of search_path.
Example of setting at the user level. This can be set by executing an SQL command. In this example, user1 is used as the username.
```
     ALTER USER user1 SET search_path = "$user",public,oracle,pg_catalog;
    
```
Example of setting at the database level. This can be set by executing an SQL command. In this example, db1 is used as the database name. You must specify "oracle" before "pg_catalog".
```
     ALTER DATABASE db1 SET search_path = "$user",public,oracle,pg_catalog;
    
```

See

Refer to "Server Administration" > "Client Connection Defaults" > "Statement Behavior" in the LightDB Documentation for information on search_path.
Refer to "Reference" > "SQL Commands" in the LightDB Documentation for information on ALTER USER and ALTER DATABASE.

Prev	Up	Next
F.37. oracle_fdw	Home	F.39. pageinspect