F.55. orafce

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

F.55.1. Features Compatible with Oracle Databases
F.55.2. Notes on Using orafce
F.55.3. Data Types
F.55.4. Queries
F.55.5. SQL Functions Reference
F.55.6. Package Reference
F.55.7. Transaction behavior
F.55.8. System View Reference

orafce - Oracle's compatibility functions and packages.

F.55.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.42. 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
DATETIME	DATETIME is an alias for timestamp without zone.
CLOB	CLOB is an alias for TEXT.
BLOB	BLOB is an alias for BYTEA.
RAW	Variable-length hex data type .
PLS_INTEGER	A PL/SQL data type used for storing signed integers.

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.43. SQL Queries

Item	Overview
DUAL table	Table provided by the system

Table F.44. Mathematical Functions

Item	Overview
BIN_TO_NUM	Converts a bit vector to its equivalent number
BITAND	Performs a bitwise AND operation
BITOR	Performs a bitwise OR operation
BITXOR	Performs a bitwise XOR operation
COSH	Calculates the hyperbolic cosine of a number
REMAINDER	Returns the remainder of n2 divided by n1
ROUND_TIES_TO_EVEN	Returns n rounded to integer places
SINH	Calculates the hyperbolic sine of a number
TANH	Calculates the hyperbolic tangent of a number

Table F.45. String Functions

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

Table F.46. Date/Time Functions

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

Table F.47. Data Type Formatting Functions

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

Table F.48. Conditional Expressions

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

Table F.49. Aggregate Functions

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

Table F.50. Functions that return internal information

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

Table F.51. SQL Operators

Item	Overview
Datetime operator	Datetime operator for the DATE type

Table F.52. Other functions

Item	Overview
EMPTY_CLOB	Return an empty CLOB(actually '')
ORA_HASH	ORA_HASH is a function that computes a hash value for a given expression
VSIZE	Returns the number of bytes in the internal representation of expr
VSIZE	Returns the number of bytes in the internal representation of expr
DEPS_SAVE_AND_DROP_DEPENDENCIES	Save and drop dependencies for table
DEPS_RESTORE_DEPENDENCIES	Restore dependencies for table

Table F.53. Packages

Item	Overview
DBMS_ALERT	Sends alerts to multiple sessions
DBMS_ASSERT	Validates the properties of an input value
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)
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.54. System View

Item	Overview
[DBA/ALL/USER]_SEQUENCES	Describes sequences
[DBA/ALL/USER]_SYNONYMS	Describes the synonyms, empty now
[DBA/ALL/USER]_TAB_COLS COLS [DBA/ALL/USER]_TAB_COLUMNS	Describes the columns of tables, views
[DBA/ALL/USER]_TAB_COL_STATISTICS	Contains column statistics and histogram information extracted from "[DBA/ALL/USER]_TAB_COLUMNS"
[DBA/ALL/USER]_OBJECTS OBJ	Describes objects
[DBA/ALL/USER]_CATALOG	Lists tables, views, and sequences
DICTIONARY/DICT	Contains descriptions of data dictionary tables and views.
[DBA/ALL/USER]_DEPENDENCIES	Describes dependencies between objects
[DBA/ALL/USER]_SOURCE	Describes the text source of stored objects
[DBA/ALL/USER]_PROCEDURES	Lists functions and procedures
[DBA/ALL/USER]_TRIGGERS	Describes triggers
[DBA/ALL/USER]_TRIGGER_COLS	Describes the use of columns in triggers
[DBA/ALL/USER]_TYPES	Describes object types
[DBA/ALL/USER]_CONSTRAINTS	Describes constraint definitions
[DBA/ALL/USER]_CONS_COLUMNS	Dscribes columns that are specified in constraints
[DBA/ALL/USER]_VIEWS	Describes views
[DBA/ALL/USER]_TABLES [DBA/ALL/USER]_ALL_TABLES TAB	Describes object tables and relational tables
[DBA/ALL/USER]_TAB_STATISTICS	Displays optimizer statistics for tables
[DBA/ALL/USER]_TAB_COMMENTS	Displays comments on tables and views
[DBA/ALL/USER]_COL_COMMENTS	Displays comments on the columns of tables and views
[DBA/ALL/USER]_TAB_MODIFICATIONS	Describes modifications to all tables
[DBA/ALL/USER]_INDEXES IND	Describes indexes
[DBA/ALL/USER]_INDEX_USAGE	Displays cumulative statistics for each index.
[DBA/ALL/USER]_IND_COLUMNS	Describes the columns of indexes on tables
[DBA/ALL/USER]_IND_EXPRESSIONS	Describes the expressions of function-based indexes
[DBA/ALL/USER]_IND_STATISTICS	Displays optimizer statistics for indexes
[DBA/ALL/USER]_USERS	Describes users
DBA_ROLES	Describes all roles in the database
[DBA/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/ALL/USER]_JOBS	Describes jobs
DBA_JOBS_RUNNING	Lists all jobs that are currently running in the instance
[DBA/ALL/USER]_MVIEWS	Describes materialized views
[DBA/ALL/USER]_MVIEW_LOGS	Describes all materialized view logs now it is empty
[DBA/ALL/USER]_MVIEW_COMMENTS	Displays comments on the materialized views
[DBA/USER]_TABLESPACES	Describes tablespaces
NLS_[DATABASE/INSTANCE/SESSION]_PARAMETERS	Lists NLS parameters
[DBA/USER]_SEGMENTS	Describes the storage allocated for segments
[DBA/USER/ALL]_PART_TABLES	Displays the object-level partitioning information for the partitioned tables
[DBA/USER/ALL]_TAB_PARTITIONS	Displays partition-level partitioning information, partition storage parameters, and partition statistics
[DBA/USER/ALL]_TAB_SUBPARTITIONS	Displays information for all subpartitions
[DBA/USER/ALL]_PART_KEY_COLUMNS	Describes the partitioning key columns for the partitioned objects
[DBA/USER/ALL]_SUBPART_KEY_COLUMNS	Displays subpartitioning key columns for composite-partitioned tables(and local indexes on composite-partitioned tables)
[DBA/USER/ALL]_IND_PARTITIONS	Describes index partitions
[DBA/USER/ALL]_PART_INDEXES	Displays the object-level partitioning information for the partitioned indexes

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).

F.55.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.55. Data type

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

Table F.56. 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.57. Function

Feature
SYSDATE
DBTIMEZONE
SESSIONTIMEZONE
TO_CHAR (date/time value)

Table F.58. 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.

F.55.3. Data Types

The following data types are supported:

VARCHAR2
NVARCHAR2
DATE
RAW
PLS_INTEGER

F.55.3.1. VARCHAR2

Syntax

Specify the VARCHAR2 type as follows.

Table F.59. VARCHAR2 Syntax

Data type syntax	Explanation
VARCHAR2(len)	String with a variable length up to len characters. For len, specify an integer greater than 0. If len is omitted, the string can be any length.

General rules

VARCHAR2 is a character data type. Specify the number of characters for the length.
Strings are of variable length. The specified value will be stored as is. The upper limit for this data type is approximately one gigabyte.

Note

The VARCHAR2 type does not support collating sequences. Therefore, the following error occurs when a collating sequence like that of an ORDER BY clause is required. At this time, the following HINT will prompt to use a COLLATE clause, however, because collating sequences are not supported, it is not possible to use this clause.

    ERROR:   could not determine which collation to use for string comparison
    HINT:   Use the COLLATE clause to set the collation explicitly.

If the error shown above is displayed, explicitly cast the column to VARCHAR or TEXT type.

F.55.3.2. NVARCHAR2

Syntax

Specify the NVARCHAR2 type as follows.

Table F.60. NVARCHAR2 Syntax

Data type syntax	Explanation
NVARCHAR2(len)	National character string with a variable length up to len characters. For len, specify an integer greater than 0. If len is omitted, the string can be any length.

General rules

NVARCHAR2 is a national character data type. Specify the number of characters for the length.
Strings are of variable length. The specified value will be stored as is. The upper limit for this data type is approximately one gigabyte.

Note

The NVARCHAR2 type does not support collating sequences. Therefore, the following error occurs when a collating sequence like that of an ORDER BY clause is required. At this time, the following HINT will prompt to use a COLLATE clause, however, because collating sequences are not supported, it is not possible to use this clause.

    ERROR:   could not determine which collation to use for string comparison
    HINT:   Use the COLLATE clause to set the collation explicitly.

If the error shown above is displayed, explicitly cast the column to NCHAR VARYING or TEXT type.

F.55.3.3. DATE

Syntax

Specify the DATE type as follows.

Table F.61. DATE Syntax

Data type syntax	Explanation
DATE	Stores date and time

General rules

DATE is a date/time data type.
Date and time are stored in DATE. The time zone is not stored.

Note

If the DATE type of orafce is used in DDL statements such as table definitions, always set search_path before executing a DDL statement. Even if search_path is changed after definition, the data type will be the DATE type of LightDB.

Information

The DATE type of orafce is equivalent to the TIMESTAMP type of LightDB. Therefore, of the existing functions of LightDB, functions for which the data type of the argument is TIMESTAMP can be used.

F.55.3.4. RAW

Syntax

Specify the RAW type as follows.

Table F.62. RAW Syntax

RAW type syntax	Explanation
RAW(len)	Store variable-length Binary data. External representation is hexadecimal. The len is only used to be compatible with oracle, it has no effect. the binary data can be any length.

General rules

RAW is a variable-length hex data type.
Hex strings are of variable length. The specified value will be stored as binary data. The upper limit for this data type is approximately one gigabyte.

F.55.3.5. PLS_INTEGER

Syntax

Specify the PLS_INTEGER type as follows.

Table F.63. PLS_INTEGER Syntax

PLS_INTEGER type syntax	Explanation
PLS_INTEGER	The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

General rules

PLS_INTEGER behaves like int4.

F.55.4. Queries

The following queries are supported:

DUAL Table

DUAL table is a virtual table provided by the system. Use when executing SQL where access to a base table is not required, such as when performing tests to get result expressions such as functions and operators.

Example

In the following example, the current system date is returned.

   SELECT  CURRENT_DATE  "date" FROM DUAL;
       date
   ------------
    2013-05-14
   (1 row)

F.55.5. SQL Functions Reference

F.55.5.1. Mathematical Functions

The following mathematical functions are supported:

BIN_TO_NUM
BITAND
BITOR
BITXOR
COSH
REMAINDER
ROUND_TIES_TO_EVEN
SINH
TANH

F.55.5.1.1. BIN_TO_NUM

Description

BIN_TO_NUM converts a bit vector to its equivalent number.

Syntax

     BIN_TO_NUM(VARIADIC integer []) returns int

General rules

Each argument to this function represents a bit in the bit vector.
This function takes as arguments any integer data type, or any noninteger data type that can be implicitly converted to integer.
BIN_TO_NUM is useful in data warehousing applications for selecting groups of interest from a materialized view using grouping sets.

Note

If using BIN_TO_NUM for converting, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
 bin_to_num 
------------
         10
(1 row)

F.55.5.1.2. BITAND

Description

Performs a bitwise AND operation.

Syntax

     BITAND(int1 integers, int2 integers) returns bigint

General rules

BITAND performs an AND operation on each bit of two integers, and returns the result.
Specify integer type values.
The data type of the return value is BIGINT.

Example

In the following example, the result of the AND operation on numeric literals 5 and 3 is returned.

    SELECT BITAND(5,3) FROM DUAL;
     bitand
    -------
          1
    (1 row)

F.55.5.1.3. BITOR

Description

Performs a bitwise OR operation.

Syntax

     BITOR(int1 bigint, int2 bigint) returns bigint

General rules

BITOR performs an OR operation on each bit of two bigint, and returns the result.
Specify bigint type values.
The data type of the return value is BIGINT.

Note

If using BITOR, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

In the following example, the result of the OR operation on numeric literals 5 and 3 is returned.

SELECT BITOR(5,3) FROM DUAL;
 bitor 
-------
     7
(1 row)

F.55.5.1.4. BITXOR

Description

Performs a bitwise XOR operation.

Syntax

     BITXOR(int1 bigint, int2 bigint) returns bigint

General rules

BITXOR performs an XOR operation on each bit of two integers, and returns the result.
Specify bigint type values.
The data type of the return value is BIGINT.

Note

If using BITXOR, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

In the following example, the result of the XOR operation on numeric literals 5 and 3 is returned.

SELECT BITXOR(5,3) FROM DUAL;
 bitxor 
--------
      6
(1 row

F.55.5.1.5. COSH

Description

Calculates the hyperbolic cosine of a number.

Syntax

     COSH(num numeric) returns double precision

General rules

COSH returns the hyperbolic cosine of the specified number.
The number must be a numeric data type.
The data type of the return value is DOUBLE PRECISION.

Example

In the following example, the hyperbolic cosine of the numeric literal 2.236 is returned.

    SELECT COSH(2.236) FROM DUAL;
          cosh
    -----------------
    4.7313591000247
    (1 row)

F.55.5.1.6. REMAINDER

Description

Returns the remainder of n2 divided by n1.

Syntax

     REMAINDER(n2 numeric, n1 numeric) returns numeric

General rules

This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.
If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1. If n2/n1 equals x.5, then N is the nearest even integer.

Note

If using REMAINDER, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT REMAINDER(5.5,2) FROM DUAL;
 remainder 
-----------
      -0.5
(1 row)

F.55.5.1.7. ROUND_TIES_TO_EVEN

Description

The function returns n rounded to integer places.

Syntax

     ROUND_TIES_TO_EVEN(n NUMERIC,integer int4 DEFAULT 0) returns numeric

General rules

If integer is positive, n is rounded to integer places to the right of the decimal point.
If integer is not specified, then n is rounded to 0 places.
If integer is negative, then n is rounded to integer places to the left of the decimal point.

Note

If using ROUND_TIES_TO_EVEN, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

The following example rounds a number to one decimal point to the right:

SELECT ROUND_TIES_TO_EVEN(45.177,1) "ROUND_EVEN" FROM DUAL;
 ROUND_EVEN 
------------
       45.2
(1 row)

The following example rounds a number to one decimal point to the left:

SELECT ROUND_TIES_TO_EVEN(45.177,-1) "ROUND_EVEN" FROM DUAL;
 ROUND_EVEN 
------------
         50
(1 row)

F.55.5.1.8. SINH

Description

Calculates the hyperbolic sine of a number.

Syntax

     SINH(num numeric) returns double precision

General rules

SINH returns the hyperbolic sine of the specified number.
The number must be a numeric data type.
The data type of the return value is DOUBLE PRECISION.

Example

In the following example, the hyperbolic sine of the numeric literal 1.414 is returned.

    SELECT SINH(1.414) FROM DUAL;
          sinh
    -----------------
    1.93460168824956
    (1 row)

F.55.5.1.9. TANH

Description

Calculates the hyperbolic tangent of a number.

Syntax

     TANH(num numeric) returns double precision

General rules

TANH returns the hyperbolic tangent of the specified number.
The number must be a numeric data type.
The data type of the return value is DOUBLE PRECISION.

Example

In the following example, the hyperbolic tangent of the numeric literal 3 is returned.

    SELECT TANH(3) FROM DUAL;
          tanh
    -----------------
    0.995054753686731
    (1 row)

F.55.5.2. String Functions

The following string functions are supported:

BTRIM
INSTR
INSTRB
LENGTH
LENGTHB
LPAD
LTRIM
NLSSORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTRB

F.55.5.2.1. BTRIM

Description

Removes the specified characters from the beginning and end of a string.

Syntax

     BTRIM(str text) returns text
     BTRIM(str text, trimChars text) returns text

General rules

BTRIM returns a string with trimChars removed from the beginning and end of string str.
If multiple trim characters are specified, all characters matching the trim characters are removed. If trimChars is omitted, all leading and trailing halfwidth spaces are removed.
The data type of the return value is TEXT.

Note

BTRIM does not exist for Oracle databases.
The CHAR type specification for BTRIM uses orafce for its behavior, which is different to that of BTRIM of LightDB. The search_path parameter must be modified for it to behave the same as the specification described above.

Information

The general rule for BTRIM of LightDB is as follows:

If the string is CHAR type, trailing spaces are removed and then the trim characters are removed.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on BTRIM.

Example

In the following example, a string that has had "a" removed from both ends of "aabcaba" is returned.

    SELECT BTRIM('aabcaba','a') FROM DUAL;
     btrim
    -------
     bcab
    (1 row)

F.55.5.2.2. INSTR

Description

Returns the position of a substring in a string.

Syntax

     INSTR(str1 text, str2 text) returns integer
     INSTR(str1 text, str2 text, startPos integer) returns integer
     INSTR(str1 text, str2 text, startPos integer, occurrences integer) returns integer

General rules

INSTR searches for substring str2 in string str1 and returns the position (in characters) in str1 of the first character of the occurrence.
The search starts from the specified start position startPos in str1.
When startPos is 0 or negative, the start position will be the specified number of characters from the left of the end of str1, and INSTR will search backward from that point.
If the start position is not specified, the search will be performed from the beginning of str1.
If occurrences is specified, the position in str1 of the nth occurrence of str2 is returned. Only positive numbers can be specified.
If occurrences is not specified, the start position of the first occurrence that is found is returned.
If str2 is not found in str1, 0 is returned.
For startPos and occurrences, specify a SMALLINT or INTEGER type.
The data type of the return value is INTEGER.

Example

In the following example, characters "BC" are found in string "ABCACBCAAC", and the position of those characters is returned.

    SELECT INSTR('ABCACBCAAC','BC') FROM DUAL;
     instr
    -------
         2
    (1 row)

    SELECT INSTR('ABCACBCAAC','BC',-1,2) FROM DUAL;
     instr
    -------
         2
    (1 row)

F.55.5.2.3. INSTRB

Description

The INSTRB function searches a string for a substring using bytes and returns the position in the string that is the first byte of a specified occurrence of the substring.

Syntax

     INSTRB( p_source text, p_search text, p_position int4  default 1, p_occurrence  int4  default 1) RETURNS integer

General rules

INSTRB searches for substring p_search in string p_source and returns the position (in bytes) in p_source of the first byte of the occurrence.
The search starts from the specified start position p_position in p_source.
When p_position is 0 or negative, it will raise an error. It is different from ORACLE, in oracle , p_position will be the specified number of bytes from the left of the end of p_source, and INSTRB will search backward from that point.
If the start position is not specified, the search will be performed from the beginning of p_source.
If p_occurrence is specified, the position in p_source of the nth occurrence of p_search is returned. Only positive numbers can be specified.
If occurrences is not specified, the start position of the first occurrence that is found is returned.
If p_search is not found in p_source, 0 is returned.
The data type of the return value is integer.

Note

If using INSTRB, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

In the following example, characters "on" are found in string "hhh测试onhx", and the position of those bytes is returned.

SELECT INSTRB('hhh测试onhx', 'on') FROM DUAL;
 instrb 
--------
     10
(1 row)

SELECT INSTRB('hhh测试onhx on xxxx', 'on', 7, 2) FROM DUAL;
 instrb 
--------
     15
(1 row)

F.55.5.2.4. LENGTH

Description

Returns the length of a string in number of characters.

Syntax

     LENGTH(str text) returns integer

General rules

LENGTH returns the number of characters in string str.
If the string is CHAR type, trailing spaces are included in the length.
The data type of the return value is INTEGER.

Note

The LENGTH specification above uses orafce for its behavior, which is different to that of LENGTH of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.

Information

The general rule for LENGTH of LightDB is as follows:

If the string is CHAR type, trailing spaces are not included in the length.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on LENGTH.

Example

In the following example, the number of characters in column col2 (defined using CHAR(10)) in table t1 is returned.

    SELECT col2,LENGTH(col2) FROM t1 WHERE col1 = '1001';
        col2    | length
    ------------+--------
     AAAAA      |     10
    (1 row)

F.55.5.2.5. LENGTHB

Description

Returns the length of a string in number of bytes.

Syntax

     LENGTHB(str text) returns integer

General rules

LENGTHB returns the number of bytes in string str.
If the string is CHAR type, trailing spaces are included in the length.
The data type of the return value is INTEGER.

Example

In the following example, the number of bytes in column col2 (defined using CHAR(10)) in table t1 is returned. Note that, in the second SELECT statement, each character in "*" has a length of 3 bytes, for a total of 9 bytes, and 7 bytes are added for the 7 trailing spaces. This gives a result of 16 bytes.

    SELECT col2,LENGTHB(col2) FROM t1 WHERE col1 = '1001';
         col2      | lengthb
    ---------------+---------
     AAAAA         |      10
    (1 row)

    SELECT col2,LENGTHB(col2) FROM t1 WHERE col1 = '1004';
         col2      | lengthb
    ---------------+---------
     ***           |      16
    (1 row)

F.55.5.2.6. LPAD

Description

Left-pads a string to a specified length with a sequence of characters.

Syntax

     LPAD(str text, len integer) returns text
     LPAD(str text, len integer, paddingStr text) returns text

General rules

LPAD returns the result after repeatedly padding the beginning of string str with padding characters paddingStr until the string reaches length len.
If the string is CHAR type, the padding characters are added to the string without removing trailing spaces.
In the resultant string, fullwidth characters are recognized as having a length of 2, and halfwidth characters having a length of 1. If a fullwidth character cannot be included in the resultant string because there is only space available for one halfwidth character, the string is padded with a single-byte space.
The data type of the return value is TEXT.

Note

The LPAD specification above uses orafce for its behavior, which is different to that of LPAD of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.

Information

The general rules for LPAD of LightDB are as follows:

If the string is CHAR type, trailing spaces are removed and then the padding characters are added to the string.
The result length is the number of characters.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on LPAD.

Example

In the following example, a 10-character string that has been formed by left-padding the string "abc" with "a" is returned.

    SELECT LPAD('abc',10,'a') FROM DUAL;
        lpad
    ------------
     aaaaaaaabc
    (1 row)

F.55.5.2.7. LTRIM

Description

Removes the specified characters from the beginning of a string.

Syntax

     LTRIM(str text) returns text
     LTRIM(str text, trimChars text) returns text

General rules

LTRIM returns a string with trimChars removed from the beginning of string str.
If multiple trim characters are specified, all characters matching the trim characters are removed. If trimChars is omitted, all leading halfwidth spaces are removed.
The data type of the return value is TEXT.

Note

The LTRIM specification above uses orafce for its behavior, which is different to that of LTRIM of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.

Information

The general rules for LTRIM of LightDB are as follows:

If the string is CHAR type, trailing spaces are removed and then the trim characters are removed.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on LTRIM.

Example

In the following example, a string that has had "ab" removed from the beginning of "aabcab" is returned.

    SELECT LTRIM('aabcab','ab') FROM DUAL;
     ltrim
    -------
     cab
    (1 row)

F.55.5.2.8. NLSSORT

Description

Returns a byte string that denotes the lexical order of the locale (COLLATE).

Syntax

     NLSSORT(str text) returns bytea
     NLSSORT(str text, locale text) returns bytea

General rules

NLSSORT is used for comparing and sorting in the collating sequence of a locale (COLLATE) that differs from the default locale.
Values that can be specified for the locale differ according to the operating system of the database server.
If the locale is omitted, it is necessary to use set_nls_sort to set the locale in advance. To set the locale using set_nls_sort, execute a SELECT statement.
The data type of the return value is BYTEA.

Example of setting set_nls_sort using a SELECT statement

     SELECT set_nls_sort('en_US.UTF8');

Note

If specifying locale encoding, ensure it matches the database encoding.

See

Refer to "Server Administration" > "Localization" > "Locale Support" in the LightDB Documentation for information on the locales that can be specified.

Example

Table F.64. Composition of table (t3)

col1	col2
1001	aabcababc
2001	abcdef
3001	aacbaab

In the following example, the result of sorting column col2 in table t3 by "da_DK.UTF8" is returned.

    SELECT col1,col2 FROM t3 ORDER BY NLSSORT(col2,'da_DK.UTF8');
     col1 |    col2
    ------+------------
     2001 | abcdef
     1001 | aabcababc
     3001 | aacbaab
    (3 row)

F.55.5.2.9. REGEXP_COUNT

Description

Searches a string for a regular expression, and returns a count of the matches.

Syntax

     REGEXP_COUNT(string text, pattern text) returns integer
     REGEXP_COUNT(string text, pattern text, startPos integer) returns integer
     REGEXP_COUNT(string text, pattern text, startPos integer, flags text) returns integer

General rules

REGEXP_COUNT returns the number of times pattern occurs in a source string. It returns an integer indicating the number of occurrences of pattern. If no match is found, then the function returns 0.
The search starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
flags is a character expression that lets you change the default matching behavior of the function.

The value of flags can include one or more of the following characters:

'i': case-insensitive matching.
'c': case-sensitive and accent-sensitive matching.
'n': the period (.) match the newline character. By default the period does not match the newline character.
'm': treats the source string as multiple lines.
'x': ignores whitespace characters. By default, whitespace characters match themselves.

If you omit flags then:

The default is case and accent sensitivity.
A period (.) does not match the newline character.
The source string is treated as a single line.

Note

If using the REGEXP_COUNT function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

    SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d') FROM DUAL;
     regexp_count 
    --------------
                0
    (1 row)

    SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'm') FROM DUAL;
     regexp_count 
    --------------
                0
    (1 row)

    SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'n') FROM DUAL;
     regexp_count 
    --------------
                1
    (1 row)

    SELECT REGEXP_COUNT('a'||CHR(10)||'d', '^d$', 1, 'm') FROM DUAL;
     regexp_count 
    --------------
                1
    (1 row)

F.55.5.2.10. REGEXP_INSTR

Description

Returns the beginning or ending position within the string where the match for a pattern was located.

Syntax

     REGEXP_INSTR(string text, pattern text) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text) returns integer
     REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text, group integer) returns integer

General rules

REGEXP_INSTR returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_opt argument. If no match is found, then the function returns 0.
The search starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
occurrence is a positive integer indicating which occurrence of pattern in string should be search for. The default is 1, meaning the first occurrence of pattern in string.
return_opt lets you specify what should be returned in relation to the occurrence:
- return_opt = 0, the position of the first character of the occurrence is returned. This is the default.
- return_opt = 1, the position of the character following the occurrence is returned.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.
For a pattern with capture group, group is a positive integer indicating which capture group in pattern shall be returned by the function. Capture groups can be nested, they are numbered in order in which their left parentheses appear in pattern. If group is zero, then the position of the entire substring that matches the pattern is returned. If group value exceed the number of capture groups in pattern, the function returns zero. A null group value returns NULL. The default value for group is zero.

Note

If using the REGEXP_INSTR function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

    SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))') FROM DUAL;
     regexp_instr 
    --------------
                1
    (1 row)

    SELECT REGEXP_INSTR('1234567890', '(4(56)(78))', 3) FROM DUAL;
     regexp_instr 
    --------------
                4
    (1 row)

    SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6) FROM DUAL;
     regexp_instr 
    --------------
               37

    (1 row)

    SELECT REGEXP_INSTR('199 Oretax Prayers, Riffles Stream, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 1) FROM DUAL;
     regexp_instr 
    --------------
               28
    (1 row)

F.55.5.2.11. REGEXP_LIKE

Description

Condition in the WHERE clause of a query, causing the query to return rows that match the given pattern.

Syntax

     REGEXP_LIKE(string text, pattern text) returns boolean
     REGEXP_LIKE(string text, pattern text, flags text) returns boolean

General rules

REGEXP_LIKE is similar to the LIKE condition, except it performs regular expression matching instead of the simple pattern matching performed by LIKE.
Returns a boolean, true when pattern match in string, false otherwise.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.

Note

If using the REGEXP_LIKE function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

    SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'm') FROM DUAL;
     regexp_like 
    -------------
     f
    (1 row)

    SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'n') FROM DUAL;
     regexp_like 
    -------------
     t
    (1 row)

F.55.5.2.12. REGEXP_SUBSTR

Description

Returns the string that matches the pattern specified in the call to the function.

Syntax

     REGEXP_SUBSTR(string text, pattern text) returns text
     REGEXP_SUBSTR(string text, pattern text, startPos integer) returns text
     REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence integer) returns text
     REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence integer, flags text) returns text
     REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence integer, flags text, group int) returns text

General rules

REGEXP_SUBSTR returns the matched substring resulting from matching a POSIX regular expression pattern to a string. If no match is found, then the function returns NULL.
The search starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
occurrence is a positive integer indicating which occurrence of pattern in string should be search for. The default is 1, meaning the first occurrence of pattern in string.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.
For a pattern with capture group, *group* is a positive integer indicating which capture group in pattern shall be returned by the function. Capture groups can be nested, they are numbered in order in which their left parentheses appear in pattern. If group is zero, then the position of the entire substring that matches the pattern is returned. If group value exceed the number of capture groups in pattern, the function returns NULL. A null group value returns NULL. The default value for group is zero.

Note

If using the REGEXP_SUBSTR function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

    SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+') FROM DUAL;
     regexp_substr  
    ----------------
     , zipcode town
    (1 row)

    SELECT regexp_substr('number of your street, zipcode town, FR', ',[^,]+', 24) FROM DUAL;
     regexp_substr 
    ---------------
     , FR
    (1 row)

    SELECT regexp_substr('number of your street, zipcode town, FR', ',[^,]+', 1, 2) FROM DUAL;
     regexp_substr 
    ---------------
     , FR
    (1 row)

    SELECT regexp_substr('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) FROM DUAL;
     regexp_substr 
    ---------------
     12345678
    (1 row)

F.55.5.2.13. REGEXP_REPLACE

Description

Returns the string that matches the pattern specified in the call to the function.

Syntax

     REGEXP_REPLACE(string text, pattern text, replace_string text, startPos integer,
     occurrence integer, flags text) returns text

General rules

REGEXP_REPLACE returns a modified version of the source string where occurrences of a POSIX regular expression pattern found in the source string are replaced with the specified replacement string. If no match is found or the occurrence queried exceed the number of match, then the source string untouched is returned.
The search and replacement starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
occurrence is a positive integer indicating which occurrence of pattern in string should be search for and replaced. The default is 0, meaning all occurrences of pattern in string.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.

Note

If using the REGEXP_REPLACE function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

    SELECT regexp_replace('512.123.4567 612.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') FROM DUAL;
            regexp_replace
    -------------------------------
     (512) 123-4567 (612) 123-4567
    (1 row)

    SELECT oracle.REGEXP_REPLACE('number   your     street,    zipcode  town, FR', '( ){2,}', ' ', 9);
                 regexp_replace             
    ----------------------------------------
     number   your street, zipcode town, FR
    (1 row)

    SELECT oracle.REGEXP_REPLACE('number   your     street,    zipcode  town, FR', '( ){2,}', ' ', 9, 2);
                   regexp_replace                
    ---------------------------------------------
     number   your     street, zipcode  town, FR
    (1 row)

F.55.5.2.14. REPLACE

Description

Returns string with every occurrence of search_string replaced with replacement_string.

Syntax

     REPLACE(string text, pattern text) returns text
     REPLACE(string text, pattern text, replace_string text) returns text
     REPLACE(string uuid, pattern text, replace_string text) returns text

General rules

If replace_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then string is returned.

Note

The above REPLACE specification uses orafce for its behavior, which is different to that of REPLACE of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.

Information

The general rules for REPLACE of LightDB are as follows:

If replace_string or search_string is null, then null is returned.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

SELECT REPLACE('abcdAbcdasd', 'a') FROM DUAL;
  replace  
-----------
 bcdAbcdsd
(1 row)

SELECT REPLACE('abcdAbcdasd', 'a','c') FROM DUAL;
   replace   
-------------
 cbcdAbcdcsd
(1 row)

SELECT REPLACE('abcdAbcdasd', NULL,'c') FROM DUAL;
   replace   
-------------
 abcdAbcdasd
(1 row)

SELECT REPLACE('abcdAbcdasd', 'a',NULL) FROM DUAL;
  replace  
-----------
 bcdAbcdsd
(1 row)

F.55.5.2.15. RPAD

Description

Right-pads a string to a specified length with a sequence of characters.

Syntax

     RPAD(str text, len integer) returns text
     RPAD(str text, len integer, paddingStr text) returns text

General rules

RPAD returns the result after repeatedly padding the end of string str with padding characters paddingStr until the string reaches length len.
If the string is CHAR type, the padding characters are added to the string without removing trailing spaces.
In the resultant string, fullwidth characters are recognized as having a length of 2, and halfwidth characters having a length of 1. If a fullwidth character cannot be included in the resultant string because there is only space available for one halfwidth character, the string is padded with a single-byte space.
The data type of the return value is TEXT.

Note

The RPAD specification above uses orafce for its behavior, which is different to that of RPAD of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.

Information

The general rules for RPAD of LightDB are as follows:

If the string is CHAR type, trailing spaces are removed and then the padding characters are added to the string.
The result length is the number of characters.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on RPAD.

Example

In the following example, a 10-character string that has been formed by right-padding the string "abc" with "a" is returned.

    SELECT RPAD('abc',10,'a') FROM DUAL;
        rpad
    ------------
     abcaaaaaaa
    (1 row)

F.55.5.2.16. RTRIM

Description

Removes the specified characters from the end of a string.

Syntax

     RTRIM(str text) returns text
     RTRIM(str text, trimChars text) returns text

General rules

RTRIM returns a string with trimChars removed from the end of string str.
If multiple trim characters are specified, all characters matching the trim characters are removed. If trimChars is omitted, all trailing halfwidth spaces are removed.
The data type of the return value is TEXT.

Note

The RTRIM specification above uses orafce for its behavior, which is different to that of RTRIM of LightDB. The search_path parameter must be modified for it to behave the same as the orafce specification.

Information

The general rules for RTRIM of LightDB are as follows:

If the string is CHAR type, trailing spaces are removed and then the trim characters are removed.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on RTRIM.

Example

In the following example, a string that has had "ab" removed from the end of "aabcab" is returned.

    SELECT RTRIM('aabcab','ab') FROM DUAL;
     rtrim
    -------
     aabc
    (1 row)

F.55.5.2.17. SOUNDEX

Description

SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.

Syntax

     SOUNDEX(str text) returns text

General rules

The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.

Assign numbers to the remaining letters as follows:

b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6

If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then retain the first letter and omit rest of all the adjacent letters with same number.
Return the first four bytes padded with 0.

Note

If using the SOUNDEX function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

In the following example, a string that has had "ab" removed from the end of "aabcab" is returned.

SELECT SOUNDEX('SMYTHE')=SOUNDEX('Smith') as same FROM DUAL;
 same 
------
 t
(1 row)

F.55.5.2.18. SUBSTR

Description

Extracts part of a string using characters to specify position and length.

Syntax

     SUBSTR(str text, startPos integer) returns text
     SUBSTR(str text, startPos integer, len integer) returns text

General rules

SUBSTR extracts and returns a substring of string str, beginning at position startPos, for number of characters len.
When startPos is positive, it will be the number of characters from the beginning of the string.
When startPos is 0, it will be treated as 1.
When startPos is negative, it will be the number of characters from the end of the string.
When len is not specified, all characters to the end of the string are returned. NULL is returned when len is less than 1.
For startPos and len, specify an integer or NUMERIC type. If numbers including decimal places are specified, they are truncated to integers.
The data type of the return value is TEXT.

Note

There are two types of SUBSTR. One that behaves as described above and one that behaves the same as SUBSTRING. The search_path parameter must be modified for it to behave the same as the specification described above.
If the change has not been implemented, SUBSTR is the same as SUBSTRING.

Information

The general rules for SUBSTRING of LightDB are as follows:

The start position will be from the beginning of the string, whether the start position is positive, 0, or negative.
When len is not specified, all characters to the end of the string are returned.
An empty string is returned if no string is extracted or len is less than 1.

See

Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on SUBSTRING.

Example

In the following example, part of the string "ABCDEFG" is extracted.

    SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
     Substring
    -----------
     CDEF
    (1 row)

    SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;
     Substring
    -----------
     CDEF
    (1 row)

F.55.5.2.19. SUBSTRB

Description

Extracts part of a string using bytes to specify position and length.

Syntax

     SUBSTRB(str text, startPos integer) returns varchar2
     SUBSTRB(str text, startPos integer, len integer) returns varchar2

General rules

SUBSTR extracts and returns a substring of string str, beginning at byte position startPos, for number of byte len.
When startPos is 0 or negative, extraction starts at the position found by subtracting 1 from the start position and shifting by that number of positions to the left.
When len is not specified, all bytes to the end of the string are returned.
An empty string is returned if no string is extracted or len is less than 1.
For startPos and len, specify a SMALLINT or INTEGER type.
The data type of the return value is VARCHAR2.

Note

The external specification of SUBSTRB is different to that of SUBSTR added by orafce, conforming with SUBSTRING of LightDB.

Example

In the following example, part of the string "aaabbbccc" is extracted.

    SELECT SUBSTRB('aaabbbccc',4,3) FROM DUAL;
     substrb
    -----------
     bbb
    (1 row)

    SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;
     substrb
    -----------
     aaa
    (1 row)

F.55.5.3. Date/Time Functions

The following date/time functions are supported:

ADD_MONTHS
DBTIMEZONE
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
SESSIONTIMEZONE
SYSDATE
TRUNC
TZ_OFFSET

Note

If the DATE type only is shown in the date/time functions, these functions can be used in both orafce and LightDB.

F.55.5.3.1. ADD_MONTHS

Description

Adds months to a date.

Syntax

     ADD_MONTHS(date DATE, months INTEGER) returns DATE
     ADD_MONTHS(date TIMESTAMP WITH TIME ZONE, months INTEGER) returns TIMESTAMP

General rules

ADD_MONTHS returns date plus months.
For date, specify a DATE or TIMESTAMP WITH TIME ZONE type.
For months, specify a SMALLINT or INTEGER type.
If a negative value is specified for months, the number of months is subtracted from the date.
The data type of the return value is DATE or TIMESTAMP.

Note

If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

The example below shows the result of adding 3 months to the date May 1, 2016.

    SELECT ADD_MONTHS(DATE'2016/05/01',3) FROM DUAL;
         add_months
    ---------------------
     2016-08-01 00:00:00
    (1 row)

F.55.5.3.2. DBTIMEZONE

Description

Returns the value of the database time zone.

Syntax

     DBTIMEZONE() returns text

General rules

DBTIMEZONE returns the time zone value of the database.
The data type of the return value is TEXT.

Note

If using DBTIMEZONE, it is necessary to specify "oracle" for search_path in advance.
The time zone of the database is set to "GMT" by default. To change the time zone, change the "orafce.timezone" parameter. An example using the SET statement is shown below.
```
       SET orafce.timezone = 'Japan';
      
```
The orafce.timezone settings can be set using any of the methods for setting server parameters.
If the SQL statement is executed with orafce.timezone set, the following message may be displayed, however, the parameter settings are enabled, so you can ignore this.
```
       WARNING:   unrecognized configuration parameter "orafce.timezone"
      
```
The time zones that can be set in "orafce.timezone" are the same as for the "TimeZone" server parameter.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Data Types" > "Date/Time Types" in the LightDB Documentation for information on the time zone.

Example

In the following example, the DBTIMEZONE result is returned.

     SELECT DBTIMEZONE() FROM DUAL;
      dbtimezone
     ------------
      GMT
     (1 row)

F.55.5.3.3. LAST_DAY

Description

Returns the last day of the month in which the specified date falls.

Syntax

     LAST_DAY(date DATE) returns DATE
     LAST_DAY(date TIMESTAMPTZ) returns TIMESTAMPTZ

General rules

LAST_DAY returns the last day of the month in which the specified date falls.
For date, specify a DATE or TIMESTAMPTZ type.
The data type of the return value is DATE or TIMESTAMPTZ.

Note

If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

In the example below, the last date of "February 01, 2016" is returned.

    SELECT LAST_DAY(DATE'2016/02/01') FROM DUAL;
         last_day
    ---------------------
     2016-02-29 00:00:00
    (1 row)

F.55.5.3.4. MONTHS_BETWEEN

Description

Returns the number of months between two dates.

Syntax

     MONTHS_BETWEEN(date1 DATE, date2 DATE) returns NUMERIC
     MONTHS_BETWEEN(date1 TIMESTAMP WITH TIME ZONE, date2 TIMESTAMP WITH TIME ZONE) returns NUMERIC

General rules

MONTHS_BETWEEN returns the difference in the number of months between date1 and date2.
For date1 and date2, specify a DATE or TIMESTAMPTZ type.
If date2 is earlier than date1, the return value will be negative.
If two dates fall on the same day, or each of the two dates are the last day of the month to which they belong, an integer is returned. If the days are different, one month is considered to be 31 days, and a value with the difference in the number of days divided by 31 added is returned.
The data type of the return value is DATE or NUMERIC.

Note

If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

In the following example, the difference between the months of March 15, 2016 and November 15, 2015 is returned.

     SELECT MONTHS_BETWEEN(DATE'2016/03/15', DATE'2015/11/15') FROM DUAL;
      months_between
     ----------------
                    4
     (1 row)

F.55.5.3.5. NEXT_DAY

Description

Returns the date of the first instance of a particular day of the week that follows the specified date.

Syntax

     NEXT_DAY(date DATE, dayOfWk TEXT) returns DATE
     NEXT_DAY(date DATE, dayOfWk INTEGER) returns DATE
     NEXT_DAY(date TIMESTAMP WITH TIME ZONE, dayOfWk TEXT) returns TIMESTAMP
     NEXT_DAY(date TIMESTAMP WITH TIME ZONE, dayOfWk INTEGER) returns TIMESTAMP

General rules

NEXT_DAY returns the date matching the first instance of dayOfWk that follows date.
For date, specify a DATE or TIMESTAMPTZ WITH TIME ZONE type.
Specify a numeric value or string indicating the day of the week.

Values that can be specified for the day

Table F.65. Setting dayOfWk for NEXT_DAY Function

Setting example	Overview
1	1 (Sunday) to 7 (Saturday) can be specified
'Sun', or 'Sunday'	English display of the day
'*'	Japanese display of the day

The data type of the return value is DATE or TIMESTAMP.

Note

If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.
The ability to use Japanese for entering days is provided by the orafce proprietary specification. Japanese cannot be used for entering days when using date/time functions other than NEXT_DAY (such as TO_DATE).

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

In the example below, the date of the first Friday on or after "May 1, 2016" is returned.

    SELECT NEXT_DAY(DATE'2016/05/01', 'Friday') FROM DUAL;
          next_day
    ---------------------
     2016-05-06 00:00:00
    (1 row)

F.55.5.3.6. ROUND

Description

Rounds a date.

Syntax

     ROUND(date DATE) returns DATE
     ROUND(date DATE, fmt TEXT) returns DATE
     ROUND(date TIMESTAMP WITH TIME ZONE) returns TIMESTAMP WITH TIME ZONE
     ROUND(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TIMESTAMP WITH TIME ZONE
     ROUND(date TIMESTAMP WITHOUT TIME ZONE) returns TIMESTAMP WITHOUT TIME ZONE
     ROUND(date TIMESTAMP WITHOUT TIME ZONE, fmt TEXT) returns TIMESTAMP WITHOUT TIME ZONE

General rules

ROUND returns a date rounded to the unit specified by format model fmt.
For date, specify a DATE or TIMESTAMPTZ type.
Specify the format model as a string.

Values that can be specified for the format model

Table F.66. Setting Format Model for ROUND Function

Format model	Rounding unit
Y,YY,YYY,YYYY, SYYYY,YEAR,SYEAR	Year
I,IY,IYY,IYYY	Year (values including calendar weeks, in compliance with the ISO standard)
Q	Quarter
WW	Week (first day of the year)
IW	Week (Monday of that week)
W	Week (first weekday on which the first day of the month falls)
DAY,DY,D	Week (Sunday of that week)
MONTH,MON,MM,RM	Month
CC,SCC	Century
DDD,DD,J	Day
HH,HH12,HH24	Hour
MI	Minute

If decimal places are rounded: for year, the boundary for rounding is July 1; for month, the day is 16; and for week, the weekday is Thursday.
If fmt is omitted, the date is rounded by day.
If the DATE type of LightDB is specified for the date, that DATE type will be the data type of the return value. If the TIMESTAMP type is specified for the date, the data type will be TIMESTAMP WITH TIME ZONE, irrespective of whether a time zone is used.

Example

In the example below, the result of "June 20, 2016 18:00:00" rounded by Sunday of the week is returned.

    SELECT ROUND(TIMESTAMP'2016/06/20 18:00:00','DAY') FROM DUAL;
             round
    ------------------------
     2016-06-19 00:00:00+09
    (1 row)

F.55.5.3.7. SESSIONTIMEZONE

Description

Returns the time zone of the session.

Syntax

     SESSIONTIMEZONE() returns text

General rules

SESSIONTIMEZONE returns the time zone value between sessions.
The data type of the return value is TEXT.

Note

If using SESSIONTIMEZONE, it is necessary to specify "oracle" for search_path in advance.
The value returned by SESSIONTIMEZONE becomes the value set in the "TimeZone" server parameter.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

Example

In the following example, the time zone of the session is returned.

    SELECT SESSIONTIMEZONE() FROM DUAL;
     sessiontimezone
    -----------------
     Japan
    (1 row)

F.55.5.3.8. SYSDATE

Description

Returns the system date.

Syntax

     SYSDATE() returns oracle.date

General rules

SYSDATE returns the system date.
The data type of the return value is the DATE type of orafce.

Note

If using SYSDATE, it is necessary to specify "oracle" for search_path in advance.
The date returned by SYSDATE depends on the time zone value of the orafce database.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "DBTIMEZONE" for information on the time zone values of the database.
Refer to "The SQL Language" > "Data Types" > "Date/Time Types" in the LightDB Documentation for information on the time zone.

Example

In the following example, the system date is returned.

    SELECT SYSDATE() FROM DUAL;
           sysdate
    ---------------------
     2016-06-22 08:06:51
    (1 row)

F.55.5.3.9. TRUNC

Description

Truncates a date.

Syntax

     TRUNC(date DATE) returns DATE
     TRUNC(date DATE, fmt TEXT) returns DATE
     TRUNC(date TIMESTAMP WITH TIME ZONE) returns TIMESTAMP WITH TIME ZONE
     TRUNC(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TIMESTAMP WITH TIME ZONE
     TRUNC(date TIMESTAMP WITHOUT TIME ZONE) returns TIMESTAMP WITHOUT TIME ZONE
     TRUNC(date TIMESTAMP WITHOUT TIME ZONE, fmt TEXT) returns TIMESTAMP WITHOUT TIME ZONE

General rules

TRUNC returns a date truncated to the unit specified by format model fmt.
For date, specify a DATE or TIMESTAMP type.
Specify the format model as a string. The values that can be specified are the same as for ROUND.
If fmt is omitted, the date is truncated by day.
If the DATE type of LightDB is specified for the date, that DATE type will be the data type of the return value. If the TIMESTAMP type is specified for the date, the data type will be TIMESTAMP WITH TIME ZONE, irrespective of whether a time zone is used.

See

Refer to "ROUND" for information on the values that can be specified for the format model.

Example

In the example below, the result of "August 10, 2016 15:30:00" truncated by the day is returned.

    SELECT TRUNC(TIMESTAMP'2016/08/10 15:30:00','DDD') FROM DUAL;
             trunc
    ------------------------
     2016-08-10 00:00:00+09
    (1 row)

F.55.5.3.10. TZ_OFFSET

Description

Returns the time zone offset.

Syntax

     TZ_OFFSET(text) returns text

General rules

You can enter a valid time zone region name, a time zone offset from UTC (which simply returns itself).
For a listing of valid values for time_zone_name, query the name column of the pg_timezone_names.

Note

If using TZ_OFFSET, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT TZ_OFFSET('Asia/Shanghai') FROM DUAL;
 tz_offset 
-----------
 08:00:00
(1 row)
    (1 row)

F.55.5.4. Data Type Formatting Functions

The following data type formatting functions are supported:

CONVERT
NUMTOYMINTERVAL
TO_BLOB(raw)
TO_CHAR
TO_DATE
TO_MULTI_BYTE
TO_NUMBER
TO_SINGLE_BYTE
ASCIISTR
ASCII

F.55.5.4.1. CONVERT

Description

CONVERT converts a character string from one character set to another.

Syntax

     CONVERT(str TEXT,dest_char_set TEXT,source_char_set TEXT DEFAULT 'utf8') returns TEXT

General rules

TThe str argument is the value to be converted.
The dest_char_set argument is the name of the character set to which str is converted.
The source_char_set argument is the name of the character set in which str is stored in the database. The default value is the database character set.
Both the destination and source character set arguments can be either literals or columns containing the name of the character set.
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

Note

If using CONVERT for converting string, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

 SELECT CONVERT('娴嬭瘯', 'gbk', 'utf8') from dual; FROM DUAL;
 convert 
---------
 测试
(1 row)

F.55.5.4.2. NUMTOYMINTERVAL

Description

NUMTOYMINTERVAL converts number to an INTERVAL YEAR TO MONTH literal.

Syntax

     NUMTOYMINTERVAL(n numeric, interval_unit text) returns interval

General rules

The value for interval_unit specifies the unit of n and must resolve to one of the following string values: 'YEAR', 'MONTH'.
interval_unit is case insensitive.

Note

If using NUMTOYMINTERVAL for specifying date/time values, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT NUMTOYMINTERVAL(1,'YEAR') FROM DUAL;
 numtoyminterval 
-----------------
 1 year
(1 row)

F.55.5.4.3. TO_BLOB(raw)

Description

TO_BLOB(raw) converts RAW values to BLOB values.

Syntax

     TO_BLOB(raw) returns blob

Note

If using the TO_BLOB(raw) function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT TO_BLOB(UTL_RAW.CAST_TO_RAW('测试')) FROM DUAL;
    to_blob     
----------------
 \xe6b58be8af95
(1 row)

F.55.5.4.4. TO_CHAR

Description

Converts a value to a string.

Syntax

     TO_CHAR(num SMALLINT) returns TEXT
     TO_CHAR(num INTEGER) returns TEXT
     TO_CHAR(num BIGINT) returns TEXT
     TO_CHAR(num REAL) returns TEXT
     TO_CHAR(num DOUBLE PRECISION) returns TEXT
     TO_CHAR(num NUMERIC) returns TEXT
     TO_CHAR(date TIMESTAMP) returns TEXT

General rules

TO_CHAR converts the specified number or date/time value to a string.
For num, specify a numeric data type.
For date, specify a DATE or TIMESTAMP type. Also, you must set a date/time format for the nls_date_format variable in advance. A setting example using the SET statement is shown below.
```
       SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
      
```
The data type of the return value is TEXT.

Note

If using TO_CHAR for specifying date/time values, it is necessary to specify "oracle" for search_path in advance.
The nls_date_format settings can be set using any of the methods for setting server parameters.
If nls_date_format is set, the following message may be displayed when an SQL statement is executed, however, the parameter settings are enabled, so you can ignore this.
```
       WARNING:  unrecognized configuration parameter "nls_date_format"
      
```

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

In the following example, the numeric value "123.45" is returned as a string.

     SELECT TO_CHAR(123.45) FROM DUAL;
      to_char
     ---------
      123.45
     (1 row)

F.55.5.4.5. TO_DATE

Description

Converts a string to a date in accordance with the specified format.

Syntax

     TO_DATE(str TEXT) returns TIMESTAMP
     TO_DATE(str TEXT, fmt TEXT) returns TIMESTAMP

General rules

TO_DATE converts string str to a date in accordance with the specified format fmt.
Specify a string indicating the date/time.
Specify the required date/time format. If omitted, the format specified in the nls_date_format variable is used. If the nls_date_format variable has not been set, the existing date/time input interpretation is used. A setting example using the SET statement is shown below.
```
       SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
      
```
The data type of the return value is TIMESTAMP.

Note

The above TO_DATE specification uses orafce for its behavior, which is different to that of TO_DATE of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
The nls_date_format settings can be set using any of the methods for setting server parameters.
If nls_date_format is set, the following message may be displayed when an SQL statement is executed, however, the parameter settings are enabled, so you can ignore this.
```
       WARNING:  unrecognized configuration parameter "nls_date_format"
      
```

Information

The general rule for TO_DATE for specifying the data type format of LightDB is as follows:

The data type of the return value is the DATE type of LightDB.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "Data Type Formatting Functions" in the LightDB Documentation for information on TO_DATE of LightDB.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Refer to "Date/Time Support" > "Date/Time Input Interpretation" in the LightDB Documentation for information on the interpretation of existing date/time input.

Example

In the following example, the string "2016/12/31" is converted to a date and returned.

     SELECT TO_DATE('2016/12/31','YYYY/MM/DD') FROM DUAL;
            to_date
     ---------------------
      2016-12-31 00:00:00
     (1 row)

F.55.5.4.6. TO_MULTI_BYTE

Description

Converts a single-byte string to a multibyte string.

Syntax

     TO_MULTI_BYTE(str TEXT) returns TEXT

General rules

TO_MULTI_BYTE converts halfwidth characters in string str to fullwidth characters, and returns the converted string.
Specify a string indicating the date/time.
Specify the required date/time format. If omitted, the format specified in the nls_date_format variable is used. If the nls_date_format variable has not been set, the existing date/time input interpretation is used. A setting example using the SET statement is shown below.
```
       Only halfwidth alphanumeric characters, spaces and symbols can be converted.
      
```
The data type of the return value is TEXT.

Example

In the following example, "abc123" is converted to fullwidth characters and returned.

     SELECT TO_MULTI_BYTE('abc123') FROM DUAL;
      to_multi_byte
     ---------------
        ******
     (1 row)

"******" is multibyte "abc123".

F.55.5.4.7. TO_NUMBER

Description

Converts a value to a number in accordance with the specified format.

Syntax

     TO_NUMBER(str TEXT) returns NUMERIC
     TO_NUMBER(num NUMERIC) returns NUMERIC
     TO_NUMBER(num NUMERIC, fmt NUMERIC) returns NUMERIC

General rules

TO_NUMBER converts the specified value to a numeric value in accordance with the specified format fmt.
For num, specify a numeric data type.
For str, specify a string indicating the numeric value. Numeric values must comprise only of convertible characters.
Specify the required numeric data format. The specified numeric value is handled as is as a data type expression.
The data type of the return value is NUMERIC.

See

Refer to "The SQL Language" > "Functions and Operators" > "Data Type Formatting Functions" in the LightDB Documentation for information on numeric value formats.

Example

In the following example, the numeric literal "-130.5" is converted to a numeric value and returned.

     SELECT TO_NUMBER(-130.5) FROM DUAL;
      to_number
     -----------
         -130.5
     (1 row)

F.55.5.4.8. TO_SINGLE_BYTE

Description

Converts a multibyte string to a single-byte string.

Syntax

     TO_SINGLE_BYTE(str TEXT) returns TEXT

General rules

TO_SINGLE_BYTE converts fullwidth characters in string str to halfwidth characters, and returns the converted string.
Only fullwidth alphanumeric characters, spaces and symbols that can be displayed in halfwidth can be converted.
The data type of the return value is TEXT.

Example

In the following example, "******" is converted to halfwidth characters and returned. "******" is multibyte "xyz999".

     SELECT TO_SINGLE_BYTE('******') FROM DUAL;
      to_single_byte
     ----------------
      xyz999
     (1 row)

F.55.5.4.9. ASCIISTR

Description

ASCIISTR takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set.

Syntax

     ASCIISTR(str TEXT) returns TEXT

General rules

Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.
The data type of the return value is TEXT.

Example

In the following example, "Ä" is converted to "\00C4".

     SELECT ASCIISTR('ABÄCDE') FROM DUAL;
      asciistr
     ----------------
      AB\00C4CDE
     (1 row)

F.55.5.4.10. ASCII

Description

ASCII takes as its argument a string, or an expression that resolves to a string, in any character set and returns an value in the database character set of the first character.

Syntax

     ASCIISTR(str TEXT) returns BIGINT

General rules

If your database character set is 7-bit ASCII, then this function returns an ASCII value. If your database character set is UTF-8 Code, then this function returns an UTF-8 value.
The data type of the return value is BIGINT.

Example

In the following example, "Ä" is converted to "50052" in UTF-8.

     SELECT ORACLE.ASCII('Ä') FROM DUAL;
      ascii
     -------
      50052
     (1 row)

F.55.5.5. Conditional Expressions

The following functions for making comparisons are supported:

DECODE
LNNVL
NANVL
NVL
NVL2

F.55.5.5.1. DECODE

Description

Compares values and if they match, returns a corresponding value.

Syntax

     DECODE(expr, srch,  result)
     DECODE(expr, srch,  result,  default)
     DECODE(expr, srch1, result1, srch2, result2)
     DECODE(expr, srch1, result1, srch2, result2, default)
     DECODE(expr, srch1, result1, srch2, result2, srch3, result3)
     DECODE(expr, srch1, result1, srch2, result2, srch3, result3, default)

General rules

DECODE compares values of the value expression to be converted and the search values one by one. If the values match, a corresponding result value is returned. If no values match, the default value is returned if it has been specified. A NULL value is returned if a default value has not been specified.
If the same search value is specified more than once, then the result value returned is the one listed for the first occurrence of the search value.
The following data types can be used in result values and in the default value:
- CHAR
- VARCHAR
- VARCHAR2
- NCHAR
- NCHAR VARYING
- NVARCHAR2
- TEXT
- INTEGER
- BIGINT
- NUMERIC
- DATE
- TIME WITHOUT TIME ZONE
- TIMESTAMP WITHOUT TIME ZONE
- TIMESTAMP WITH TIME ZONE
The same data type must be specified for the values to be converted and the search values. However, note that different data types may also be specified if a literal is specified in the search value, and the value expression to be converted contains data types that can be converted.
If the result values and default value are all literals, the data types for these values will be as shown below:
- If all values are string literals, all will become character types.
- If there is one or more numeric literal, all will become numeric types.
- If there is one or more literal cast to the datetime/time types, all will become datetime/time types.
If the result values and default value contain a mixture of literals and non-literals, the literals will be converted to the data types of the non-literals.
The same data type must be specified for all result values and for the default value. However, different data types can be specified if the data type of any of the result values or default value can be converted - these data types are listed below:

Table F.67. Data type combinations that can be converted by DECODE (summary)

		Other result values or default value
		Numeric type	Character type	Date/time type
Result value (any)	Numeric type	Y	N	N
	Character type	N	Y	N
	Date/time type	N	N	S(*1)

Y: Can be converted

S: Some data types can be converted

N: Cannot be converted

*1: The data types that can be converted for date/time types are listed below:

Table F.68. Result value and default value date/time data types that can be converted by DECODE

		Other result values or default value
		DATE	TIME WITHOUT TIME ZONE	TIMESTAMP WITHOUT TIME ZONE	TIMESTAMP WITH TIME ZONE
Result value (any)	DATE	Y	N	Y	Y
	TIME WITHOUT TIME ZONE	N	Y	N	N
	TIMESTAMP WITHOUT TIME ZONE	Y	N	Y	Y
	TIMESTAMP WITH TIME ZONE	Y	N	Y	Y

Y: Can be converted

N: Cannot be converted

The data type of the return value will be the data type within the result or default value that is longest and has the highest precision.

Example

In the following example, the value of col3 in table t1 is compared and converted to a different value. If the col3 value matches search value 1, the result value returned is "one". If the col3 value does not match any of search values 1, 2, or 3, the default value "other number" is returned.

     SELECT col1,
            DECODE(col3, 1, 'one',
                         2, 'two',
                         3, 'three',
                         'other number') "num-word"
            FROM t1;
     col1  | num-word
     ------+----------
      1001 | one
      1002 | two
      1003 | three
     (3 rows)

F.55.5.5.2. LNNVL

Description

Determines if a value is TRUE or FALSE for the specified condition.

Syntax

     LNNVL(cond BOOLEAN) returns BOOLEAN

General rules

LNNVL determines if a value is TRUE or FALSE for the specified condition. If the result of the condition is FALSE or NULL, TRUE is returned. If the result of the condition is TRUE, FALSE is returned.
The expression for returning TRUE or FALSE is specified in the condition.
The data type of the return value is BOOLEAN.

Example

In the following example, col1 and col3 of table t1 are returned when col3 has a value of 2000 or less, or null values.

     SELECT col1,col3 FROM t1 WHERE LNNVL( col3 > 2000 );
      col1 | col3
     ------+------
      1001 | 1000
      1002 | 2000
      2002 |
     (3 row)

F.55.5.5.3. NANVL

Description

Returns a substitute value when a value is not a number (NaN).

Syntax

     NANVL(expr FLOAT4,  substitute FLOAT4)  returns FLOAT4
     NANVL(expr FLOAT8,  substitute FLOAT8)  returns FLOAT8
     NANVL(expr NUMERIC, substitute NUMERIC) returns NUMERIC
     NANVL(expr FLOAT4,  substitute VARCHAR) returns FLOAT4
     NANVL(expr FLOAT8,  substitute VARCHAR) returns FLOAT8
     NANVL(expr NUMERIC, substitute VARCHAR) returns NUMERIC

General rules

NANVL returns a substitute value when the specified value is not a number (NaN). The substitute value can be either a number or a string that can be converted to a number.
For expr and substitute, specify a numeric data type. If expr and substitute have different data types, they will be converted to the data type with greater length or precision, and that is the data type that will be returned.
For substitute, you can also specify a string indicating the numeric value.
The data type used for the return value if a string is specified for the substitute value will be the same as the data type of expr.

Example

In the following example, "0" is returned if the value of col1 in table t1 is a NaN value.

     SELECT col1, NANVL(col3,0) FROM t1;
      col1 | nanvl
     ------+-------
      2001 |     0
     (1 row)

F.55.5.5.4. NVL

Description

Returns a substitute value when a value is NULL.

Syntax

     NVL(expr1 anyelement, expr2 anyelement) return anyelement

General rules

NVL returns a substitute value when the specified value is NULL. When expr1 is NULL, expr2 is returned. When expr1 is not NULL, expr1 is returned.
Specify the same data types for expr1 and expr2. However, if a constant is specified in expr2, and the data type can also be converted by expr1, different data types can be specified. When this happens, the conversion by expr2 is done to suit the data type in expr1, so the value of expr2 returned when expr1 is a NULL value will be the value converted in the data type of expr1. This is not necessary for types (numeric, int) and (bigint, int).

Example

In the following example, "IS NULL" is returned if the value of col1 in table t1 is a NULL value.

     SELECT col2, NVL(col1,'IS NULL') "nvl" FROM t1;
      col2 |   nvl
     ------+---------
      aaa  | IS NULL
     (1 row)

F.55.5.5.5. NVL2

Description

Returns a substitute value based on whether a value is NULL or not NULL.

Syntax

     NVL2(expr anyelement, substitute1 anyelement, substitute2 anyelement) return anyelement

General rules

NVL2 returns a substitute value based on whether the specified value is NULL or not NULL. When expr is NULL, substitute2 is returned. When it is not NULL, substitute1 is returned.
Specify the same data types for expr, substitute1, and substitute2. However, if a literal is specified in substitute1 or substitute2, and the data type can also be converted by expr, different data types can be specified. When this happens, substitute1 or substitute2 is converted to suit the data type in expr, so the value of substitute2 returned when expr is a NULL value will be the value converted to the data type of expr.

Example

In the following example, if a value in column col1 in table t1 is NULL, "IS NULL" is returned, and if not NULL, "IS NOT NULL" is returned.

     SELECT col2, NVL2(col1,'IS NOT NULL','IS NULL') FROM t1;
      col2 |   nvl2
     ------+---------
      aaa  | IS NULL
      bbb  | IS NOT NULL
     (2 row)

F.55.5.6. Aggregate Functions

The following aggregation functions are supported:

ANY_VALUE
BIT_AND_AGG
BIT_OR_AGG
BIT_XOR_AGG
KURTOSIS_POP
KURTOSIS_SAMP
LISTAGG
MEDIAN
SKEWNESS_POP
SKEWNESS_SAMP

F.55.5.6.1. ANY_VALUE

Description

ANY_VALUE returns a single non-deterministic value of expr.

Syntax

     ANY_VALUE(expr anyelement) returns anyelement

General rules

Use ANY_VALUE to optimize a query that has a GROUP BY clause. ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.
It ensures that there are no comparisons for any incoming row and also eliminates the necessity to specify every column as part of the GROUP BY clause.
Because it does not compare values, ANY_VALUE returns a value more quickly than MIN or MAX in a GROUP BY query.
Returns any value within each group based on the GROUP BY specification. Returns NULL if all rows in the group have NULL expression values.
The result of ANY_VALUE is not deterministic.

Note

If using ANY_VALUE, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

create table test_any_value(key1 int, key2 int);
insert into test_any_value values(1, 1);
select any_value(key2)  from test_any_value group by key1;
 any_value 
-----------
         1
(1 row)

F.55.5.6.2. BIT_AND_AGG

Description

BIT_AND_AGG is a bitwise aggregation function that returns the result of a bitwise AND operation.

Syntax

     BIT_AND_AGG(numeric) returns int

General rules

You can use BIT_AND_AGG as part of a GROUP BY query, window function, or as an analytical function.
For a given set of values, the result of a bitwise aggregate is always deterministic and independent of ordering.
The data type of the return value is int.

Note

If using BIT_AND_AGG, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select bit_and_agg(column1)  from (values (1),(2),(4),(8)) x;
 bit_and_agg 
-------------
           0
(1 row)

F.55.5.6.3. BIT_OR_AGG

Description

BIT_OR_AGG is a bitwise aggregation function that returns the result of a bitwise OR operation.

Syntax

     BIT_OR_AGG(numeric) returns int

General rules

You can use BIT_OR_AGG as part of a GROUP BY query, window function, or as an analytical function.
For a given set of values, the result of a bitwise aggregate is always deterministic and independent of ordering.
The data type of the return value is int.

Note

If using BIT_OR_AGG, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select bit_or_agg(column1)  from (values (1),(2),(4),(8)) x;
 bit_or_agg 
------------
         15
(1 row)

F.55.5.6.4. BIT_XOR_AGG

Description

BIT_XOR_AGG is a bitwise aggregation function that returns the result of a bitwise XOR operation.

Syntax

     BIT_XOR_AGG(numeric) returns int

General rules

You can use BIT_XOR_AGG as part of a GROUP BY query, window function, or as an analytical function.
For a given set of values, the result of a bitwise aggregate is always deterministic and independent of ordering.
The data type of the return value is int.

Note

If using BIT_XOR_AGG, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select bit_xor_agg(column1)  from (values (1),(3),(4),(8)) x;
 bit_xor_agg 
-------------
          14
(1 row)

F.55.5.6.5. KURTOSIS_POP

Description

The population kurtosis function KURTOSIS_POP is primarily used to determine the characteristics of outliers in a given distribution.

Syntax

     KURTOSIS_POP(numeric) returns numeric

General rules

NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of population kurtosis (KURTOSIS_POP) and sample kurtosis (KURTOSIS_SAMP) are always deterministic. However, the values of KURTOSIS_POP and KURTOSIS_SAMP differ. As the number of values in the data set increases, the difference between the computed values of KURTOSIS_SAMP and KURTOSIS_POP decreases.

Note

If using the KURTOSIS_POP function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select kurtosis_pop(column1) from  (values (1),(2),(4),(8)) x;
      kurtosis_pop       
-------------------------
 -1.09897920604914942667
(1 row)

F.55.5.6.6. KURTOSIS_SAMP

Description

The sample kurtosis function KURTOSIS_SAMP is primarily used to determine the characteristics of outliers in a given distribution.

Syntax

     KURTOSIS_SAMP(numeric) returns numeric

General rules

NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of sample kurtosis (KURTOSIS_SAMP) and population kurtosis (KURTOSIS_POP) are always deterministic. However, the values of KURTOSIS_SAMP and KURTOSIS_POP differ. As the number of values in the data set increases, the difference between the computed values of KURTOSIS_SAMP and KURTOSIS_POP decreases.

Note

If using the KURTOSIS_SAMP function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select kurtosis_samp(column1) from  (values (1),(2),(4),(8)) x;
   kurtosis_samp    
--------------------
 0.7576559546313793
(1 row)

F.55.5.6.7. LISTAGG

Description

Returns a concatenated, delimited list of string values.

Syntax

     LISTAGG(strExpr TEXT) returns TEXT
     LISTAGG(strExpr TEXT, delimiter TEXT) []returns TEXT

General rules

LISTAGG concatenates and delimits a set of string values and returns the result.
For delimiter, specify a string. If the delimiter is omitted, a list of strings without a delimiter is returned.
In LightDB, LISTAGG support using distinct in function with within group clauses. e.g: LISTAGG(distinct c1) within group(order by c1)
In LightDB, LISTAGG support using within group clauses with over clauses. In this scenario we cannot use distinct in function yet. e.g: LISTAGG(c1) within group(order by c1) over(partition by c2)
The data type of the return value is TEXT.

Example

In the following example, the result with values of column col2 in table t1 delimited by ':' is returned.

     SELECT LISTAGG(col2,':') FROM t1;
           listagg
     -------------------
      AAAAA:BBBBB:CCCCC
     (1 row)

In the following example, using within group with over.

CREATE TABLE EMP
(   EMPNO    NUMBER(4, 0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4, 0),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2, 0),
    DNAME    VARCHAR2(100),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH',  'CLERK',    7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00,  null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN',  'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD',   'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES',  'MANAGER',  7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7698, 'BLAKE',  'MANAGER',  7839, to_date('1981-05-01','yyyy-mm-dd'), 2850.00, null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7782, 'CLARK',  'MANAGER',  7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7788, 'SCOTT',  'ANALYST',  7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7839, 'KING',   'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7876, 'ADAMS',  'CLERK',    7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7900, 'JAMES',  'CLERK',    7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00,  null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7902, 'FORD',   'ANALYST',  7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7934, 'MILLER', 'CLERK',    7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null,    10, null);

select listagg(ename, ',') within group(order by ename) over(partition by deptno) as enames,
       deptno,
       ename
  from EMP;
                enames                | deptno | ename  
--------------------------------------+--------+--------
 CLARK,KING,MILLER                    |     10 | CLARK
 CLARK,KING,MILLER                    |     10 | KING
 CLARK,KING,MILLER                    |     10 | MILLER
 ADAMS,FORD,JONES,SCOTT,SMITH         |     20 | ADAMS
 ADAMS,FORD,JONES,SCOTT,SMITH         |     20 | FORD
 ADAMS,FORD,JONES,SCOTT,SMITH         |     20 | JONES
 ADAMS,FORD,JONES,SCOTT,SMITH         |     20 | SCOTT
 ADAMS,FORD,JONES,SCOTT,SMITH         |     20 | SMITH
 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |     30 | ALLEN
 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |     30 | BLAKE
 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |     30 | JAMES
 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |     30 | MARTIN
 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |     30 | TURNER
 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |     30 | WARD
(14 rows)

F.55.5.6.8. MEDIAN

Description

Calculates the median of a set of numbers.

Syntax

     MEDIAN(numExpr REAL) returns REAL
     MEDIAN(numExpr DOUBLE PRECISION) returns DOUBLE PRECISION

General rules

MEDIAN returns the median of a set of numbers.
The numbers must be numeric data type.
The data type of the return value will be REAL if the numbers are REAL type, or DOUBLE PRECISION if any other type is specified.

Example

In the following example, the median of column col3 in table t1 is returned.

     SELECT MEDIAN(col3) FROM t1;
      median
     --------
        2000
     (1 row)

F.55.5.6.9. SKEWNESS_POP

Description

SKEWNESS_POP is an aggregate function that is primarily used to determine symmetry in a given distribution.

Syntax

     SKEWNESS_POP(numeric) returns numeric

General rules

NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of population skewness (SKEWNESS_POP) and sample skewness (SKEWNESS_SAMP) are always deterministic. However, the values of SKEWNESS_POP and SKEWNESS_SAMP differ. As the number of values in the data set increases, the difference between the computed values of SKEWNESS_SAMP and SKEWNESS_POP decreases.

Note

If using the SKEWNESS_POP function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT SKEWNESS_POP(column1) from  (values (1),(2),(4),(8)) x;
      skewness_pop      
------------------------
 0.65680773449969915746
(1 row)

F.55.5.6.10. SKEWNESS_SAMP

Description

SKEWNESS_SAMP is an aggregate function that is primarily used to determine symmetry in a given distribution.

Syntax

     SKEWNESS_SAMP(numeric) returns numeric

General rules

NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of population skewness (SKEWNESS_POP) and sample skewness (SKEWNESS_SAMP) are always deterministic. However, the values of SKEWNESS_POP and SKEWNESS_SAMP differ. As the number of values in the data set increases, the difference between the computed values of SKEWNESS_SAMP and SKEWNESS_POP decreases.

Note

If using the SKEWNESS_SAMP function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

SELECT SKEWNESS_SAMP(column1) from  (values (1),(2),(4),(8)) x;
     skewness_samp      
------------------------
 1.13762436695768880892
(1 row)

F.55.5.7. Functions That Return Internal Information

The following functions that return internal information are supported:

DUMP
NLS_CHARSET_ID
NLS_CHARSET_NAME
SYS_CONTEXT
USERENV

F.55.5.7.1. DUMP

Description

Returns internal information of a value.

Syntax

     DUMP(expr TEXT) returns VARCHAR
     DUMP(expr TEXT, fmt INTEGER) returns VARCHAR

General rules

DUMP returns the internal information of the values specified in expressions in a display format that is in accordance with the output format.
The internal code (Typ) of the data type, the data length (Len) and the internal expression of the data are output as internal information.
Any data type can be specified for the expressions.
The display format (base n ) of the internal expression of the data is specified for the output format. The base numbers that can be specified are 8, 10, and 16. If omitted, 10 is used as the default.
The data type of the return value is VARCHAR.

Note

The information output by DUMP will be the complete internal information. Therefore, the values may change due to product updates, and so on.

Example

In the following example, the internal information of column col1 in table t1 is returned.

     SELECT col1, DUMP(col1) FROM t1;
      col1 |                dump
     ------+------------------------------------
      1001 | Typ=25 Len=8: 32,0,0,0,49,48,48,49
      1002 | Typ=25 Len=8: 32,0,0,0,49,48,48,50
      1003 | Typ=25 Len=8: 32,0,0,0,49,48,48,51
     (3 row)

F.55.5.7.2. NLS_CHARSET_ID

Description

NLS_CHARSET_ID returns the character set ID number corresponding to character set name string.

Syntax

     NLS_CHARSET_ID(str text) returns integer

General rules

Invalid character set names return null.

Note

The character set name is from Oracle, use this function only for compatibility.
If using the NLS_CHARSET_ID function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

The following example returns the character set ID of a character set:

SELECT NLS_CHARSET_ID('AL32UTF8') FROM DUAL;
 nls_charset_id 
----------------
            873
(1 row)

F.55.5.7.3. NLS_CHARSET_NAME

Description

NLS_CHARSET_NAME returns the name of the character set corresponding to ID number number.

Syntax

     NLS_CHARSET_NAME(str text) returns integer

General rules

If number is not recognized as a valid character set ID, then this function returns null.

Note

The character set name is from Oracle, use this function only for compatibility.
If using the NLS_CHARSET_NAME function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

The following example returns the character set corresponding to character set ID number 873:

SELECT NLS_CHARSET_NAME(873) FROM DUAL;
 nls_charset_name 
------------------
 AL32UTF8
(1 row)

F.55.5.7.4. SYS_CONTEXT

Description

Returns the value of parameter associated with the context namespace at the current instant

Syntax

     SYS_CONTEXT(namespace text, parameter text, length int4 default 256) returns text

General rules

For namespace and parameter, you can specify either a string or an expression that resolves to a string designating a namespace or an attribute.
The context namespace must already have been created, and the associated parameter and its value must also have been set.
The namespace must be a valid identifier. The parameter name can be any string. It is not case sensitive, but it cannot exceed 4000 bytes in length.

Note

LightDB provides the following built-in namespaces:

USERENV - Describes the current session. The predefined parameters of namespace USERENV are listed in table Table F.69
SYS_SESSION_ROLES - Indicates whether a specified role is currently enabled for the session.

Table F.69. Predefined Parameters of Namespace USERENV

Parameters	Overview
CLIENT_IDENTIFIER	The name of the program used for the database session
CLIENT_INFO	The name of the program used for the database session
CLIENT_PROGRAM_NAME	The name of the program used for the database session
CDB_NAME	Current database
CON_ID	Always 1
CON_NAME	Current database
CURRENT_SCHEMA	Current schema
CURRENT_SCHEMAID	Current schema id
CURRENT_USER	Current user
CURRENT_USERID	Current user id
DATABASE_ROLE	The role is one of the following: PRIMARY, PHYSICAL STANDBY
DB_NAME	Current database
DB_UNIQUE_NAME	Current database
HOST	Name of the host machine from which the client has connected
INSTANCE	Always 1
INSTANCE_NAME	Always 'LightDB'
IP_ADDRESS	IP address of the machine from which the client is connected
ISDBA	Returns TRUE if the user has been authenticated as having DBA privileges
LANG	The abbreviated name for the language, a shorter form than the existing 'LANGUAGE' parameter
LANGUAGE	The language and territory currently used by your session, along with the database character set
MODULE	The name of the program used for the database session
NETWORK_PROTOCOL	Network protocol being used for communication
NLS_DATE_FORMAT	The date format for the session
ORACLE_HOME	The full path name for the data home directory
PID	Current process ID
SERVER_HOST	listen_addresses
SERVICE_NAME	Current database
SESSION_USER	Current user
SESSION_USERID	Current user id
SESSIONID	Current session process id
SID	Current session process id

Note

If using SYS_CONTEXT, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

The following statement returns the name of the user who logged onto the database:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
 sys_context 
-------------
 lightdb
(1 row)

F.55.5.7.5. USERENV

Description

Returns information about the current session

Syntax

     USERENV(parameter text) returns text

General rules

This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session.
Table Table F.69 describes the values for the parameter argument.

Note

If using USERENV, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

The following example returns the LANGUAGE parameter of the current session:

SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
  Language   
-------------
 en_US.UTF-8
(1 row)

F.55.5.8. Datetime Operator

The following datetime operators are supported for the DATE type of orafce.

Table F.70. Datetime operator

Operation	Example	Result
+	DATE'2016/01/01' + 10	2016-01-11 00:00:00
-	DATE'2016/03/20' - 35	2016-02-14 00:00:00
-	DATE'2016/09/01' - DATE'2015/12/31'	245

Note

If using datetime operators for the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.

F.55.5.9. Other functions

The following functions is used for other purpose:

EMPTY_CLOB
ORA_HASH
VSIZE
DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES

F.55.5.9.1. EMPTY_CLOB

Description

Return an empty CLOB

Syntax

     EMPTY_CLOB() RETURNS clob

General rules

Actually return ''.
EMPTY means that the CLOB is initialized, but not populated with data.

Note

If using the EMPTY_CLOB function, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select length(empty_clob()) FROM DUAL;
 length 
--------
      0
(1 row)

F.55.5.9.2. ORA_HASH

Description

ORA_HASH is a function that computes a hash value for a given expression. This function is useful for operations such as analyzing a subset of data and generating a random sample.

Syntax

     ORA_HASH(p_data anyelement,p_buckets int4, p_seed int4) RETURNS int

General rules

The p_data argument determines the data for which you want LightDB Database to compute a hash value. There are no restrictions on the length of data represented by expr, which commonly resolves to a column name.
The optional p_buckets argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 2147483647. The default is 0.
The optional p_seed argument enables LightDB to produce many different results for the same set of data. LightDB applies the hash function to the combination of expr and p_seed. You can specify any value between 0 and 2147483647. The default is 0.
The data type of the return value is int.

Note

If using ORA_HASH, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select ora_hash('abcdAbcdasd'::text) FROM DUAL;
 ora_hash  
-----------
 303228277
(1 row)

F.55.5.9.3. VSIZE

Description

VSIZE returns the number of bytes in the internal representation of expr. If expr is null, then this function returns null.

Syntax

     VSIZE(anyelement) RETURNS integer

General rules

Same with pg_column_size.

Note

If using VSIZE, it is necessary to specify "oracle" for search_path in advance.

See

Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.

Example

select vsize(123456789) FROM DUAL;
 vsize 
-------
     4
(1 row)

F.55.5.9.4. DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES

Description

These functions are used to modify tables that have view dependencies. Recursively backup all dependent views, then modify base tables, then recreate all backuped views.

DEPS_SAVE_AND_DROP_DEPENDENCIES save and drop dependencies for table.

DEPS_RESTORE_DEPENDENCIES restore dependencies for table.

Syntax

     DEPS_SAVE_AND_DROP_DEPENDENCIES(name, name default current_schema()::name, jsonb default '{}'::jsonb) RETURNS void
     DEPS_RESTORE_DEPENDENCIES(name, name default current_schema()::name, jsonb default '{}'::jsonb)

General rules

Create under lt_catalog.
dependencies is saved in lt_catalog.deps_saved_ddl table.
dry_run Run without actually dropping dependencies, default false.
verbose Show debug log, , default false.
populate_materialized_view Enable or disable materialized view refresh-on-create via WITH [NO] DATA flag, default false.

Note

If using DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES, it is not necessary to specify "oracle" for search_path in advance.

Example

create table test_t(key1 int);
create view test_t_v as select * from test_t;
begin;
select deps_save_and_drop_dependencies('test_t');
alter table test_t modify key1 number;
select * from deps_restore_dependencies('test_t');
commit;

F.55.6. Package Reference

A "package" is a group of features, brought together by schemas, that have a single functionality, and are used by calling from PL/pgSQL.

The following packages are supported:

DBMS_ALERT
DBMS_ASSERT
DBMS_LOB
DBMS_OUTPUT
DBMS_PIPE
DBMS_RANDOM
DBMS_UTILITY
DBMS_JOB
DBMS_LOCK
DBMS_METADATA
DBMS_OBFUSCATION_TOOLKIT
DBMS_SNAPSHOT
UTL_FILE
UTL_RAW
UTL_URL
UTL_ENCODE

To call the different functionalities from PL/pgSQL, use the PERFORM statement or SELECT statement, using the package name to qualify the name of the functionality. Refer to the explanations for each of the package functionalities for information on the format for calling.

F.55.6.1. DBMS_ALERT

Overview

The DBMS_ALERT package sends alerts from a PL/pgSQL session to multiple other PL/pgSQL sessions.

This package can be used when processing 1:N, such as when notifying alerts from a given PL/pgSQL session to another PL/pgSQL session at the same time.

Table F.71. DBMS_ALERT Features

Feature	Description
REGISTER	Registers the specified alert.
REMOVE	Removes the specified alert.
REMOVEALL	Removes all alerts from a session.
SIGNAL	Notifies alerts.
WAITANY	Waits for notification of any alerts for which a session is registered.
WAITONE	Waits for notification of a specific alert for which a session is registered.

Syntax

F.55.6.1.1. Description of Features

This section explains each feature of DBMS_ALERT.

REGISTER

REGISTER registers the specified alert to a session. By registering alerts to a session, SIGNAL notifications can be received.
Specify the name of the alert.
Alerts are case-sensitive.
Multiple alerts can be registered within a single session. If registering multiple alerts, call REGISTER for each alert.

Example

     PERFORM DBMS_ALERT.REGISTER('sample_alert');

REMOVE

REMOVE removes the specified alert from a session.
Specify the name of the alert.
Alerts are case-sensitive.
The message left by the alert will be removed.

Example

     PERFORM DBMS_ALERT.REMOVE('sample_alert');

REMOVEALL

REMOVEALL removes all alerts registered within a session.
All messages left by the alerts will be removed.

Example

     PERFORM DBMS_ALERT.REMOVEALL();

SIGNAL

SIGNAL sends a message notification for the specified alert.
Specify the name of the alert for which message notifications are sent.
Alerts are case-sensitive.
In the message, specify the alert message for notifications.
Message notifications are not complete at the stage when SIGNAL is executed. Message notifications are sent upon committing the transaction. Message notifications are discarded if a rollback is performed after SIGNAL is executed.
If message notifications are sent for the same alert from multiple sessions, the messages will be accumulated without being removed.

Example

     PERFORM DBMS_ALERT.SIGNAL('ALERT001','message001');

Note

If SIGNAL is issued continuously and the accumulated messages exceed a certain amount, an insufficient memory error may be output. If the memory becomes insufficient, call AITANY or WAITONE to receive an alert, and reduce the accumulated messages.

WAITANY

WAITANY waits for notification of any alerts registered for a session.
Specify the maximum wait time timeout in seconds to wait for an alert.
Use a SELECT statement to obtain the notified information, which is stored in the name, message and status columns.
The name column stores the alert names. The data type of name is TEXT.
The message column stores the messages of notified alerts. The data type of message is TEXT.
The status column stores the status code returned by the operation: 0-an alert occurred; 1-a timeout occurred. The data type of status is INTEGER.

Example

     DECLARE
         alert_name         TEXT := 'sample_alert';
         alert_message      TEXT;
         alert_status       INTEGER;
     BEGIN
         SELECT name,message,status INTO alert_name,alert_message,alert_status FROM DBMS_ALERT.WAITANY(60);

WAITONE

WAITONE waits for notification of the specified alert.
Specify the name of the alert to wait for.
Alerts are case-sensitive.
Specify the maximum wait time timeout in seconds to wait for the alert.
Use a SELECT statement to obtain the notified information, which is stored in the message and status columns.
The message column stores the messages of notified alerts. The data type of message is TEXT.
The status column stores the status code returned by the operation: 0-an alert occurred; 1-a timeout occurred. The data type of status is INTEGER.

Example

     DECLARE
         alert_message   TEXT;
         alert_status    INTEGER;
     BEGIN
         SELECT message,status INTO alert_message,alert_status FROM DBMS_ALERT.WAITONE('sample_alert', 60);

F.55.6.1.2. Usage Example

Below is a usage example of the processing flow of DBMS_ALERT.

DBMS_ALERT flow

Note

The target of message notifications by SIGNAL is sessions for which REGISTER is executed at the time of executing SIGNAL.
On the receiving side, always ensure that REMOVE or REMOVEALL is used to remove alerts as soon as the alerts are no longer needed. If a session is closed without removing the alerts, it may no longer be possible to receive a SIGNAL for alerts of the same name in another session.
DBMS_ALERT and DBMS_PIPE use the same memory environment. Therefore, when insufficient memory is detected for DBMS_PIPE, it is possible that insufficient memory will also be detected for DBMS_ALERT.

Usage example

Sending side

     CREATE FUNCTION send_dbms_alert_exe() RETURNS VOID AS $$
     BEGIN
         PERFORM DBMS_ALERT.SIGNAL('sample_alert','SIGNAL ALERT');
     END;
     $$ LANGUAGE plpgsql;
     SELECT send_dbms_alert_exe();
     DROP FUNCTION send_dbms_alert_exe();

Receiving side

     CREATE FUNCTION receive_dbms_alert_exe() RETURNS VOID AS $$
     DECLARE
         alert_name    TEXT := 'sample_alert';
     	alert_message TEXT;
     	alert_status  INTEGER;
     BEGIN
     	PERFORM DBMS_ALERT.REGISTER(alert_name);
     	SELECT message,status INTO alert_message,alert_status FROM DBMS_ALERT.WAITONE(alert_name,300);
     	RAISE NOTICE 'Message : %', alert_message;
     	RAISE NOTICE 'Status  : %', alert_status;
     	PERFORM DBMS_ALERT.REMOVE(alert_name);
     END;
     $$ LANGUAGE plpgsql;
     SELECT receive_dbms_alert_exe();
     DROP FUNCTION receive_dbms_alert_exe();

F.55.6.2. DBMS_ASSERT

Overview

Performs verification of the properties of input values in PL/pgSQL.

Table F.72. DBMS_ASSERT Features

Feature	Description
ENQUOTE_LITERAL	Returns the specified string enclosed in single quotation marks.
ENQUOTE_NAME	Returns the specified string enclosed in double quotation marks.
NOOP	Returns the specified string as is.
OBJECT_NAME	Verifies if the specified string is a defined identifier.
QUALIFIED_SQL_NAME	Verifies if the specified string is in the appropriate format as an identifier.
SCHEMA_NAME	Verifies if the specified string is a defined schema.
SIMPLE_SQL_NAME	Verifies if the specified string is in the appropriate format as a single identifier.

Syntax

F.55.6.2.1. Description of Features

This section explains each feature of DBMS_ASSERT.

ENQUOTE_LITERAL

ENQUOTE_LITERAL returns the specified string enclosed in single quotation marks.
Specify a string enclosed in single quotation marks.
The data type of the return value is VARCHAR.

Example

     DECLARE
         q_literal    VARCHAR(256);
     BEGIN
         q_literal := DBMS_ASSERT.ENQUOTE_LITERAL('literal_word');

ENQUOTE_NAME

ENQUOTE_NAME returns the specified string enclosed in double quotation marks.
Specify a string enclosed in double quotation marks.
For lowercase conversion, specify TRUE or FALSE. Specify TRUE to convert uppercase characters in the string to lowercase. If FALSE is specified, conversion to lowercase will not take place. The default is TRUE.
If all the characters in the string are lowercase, they will not be enclosed in double quotation marks.
The data type of the return value is VARCHAR.

See

Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.

Example

     DECLARE
         dq_literal    VARCHAR(256);
     BEGIN
         dq_literal := DBMS_ASSERT.ENQUOTE_NAME('TBL001');

NOOP

NOOP returns the specified string as is.
Specify a string.
The data type of the return value is VARCHAR.

Example

     DECLARE
         literal    VARCHAR(256);
     BEGIN
         literal := DBMS_ASSERT.NOOP('NOOP_WORD');

OBJECT_NAME

OBJECT_NAME verifies if the specified string is a defined identifier.
Specify the identifier for verification. If the identifier has been defined, the specified identifier will be returned. Otherwise, the following error will occur.

     ERROR:  invalid object name

The data type of the return value is VARCHAR.

Example

     DECLARE
         object_name    VARCHAR(256);
     BEGIN
         object_name := DBMS_ASSERT.OBJECT_NAME('SCM001.TBL001');

QUALIFIED_SQL_NAME

QUALIFIED_SQL_NAME verifies if the specified string is in the appropriate format as an identifier.
Specify the identifier for verification. If the string can be used as an identifier, the specified identifier will be returned. Otherwise, the following error will occur.

     ERROR:  string is not qualified SQL name

The data type of the return value is VARCHAR.

See

Refer to "The SQL Language" > "Lexical Structure" > "Identifiers and Key Words" in the LightDB Documentation for information on the formats that can be used as identifiers.

Example

     DECLARE
         object_name    VARCHAR(256);
     BEGIN
         object_name := DBMS_ASSERT.QUALIFIED_SQL_NAME('SCM002.TBL001');

SCHEMA_NAME

SCHEMA_NAME verifies if the specified string is a defined schema.
Specify a schema name for verification. If the schema has been defined, the specified schema name will be returned. Otherwise, the following error will occur.

     ERROR:  invalid schema name

The data type of the return value is VARCHAR.

Example

     DECLARE
         schema_name    VARCHAR(256);
     BEGIN
         schema_name := DBMS_ASSERT.SCHEMA_NAME('SCM001');

SIMPLE_SQL_NAME

SIMPLE_SQL_NAME verifies if the specified string is in the appropriate format as a single identifier.
Specify an identifier for verification. If the specified string can be used as an identifier, the specified identifier will be returned. Otherwise, the following error will occur.

     ERROR:  string is not qualified SQL name

The data type of the return value is VARCHAR.

See

Refer to "The SQL Language" > "Lexical Structure" > "Identifiers and Key Words" in the LightDB Documentation for information on the formats that can be used as identifiers. Note that an error will occur if an identifier using fullwidth characters is specified. If fullwidth characters are included, specify a quoted identifier.

Example

     DECLARE
         simple_name    VARCHAR(256);
     BEGIN
         simple_name := DBMS_ASSERT.SIMPLE_SQL_NAME('COL01');

F.55.6.2.2. Usage Example

A usage example of DBMS_ASSERT is shown below.

     CREATE FUNCTION dbms_assert_exe() RETURNS VOID AS $$
     DECLARE
     	w_schema VARCHAR(20) := 'public';
     	w_table  VARCHAR(20) := 'T1';
     	w_object VARCHAR(40);
     BEGIN
     	PERFORM DBMS_ASSERT.NOOP(w_schema);
     	PERFORM DBMS_ASSERT.SIMPLE_SQL_NAME(w_table);
     	PERFORM DBMS_ASSERT.SCHEMA_NAME(w_schema);
     	w_object := w_schema || '.' || w_table;
     	PERFORM DBMS_ASSERT.QUALIFIED_SQL_NAME(w_object);
     	PERFORM DBMS_ASSERT.OBJECT_NAME(w_object);
     	RAISE NOTICE 'OBJECT     : %', DBMS_ASSERT.ENQUOTE_LITERAL(w_object);
     	RAISE NOTICE 'TABLE_NAME : %', DBMS_ASSERT.ENQUOTE_NAME(w_table);
     END;
     $$
     LANGUAGE plpgsql;
     SELECT dbms_assert_exe();
     DROP FUNCTION dbms_assert_exe();

F.55.6.3. DBMS_LOB

Overview

Some functions compatible with DBMS_LOB package.

Table F.73. DBMS_LOB Features

Feature	Description
INSTR	Returns starting index of specified LOB.
SUBSTR	Extracts the substring of LOB.
GETLENGTH	Returns the number of characters in the LOB.
APPEND	Appends the contents of the source LOB to the destination LOB.
CLOSE	Closes a previously opened internal or external LOB.(invalid)
COMPARE	Compares two entire LOBs or parts of two LOBs.
COPY	Copies all, or part, of the source LOB to the destination LOB.
CREATETEMPORAR	Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace.(invalid)
ERASE	Erases all or part of a LOB.
FREETEMPORARY	Erases all or part of a LOB.
OPEN	Frees the temporary BLOB or CLOB in the default temporary tablespace.(invalid)
READ	Reads data from the LOB starting at the specified offset.
TRIM	Trims the LOB value to the specified shorter length.
WRITE	Writes data to the LOB from a specified offset.
WRITEAPPEND	Writes a buffer to the end of a LOB.
COMPARE	Compares two entire LOBs or parts of two LOBs.
COMPARE	Compares two entire LOBs or parts of two LOBs.
COMPARE	Compares two entire LOBs or parts of two LOBs.

Syntax

     INSTR(STR TEXT, PATT TEXT, START INTEGER DEFAULT  1, NTH INTEGER DEFAULT 1) RETURNS INTEGER
     INSTR(STR BLOB, PATT TEXT, START INTEGER DEFAULT  1, NTH INTEGER DEFAULT 1) RETURNS INTEGER
     SUBSTR(STR TEXT, AMOUNT INTEGER DEFAULT 32767, START_OFFSET INTEGER DEFAULT 1) RETURNS TEXT
     SUBSTR(STR BLOB, AMOUNT INTEGER DEFAULT 32767, START_OFFSET INTEGER DEFAULT 1) RETURNS TEXT
     GETLENGTH(STR TEXT) RETURNS INTEGER
     GETLENGTH(STR BLOB) RETURNS INTEGER 
     APPEND(dest_lob BLOB,src_lob BLOB)/APPEND(CLOB,CLOB) 
     COMPARE(lob_1 BLOB,lob_2 BLOB,amount INT4 DEFAULT 2147483647,offset_1 INT4 DEFAULT 1,offset_2 INT4 DEFAULT 1)/COMPARE(CLOB,CLOB,INT4 DEFAULT 2147483647,INT4 DEFAULT 1,INT4 DEFAULT 1)
     COPY(dest_lob BLOB,src_lob BLOB,amount INT4,dest_offset INT4 DEFAULT 1,src_offset INT4 DEFAULT 1)/COPY(CLOB,CLOB,INT4,INT4 DEFAULT 1,INT4 DEFAULT 1)
     ERASE(lob_loc BLOB,amount INT4,p_offset INT4 DEFAULT 1)/ERASE(CLOB,INT4,INT4 DEFAULT 1)
     READ(lob_loc BLOB,amount INT4,p_offset INT4,buffer RAW)/READ(CLOB,INT4,INT4,TEXT)
     TRIM(lob_loc BLOB,newlen INT4)/TRIM(CLOB,INT4)
     WRITE(lob_loc BLOB,amount INT4,p_offset INT4,buffer RAW)/WRITE(CLOB,INT4,INT4,TEXT)
     WRITEAPPEND(lob_loc BLOB,amount INT4,buffer RAW)/WRITEAPPEND(CLOB,INT4,TEXT)

Features

F.55.6.3.1. Description

This section explains each feature of DBMS_LOB.

INSTR

Offset of the start of the matched pattern, in bytes or characters.It returns 0 if the pattern is not found.
It retruns null if -any one or more of the IN parameters was NULL or INVALID.
It returns NULL if offset < 1.

Example

     select dbms_lob.INSTR('Thomas', 'om') from  dual;
     select dbms_lob.INSTR('Thomas'::blob, utl_raw.CAST_TO_RAW('om')) from  dual;

SUBSTR

Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified.
It returns NULL if any input parameter is NULL
It returns NULL if amount < 1.
It returns NULL if offset < 1.

Example

     select dbms_lob.substr('1234567',1,2) from  dual;
     select dbms_lob.substr('1234567'::blob,1,2) from  dual;

GETLENGTH

The length of the text in bytes or characters as an INTEGER.
NULL is returned if the input str is NULL.

Example

     select DBMS_LOB.GETLENGTH('abc') from dual;

APPEND

Appends the contents of the source LOB to the destination LOB.

Example

declare
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
begin
    dbms_output.serveroutput(true);
    dbms_lob.append(l_blob, 'ext'::blob);
    dbms_output.put_line(l_blob::text);

    dbms_lob.append(l_clob, 'ext'::clob);
    dbms_output.put_line(l_clob);
end;
/

COMPARE

Compares two entire LOBs or parts of two LOBs.
0 if the comparison succeeds, nonzero if not.
If any of amount, offset_1 or offset_2 is not a valid LOB offset value. A valid offset is within the range of 1 to LOBMAXSIZE inclusive.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);
    l_result := dbms_lob.compare('abcd'::blob, 'abcd'::blob);
    dbms_output.put_line(l_result);
    l_result := dbms_lob.compare('abcd'::clob, 'abcd'::clob);
    dbms_output.put_line(l_result);

end;
/

COPY

Copies all, or part, of the source LOB to the destination LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes or characters to copy.
If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, then zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, then existing data is overwritten.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);
    dbms_lob.copy(l_blob, 'new_blob'::blob, 100);
    dbms_output.put_line(l_blob::text);
    dbms_lob.copy(l_blob, 'xnew_blob'::blob, 100, 1, 2);
    dbms_output.put_line(l_blob::text);
    dbms_lob.copy(l_blob, 'xnew_blob'::blob, 100, 2, 2);
    dbms_output.put_line(l_blob::text);
end;
/

ERASE

Erases an entire internal LOB or part of an internal LOB.
When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for BLOBs or CLOBs respectively.
The actual number of bytes or characters erased can differ from the number you specified in the amount parameter if the end of the LOB value is reached before erasing the specified number. The actual number of characters or bytes erased is returned in the amount parameter.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);

    l_blob := 'abcd'::blob;
    l_amout := 100;
    dbms_output.put_line(l_blob::text);
    dbms_lob.erase(l_blob, l_amout);
    dbms_output.put_line(l_blob::text);

    l_clob := 'abcd'::clob;
    l_amout := 4;
    dbms_lob.erase(l_clob, l_amout);
    dbms_output.put_line(l_clob);
end;
/

READ

This procedure reads a piece of a LOB, and returns the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.
The number of bytes or characters actually read is returned in the amount parameter. If the input offset points past the End of LOB, then amount is set to 0, and a NO_DATA_FOUND exception is raised.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);

    l_amout := 5;
    dbms_lob.read('abcde'::blob, l_amout, 1, l_raw_result);
    dbms_output.put_line(l_raw_result);

    l_amout := 5;
    dbms_lob.read('abcde'::clob, l_amout, 1, l_text_result);
    dbms_output.put_line(l_text_result);
end;
/

TRIM

This procedure trims the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and specify the length in characters for CLOBs.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);

    l_blob := 'abcde'::blob;
    dbms_lob.trim(l_blob, 1);
    dbms_output.put_line(l_blob::text);

    l_clob := 'abcde'::clob;
    dbms_lob.trim(l_clob, 1);
    dbms_output.put_line(l_clob);
end;
/

WRITE

This procedure writes a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter.
WRITE replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);

    l_blob := 'abcde'::blob;
    dbms_lob.write(l_blob, pg_catalog.length(utl_raw.CAST_TO_RAW('测试')), 2, utl_raw.CAST_TO_RAW('测试'));
    dbms_output.put_line(l_blob::text);

    -- write clob
    l_clob := 'abcde'::clob;
    dbms_lob.write(l_clob, 2, 2,'测试'::text);
    dbms_output.put_line(l_clob);
end;
/

WRITEAPPEND

This procedure writes a specified amount of data to the end of an internal LOB. The data is written from the buffer parameter.

Example

declare
    l_result        int;
    l_blob          blob := 'abcd'::blob;
    l_clob          clob := 'abcd'::clob;
    l_raw_result    raw;
    l_amout         INTEGER := 100;
    l_text_result  text;
begin
    dbms_output.serveroutput(true);

    -- writeappend blob
    l_blob := 'abcde'::blob;
    dbms_lob.writeappend(l_blob, pg_catalog.length(utl_raw.CAST_TO_RAW('测试')), utl_raw.CAST_TO_RAW('测试'));
    dbms_output.put_line(l_blob::text);

    -- write clob
    l_clob := 'abcde'::clob;
    dbms_lob.writeappend(l_clob, 2,'测试'::text);
    dbms_output.put_line(l_clob);
end;
/

F.55.6.3.2. Usage Example

A usage example of DBMS_LOB is shown below.

     CREATE PROCEDURE dbms_lob_exe() AS $$
     DECLARE
     	str1 text;
        str2 text;
     	pos INTEGER;
        count INTEGER;
     BEGIN
     	str2 := dbms_lob.substr('1234567',2,3);
        pos := dbms_lob.instr('Tech on the net', 'e');
        count := dbms_lob.getlength('1234567');
        raise info 'str2=%,pos=%,count=%', str2, pos, count;
     END;
     $$ LANGUAGE plpgsql;
     call dbms_lob_exe();
     DROP PROCEDURE dbms_lob_exe();

F.55.6.4. DBMS_OUTPUT

Overview

Sends messages to clients such as ltsql from PL/pgSQL.

Features

Table F.74. DBMS_OUTPUT Features

Feature	Description
ENABLE	Enables features of this package.
DISABLE	Disables features of this package.
SERVEROUTPUT	Controls whether messages are sent.
PUT	Sends messages.
PUT_LINE	Sends messages with a newline character appended.
NEW_LINE	Sends a newline character.
GET_LINE	Retrieves a line from the message buffer.
GET_LINES	Retrieves multiple lines from the message buffer.

Syntax

F.55.6.4.1. Description

This section explains each feature of DBMS_OUTPUT.

ENABLE

ENABLE enables the use of PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES.
With multiple executions of ENABLE, the value specified last is the buffer size (in bytes). Specify a buffer size from 2000 to 1000000.
The default value of the buffer size is 20000. If NULL is specified as the buffer size, 1000000 will be used.
If ENABLE has not been executed, PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES are ignored even if they are executed.

Example

     PERFORM DBMS_OUTPUT.ENABLE(20000);

DISABLE

DISABLE disables the use of PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES.
Remaining buffer information is discarded.

Example

     PERFORM DBMS_OUTPUT.DISABLE();

SERVEROUTPUT

SERVEROUTPUT controls whether messages are sent.
Specify TRUE or FALSE for sendMsgs.
If TRUE is specified, when PUT, PUT_LINE, or NEW_LINE is executed, the message is sent to a client such as ltsql and not stored in the buffer.
If FALSE is specified, when PUT, PUT_LINE, or NEW_LINE is executed, the message is stored in the buffer and not sent to a client such as ltsql.

See

Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.

Example

     PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);

PUT

PUT sets the message to be sent.
The string is the message to be sent.
When TRUE is specified for SERVEROUTPUT, the messages are sent to clients such as ltsql.
When FALSE is specified for SERVEROUTPUT, the messages are retained in the buffer.
PUT does not append a newline character. To append a newline character, execute NEW_LINE.
If a string longer than the buffer size specified in ENABLE is sent, an error occurs.

Example

     PERFORM DBMS_OUTPUT.PUT('abc');

PUT_LINE

PUT_LINE sets the message to be sent appended with a newline character.
The string is the message to be sent.
When TRUE is specified for SERVEROUTPUT, the messages are sent to clients such as ltsql.
When FALSE is specified for SERVEROUTPUT, the messages are retained in the buffer.
If a string longer than the buffer size specified in ENABLE is sent, an error occurs.

Example

     PERFORM DBMS_OUTPUT.PUT_LINE('abc');

NEW_LINE

NEW_LINE appends a newline character to the message created with PUT.
When TRUE is specified for SERVEROUTPUT, the messages are sent to clients such as ltsql.
When FALSE is specified for SERVEROUTPUT, the messages are retained in the buffer.

Example

     PERFORM DBMS_OUTPUT.NEW_LINE();

GET_LINE

GET_LINE retrieves a line from the message buffer.
Use a SELECT statement to obtain the retrieved line and status code returned by the operation, which are stored in the line and status columns.
The line column stores the line retrieved from the buffer. The data type of line is TEXT.
The status column stores the status code returned by the operation: 0-completed successfully; 1-failed because there are no more lines in the buffer. The data type of status is INTEGER.
If GET_LINE or GET_LINES is executed and then PUT, PUT_LINE or PUT_LINES is executed while messages that have not been retrieved from the buffer still exist, the messages not retrieved from the buffer will be discarded.

Example

     DECLARE
         buff1   VARCHAR(20);
         stts1   INTEGER;
     BEGIN
         SELECT line,status INTO buff1,stts1 FROM DBMS_OUTPUT.GET_LINE();

GET_LINES

GET_LINES retrieves multiple lines from the message buffer.
Specify the number of lines to retrieve from the buffer.
Use a SELECT statement to obtain the retrieved lines and the number of lines retrieved, which are stored in the lines and numlines columns.
The lines column stores the lines retrieved from the buffer. The data type of lines is TEXT.
The numlines column stores the number of lines retrieved from the buffer. If this number is less than the number of lines requested, then there are no more lines in the buffer. The data type of numlines is INTEGER.
If GET_LINE or GET_LINES is executed and then PUT, PUT_LINE, or NEW_LINE is executed while messages that have not been retrieved from the buffer still exist, the messages not retrieved from the buffer will be discarded.

Example

     DECLARE
         buff    VARCHAR(20)[10];
         stts    INTEGER := 10;
     BEGIN
         SELECT lines, numlines INTO buff,stts FROM DBMS_OUTPUT.GET_LINES(stts);

F.55.6.4.2. Usage Example

A usage example of DBMS_OUTPUT is shown below.

     CREATE FUNCTION dbms_output_exe() RETURNS VOID AS $$
     DECLARE
     	buff1 VARCHAR(20);
     	buff2 VARCHAR(20);
     	stts1 INTEGER;
     	stts2 INTEGER;
     BEGIN
     	PERFORM DBMS_OUTPUT.DISABLE();
     	PERFORM DBMS_OUTPUT.ENABLE();
     	PERFORM DBMS_OUTPUT.SERVEROUTPUT(FALSE);
     	PERFORM DBMS_OUTPUT.PUT('DBMS_OUTPUT TEST 1');
     	PERFORM DBMS_OUTPUT.NEW_LINE();
     	PERFORM DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT TEST 2');
     	SELECT line,status INTO buff1,stts1 FROM DBMS_OUTPUT.GET_LINE();
     	SELECT line,status INTO buff2,stts2 FROM DBMS_OUTPUT.GET_LINE();
     	PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
     	PERFORM DBMS_OUTPUT.PUT_LINE(buff1);
     	PERFORM DBMS_OUTPUT.PUT_LINE(buff2);
     END;
     $$ LANGUAGE plpgsql;
     SELECT dbms_output_exe();
     DROP FUNCTION dbms_output_exe();

F.55.6.5. DBMS_PIPE

Overview

Performs communication between sessions that execute PL/pgSQL.

This package can be used for 1:1 communication, such as when data is being exchanged between sessions executing PL/pgSQL.

For pipes, there are explicit pipes and implicit pipes, and furthermore, for explicit pipes, you can select public pipes and private pipes. The characteristics of each type are as follows:

Types of pipes

Table F.75. Types of pipes

Type	Characteristics
Explicit pipe	- CREATE_PIPE is used to create a pipe explicitly. - While creating a pipe, you can select between a public pipe and private pipe. - It is necessary to use REMOVE_PIPE to explicitly remove a pipe.
Implicit pipe	- Created automatically when SEND_MESSAGE and RECEIVE_MESSAGE are used. - The pipe that is created becomes a public pipe. - When messages are received using RECEIVE_MESSAGE, if there are no additional messages remaining in the pipe, the pipe will be removed automatically.
Public pipe	- Can be created as an explicit pipe or implicit pipe. - Can also be used by users other than the creator.
Private pipe	- Can only be created as an explicit pipe. - Can only be used by its creator.

Note

Up to 50 pipes can be used concurrently by a single instance.
In cases where pipes are frequently created and removed repetitively, use public pipes. If you create a private pipe, internal information (the creator of the private pipe) will remain even after the pipe is removed. Thus, repeatedly creating and removing pipes may ultimately cause memory to run out.
If a timeout occurs without receiving a message when an implicit pipe is created by RECEIVE_MESSAGE, the pipe will not be removed.

Features

Table F.76. DBMS_PIPE Features

Feature	Description
CREATE_PIPE	Creates a public or private pipe.
NEXT_ITEM_TYPE	Determines the data type of the next item in the local buffer, and returns that type.
PACK_MESSAGE	Sets a message in the local buffer.
PURGE	Empties the contents of the specified pipe.
RECEIVE_MESSAGE	Sets a received message in the local buffer.
REMOVE_PIPE	Removes the specified pipe.
RESET_BUFFER	Resets the set position of the local buffer.
SEND_MESSAGE	Sends the contents of the local buffer.
UNIQUE_SESSION_NAME	Returns a unique session name.
UNPACK_MESSAGE_BYTEA	Receives a message in the local buffer in BYTEA type.
UNPACK_MESSAGE_DATE	Receives a message in the local buffer in DATE type.
UNPACK_MESSAGE_NUMBER	Receives a message in the local buffer in NUMERIC type.
UNPACK_MESSAGE_RECORD	Receives a message in the local buffer in RECORD type.
UNPACK_MESSAGE_TEXT	Receives a message in the local buffer in TEXT type.
UNPACK_MESSAGE_TIMESTAMP	Receives a message in the local buffer in TIMESTAMP type.

Syntax

F.55.6.5.1. Description of Features

This section explains each feature of DBMS_PIPE.

CREATE_PIPE

CREATE_PIPE explicitly creates a pipe environment for data communication.
Specify the name of the pipe to be created.
Pipe names are case-sensitive.
Specify the maximum number of messages that can be sent or received. If omitted, 0 (cannot send messages) will be used. Specify from 1 to 32767.
Specify TRUE or FALSE for private. If TRUE is specified, a private pipe will be created. If FALSE is specified, a public pipe will be created. The default is FALSE.
An error will occur if a pipe of the same name has already been created.

See

Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.

Example

     PERFORM DBMS_PIPE.CREATE_PIPE('P01', 100, FALSE);

NEXT_ITEM_TYPE

NEXT_ITEM_TYPE returns the next data type in the local buffer.
The data type of the return value is INTEGER. One of the following values is returned:

Table F.77. Values returned by NEXT_ITEM_TYPE

Return value	Data type
9	NUMERIC type
11	TEXT type
12	DATE type
13	TIMESTAMP type
23	BYTEA type
24	RECORD type
0	No data in the buffer

Example

     DECLARE
         i_iType    INTEGER;
     BEGIN
         i_iType := DBMS_PIPE.NEXT_ITEM_TYPE();

PACK_MESSAGE

PACK_MESSAGE sets the specified message in the local buffer.
Specify the data to be set in the local buffer. The following data types can be used:
- Character type (*1)
- Integer type (*2)
- NUMERIC type
- DATE type
- TIMESTAMP type (*3)
- BYTEA type
- RECORD type

*1: The character type is converted internally to TEXT type.

*2: The integer type is converted internally to NUMERIC type.

*3: The TIMESTAMP type is converted internally to TIMESTAMP WITH TIME ZONE type.

Each time PACK_MESSAGE is called, a new message is added to the local buffer.
The size of the local buffer is approximately 8 KB. However, each message has overhead, so the total size that can be stored is actually less than 8 KB. To clear the local buffer, send a message (SEND_MESSAGE), or reset the buffer (RESET_BUFFER) to its initial state.

Example

     PERFORM DBMS_PIPE.PACK_MESSAGE('Message Test001');

PURGE

PURGE removes the messages in the pipe.
Specify the name of the pipe for which the messages are to be removed.
Pipe names are case-sensitive.

Example

     PERFORM DBMS_PIPE.PURGE('P01');

Note

When PURGE is executed, the local buffer is used to remove the messages in the pipe. Therefore, if there are any messages remaining in the pipe, the local buffer will be overwritten by PURGE.

RECEIVE_MESSAGE

RECEIVE_MESSAGE receives messages that exist in the specified pipe, and sets those messages in the local buffer.
Messages are received in the units in which they are sent to the pipe by SEND_MESSAGE. Received messages are removed from the pipe after being set in the local buffer.
Specify the name of the pipe for which the messages are to be received.
Pipe names are case-sensitive.
Specify the maximum wait time timeout in seconds to wait for a message. If omitted, the default is 31536000 seconds (1 year).
The data type of the return value is INTEGER. If a message is received successfully, 0 is returned. If a timeout occurs, 1 is returned.

Example

     DECLARE
         i_Ret    INTEGER;
     BEGIN
         i_Ret := DBMS_PIPE.RECEIVE_MESSAGE('P01', 60);

REMOVE_PIPE

REMOVE_PIPE removes the specified pipe.
Specify the name of the pipe to be removed.
Pipe names are case-sensitive.

Example

     PERFORM DBMS_PIPE.REMOVE_PIPE('P01');

RESET_BUFFER

RESET_BUFFER resets the set position of the local buffer. Any unnecessary data remaining in the local buffer can be discarded using this operation.

Example

     PERFORM DBMS_PIPE.RESET_BUFFER();

SEND_MESSAGE

SEND_MESSAGE sends data stored in the local buffer to the specified pipe.
Specify the name of the pipe that the data is to be sent to.
Pipe names are case-sensitive.
Specify the maximum wait time timeout in seconds for sending data stored in the local buffer. If omitted, the default is 31536000 seconds (1 year).
Specify the maximum number of messages that can be sent or received. If omitted, the maximum number of messages set in CREATE_PIPE is used. If omitted in the implicit pipe, the number of messages will be unlimited. Specify from 1 to 32767.
If the maximum number of messages is specified in both SEND_MESSAGE and CREATE_PIPE, the larger of the values will be used.
The data type of the return value is INTEGER. If a message is received successfully, 0 is returned. If a timeout occurs, 1 is returned.

Example

     DECLARE
         i_Ret    INTEGER;
     BEGIN
         i_Ret := DBMS_PIPE.SEND_MESSAGE('P01', 10, 20);

Note

A timeout will occur during sending if the maximum number of messages is reached, or if the message being sent is too large. If a timeout occurs, use RECEIVE_MESSAGE to receive any messages that are in the pipe.

UNIQUE_SESSION_NAME

UNIQUE_SESSION_NAME returns a name that is unique among all the sessions. This name can be used as the pipe name.
Multiple calls from the same session always return the same name.
The data type of the return value is VARCHAR. Returns a string of up to 30 characters.

Example

     DECLARE
         p_Name   VARCHAR(30);
     BEGIN
         p_Name := DBMS_PIPE.UNIQUE_SESSION_NAME();

UNPACK_MESSAGE_BYTEA

NPACK_MESSAGE_BYTEA receives BTYEA type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is BYTEA.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.

     ERROR:  datatype mismatch
     DETAIL:  unpack unexpected type: xx

Example

     DECLARE
         g_Bytea   BYTEA;
     BEGIN
         g_Bytea := DBMS_PIPE.UNPACK_MESSAGE_BYTEA();

UNPACK_MESSAGE_DATE

UNPACK_MESSAGE_DATE receives DATE type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is DATE.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.

     ERROR:  datatype mismatch
     DETAIL:  unpack unexpected type: xx

Example

     DECLARE
         g_Date   DATE;
     BEGIN
         g_Date := DBMS_PIPE.UNPACK_MESSAGE_DATE();

Note

If the "oracle" schema is set in search_path, the DATE type of orafce will be used, so for receiving data, use UNPACK_MESSAGE_TIMESTAMP. UNPACK_MESSAGE_DATE is the interface for the DATE type of LightDB.

UNPACK_MESSAGE_NUMBER

UNPACK_MESSAGE_NUMBER receives NUMERIC type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is NUMERIC.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.

     ERROR:  datatype mismatch
     DETAIL:  unpack unexpected type: xx

Example

     DECLARE
         g_Number   NUMERIC;
     BEGIN
         g_Number := DBMS_PIPE.UNPACK_MESSAGE_NUMBER();

UNPACK_MESSAGE_RECORD

UNPACK_MESSAGE_RECORD receives RECORD type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is RECORD.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.

     ERROR:  datatype mismatch
     DETAIL:  unpack unexpected type: xx

Example

     DECLARE
         msg1     TEXT;
         status   NUMERIC;
     BEGIN
         SELECT col1, col2 INTO msg1, status FROM DBMS_PIPE.UNPACK_MESSAGE_RECORD();

UNPACK_MESSAGE_TEXT

UNPACK_MESSAGE_TEXT receives TEXT type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is TEXT.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.

     ERROR:  datatype mismatch
     DETAIL:  unpack unexpected type: xx

Example

     DECLARE
         g_Text   TEXT;
     BEGIN
         g_Text := DBMS_PIPE.UNPACK_MESSAGE_TEXT();

UNPACK_MESSAGE_TIMESTAMP

UNPACK_MESSAGE_TIMESTAMP receives TIMESTAMP WITH TIME ZONE type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is TIMESTAMP WITH TIME ZONE.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.

     ERROR:  datatype mismatch
     DETAIL:  unpack unexpected type: xx

Example

     DECLARE
         g_Timestamptz   TIMESTAMP WITH TIME ZONE;
     BEGIN
         g_Timestamptz := DBMS_PIPE.UNPACK_MESSAGE_TIMESTAMP();

F.55.6.5.2. Usage Example

Below is a usage example of the processing flow of DBMS_PIPE.

Flow of DBMS_PIPE

Note

When CREATE_PIPE is used to explicitly create a pipe, ensure to use REMOVE_PIPE to remove the pipe. If a pipe is not removed explicitly, once created, it will remain until the instance is stopped.
In the flow diagram, CREATE_PIPE and REMOVE_PIPE are described on the receiving side, however, these can be executed on the sending side. In order to maintain consistency, it is recommended to create and remove pipes on one side.
An error will occur for CREATE_PIPE if a pipe of the same name already exists. Implicitly created pipes are also the target of SEND_MESSAGE and RECEIVE_MESSAGE, so when executing CREATE_PIPE, ensure that SEND_MESSAGE and RECEIVE_MESSAGE are not called beforehand.
DBMS_ALERT and DBMS_PIPE use the same memory environment. Therefore, when insufficient memory is detected for DBMS_ALERT, it is possible that insufficient memory will also be detected for DBMS_PIPE.

Information

The information of pipes that are in use can be viewed in the DBMS_PIPE.DB_PIPES view.

     SELECT * from dbms_pipe.db_pipes;
      name | items | size | limit | private | owner
     ------+-------+------+-------+---------+-------
      P01  |     1 |   18 |   100 | f       |
     (1 row)

Usage example

Sending side

     CREATE FUNCTION send_dbms_pipe_exe(IN pipe_mess text) RETURNS void AS $$
     DECLARE
     	pipe_name text := 'sample_pipe';
    	 pipe_time timestamp := current_timestamp;
     	pipe_stat int;
     BEGIN
     	PERFORM DBMS_PIPE.RESET_BUFFER();
     	PERFORM DBMS_PIPE.PACK_MESSAGE(pipe_mess);
     	PERFORM DBMS_PIPE.PACK_MESSAGE(pipe_time);
     	pipe_stat := DBMS_PIPE.SEND_MESSAGE(pipe_name);
     	RAISE NOTICE 'PIPE_NAME: % SEND Return Value =%', pipe_name, pipe_stat;
     END;
     $$ LANGUAGE plpgsql;

     SELECT send_dbms_pipe_exe('Sample Message.');
     DROP FUNCTION send_dbms_pipe_exe(text);

Receiving side

     CREATE FUNCTION receive_dbms_pipe_exe() RETURNS void AS $$
     DECLARE
     	pipe_name text := 'sample_pipe';
     	pipe_text text;
     	pipe_nume numeric;
     	pipe_date date;
     	pipe_time timestamp with time zone;
     	pipe_byte bytea;
     	pipe_reco record;
     	pipe_item int;
     	pipe_stat int;
     BEGIN
     	pipe_stat := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name,300);
     	RAISE NOTICE 'Return Value = %', pipe_stat;
         LOOP
     		pipe_item := DBMS_PIPE.NEXT_ITEM_TYPE();
     		RAISE NOTICE 'Next Item : %', pipe_item;
     		IF (pipe_item = 9) THEN
     		    pipe_nume := DBMS_PIPE.UNPACK_MESSAGE_NUMBER();
     		    RAISE NOTICE 'Get Message : %' ,pipe_nume;
     		ELSIF (pipe_item =11) THEN
     		    pipe_text := DBMS_PIPE.UNPACK_MESSAGE_TEXT();
     		    RAISE NOTICE 'Get Message : %' ,pipe_text;
     		ELSIF (pipe_item = 12) THEN
     		    pipe_date := DBMS_PIPE.UNPACK_MESSAGE_DATE();
     		    RAISE NOTICE 'Get Message : %' ,pipe_date;
     		ELSIF (pipe_item = 13) THEN
    		     pipe_time := DBMS_PIPE.UNPACK_MESSAGE_TIMESTAMP();
     		    RAISE NOTICE 'Get Message : %' ,pipe_time;
     		ELSIF (pipe_item = 23) THEN
     		    pipe_byte := DBMS_PIPE.UNPACK_MESSAGE_BYTEA();
     		    RAISE NOTICE 'Get Message : %' ,pipe_byte;
     		ELSIF (pipe_item = 24) THEN
     		    pipe_reco := DBMS_PIPE.UNPACK_MESSAGE_RECORD();
     		    RAISE NOTICE 'Get Message : %' ,pipe_reco;
     		ELSE
     		    EXIT;
     		END IF;
         END LOOP;
         PERFORM DBMS_PIPE.REMOVE_PIPE(pipe_name);
     END;
     $$ LANGUAGE plpgsql;

     SELECT receive_dbms_pipe_exe();
     DROP FUNCTION receive_dbms_pipe_exe();

F.55.6.6. DBMS_RANDOM

Overview

Generates random numbers in PL/pgSQL.

Table F.78. DBMS_RANDOM Features

Feature	Description
INITIALIZE	Initializes the generation of random numbers.
NORMAL	Returns a normally distributed random number.
RANDOM	Generates a random number.
SEED	Resets the seed value.
STRING	Generates a random string.
TERMINATE	Terminates generation of random numbers.
VALUE	Generates a random decimal number between 0 and 1, or between specified values.

Syntax

F.55.6.6.1. Description of Features

This section explains each feature of DBMS_RANDOM.

INITIALIZE

INITIALIZE initializes the generation of random numbers using the specified seed value.
For seedVal, specify a SMALLINT or INTEGER type.

Example

     PERFORM DBMS_RANDOM.INITIALIZE(999);

NORMAL

NORMAL generates and returns a normally distributed random number.
The return value type is DOUBLE PRECISION.

Example

     DECLARE
         d_RunNum   DOUBLE PRECISION;
     BEGIN
         d_RunNum := DBMS_RANDOM.NORMAL();

RANDOM

RANDOM generates and returns a random number.
The data type of the return value is INTEGER.

Example

     DECLARE
         d_RunInt   INTEGER;
     BEGIN
         d_RunInt := DBMS_RANDOM.RANDOM();

SEED

SEED initializes the generation of a random number using the specified seed value or seed string.
For seedVal, specify a SMALLINT or INTEGER type.
Any string can be specified for the seed string.

Example

     PERFORM DBMS_RANDOM.SEED('123');

STRING

STRING generates and returns a random string in accordance with the specified display format and string length.
For the display format fmt, specify any of the following values. An error will occur if any other value is specified.

Table F.79. Values that can be specified for the display format

Setting value	Generated string
'u', 'U'	Uppercase letters only
'l', 'L'	Lowercase letters only
'a', 'A'	Mixture of uppercase and lowercase letters
'x', 'X'	Uppercase letters and numbers
'p', 'P'	Any displayable character

Specify the length of the string to be generated. Specify a SMALLINT or INTEGER type.
The data type of the return value is TEXT.

Example

     DECLARE
         d_RunStr   TEXT;
     BEGIN
         d_RunStr := DBMS_RANDOM.STRING('a', 20);

TERMINATE

Call TERMINATE to terminate generation of random numbers.

Information

TERMINATE does not do anything, but has been included for compatibility with Oracle databases.

Example

     PERFORM DBMS_RANDOM.TERMINATE();

VALUE

VALUE generates and returns a random number within the specified range.
For min and max, specify a numeric data type. A random number between and inclusive of the minimum value and maximum value is generated.
If the minimum value and maximum value are omitted, a random decimal number between 0 and 1 will be generated.
The data type of the return value is DOUBLE PRECISION.

Example

     DECLARE
         d_RunDbl   DOUBLE PRECISION;
     BEGIN
         d_RunDbl := DBMS_RANDOM.VALUE();

F.55.6.6.2. Usage Example

A usage example of DBMS_RANDOM is shown below.

     CREATE FUNCTION dbms_random_exe() RETURNS VOID AS $$
     DECLARE
         w_rkey VARCHAR(10) := 'rnd111';
         i_rkey INTEGER := 97310;
     BEGIN
         PERFORM DBMS_RANDOM.INITIALIZE(i_rkey);
         RAISE NOTICE 'RANDOM -> NORMAL : %', DBMS_RANDOM.NORMAL();
         RAISE NOTICE 'RANDOM -> RANDOM : %', DBMS_RANDOM.RANDOM();
         RAISE NOTICE 'RANDOM -> STRING : %', DBMS_RANDOM.STRING('a',10);
         RAISE NOTICE 'RANDOM -> VALUE  : %', DBMS_RANDOM.VALUE();
         PERFORM DBMS_RANDOM.SEED(w_rkey);
         RAISE NOTICE 'RANDOM -> NORMAL : %', DBMS_RANDOM.NORMAL();
         RAISE NOTICE 'RANDOM -> RANDOM : %', DBMS_RANDOM.RANDOM();
         RAISE NOTICE 'RANDOM -> STRING : %', DBMS_RANDOM.STRING('p',10);
         RAISE NOTICE 'RANDOM -> VALUE  : %', DBMS_RANDOM.VALUE(1,100);
         PERFORM DBMS_RANDOM.TERMINATE();
     END;
     $$ LANGUAGE plpgsql;
     SELECT dbms_random_exe();
     DROP FUNCTION dbms_random_exe();

F.55.6.7. DBMS_UTILITY

Overview

Provides utilities of PL/pgSQL.

Table F.80. DBMS_UTILITY Features

Feature	Description
FORMAT_CALL_STACK	Returns the current call stack.
GET_HASH_VALUE	A hash value based on the input string.
CANONICALIZE	Canonicalizes a given string.
COMMA_TO_TABLE	Converts a comma-delimited list of names into a PL/SQL table of names.
TABLE_TO_COMMA	Converts a PL/SQL table of names into a comma-delimited list of names.
DB_VERSION	Returns version information for the database.
EXEC_DDL_STATEMENT	Executes the DDL statement in parse_string.

Syntax

     FORMAT_CALL_STACK(fmt text);
     FORMAT_CALL_STACK();
     GET_HASH_VALUE(name VARCHAR2,base INTEGER,hash_size INTEGER)
     CANONICALIZE(name TEXT,canon_name TEXT,canon_len INT4)
     COMMA_TO_TABLE(list TEXT,tablen INT4,tab TEXT[])
     TABLE_TO_COMMA(tab TEXT[],tablen INT4,list TEXT)
     DB_VERSION(version TEXT,compatibility TEXT)
     EXEC_DDL_STATEMENT(parse_string TEXT)

F.55.6.7.1. Description of Features

This section explains each feature of DBMS_UTILITY.

FORMAT_CALL_STACK

FORMAT_CALL_STACK returns the current call stack of PL/pgSQL.
For the display format fmt, specify any of the following values. An error will occur if any other value is specified.

Table F.81. Values that can be specified for the display format

Setting value	Displayed content
'o'	Standard-format call stack display (with header)
's'	Standard-format call stack display (without header)
'p'	Comma-delimited call stack display (without header)

If the display format is omitted, display format 'o' will be used.
The data type of the return value is TEXT.

Example

     DECLARE
         s_StackTrace   TEXT
     BEGIN
         s_StackTrace := DBMS_UTILITY.FORMAT_CALL_STACK();

Note

If a locale other than English is specified for the message locale, the call stack result may not be retrieved correctly. To correctly retrieve the call stack result, specify English as the message locale.

GET_HASH_VALUE

A hash value based on the input string where the hash value should be between base and base + hash_size -1.

Example

     PERFORM DBMS_UTILITY.GET_HASH_VALUE('abcdef',0,10000);

CANONICALIZE

The procedure handles a single reserved or key word (such as 'table'), and strips off white spaces for a single identifier so that ' table ' becomes TABLE.

Example

declare
    l_result        text := '';
begin
    dbms_output.serveroutput(True);
    dbms_utility.canonicalize('taBle', l_result, 100);
    dbms_output.put_line(l_result);
end;
/

COMMA_TO_TABLE

These procedures converts a comma-delimited list of names into a PL/SQL table of names.
The list must be a non-empty comma-delimited list: Anything other than a comma-delimited list is rejected. Commas inside double quotes do not count.

Example

declare
    tablen          int4;
    tab             text[];
begin
    dbms_output.serveroutput(True);
    dbms_utility.comma_to_table('table, "Table","Table,122324" , Tablesd', tablen, tab);
    dbms_output.put_line(tablen);
    dbms_output.put_line(array_to_string(tab,','));
end;
/

TABLE_TO_COMMA

This procedure converts a PL/SQL table of names into a comma-delimited list of names.

Example

declare
    tablen          int4;
    l_str             text;
begin
    dbms_output.serveroutput(True);

    dbms_utility.table_to_comma('{table," \"table\"","\"table.1234567890\" "," tablezx",NULL}'::text[], tablen, l_str);
    dbms_output.put_line(tablen);
    dbms_output.put_line(l_str);

end;
/

DB_VERSION

This procedure returns version information for the database.

Example

declare
    lt_version        text;
    compatibility     text;
begin
    dbms_output.serveroutput(True);
    dbms_utility.db_version(lt_version, compatibility);
    dbms_output.put_line(lt_version);
    dbms_output.put_line(oracle.nvl(compatibility,'NULL'));
end;
/

EXEC_DDL_STATEMENT

his procedure executes the DDL statement in parse_string.

Example

declare
    l_result        text := '';
begin
    dbms_output.serveroutput(True);
    dbms_utility.exec_ddl_statement('create table if not exists public.test_exec_ddl_stmt (id int, name text);');
end;
/

F.55.6.7.2. Usage Example

A usage example of DBMS_UTILITY is shown below.

CREATE FUNCTION dbms_utility1_exe() RETURNS VOID AS $$
DECLARE
    s_StackTrace TEXT;
    v_hashvalue  numeric(38,0);
BEGIN
    s_StackTrace := DBMS_UTILITY.FORMAT_CALL_STACK();
    RAISE NOTICE '%', s_StackTrace;
    v_hashvalue := DBMS_UTILITY.GET_HASH_VALUE('abcdef',0,10000);
    RAISE NOTICE 'v_hashvalue=%', v_hashvalue;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION dbms_utility2_exe() RETURNS VOID AS $$
BEGIN
    PERFORM dbms_utility1_exe();
END;
$$ LANGUAGE plpgsql;

SELECT dbms_utility2_exe();
DROP FUNCTION dbms_utility2_exe();
DROP FUNCTION dbms_utility1_exe();

declare
    l_result        text := '';
begin
    dbms_output.serveroutput(True);
    dbms_utility.exec_ddl_statement('create table if not exists public.test_exec_ddl_stmt (id int, name text);');
    l_result := dbms_metadata.get_ddl('table', 'test_exec_ddl_stmt');
    dbms_output.put_line(l_result);
    dbms_utility.exec_ddl_statement(' truncate table public.test_exec_ddl_stmt');
    dbms_output.put_line('truncate SUCCESS');
    dbms_utility.exec_ddl_statement('drop table if exists public.test_exec_ddl_stmt ; ');
    dbms_output.put_line('drop SUCCESS');
end;
/

F.55.6.8. DBMS_JOB

Overview

Schedules and manages jobs in the lt_cron.

Table F.82. DBMS_JOB Features

Feature	Description
BROKEN	Disables job execution.(invalid)
CHANGE	Alters any of the user-definable parameters associated with a job.
INSTANCE	Assigns a job to be run by a instance.(invalid)
INTERVAL	Alters the interval between executions for a specified job.
NEXT_DATE	Alters the next execution time for a specified job.(invalid)
REMOVE	Removes specified job from the job queue.
RUN	Forces a specified job to run.(invalid)
SUBMIT	Submits a new job to the job queue.
USER_EXPORT	Re-creates a given job for export.
WHAT	Alters the job description for a specified job.

Syntax

     CHANGE(job INT8,what TEXT,next_date TIMESTAMP,interval TEXT,instance INT4 default null,force BOOL default 'false')
     INTERVAL(job INT8,interval TEXT)
     REMOVE(job INT8)
     SUBMIT(job INT8,what TEXT,next_date TIMESTAMP default sysdate,interval TEXT default 'null',no_parse BOOL default 'false',instance INT4 default null,force BOOL default 'false')
     USER_EXPORT(job INT8,mycall TEXT)
     WHAT(job INT8,what TEXT)

F.55.6.8.1. Description of Features

This section explains each feature of dbms_job.

CHANGE

This procedure changes any of the fields a user can set in a job.

Example

dbms_job.change(l_job_id, 'select 3', sysdate, '0 11 * * *');

INTERVAL

This procedure changes how often a job runs.
he interval parameter format is lt_cron's format.

Example

dbms_job.interval(l_job_id, '0 11 * * *');

REMOVE

This procedure removes an existing job from the job queue.

Example

dbms_job.remove(2);

SUBMIT

This procedure submits a new job.
The job submited by this procedure will not have name. It's suggested to submit by 'select cron.Schedule(name,xxx)'.

Example

dbms_job.submit(l_job_id, 'select 1',sysdate, '0 10 * * *');

USER_EXPORT

Produces the text of a call to re-create the given job.

Example

dbms_job.user_export(2, l_result);

WHAT

This procedure changes what an existing job does.

Example

     dbms_job.what(2, 'select 2');

F.55.6.8.2. Usage Example

A usage example of DBMS_JOB is shown below.

declare
    l_job_id   int8;
    l_result        text := '';
begin
    dbms_output.serveroutput(true);

    -- use dbms_job.submit will not have jobname  
    dbms_job.submit(l_job_id, 'select 1',sysdate, '0 10 * * *');
    dbms_output.put_line('submit job, job id:'||l_job_id);

    dbms_job.user_export(l_job_id, l_result);
    dbms_output.put_line('user_export(job id'||l_job_id||'):'||l_result);

    -- update can't be used for job without name
    dbms_job.what(l_job_id, 'select 2');
    dbms_job.user_export(l_job_id, l_result);
    dbms_output.put_line('after dbms_job.what user_export(job id:'||l_job_id||'):'||l_result);

    dbms_job.interval(l_job_id, '0 11 * * *');
    dbms_job.user_export(l_job_id, l_result);
    dbms_output.put_line('after dbms_job.interval user_export(job id:'||l_job_id||'):'||l_result);

    dbms_job.change(l_job_id, 'select 3', sysdate, '0 11 * * *');
    dbms_job.user_export(l_job_id, l_result);
    dbms_output.put_line('after dbms_job.change user_export(job id:'||l_job_id||'):'||l_result);


    dbms_job.next_date(l_job_id, sysdate);
    dbms_job.broken(l_job_id, True);
    dbms_job.instance(l_job_id, 10);
    dbms_job.run(l_job_id);

    dbms_job.remove(l_job_id);

end;
/

F.55.6.9. DBMS_LOCK

Overview

Provides an interface to Lock Management services.

Table F.83. DBMS_LOCK Features

Feature	Description
sleep	Puts a session to sleep for a specific time.

Syntax

     SLEEP(pi_seconds FLOAT8)

F.55.6.9.1. Description of Features

This section explains each feature of dbms_job.

SLEEP

This procedure suspends the session for a specified period of time.
suspends in seconds, to suspend the session.

Example

dbms_lock.sleep(0.1);

F.55.6.10. DBMS_METADATA

Overview

Provides a way for you to retrieve metadata from the database dictionary.

Table F.84. DBMS_METADATA Features

Feature	Description
get_ddl	Let you fetch metadata for objects.

Syntax

     GET_DDL(object_type VARCHAR,name VARCHAR,schema VARCHAR DEFAULT current_schema(),VARCHAR DEFAULT 'COMPATIBLE',VARCHAR DEFAULT  'ORACLE',VARCHAR DEFAULT  'DDL',BOOL DEFAULT  'false') RETURNS TEXT

F.55.6.10.1. Description of Features

This section explains each feature of dbms_job.

GET_DDL

Let you fetch metadata for objects.

Example

select dbms_metadata.get_ddl('constraint', 'key1_check') from dual;

F.55.6.11. DBMS_OBFUSCATION_TOOLKIT

Overview

Enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms.

Table F.85. DBMS_OBFUSCATION_TOOLKIT Features

Feature	Description
md5	Generates MD5 hashes of data.

Syntax

     MD5(input_string TEXT) RETURNS RAW

F.55.6.11.1. Description of Features

This section explains each feature of dbms_job.

MD5

Generates MD5 hashes of data.

Example

select dbms_obfuscation_toolkit.md5(input_string => '测试')::text from dual;

F.55.6.12. DBMS_SNAPSHOT

Overview

Enables you to refresh snapshots(MVIEW).

Table F.86. DBMS_SNAPSHOT Features

Feature	Description
refresh	Refreshes a list of snapshots.

Syntax

     REFRESH(list TEXT,method TEXT default 'C')

F.55.6.12.1. Description of Features

This section explains each feature of dbms_job.

REFRESH

This procedure refreshes a list of snapshots(MVIEW).

Example

select dbms_snapshot.refresh('mv_test_dbms_snapshot,mv_test_dbms_snapshot1') from dual;

F.55.6.13. UTL_FILE

Overview

Text files can be written and read using PL/pgSQL.

To perform these file operations, the directory for the operation target must be registered in the UTL_FILE.UTL_FILE_DIR table beforehand. Use the INSERT statement as the database administrator or a user who has INSERT privileges to register the directory. Also, if the directory is no longer necessary, delete it from the same table. Refer to "Registering and Deleting Directories" for information on the how to register and delete the directory.

Declare the file handler explained hereafter as follows in PL/pgSQL:

    DECLARE f UTL_FILE.FILE_TYPE;

Table F.87. UTL_FILE Features

Feature	Description
FCLOSE	Closes a file.
FCLOSE_ALL	Closes all files open in a session.
FCOPY	Copies a whole file or a contiguous portion thereof.
FFLUSH	Flushes the buffer.
FGETATTR	Retrieves the attributes of a file.
FOPEN	Opens a file.
FREMOVE	Deletes a file.
FRENAME	Renames a file.
GET_LINE	Reads a line from a text file.
IS_OPEN	Checks if a file is open.
NEW_LINE	Writes newline characters.
PUT	Writes a string.
PUT_LINE	Appends a newline character to a string and writes the string.
PUTF	Writes a formatted string.

Syntax

F.55.6.13.1. Registering and Deleting Directories

Registering the directory

1. Check if the directory is already registered (if it is, then step 2 is not necessary).

     SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/pgsql';

2. Register the directory.

     INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('/home/pgsql');

Deleting the directory

     DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/lightdb';

F.55.6.13.2. Description

This section explains each feature of UTL_FILE.

FCLOSE

FCLOSE closes a file that is open.
Specify an open file handle.
The value returned is a NULL value.

Example

     f := UTL_FILE.FCLOSE(f);

FCLOSE_ALL

FCLOSE_ALL closes all files open in a session.
Files closed with FCLOSE_ALL can no longer be read or written.

Example

     PERFORM UTL_FILE.FCLOSE_ALL();

FCOPY

FCOPY copies a whole file or a contiguous portion thereof. The whole file is copied if startLine and endLine are not specified.
Specify the directory location of the source file.
Specify the source file.
Specify the directory where the destination file will be created.
Specify the name of the destination file.
Specify the line number at which to begin copying. Specify a value greater than 0. If not specified, 1 is used.
Specify the line number at which to stop copying. If not specified, the last line number of the file is used.

Example

     PERFORM UTL_FILE.FCOPY('/home/lightdb', 'regress_ltsql.txt', '/home/lightdb', 'regress_ltsql2.txt');

FFLUSH

FFLUSH forcibly writes the buffer data to a file.
Specify an open file handle.

Example

     PERFORM UTL_FILE.FFLUSH(f);

FGETATTR

FGETATTR retrieves file attributes: file existence, file size, and information about the block size of the file.
Specify the directory where the file exists.
Specify the relevant file name.
Use a SELECT statement to obtain the file attributes, which are stored in the fexists, file_length, and blocksize columns.
The fexists column stores a boolean (TRUE/FALSE) value. If the file exists, fexists is set to TRUE. If the file does not exist, fexists is set to FALSE. The data type of fexists is BOOLEAN.
The file_length column stores the length of the file in bytes. If the file does not exist, file_length is NULL. The data type of file_length is INTEGER.
The blocksize column stores the block size of the file in bytes. If the file does not exist, blocksize is NULL. The data type of blocksize is INTEGER.

Example

     SELECT fexists, file_length, blocksize INTO file_flag, file_len, size
     FROM UTL_FILE.FGETATTR('/home/lightdb', 'regress_ltsql.txt');

FOPEN

FOPEN opens a file.
Specify the directory where the file exists.
Specify the file name.
Specify the mode for opening the file:

r: Read

w: Write

a: Add

Specify the maximum string length (in bytes) that can be processed with one operation. If omitted, the default is 1024. Specify a value from 1 to 32767.
Up to 50 files per session can be open at the same time.

Example

     f := UTL_FILE.FOPEN('/home/pgsql','regress_pgsql.txt','r',1024);

FREMOVE

FREMOVE deletes a file.
Specify the directory where the file exists.
Specify the file name.

Example

     PERFORM UTL_FILE.FREMOVE('/home/lightdb', 'regress_ltsql.txt');

FRENAME

FRENAME renames a file.
Specify the directory location of the source file.
Specify the source file to be renamed.
Specify the directory where the renamed file will be created.
Specify the new name of the file.
Specify whether to overwrite a file if one exists with the same name and in the same location as the renamed file. If TRUE is specified, the existing file will be overwritten. If FALSE is specified, an error occurs. If omitted, FALSE is set.

See

Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.

Example

     PERFORM UTL_FILE.FRENAME('/home/lightdb', 'regress_ltsql.txt', '/home/lightdb', 'regress_ltsql2.txt', TRUE);

GET_LINE

GET_LINE reads a line from a file.
Specify the file handle returned by FOPEN using r (read) mode.
Specify the number of bytes to read from the file. If not specified, the maximum string length specified at FOPEN will be used.
The return value is the buffer that receives the line read from the file.
Newline characters are not loaded to the buffer.
An empty string is returned if a blank line is loaded.
Specify the maximum length (in bytes) of the data to be read. Specify a value from 1 to 32767. If not specified, the maximum string length specified at FOPEN is set. If no maximum string length is specified at FOPEN, 1024 is set.
If the line length is greater than the specified number of bytes to read, the remainder of the line is read on the next call.
A NO_DATA_FOUND exception will occur when trying to read past the last line.

Example

     buff := UTL_FILE.GET_LINE(f);

IS_OPEN

IS_OPEN checks if a file is open.
Specify the file handle.
The return value is a BOOLEAN type. TRUE represents an open state and FALSE represents a closed state.

See

Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.

Example

     IF UTL_FILE.IS_OPEN(f) THEN
         PERFORM UTL_FILE.FCLOSE(f);
     END IF;

NEW_LINE

NEW_LINE writes one or more newline characters.
Specify an open file handle.
Specify the number of newline characters to be written to the file. If omitted, "1" is used.

Example

     PERFORM UTL_FILE.NEW_LINE(f, 2);

PUT

PUT writes a string to a file.
Specify the file handle that was opened with FOPEN using w (write) or a (append).
Specify the string to be written to the file.
The maximum length (in bytes) of the string to be written is the maximum string length specified at FOPEN.
PUT does not append a newline character. To append a newline character, execute NEW_LINE.

Example

     PERFORM UTL_FILE.PUT(f, 'ABC');

PUT_LINE

PUT_LINE appends a newline character to a string and writes the string.
Specify the file handle that was opened with FOPEN w (write) or a (append).
Specify whether to forcibly write to the file. If TRUE is specified, file writing is forced. If FALSE is specified, file writing is asynchronous. If omitted, FALSE will be set.
The maximum length of the string (in bytes) is the maximum string length specified at FOPEN.

Example

     PERFORM UTL_FILE.PUT_LINE(f, 'ABC', TRUE);

PUTF

PUTF writes a formatted string.
Specify the file handle that was opened with FOPEN w (write) or a (append).
Specify the format, which is a string that includes the formatting characters \n and %s.
The \n in the format is code for a newline character.
Specify the same number of input values as there are %s in the format. Up to a maximum of five input values can be specified. The %s in the format are replaced with the corresponding input characters. If an input value corresponding to %s is not specified, it is replaced with an empty string.

Example

     PERFORM UTL_FILE.PUTF(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]\n', '1', '2', '3', '4', '5');

F.55.6.13.3. Usage Example

The procedure when using UTL_FILE, and a usage example, are shown below.

1. Preparation

Before starting a new job that uses UTL_FILE, register the directory in the UTL_FILE.UTL_FILE_DIR table.

Refer to "Registering and Deleting Directories" for information on how to register the directory.

2. Performing a job

Perform a job that uses UTL_FILE. The example is shown below.

     CREATE OR REPLACE FUNCTION gen_file(mydir TEXT, infile TEXT, outfile TEXT, copyfile TEXT) RETURNS void AS $$
     DECLARE
       v1 VARCHAR(32767);
       inf UTL_FILE.FILE_TYPE;
       otf UTL_FILE.FILE_TYPE;
     BEGIN
       inf := UTL_FILE.FOPEN(mydir, infile,'r',256);
       otf := UTL_FILE.FOPEN(mydir, outfile,'w');
       v1 := UTL_FILE.GET_LINE(inf,256);
       PERFORM UTL_FILE.PUT_LINE(otf,v1,TRUE);
       v1 := UTL_FILE.GET_LINE(inf,256);
       PERFORM UTL_FILE.PUTF(otf,'%s\n',v1);
       v1 := UTL_FILE.GET_LINE(inf, 256);
       PERFORM UTL_FILE.PUT(otf,v1);
       PERFORM UTL_FILE.NEW_LINE(otf);
       PERFORM UTL_FILE.FFLUSH(otf);

       inf := UTL_FILE.FCLOSE(inf);
       otf := UTL_FILE.FCLOSE(otf);

       PERFORM UTL_FILE.FCOPY(mydir, outfile, mydir, copyfile, 2, 3);
       PERFORM UTL_FILE.FRENAME(mydir, outfile, mydir, 'rename.txt');

     END;
     $$ LANGUAGE plpgsql;

     SELECT gen_file('/home/pgsql', 'input.txt', 'output.txt', 'copyfile.txt');

3. Post-processing

If you remove a job that uses UTL_FILE, delete the directory information from the UTL_FILE.UTL_FILE_DIR table. Ensure that the directory information is not being used by another job before deleting it.

Refer to "Registering and Deleting Directories" for information on how to delete the directory.

F.55.6.14. UTL_RAW

Overview

Provides SQL functions for manipulating RAW datatypes.

Table F.88. UTL_RAW Features

Feature	Description
BIT_AND	Performs bitwise logical "and" of the values in RAW r1 with RAW r2 and returns the "anded" result RAW.
BIT_COMPLEMENT	Performs bitwise logical "complement" of the values in RAW r and returns the "complement'ed" result RAW.
BIT_OR	Performs bitwise logical "or" of the values in RAW r1 with RAW r2 and returns the "or'd" result RAW.
BIT_XOR	Performs bitwise logical "exclusive or" of the values in RAW r1 with RAW r2 and returns the "xor'd" result RAW.
CAST_FROM_BINARY_INTEGER	Returns the RAW binary representation of a BINARY_INTEGER value.
CAST_FROM_NUMBER	Returns the RAW binary representation of a NUMBER value.
CAST_TO_BINARY_INTEGER	Casts the RAW binary representation of a BINARY_INTEGER into a BINARY_INTEGER.
CAST_TO_NUMBER	Casts the RAW binary representation of a NUMBER into a NUMBER.
CAST_TO_RAW	Converts a text value into a RAW value.
CAST_TO_VARCHAR2	Converts a RAW value into a text value.
COMPARE	Compares RAW r1 against RAW r2.
CONCAT	Concatenates up to 12 RAWs into a single RAW.
CONVERT	Converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW.
COPIES	Returns n copies of r concatenated together.
LENGTH	Returns the length in bytes of a RAW r.
OVERLAY	Overlays the specified portion of target RAW with overlay RAW, starting from byte position pos of target and proceeding for len bytes.
REVERSE	Reverses a byte sequence in RAW r from end to end.
TRANSLITERATE	Translates the bytes in the input RAW r according to the bytes in the translation RAWs from_set and to_set.
TRANSLATE	Converts the bytes in the input RAW r according to the bytes in the transliteration RAWs from_set and to_set.
XRANGE	Reverses a byte sequence in RAW r from end to end.
REVERSE	Returns a RAW containing all valid 1-byte encodings in succession, beginning with the value start_byte and ending with the value end_byte.

Syntax

CAST_TO_VARCHAR2(r raw)  RETURNS TEXT
CAST_TO_RAW(c TEXT)  RETURNS RAW
CONCATr1 RAW,r2 RAW,r3 RAW,r4 RAW,r5 RAW,r6 RAW,r7 RAW,r8 RAW,r9 RAW,r10 RAW,r11 RAW,r12 RAW)  RETURNS RAW
LENGTH(r RAW) RETURNS INT4
SUBSTR(r RAW,pos INT4,len INT4 DEFAULT null)  RETURNS RAW
TRANSLITERATE(r RAW,to_set RAW DEFAULT ''::raw ,from_set RAW DEFAULT ''::raw,pad RAW DEFAULT '00'::raw)  RETURNS RAW
TRANSLATE(r RAW,from_set RAW,to_set RAW) RETURNS RAW
COPIES(r RAW,n INT8) RETURNS RAW
OVERLAY(overlay_str RAW,target RAW,pos INT4 DEFAULT 1,len INT4 DEFAULT NULL,pad RAW DEFAULT '00'::raw) RETURNS RAW
XRANGE(start_byte RAW,end_byte RAW) RETURNS RAW
REVERSE(r RAW) RETURNS RAW
COMPARE(r1 RAW,r2 RAW,pad RAW)  RETURNS INT4
CONVERT(r RAW,to_charset TEXT,from_charset TEXT) RETURNS RAW
BIT_AND(r1 RAW,r2 RAW) RETURNS RAW
BIT_OR(r1 RAW,r2 RAW) RETURNS RAW
BIT_XOR(r1 RAW,r2 RAW) RETURNS RAW
BIT_COMPLEMENT(r RAW) RETURNS RAW
CAST_TO_NUMBER(r RAW)RETURNS NUMERIC
CAST_FROM_NUMBER(n NUMERIC) RETURNS RAW
CAST_TO_BINARY_INTEGER(r RAW,endianess INT)RETURNS INT4
CAST_FROM_BINARY_INTEGER(n INT4,endianess INT) RETURNS RAW

F.55.6.14.1. Description of Features

This section explains each feature of dbms_job.

CAST_TO_VARCHAR2

This function converts a RAW value represented using some number of data bytes into a text value with that number of data bytes.

Example

select utl_raw.cast_to_varchar2('43616D65726F6E') from dual;

CAST_TO_RAW

This function converts a text value represented using some number of data bytes into a RAW value with that number of data bytes. The data itself is not modified in any way, but its datatype is recast to a RAW datatype.

Example

select utl_raw.cast_to_raw('测试') from dual;

CONCAT

This function concatenates up to 12 RAWs into a single RAW.

Example

select  utl_raw.concat( '1', '0102', 'f', '1a2b' ) from dual;

LENGTH

This function returns the length in bytes of a RAW r.

Example

select  UTL_RAW.length('FFFF'::raw) from dual;

SUBSTR

If pos is positive, then SUBSTR counts from the beginning of r to find the first byte. If pos is negative, then SUBSTR counts backward from the end of the r. The value pos can be 0.

Example

select utl_raw.substr( '0102030405', 3, 2 ) from dual;

TRANSLITERATE

This function converts the bytes in the input RAW r according to the bytes in the transliteration RAWs from_set and to_set. Successive bytes in r are looked up in the from_set, and, if not found, copied unaltered to the result RAW. If found, then they are replaced in the result RAW by either corresponding bytes in the to_set, or the pad byte when no correspondence exists.

Example

select utl_raw.transliterate( '010203040502', '0709', '01020304', 'ff' ) from dual;

TRANSLATE

This function translates the bytes in the input RAW r according to the bytes in the translation RAWs from_set and to_set. If a byte in r has a matching byte in from_set, then it is replaced by the byte in the corresponding position in to_set, or deleted.

Example

select utl_raw.translate( '0102030405', '0304', '09' ) from dual;

COPIES

This function returns n copies of r concatenated together.

Example

select utl_raw.copies( '010203', 3 ) from dual;

OVERLAY

This function overlays the specified portion of target RAW with overlay_str RAW, starting from byte position pos of target and proceeding for len bytes.
If overlay_str has less than len bytes, then it is extended to len bytes using the pad byte. If overlay_str exceeds len bytes, then the extra bytes in overlay_str are ignored. If len bytes beginning at position pos of target exceeds the length of target, then target is extended to contain the entire length of overlay_str.

Example

select utl_raw.overlay( 'aabb', '010203', 5, 1, 'FF' ) from dual;

XRANGE

This function returns a RAW value containing the succession of one-byte encodings beginning and ending with the specified byte-codes.
If the start_byte value is greater than the end_byte value, then the succession of resulting bytes begins with start_byte, wraps through x'FF' back to x'00', then ends at end_byte.

Example

select utl_raw.xrange( 'FA', '01' ) from dual;

REVERSE

This function reverses a byte sequence in RAW r from end to end. For example, x'0102F3' would be reversed to x'F30201', and 'xyz' would be reversed to 'zyx'. The result length is the same as the input RAW length.

Example

select utl_raw.reverse( '010203040506070809' ) from dual;

COMPARE

This function compares two RAW values. If they differ in length, then the shorter is extended on the right according to the optional pad parameter.

Example

select utl_raw.compare( '010203', '01020304', '04' ) from dual;

CONVERT

This function converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW.
Both from_charset and to_charset must be supported character sets defined to the LightDB server.

Example

select utl_raw.convert(rawout('测试'::BYTEA)::text::raw,'GBK','UTF8') from dual;

BIT_AND

This function performs bitwise logical "and" of the values in RAW r1 with RAW r2 and returns the "anded" result RAW.
If r1 and r2 differ in length, the shorter of the two RAWs will extend to same length by add '00'. (it is not same with oracle).

Example

select utl_raw.bit_and('1234ffdd','fff1234f') from dual;

BIT_OR

This function performs bitwise logical "or" of the values in RAW r1 with RAW r2 and returns the or'd result RAW.
If r1 and r2 differ in length, the shorter of the two RAWs will extend to same length by add '00'. (it is not same with oracle).

Example

select utl_raw.bit_or('1234ffdd','fff1234f') from dual;

BIT_XOR

This function performs bitwise logical "exclusive or" of the values in RAW r1 with RAW r2 and returns the xor'd result RAW.
If r1 and r2 differ in length, the shorter of the two RAWs will extend to same length by add '00'. (it is not same with oracle).

Example

select utl_raw.bit_xor('1234ffdd','1234ffee') from dual;

BIT_COMPLEMENT

This function performs bitwise logical "complement" of the values in RAW r and returns the complement'ed result RAW. The result length equals the input RAW r length.

Example

select UTL_raw.bit_complement('1122FF') from dual;

CAST_TO_NUMBER

This function casts the RAW binary representation of a NUMBER into a NUMBER.

Example

select utl_raw.cast_TO_number('C10215') from dual;

CAST_FROM_NUMBER

This function returns the RAW binary representation of a NUMBER value.

Example

select UTL_raw.cast_from_number(1.2) from dual;

CAST_TO_BINARY_INTEGER

This function casts the RAW binary representation of a INTEGER into a INTEGER.(oracle is BINARY_INTEGER)

Example

select utl_raw.cast_to_binary_integer('FF00') from dual;

CAST_FROM_BINARY_INTEGER

This function returns the RAW binary representation of a INTEGER value.(oracle is BINARY_INTEGER)

Example

select utl_raw.cast_from_binary_integer(65280) from dual;

F.55.6.15. UTL_URL

Overview

Has two functions that provide escape and unescape mechanisms for URL characters.

Table F.89. UTL_URL Features

Feature	Description
ESCAPE	Returns a URL with illegal characters (and optionally reserved characters) escaped using the %2-digit-hex-code format.
UNESCAPE	Unescapes the escape character sequences to their original forms in a URL. Convert the %XX escape character sequences to the original characters.

Syntax

ESCAPE(url TEXT,escape_reserved_chars BOOL DEFAULT FALSE,url_charset TEXT DEFAULT 'UTF8')  RETURNS TEXT
UNESCAPE(url TEXT,url_charset TEXT DEFAULT 'UTF8') RETURNS TEXT

F.55.6.15.1. Description of Features

This section explains each feature of dbms_job.

ESCAPE

This function returns a URL with illegal characters (and optionally reserved characters) escaped using the %2-digit-hex-code format.

Example

select utl_url.escape('http://新年好.com') from dual;

UNESCAPE

This function unescapes the escape character sequences to its original form in a URL, to convert the %XX escape character sequences to the original characters.

Example

select utl_url.unescape('http%3A%2F%2F%E6%96%B0%E5%B9%B4%E5%A5%BD.com', 'utf8') from dual;

F.55.6.16. UTL_ENCODE

Overview

Provides functions that encode RAW data into a standard encoded format.

Table F.90. UTL_ENCODE Features

Feature	Description
BASE64_DECODE	Reads the base 64-encoded RAW input string and decodes it to its original RAW value.
BASE64_ENCODE	Encodes the binary representation of the RAW value into base 64 elements and returns it in the form of a RAW string.
MIMEHEADER_DECODE	Decodes a string from mime header format.
MIMEHEADER_ENCODE	Encodes a string into mime header format.
QUOTED_PRINTABLE_DECODE	Reads the text quoted printable format input string and decodes it to the corresponding RAW string.
QUOTED_PRINTABLE_ENCODE	Reads the RAW input string and encodes it to the corresponding quoted printable format string.
TEXT_DECODE	Decodes a character set sensitive text string.
TEXT_ENCODE	Encodes a character set sensitive text string.
UUDECODE	Reads the RAW uuencode format input string and decodes it to the corresponding RAW string.
UUDECODE	Reads the RAW input string and encodes it to the corresponding uuencode format string.

Syntax

BASE64_DECODE(r RAW) RETURNS RAW
BASE64_ENCODE(r RAW) RETURNS RAW
MIMEHEADER_DECODE(buf TEXT) RETURNS TEXT
MIMEHEADER_ENCODE(buf TEXT,encode_charset TEXT default 'UTF8'::text,encoding INT4 default 2::int4) RETURNS TEXT
QUOTED_PRINTABLE_DECODE(r RAW)  RETURNS RAW
QUOTED_PRINTABLE_ENCODE(r RAW) RETURNS RAW
TEXT_DECODE(buf TEXT,encode_charset TEXT default 'UTF8'::text,encoding INT4 default 2::int4) RETURNS TEXT
TEXT_ENCODE(buf TEXT,encode_charset TEXT default 'UTF8'::text,encoding INT4 default 2::int4) RETURNS TEXT
UUDECODE(r RAW) RETURNS RAW
UUENCODE(r RAW,type INT default 1::int,filename TEXT default 'uuencode.txt',permission TEXT default '0') RETURNS RAW

F.55.6.16.1. Description of Features

This section explains each feature of dbms_job.

BASE64_DECODE

This function reads the base 64-encoded RAW input string and decodes it to its original RAW value.

Example

select utl_encode.base64_decode('3572574C364B2B56') from dual;

BASE64_ENCODE

This function encodes the binary representation of the RAW value into base 64 elements and returns it in the form of a RAW string.

Example

select utl_encode.base64_encode(rawtohex('测试')::raw) from dual;

MIMEHEADER_DECODE

Decodes a string from mime header format('=?<charset> ?<encoding> ?<encoded text>?= ').

Example

select UTL_ENCODE.mimeheader_decode('=?UTF-8?Q?What=20is=20the=20date=20=E7=8E=8B=20=3D20=20/=20\=3F?=') from dual;

MIMEHEADER_ENCODE

This function produces as output an "encoded word" of the form('=?<charset> ?<encoding> ?<encoded text>?= ').
The ENCODING input parameter accepts as valid values UTL_ENCODE.QUOTED_PRINTABLE or UTL_ENCODE.BASE64 or NULL. If NULL, quoted-printable encoding is selected as a default value.
The <charset> value is specified as the input parameter encode_charset.

Example

select utl_encode.MIMEHEADER_ENCODE('What is the date 王 =20 / \?', encode_charset =>'UTF8', encoding => 1 ) from dual;

QUOTED_PRINTABLE_DECODE

This function reads the raw quoted printable format input string and decodes it to the corresponding RAW string.

Example

select utl_encode.quoted_printable_decode('3D45353D42453D3830313233345C61732064663D33444142') from dual;

QUOTED_PRINTABLE_ENCODE

This function reads the RAW input string and encodes it to the corresponding quoted printable format string.

Example

select utl_encode.quoted_printable_decode('3D45353D42453D3830313233345C61732064663D33444142') from dual;

TEXT_DECODE

This function converts the input text to the target character set as specified by the encode_charset parameter, if not NULL. The encoded text is converted to the base character set of database.
You can decode from either quoted-printable or base64 format, with regard to each encoding parameter. 1 for base64, 2 for quoted-printable, default is quoted-printable format.

Example

select utl_encode.text_decode('=CD=F91234\as df=3DAB',encode_charset => 'GBK',encoding => 2) from dual;

TEXT_ENCODE

This function converts the input text to the target character set as specified by the encode_charset parameter, if not NULL. The text is encoded to either base64 or quoted-printable format, as specified by the encoding parameter.

Example

select utl_encode.text_encode('往12\as df=AB',encode_charset => 'GBK', encoding => 1) from dual;

UUDECODE

This function reads the RAW uuencode format input string and decodes it to the corresponding RAW string.

Example

 select UTL_ENCODE.uudecode(rawtohex(',6[C9&5FA$R,S0`')::raw) from dual;

UUENCODE

This function reads the RAW input string and encodes it to the corresponding uuencode format string.

Example

select UTL_ENCODE.uuencode('5BB8D91959A113233340'::raw) from dual;

F.55.7. Transaction behavior

Most of the transaction behavior are exactly same, however the below stuff is not.

F.55.7.1. Handled Statement Failure.

    create table t (a int primary key, b int);
    begin;
    insert into t values(1,1);
    insert into t values(1, 1);
    commit;

Oracle : commit can succeed. t has 1 row after that.

LightDB: commit failed due to the 2nd insert failed. so t has 0 row.

F.55.7.2. DML with Subquery

Case 1:

    create table dml(a int, b int);
    insert into dml values(1, 1), (2,2);

    -- session 1:
    begin;
    delete from dml where a in (select min(a) from dml);

    --session 2:
    delete from dml where a in (select min(a) from dml);

    -- session 1:
    commit;

In Oracle: 1 row deleted in sess 2. so 0 rows in the dml at last.

In LightDB: 0 rows are deleted in sess 2, so 1 rows in the dml at last.

Oracle probably detects the min(a) is changed and rollback/rerun the statement.

The same reason can cause the below difference as well.

    create table su (a int, b int);
    insert into su values(1, 1);

    - session 1:
    begin;
    update su set b = 2 where b = 1;

    - sess 2:
    select * from su where a in (select a from su where b = 1) for update;

    - sess 1:
    commit;

In Oracle, 0 row is selected. In LightDB, 1 row (1, 2) is selected.

A best practice would be never use subquery in DML & SLEECT ... FOR UPDATE. Even in Oracle, the behavior is inconsistent as well. Oracle between 11.2.0.1 and 11.2.0.3 probably behavior same as LightDB, but other versions not.

F.55.8. System View Reference

These views are based on oracle 11g, but some views include some fields from the new version.

Views are created under oracle and sys schema.

In order to be compatible with oracle, the following views have the same fields as the corresponding views in oracle , but return NULL for those fields whose field values cannot be obtained or are difficult to obtain.

The supported fields are listed below. Some fields have different meanings from oracle.

F.55.8.1. [DBA/ALL/USER]_SEQUENCES

DBA_SEQUENCES describes all sequences in the database.
The ALL_SEQUENCES view is currently the same as the DBA_SEQUENCES view, and no permission verification is done like oracle.
USER_SEQUENCES describes all sequences owned by the current user.

Table F.91. ALL_SEQUENCES

Column	Description
SEQUENCE_OWNER	Owner of the sequence
SEQUENCE_NAME	Sequence name
MIN_VALUE	Minimum value of the sequence
MAX_VALUE	Maximum value of the sequence
INCREMENT_BY	Value by which sequence is incremented
CYCLE_FLAG	Indicates whether the sequence wraps around on reaching the limit (Y) or not (N)
ORDER_FLAG	Indicates whether sequence numbers are generated in order (Y) or not (N), Under lightdb this is always Y
CACHE_SIZE	Number of sequence numbers to cache
LAST_NUMBER	Last sequence number written to disk.

F.55.8.2. [DBA/ALL/USER]_SYNONYMS

SYNONYMS is not supported in LightDB yet, so these view are empty now.

F.55.8.3. [DBA/ALL/USER]_TAB_COLS, COLS, [DBA/ALL/USER]_TAB_COLUMNS

DBA_TAB_COLS describes the columns of all tables, views in the database.
The ALL_TAB_COLS view is currently the same as the DBA_TAB_COLS view, and no permission verification is done like oracle.
USER_TAB_COLS describes the columns of the tables and views owned by the current user. This view does not display the OWNER column.
COLS is same with USER_TAB_COLS.
DBA_TAB_COLUMNS describes the columns of all tables and views in the database.
The ALL_TAB_COLUMNS view is currently the same as the DBA_TAB_COLUMNS view, and no permission verification is done like oracle.
USER_TAB_COLUMNS describes the columns of the tables and views owned by the current user. This view does not display the OWNER column.
[DBA/ALL/USER]_TAB_COLS view differs from [DBA/ALL/USER]_TAB_COLUMNS in that hidden columns are not filtered out(HIDDEN_COLUMN,VIRTUAL_COLUMN,SEGMENT_COLUMN_ID,INTERNAL_COLUMN_ID).

Table F.92. ALL_TAB_COLS

Column	Description
OWNER	Owner of the table, view
TABLE_NAME	Name of the table, view
COLUMN_NAME	Column name
DATA_TYPE	Datatype of the column
DATA_TYPE_OWNER	Owner of the datatype of the column
DATA_LENGTH	Length of the column (in bytes for varcahr2, in char for others)
DATA_PRECISION	Decimal precision for NUMBER datatype; binary precision for FLOAT datatype; NULL for all other datatypes
DATA_SCALE	Digits to the right of the decimal point in a number
NULLABLE	Indicates whether a column allows NULLs. The value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY
COLUMN_ID	Sequence number of the column as created
DEFAULT_LENGTH	Default value for the column
NUM_DISTINCT	Number of distinct values in the column
NUM_NULLS	Number of NULLs in the column
AVG_COL_LEN	Average length of the column (in bytes)
CHAR_LENGTH	Displays the length of the column in characters
CHAR_USED	Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C), or whether the datatype is not any of the following (NULL)

F.55.8.4. [DBA/ALL/USER]_TAB_COL_STATISTICS

DBA_TAB_COL_STATISTICS contains column statistics and histogram information extracted from "DBA_TAB_COLUMNS". Its columns are the same as those in "ALL_TAB_COL_STATISTICS".
The ALL_TAB_COL_STATISTICS view is currently the same as the DBA_SEQUENCES view, and no permission verification is done like oracle.
USER_TAB_COL_STATISTICS displays such information extracted from "USER_TAB_COLUMNS". This view does not display the OWNER column.

Table F.93. ALL_TAB_COL_STATISTICS

Column	Description
OWNER	Owner of the table
TABLE_NAME	Name of the table
COLUMN_NAME	Column name
NUM_DISTINCT	Number of distinct values in the column
NUM_NULLS	Number of NULLs in the column
AVG_COL_LEN	Average length of the column (in bytes)

F.55.8.5. [DBA/ALL/USER]_OBJECTS, OBJ

DBA_OBJECTS describes all objects in the database. Its columns are the same as those in "ALL_OBJECTS".
The ALL_OBJECTS view is currently the same as the DBA_OBJECTS view, and no permission verification is done like oracle.
USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.
OBJ is same with USER_OBJECTS.

Table F.94. ALL_OBJECTS

Column	Description
OWNER	Owner of the object
OBJECT_NAME	Name of the object
SUBOBJECT_NAME	Name of the subobject (for example, partition), NULL if not exist
OBJECT_ID	Dictionary object number of the object
DATA_OBJECT_ID	Dictionary object number of the segment that contains the object
OBJECT_TYPE	Type of the object
STATUS	Status of the object: VALID, INVALID
TEMPORARY	Indicates whether the object is temporary (the current session can see only data that it placed in this object itself) (Y) or not (N)

F.55.8.6. [DBA/ALL/USER]_CATALOG

DBA_CATALOG lists all tables, views, and sequences in the database(no cluster and synonyms).
The ALL_CATALOG view is currently the same as the DBA_CATALOG view, and no permission verification is done like oracle.
USER_OCATALOG displays the tables, views, and sequences in the current user's schema.
All column in oracle view is supported.

Table F.95. ALL_CATALOG

Column	Description
OWNER	Owner of the TABLE, VIEW, SEQUENCE
TABLE_NAME	Name of the TABLE, VIEW, SEQUENCE
TABLE_TYPE	Type of the TABLE, VIEW, SEQUENCE

F.55.8.7. DICTIONARY, DICT

DICTIONARY/DICT contains descriptions of data dictionary tables and views.

Table F.96. DICTIONARY

Column	Description
TABLE_NAME	Name of the object
COMMENTS	Text comment on the object

F.55.8.8. [DBA/ALL/USER]_DEPENDENCIES

DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links. This view does not display the SCHEMAID column.
The ALL_DEPENDENCIES view is currently the same as the DBA_DEPENDENCIES view, and no permission verification is done like oracle.
USER_DEPENDENCIES describes dependencies between objects in the current user's schema. This view does not display the OWNER column.

Table F.97. ALL_DEPENDENCIES

Column	Description
OWNER	Owner of the object
NAME	Name of the object
TYPE	Type of the object
REFERENCED_OWNER	Owner of the referenced object (remote owner if remote object)
REFERENCED_NAME	Name of the referenced object
REFERENCED_TYPE	Type of the referenced object
DEPENDENCY_TYPE	Type of the dependency (oracle: Indicates whether the dependency is a REF dependency (REF) or not (HARD))

F.55.8.9. [DBA/ALL/USER]_SOURCE

DBA_SOURCE describes the text source of all stored objects in the database.
The ALL_SOURCE view is currently the same as the DBA_SOURCE view, and no permission verification is done like oracle.
USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.

Table F.98. ALL_SOURCE

Column	Description
OWNER	Owner of the object
NAME	Name of the object
TYPE	Type of object: FUNCTION, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER
LINE	Line number of this line of source
TEXT	Text source of the stored object

F.55.8.10. [DBA/ALL/USER]_PROCEDURES

DBA_PROCEDURES lists all functions and procedures, along with associated properties.
The ALL_PROCEDURES view is currently the same as the DBA_PROCEDURES view, and no permission verification is done like oracle.
USER_PROCEDURES lists all functions and procedures owned by the current user, along with associated properties. It does not contain the OWNER column.
These views indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified.

Table F.99. ALL_PROCEDURES

Column	Description
OWNER	Owner of the procedure
OBJECT_NAME	Name of the object: top-level function, procedure, or package name
OBJECT_ID	Object number of the object
OBJECT_TYPE	The typename of the object
AGGREGATE	Indicates whether the procedure is an aggregate function (YES) or not (NO)
PARALLEL	Indicates whether the procedure or function is parallel-enabled (YES) or not (NO)
DETERMINISTIC	YES, if the procedure/function is declared to be deterministic; otherwise NO

F.55.8.11. [DBA/ALL/USER]_TRIGGERS

DBA_TRIGGERS describes all triggers in the database.
The ALL_TRIGGERS view is currently the same as the DBA_TRIGGERS view, and no permission verification is done like oracle.
USER_TRIGGERS describes the triggers owned by the current user

Table F.100. ALL_TRIGGERS

Column	Description
OWNER	Owner of the trigger
TRIGGER_NAME	Name of the trigger
TRIGGER_TYPE	When the trigger fires: BEFORE STATEMENT, BEFORE EACH ROW, AFTER STATEMENT, AFTER EACH ROW, INSTEAD OF EACH ROW
TRIGGERING_EVENT	DML that fires the trigger
TABLE_OWNER	Owner of the table on which the trigger is defined
BASE_OBJECT_TYPE	Base object on which the trigger is defined: TABLE, VIEW
TABLE_NAME	Indicates the table or view name on which the trigger is defined
REFERENCING_NAMES	Names used for referencing OLD and NEW column values from within the trigger
WHEN_CLAUSE	Must evaluate to TRUE for TRIGGER_BODY to execute
STATUS	Indicates whether the trigger is enabled (ENABLED) or disabled (DISABLED); a disabled trigger will not fire
ACTION_TYPE	Action type of the trigger body: 'PL/SQL'
TRIGGER_BODY	Statements executed by the trigger when it fires
BEFORE_STATEMENT	Indicates whether the trigger has a BEFORE STATEMENT section (YES) or not (NO)
BEFORE_ROW	Indicates whether the trigger has a BEFORE EACH ROW section (YES) or not (NO)
AFTER_ROW	Indicates whether the trigger has an AFTER EACH ROW section (YES) or not (NO)
AFTER_STATEMENT	Indicates whether the trigger has an AFTER STATEMENT section (YES) or not (NO)
INSTEAD_OF_ROW	Indicates whether the trigger has an INSTEAD OF section (YES) or not (NO)

F.55.8.12. [DBA/ALL/USER]_TRIGGER_COLS

DBA_TRIGGER_COLS describes the use of columns in all triggers in the database.
The ALL_TRIGGER_COLS view is currently the same as the DBA_TRIGGER_COLS view, and no permission verification is done like oracle.
USER_TRIGGER_COLS describes the use of columns in the triggers owned by the current user and in triggers on tables owned by the current user.

Table F.101. ALL_TRIGGER_COLS

Column	Description
TRIGGER_OWNER	Owner of the trigger
TRIGGER_NAME	Name of the trigger
TABLE_OWNER	Owner of the table on which the trigger is defined
TABLE_NAME	Table on which the trigger is defined
COLUMN_NAME	Name of the column used in the trigger

F.55.8.13. [DBA/ALL/USER]_TYPES

DBA_TYPES describes all object types in the database.
The ALL_TYPES view is currently the same as the DBA_TYPES view, and no permission verification is done like oracle.
USER_TYPES describes the object types owned by the current user. This view does not display the OWNER column.

Table F.102. ALL_TYPES

Column	Description
OWNER	Owner of the type
TYPE_NAME	Name of the type
TYPE_OID	Object identifier (OID) of the type
TYPECODE	Typecode of the type
PREDEFINED	Indicates whether the type is a predefined type (YES) or not (NO)

F.55.8.14. [DBA/ALL/USER]_CONSTRAINTS

DBA_CONSTRAINTS describes all constraint definitions in the database.
The ALL_CONSTRAINTS view is currently the same as the DBA_CONSTRAINTS view, and no permission verification is done like oracle.
USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema.

Table F.103. ALL_CONSTRAINTS

Column	Description
OWNER	Owner of the constraint definition
CONSTRAINT_NAME	Name of the constraint definition
CONSTRAINT_TYPE	Type of the constraint definition c - Check constraint on a table p - Primary key u - Unique key f - Constraint that involves a REF column t - Constraint triggers t - exclusivity Constraint
TABLE_NAME	Name associated with the table (or view) with the constraint definition
SEARCH_CONDITION	Text of search condition for a check constraint
R_OWNER	Owner of the table referred to in a referential constraint
DELETE_RULE	Delete rule for a referential constraint: 'NO ACTION', 'RESTRICT', 'CASCADE', 'SET NULL', 'SET DEFAULT'
STATUS	Enforcement status of the constraint: 'ENABLED', 'DISABLED'
DEFERRABLE	Indicates whether the constraint is deferrable (DEFERRABLE) or not (NOT DEFERRABLE)
DEFERRED	Indicates whether the constraint was initially deferred (DEFERRED) or not (IMMEDIATE)
VALIDATED	Has the constraint been validated? Currently, can be false only for foreign keys and CHECK constraints
INDEX_OWNER	Name of the user owning the index
INDEX_NAME	Name of the index (only shown for unique, primary key, foreign key, or exclusion constraint)
INVALID	Indicates whether the constraint is invalid (INVALID) or not (NULL)

F.55.8.15. [DBA/ALL/USER]_CONS_COLUMNS

DBA_CONS_COLUMNS describes all columns in the database that are specified in constraints.
The ALL_CONS_COLUMNS view is currently the same as the DBA_CONS_COLUMNS view, and no permission verification is done like oracle.
USER_CONS_COLUMNS describes columns that are owned by the current user and that are specified in constraints.
All column in oracle are supported

Table F.104. ALL_CONS_COLUMNS

Column	Description
OWNER	Owner of the constraint definition
CONSTRAINT_NAME	Name of the constraint definition
TABLE_NAME	Name of the table with the constraint definition
COLUMN_NAME	Name of the column or attribute of the object type column specified in the constraint definition
POSITION	Original position of the column or attribute in the definition of the object

F.55.8.16. [DBA/ALL/USER]_VIEWS

DBA_VIEWS describes all views in the database.
The ALL_VIEWS view is currently the same as the DBA_VIEWS view, and no permission verification is done like oracle.
USER_VIEWS describes the views owned by the current user. This view does not display the OWNER column.

Table F.105. ALL_VIEWS

Column	Description
OWNER	Owner of the view
VIEW_NAME	Name of the view
TEXT_LENGTH	Length of the view text
TEXT	View text

F.55.8.17. [DBA/ALL/USER]_TABLES, [DBA/ALL/USER]_ALL_TABLES, TAB

DBA_ALL_TABLES describes all object tables and relational tables in the database.
The ALL_ALL_TABLES view is currently the same as the DBA_ALL_TABLES view, and no permission verification is done like oracle.
USER_ALL_TABLES describes the object tables and relational tables owned by the current user. This view does not display the OWNER column.
[DBA/ALL/USER]_TABLES does not display the OBJECT_ID_TYPE, TABLE_TYPE_OWNER and TABLE_TYPE column.
[DBA/ALL/USER]_TABLES has RESULT_CACHE column, but [DBA/ALL/USER]_ALL_TABLES does not.
TAB is same with UUSER_TABLES.

Table F.106. ALL_ALL_TABLES

Column	Description
OWNER	Owner of the table
TABLE_NAME	Name of the table
TABLESPACE_NAME	Name of the tablespace containing the table
LOGGING	Indicates whether or not changes to the table are logged
NUM_ROWS	Number of rows in the table
BLOCKS	Number of used blocks in the table
AVG_ROW_LEN	Average row length, including row overhead
LAST_ANALYZED	Date on which the table was most recently analyzed
PARTITIONED	Indicates whether the table is partitioned (YES) or not (NO)
TEMPORARY	Indicates whether the table is temporary (Y) or not (N)
ROW_MOVEMENT	If a partitioned table, indicates whether row movement is enabled (ENABLED) or disabled (DISABLED) Always is 'ENABLE'

F.55.8.18. [DBA/ALL/USER]_TAB_STATISTICS

DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database.
The ALL_TAB_STATISTICS view is currently the same as the DBA_TAB_STATISTICS view, and no permission verification is done like oracle.
USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.
[DBA/ALL/USER]_TABLES does not display the OBJECT_ID_TYPE, TABLE_TYPE_OWNER and TABLE_TYPE column.

Table F.107. ALL_TAB_STATISTICS

Column	Description
OWNER	Owner of the object
TABLE_NAME	Name of the table
TABLESPACE_NAME	Name of the tablespace containing the table
PARTITION_NAME	Name of the partition
PARTITION_POSITION	Position of the partition within the table
OBJECT_TYPE	Type of the object：TABLE, PARTIOTION, SUBPARTITION
NUM_ROWS	Number of rows in the object
BLOCKS	Number of used blocks in the object
AVG_ROW_LEN	Average row length, including row overhead
LAST_ANALYZED	Date of the most recent time the table was analyzed

F.55.8.19. [DBA/ALL/USER]_TAB_COMMENTS

DBA_TAB_COMMENTS displays comments on all tables and views in the database.
The ALL_TAB_COMMENTS view is currently the same as the DBA_TAB_COMMENTS view, and no permission verification is done like oracle.
USER_TAB_COMMENTS displays comments on the tables and views owned by the current user. This view does not display the OWNER column.

Table F.108. ALL_TAB_COMMENTS

Column	Description
OWNER	Owner of the object
TABLE_NAME	Name of the object
TABLE_TYPE	Type of the object
COMMENTS	Comment on the object

F.55.8.20. [DBA/ALL/USER]_COL_COMMENTS

DBA_COL_COMMENTS displays comments on the columns of all tables and views in the database.
The ALL_COL_COMMENTS view is currently the same as the DBA_COL_COMMENTS view, and no permission verification is done like oracle.
USER_COL_COMMENTS displays comments on the columns of the tables and views owned by the current user. This view does not display the OWNER column.

Table F.109. ALL_COL_COMMENTS

Column	Description
OWNER	Owner of the object
TABLE_NAME	Name of the object
COLUMN_NAME	Name of the column
COMMENTS	Name of the column

F.55.8.21. [DBA/ALL/USER]_TAB_MODIFICATIONS

DBA_TAB_MODIFICATIONS describes such information for all tables in the database.
The ALL_TAB_MODIFICATIONS view is currently the same as the DBA_TAB_MODIFICATIONS view, and no permission verification is done like oracle.
USER_TAB_MODIFICATIONS describes such information for tables owned by the current user. This view does not display the TABLE_OWNER column.

Table F.110. ALL_TAB_MODIFICATIONS

Column	Description
TABLE_OWNER	Owner of the modified table
TABLE_NAME	Name of the modified table
INSERTS	Approximate number of inserts since the last time statistics were gathered
UPDATES	Approximate number of updates since the last time statistics were gathered
DELETES	Approximate number of deletes since the last time statistics were gathered
TIMESTAMP	Indicates the last time the table was modified

F.55.8.22. [DBA/ALL/USER]_INDEXES, IND

DBA_INDEXES describes all indexes in the database.
The ALL_INDEXES view is currently the same as the DBA_INDEXES view, and no permission verification is done like oracle.
USER_INDEXES describes the indexes owned by the current user. This view does not display the OWNER column.
IND is same with USER_INDEXES.

Table F.111. ALL_INDEXES

Column	Description
OWNER	Owner of the index
INDEX_NAME	Name of the index
INDEX_TYPE	Type of the index, always NORMAL
TABLE_OWNER	Owner of the indexed object
TABLE_NAME	Name of the indexed object
TABLE_TYPE	Type of the indexed object: TABLE, VIEW, INDEX, SEQUENCE
UNIQUENESS	Indicates whether the index is unique (UNIQUE) or nonunique (NONUNIQUE)
TABLESPACE_NAME	Name of the tablespace containing the index
LOGGING	Indicates whether or not changes to the index are logged: 'YES', 'NO'
DISTINCT_KEYS	Number of distinct indexed values
STATUS	Indicates whether a nonpartitioned index is VALID or UNUSABLE
NUM_ROWS	Number of rows in the index
LAST_ANALYZED	Date on which this index was most recently analyzed
PARTITIONED	Indicates whether the index is partitioned (YES) or not (NO)
TEMPORARY	Indicates whether the index is on a temporary table (Y) or not (N)

F.55.8.23. [DBA/ALL/USER]_INDEX_USAGE

DBA_INDEX_USAGE displays cumulative statistics for each index.
The ALL_INDEX_USAGE view is same with DBA_INDEX_USAGE(this view is not exist in oracle).
USER_VIEWS describes cumulative statistics for each index owned by the current user. This view does not display the OWNER column.

Table F.112. DBA_INDEX_USAGE

Column	Description
OBJECT_ID	Object ID for the index
NAME	Index name
OWNER	Index owner
TOTAL_ACCESS_COUNT	Total number of times the index has been accessed
TOTAL_ROWS_RETURNED	Total rows returned by the index

F.55.8.24. [DBA/ALL/USER]_IND_COLUMNS

DBA_IND_COLUMNS describes the columns of indexes on all tables in the database.
The ALL_IND_COLUMNS view is currently the same as the DBA_IND_COLUMNS view, and no permission verification is done like oracle.
USER_IND_COLUMNS describes the columns of indexes owned by the current user and columns of indexes on tables owned by the current user. This view does not display the INDEX_OWNER or TABLE_OWNER columns.

Table F.113. ALL_IND_COLUMNS

Column	Description
INDEX_OWNER	Owner of the index
INDEX_NAME	Name of the index
TABLE_OWNER	Owner of the table
TABLE_NAME	Name of the table
COLUMN_NAME	Column name or attribute of the object type column
COLUMN_POSITION	Position of the column or attribute within the index
COLUMN_LENGTH	Indexed length of the column
CHAR_LENGTH	Maximum codepoint length of the column
DESCEND	Indicates whether the column is sorted in descending order (DESC) or ascending order (ASC)

F.55.8.25. [DBA/ALL/USER]_IND_EXPRESSIONS

DBA_IND_EXPRESSIONS describes the expressions of all function-based indexes in the database.
The ALL_IND_EXPRESSIONS view is currently the same as the DBA_IND_COLUMNS view, and no permission verification is done like oracle.
USER_IND_EXPRESSIONS describes the expressions of function-based indexes on tables owned by the current user. This view does not display the INDEX_OWNER or TABLE_OWNER columns.

Table F.114. ALL_IND_EXPRESSIONS

Column	Description
INDEX_OWNER	Owner of the index
INDEX_NAME	Name of the index
TABLE_OWNER	Owner of the table
TABLE_NAME	Name of the table
COLUMN_EXPRESSION	Function-based index expression defining the column
COLUMN_POSITION	Position of the column or attribute within the index

F.55.8.26. [DBA/ALL/USER]_IND_STATISTICS

DBA_IND_STATISTICS displays optimizer statistics for all indexes in the database.
The ALL_IND_STATISTICS view is currently the same as the DBA_IND_STATISTICS view, and no permission verification is done like oracle.
USER_IND_STATISTICS displays optimizer statistics for the indexes on the tables owned by the current user. This view does not display the OWNER column.

Table F.115. ALL_IND_STATISTICS

Column	Description
OWNER	Owner of the index
INDEX_NAME	Name of the index
TABLE_OWNER	Owner of the indexed object
TABLE_NAME	Name of the indexed object
PARTITION_NAME	Function-based index expression defining the column
PARTITION_POSITION	Position of the column or attribute within the index
OBJECT_TYPE	Type of the object:INDEX, PARTITION Always 'INDEX' now, because this view will not to get partition info
DISTINCT_KEYS	Number of distinct keys in the index
NUM_ROWS	Number of rows in the index
LAST_ANALYZED	Date of the most recent time the index was analyzed

F.55.8.27. [DBA/ALL/USER]_USERS

DBA_USERS describes all users of the database, and contains more columns than ALL_USERS.
ALL_USERS lists all users of the database(USERNAME, USER_ID, CREATED), but no permission verification is done like oracle.
USER_USERS describes the current user, and contains more columns than ALL_USERS.

Table F.116. DBA_USERS

Column	Description
USERNAME	Name of the user
USER_ID	ID number of the user
PASSWORD	This column is deprecated in favor of the AUTHENTICATION_TYPE column
ACCOUNT_STATUS	Account status, always 'NORMAL'
EXPIRY_DATE	Date of expiration of the account

Table F.117. ALL_USERS

Column	Description
USERNAME	Name of the user
USER_ID	ID number of the user

F.55.8.28. DBA_ROLES

Describes all roles in the database

Table F.118. DBA_ROLES

Column	Description
ROLE	Name of the role
PASSWORD_REQUIRED	This column is deprecated in favor of the AUTHENTICATION_TYPE column, always 'NO'
AUTHENTICATION_TYPE	Indicates the authentication mechanism for the role, always 'NONE' NONE - CREATE ROLE role1;
COMMON	Indicates whether a given role is common. always 'YES', does not exist in oracle 11g

F.55.8.29. [DBA/USER]_ROLE_PRIVS

DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
USER_ROLE_PRIVS describes the roles granted to the current user.

Table F.119. DBA_ROLE_PRIVS

Column	Description
GRANTEE	Name of the user or role receiving the grant
GRANTED_ROLE	Granted role name
ADMIN_OPTION	Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO)
DEFAULT_ROLE	Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO) Always 'NO'
COMMON	Indicates how the grant was made always 'YES', does not exist in oracle 11g

F.55.8.30. PRODUCT_COMPONENT_VERSION

Contains version and status information for component products.

Table F.120. PRODUCT_COMPONENT_VERSION

Column	Description
PRODUCT	Product name
VERSION	Version number
STATUS	Status of release

F.55.8.31. PLAN_TABLE

PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users.
it is not supported by LightDB, so this view is empty for compatibility.

F.55.8.32. DBA_DATA_FILES

Describes database files

Table F.121. DBA_DATA_FILES

Column	Description
FILE_NAME	Name of the database file
FILE_ID	File identifier number of the database file
TABLESPACE_NAME	Name of the tablespace to which the file belongs
BYTES	Size of the file in bytes
BLOCKS	Size of the file in Oracle blocks
STATUS	File status: AVAILABLE or INVALID Always 'AVAILABLE'
RELATIVE_FNO	Relative file number
AUTOEXTENSIBLE	Autoextensible indicator Always 'YES'
INCREMENT_BY	Number of Oracle blocks used as autoextension increment
ONLINE_STATUS	Online status of the file Always 'ONLINE'

F.55.8.33. [DBA/ALL/USER]_JOBS

Get info from lt_cron
DBA_JOBS describes all jobs in the database.
USER_JOBS describes the jobs owned by the current user.
ALL_JOBS is same with USER_JOBS.

Table F.122. DBA_JOBS

Column	Description
JOB	Identifier of job
LOG_USER	Login user when the job was submitted
PRIV_USER	User whose default privileges apply to this job
SCHEMA_USER	Default schema used to parse the job Always 'CRON'
LAST_DATE	Date on which this job last successfully executed
LAST_SEC	Same as LAST_DATE. This is when the last successful execution started 'HH24:MI:SS' format
THIS_DATE	Date that this job started executing (usually null if not executing)
THIS_SEC	Same as THIS_DATE. This is when the last successful execution started 'HH24:MI:SS' format
TOTAL_TIME	Total wall clock time spent by the system on this job in the last ececuted It is not same with oracle. In oracle, it means total wall clock time spent by the system on this job (in seconds) since the first time this job executed, and this value is cumulative
BROKEN	Y: no attempt is made to run this job, N: an attempt is made to run this job Always 'N'
INTERVAL	String uses the standard cron syntax, ref to lt_cron Different from oracle
WHAT	LightDB commands Different from oracle

F.55.8.34. DBA_JOBS_RUNNING

Get info from lt_cron
Lists all jobs that are currently running in the instance.

Table F.123. DBA_JOBS_RUNNING

Column	Description
SID	Identifier of process that is executing the job
JOB	Identifier of job. This job is currently executing
LAST_DATE	Date on which this job last successfully executed
LAST_SEC	Same as LAST_DATE. This is when the last successful execution started 'HH24:MI:SS' format
THIS_DATE	Date that this job started executing (usually null if not executing)
THIS_SEC	Same as THIS_DATE. This is when the last successful execution started 'HH24:MI:SS' format

F.55.8.35. [DBA/ALL/USER]_MVIEWS

DBA_MVIEWS describes all materialized views in the database.
The ALL_MVIEWS view is currently the same as the DBA_MVIEWS view, and no permission verification is done like oracle.
USER_MVIEWS describes all materialized views owned by the current user.

Table F.124. ALL_MVIEWS

Column	Description
OWNER	Schema in which the materialized view was created
MVIEW_NAME	Name of the materialized view
CONTAINER_NAME	Name of the container in which the materialized view's data is held. Normally this is the same as MVIEW_NAME
QUERY	Query that defines the materialized view
QUERY_LEN	Length (in bytes) of the defining query
UPDATABLE	Indicates whether the materialized view is updatable (Y) or not (N) Always 'N'
REFRESH_MODE	Refresh mode of the materialized view Always 'DEMAND', it means database refreshes this materialized view whenever an appropriate refresh procedure is called
REFRESH_METHOD	Default method used to refresh the materialized view Always 'COMPLETE'
BUILD_MODE	Indicates how the materialized view was populated during creation Always 'IMMEDIATE'

F.55.8.36. [DBA/ALL/USER]_MVIEW_LOGS

It is not supported, so use as an empty view for compatibility.
DBA_MVIEW_LOGS describes all materialized view logs in the database.
ALL_MVIEW_LOGS describes all materialized view logs accessible to the current user.
USER_MVIEW_LOGS describes all materialized view logs owned by the current user.

F.55.8.37. [DBA/ALL/USER]_MVIEW_COMMENTS

DBA_MVIEW_COMMENTS displays comments on the materialized views in the database.
The ALL_MVIEW_COMMENTS view is currently the same as the DBA_MVIEW_COMMENTS view, and no permission verification is done like oracle.
USER_MVIEW_COMMENTS displays comments on the materialized views owned by the current user. This view does not display the OWNER column.

Table F.125. ALL_MVIEW_COMMENTS

Column	Description
OWNER	Owner of the materialized view
MVIEW_NAME	Name of the materialized view
COMMENTS	Comment on the materialized view

F.55.8.38. [DBA/USER]_TABLESPACES

DBA_TABLESPACES describes all tablespaces in the database.
USER_TABLESPACES describes the tablespaces accessible to the current user. This view does not display the PLUGGED_IN column.

Table F.126. DBA_TABLESPACES

Column	Description
TABLESPACE_NAME	Name of the tablespace
BLOCK_SIZE	Tablespace block size (in bytes)
STATUS	Tablespace status Always 'ONLINE'
CONTENTS	Tablespace contents Always 'PERMANENT'

F.55.8.39. NLS_[DATABASE/INSTANCE/SESSION]_PARAMETERS

NLS_DATABASE_PARAMETERS lists permanent NLS parameters of the database.
NLS_INSTANCE_PARAMETERS lists NLS parameters of the instance.
NLS_SESSION_PARAMETERS lists NLS parameters of the user session.

Table F.127. NLS_DATABASE_PARAMETERS

Column	Description
PARAMETER	Parameter name
VALUE	Parameter value

F.55.8.40. [DBA/USER]_SEGMENTS

DBA_SEGMENTS describes the storage allocated for all segments in the database.
USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.

Table F.128. DBA_SEGMENTS

Column	Description
OWNER	Username of the segment owner
SEGMENT_NAME	Name, if any, of the segment
PARTITION_NAME	Object Partition Name (Set to NULL for non-partitioned objects)
SEGMENT_TYPE	Type of segment:'TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION'
TABLESPACE_NAME	Name of the tablespace containing the segment
HEADER_FILE	Name of the tablespace containing the segment
BYTES	Size, in bytes, of the segments
BLOCKS	Size, in LightDB blocks, of the segment

F.55.8.41. [DBA/USER/ALL]_PART_TABLES

DBA_PART_TABLES displays the object-level partitioning information for all partitioned tables in the database.
The ALL_PART_TABLES view is currently the same as the DBA_PART_TABLES view, and no permission verification is done like oracle.
USER_PART_TABLES displays the object-level partitioning information for the partitioned tables owned by the current user. This view does not display the OWNER column.
The supported fields are listed below. unsupported fields are filed with NULL.

Table F.129. ALL_PART_TABLES

Column	Description
OWNER	Owner of the partitioned table
TABLE_NAME	Name of the partitioned table
PARTITIONING_TYPE	Type of the partitioning method: RANGE, HASH, LIST
SUBPARTITIONING_TYPE	Type of the composite partitioning method: RANGE, HASH, LIST
PARTITION_COUNT	Number of partitions in the table
PARTITIONING_KEY_COUNT	Number of columns in the partitioning key
SUBPARTITIONING_KEY_COUNT	For a composite-partitioned table, the number of columns in the subpartitioning key
DEF_TABLESPACE_NAME	Default tablespace to be used when adding a partition
DEF_LOGGING	Default LOGGING attribute to be used when adding a partition

F.55.8.42. [DBA/USER/ALL]_TAB_PARTITIONS

DBA_TAB_PARTITIONS displays partition-level partitioning information, partition storage parameters, and partition statistics for all partitions in the database.
The ALL_TAB_PARTITIONS view is currently the same as the DBA_TAB_PARTITIONS view, and no permission verification is done like oracle.
USER_TAB_PARTITIONS displays such information for the partitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.

Table F.130. ALL_TAB_PARTITIONS

Column	Description
TABLE_OWNER	Owner of the table
TABLE_NAME	Name of the table
COMPOSITE	Indicates whether the table is composite-partitioned (YES) or not (NO)
PARTITION_NAME	Name of the partition
SUBPARTITION_COUNT	If this is a composite partitioned table, the number of subpartitions in the partition
PARTITION_POSITION	Position of the partition within the table
TABLESPACE_NAME	Name of the tablespace containing the partition
LOGGING	Indicates whether or not changes to the table are logged
NUM_ROWS	Number of rows in the partition
BLOCKS	Number of used data blocks in the partition
LAST_ANALYZED	Date on which this partition was most recently analyzed

F.55.8.43. [DBA/USER/ALL]_TAB_SUBPARTITIONS

DBA_TAB_SUBPARTITIONS displays the subpartition name, name of the table and partition to which it belongs, its storage attributes, and statistics for all subpartitions in the database.
The ALL_TAB_SUBPARTITIONS view is currently the same as the DBA_TAB_SUBPARTITIONS view, and no permission verification is done like oracle.
USER_TAB_SUBPARTITIONS displays such information for subpartitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.

Table F.131. ALL_TAB_SUBPARTITIONS

Column	Description
TABLE_OWNER	Owner of the table
TABLE_NAME	Name of the table
PARTITION_NAME	Name of the partition
SUBPARTITION_NAME	Name of the subpartition
PARTITION_POSITION	Position of the partition within the table
SUBPARTITION_POSITION	Position of the subpartition within the partition
TABLESPACE_NAME	Name of the tablespace containing the partition
LOGGING	Indicates whether or not changes to the table are logged
NUM_ROWS	Number of rows in the subpartition
BLOCKS	Number of blocks in the subpartition
LAST_ANALYZED	Date on which this table was most recently analyzed

F.55.8.44. [DBA/USER/ALL]_PART_KEY_COLUMNS

DBA_PART_KEY_COLUMNS describes the partitioning key columns for all partitioned objects in the database.
The ALL_PART_KEY_COLUMNS view is currently the same as the DBA_PART_KEY_COLUMNS view, and no permission verification is done like oracle.
USER_PART_KEY_COLUMNS describes the partitioning key columns for the partitioned objects owned by the current user. This view does not display the OWNER column.

Table F.132. ALL_PART_KEY_COLUMNS

Column	Description
OWNER	Owner of the partitioned table or index
NAME	Name of the partitioned table or index
OBJECT_TYPE	Object type: TABLE, INDEX
COLUMN_NAME	Name of the column
COLUMN_POSITION	Position of the column within the partitioning key

F.55.8.45. [DBA/USER/ALL]_SUBPART_KEY_COLUMNS

DBA_SUBPART_KEY_COLUMNS displays subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables) for all subpartitions in the database.
The ALL_SUBPART_KEY_COLUMNS view is currently the same as the DBA_SUBPART_KEY_COLUMNS view, and no permission verification is done like oracle.
USER_SUBPART_KEY_COLUMNS displays this information for subpartitions of all partitioned objects owned by the current user. This view does not display the OWNER column.

Table F.133. ALL_SUBPART_KEY_COLUMNS

Column	Description
OWNER	Owner of the partitioned table or index
NAME	Name of the partitioned table or index
OBJECT_TYPE	Object type: TABLE, INDEX
COLUMN_NAME	Name of the column
COLUMN_POSITION	Position of the column within the subpartitioning key

F.55.8.46. [DBA/USER/ALL]_IND_PARTITIONS

DBA_IND_PARTITIONS describes all index partitions in the database.
The ALL_IND_PARTITIONS view is currently the same as the DBA_IND_PARTITIONS view, and no permission verification is done like oracle.
USER_IND_PARTITIONS describes the index partitions owned by the current user. This view does not display the INDEX_OWNER column.

Table F.134. ALL_IND_PARTITIONS

Column	Description
INDEX_OWNER	Owner of the index
INDEX_NAME	Name of the index
COMPOSITE	Indicates whether the partition belongs to a local index on a composite-partitioned table (YES) or not (NO)
PARTITION_NAME	Name of the partition
SUBPARTITION_COUNT	If a local index on a composite-partitioned table, the number of subpartitions in the partition
PARTITION_POSITION	Position of the partition within the index
TABLESPACE_NAME	Name of the tablespace containing the partition
LOGGING	Indicates whether or not changes to the index are logged
NUM_ROWS	Number of rows returned
LAST_ANALYZED	Date on which this partition was most recently analyzed

F.55.8.47. [DBA/USER/ALL]_PART_INDEXES

DBA_PART_INDEXES displays the object-level partitioning information for all partitioned indexes in the database.
The ALL_PART_INDEXES view is currently the same as the DBA_PART_INDEXES view, and no permission verification is done like oracle.
USER_PART_INDEXES displays the object-level partitioning information for the partitioned indexes owned by the current user. This view does not display the OWNER column.

Table F.135. ALL_PART_INDEXES

Column	Description
OWNER	Owner of the partitioned index
INDEX_NAME	Name of the partitioned index
TABLE_NAME	Name of the partitioned table
PARTITIONING_TYPE	Type of the partitioning method: RANGE, HASH, LIST
SUBPARTITIONING_TYPE	Type of the composite partitioning method: RANGE, HASH, LIST
PARTITION_COUNT	Number of partitions in the index
PARTITIONING_KEY_COUNT	Number of columns in the partitioning key
SUBPARTITIONING_KEY_COUNT	For a composite-partitioned table, the number of columns in the subpartitioning key
LOCALITY	Indicates whether the partitioned index is local (LOCAL) or global (GLOBAL), always 'LOCAL'
ALIGNMENT	Indicates whether the partitioned index is prefixed (PREFIXED) or non-prefixed (NON_PREFIXED)
DEF_TABLESPACE_NAME	For a local index, the default tablespace to be used when adding or splitting a table partition
DEF_LOGGING	For a local index, the default LOGGING attribute to be used when adding a table partition