orafce - Oracle's compatibility functions and packages.
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.37. 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. |
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.38. SQL Queries
|
Item |
Overview |
|---|---|
|
DUAL table |
Table provided by the system |
Table F.39. Mathematical Functions
|
Item |
Overview |
|---|---|
|
BITAND |
Performs a bitwise AND operation |
|
COSH |
Calculates the hyperbolic cosine of a number |
|
SINH |
Calculates the hyperbolic sine of a number |
|
TANH |
Calculates the hyperbolic tangent of a number |
Table F.40. String Functions
|
Item |
Overview |
|---|---|
|
INSTR |
Returns the position of a substring in a string |
|
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 |
|
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 |
|
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.41. 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 |
Table F.42. Data Type Formatting Functions
|
Item |
Overview |
|---|---|
|
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.43. 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.44. Aggregate Functions
|
Item |
Overview |
|---|---|
|
LISTAGG |
Returns a concatenated, delimited list of string values |
|
MEDIAN |
Calculates the median of a set of values |
Table F.45. Functions that return internal information
|
Item |
Overview |
|---|---|
|
DUMP |
Returns internal information of a value |
Table F.46. SQL Operators
|
Item |
Overview |
|---|---|
|
Datetime operator |
Datetime operator for the DATE type |
Table F.47. 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 |
|
UTL_FILE |
Enables text file operations |
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.48. Data type
|
Item |
Standard feature of LightDB |
Compatibility feature added by orafce |
|---|---|---|
|
DATE |
Stores date only. |
Stores date and time. |
Table F.49. 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.50. Function
|
Feature |
|---|
|
SYSDATE |
|
DBTIMEZONE |
|
SESSIONTIMEZONE |
|
TO_CHAR (date/time value) |
Table F.51. 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.
The following data types are supported:
VARCHAR2
NVARCHAR2
DATE
Syntax
Specify the VARCHAR2 type as follows.
Table F.52. 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 bytes 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.
Syntax
Specify the NVARCHAR2 type as follows.
Table F.53. 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.
Syntax
Specify the DATE type as follows.
Table F.54. 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.
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)
The following mathematical functions are supported:
BITAND
COSH
SINH
TANH
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)
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)
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)
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)
The following string functions are supported:
BTRIM
INSTR
LENGTH
LENGTHB
LPAD
LTRIM
NLSSORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
RPAD
RTRIM
SUBSTR
SUBSTRB
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)
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)
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)
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)
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)
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)
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.55. 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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
The following date/time functions are supported:
ADD_MONTHS
DBTIMEZONE
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
SESSIONTIMEZONE
SYSDATE
TRUNC
Note
If the DATE type only is shown in the date/time functions, these functions can be used in both orafce and LightDB.
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)
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)
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)
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)
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.56. 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)
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.57. 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)
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)
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)
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)
The following data type formatting functions are supported:
TO_CHAR
TO_DATE
TO_MULTI_BYTE
TO_NUMBER
TO_SINGLE_BYTE
ASCIISTR
ASCII
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 orafce.nls_date_format variable in advance. A setting example using the SET statement is shown below.
SET orafce.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 orafce.nls_date_format settings can be set using any of the methods for setting server parameters.
If orafce.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 "orafce.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)
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 oracle.nls_date_format variable is used. If the oracle.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 orafce.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 orafce.nls_date_format settings can be set using any of the methods for setting server parameters.
If orafce.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 "orafce.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)
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 oracle.nls_date_format variable is used. If the oracle.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".
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)
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)
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)
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)
The following functions for making comparisons are supported:
DECODE
LNNVL
NANVL
NVL
NVL2
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.58. 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.59. 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)
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)
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)
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)
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)
The following aggregation functions are supported:
LISTAGG
MEDIAN
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.
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)
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)
The following functions that return internal information are supported:
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)
The following datetime operators are supported for the DATE type of orafce.
Table F.60. 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.
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
UTL_FILE
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.
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.61. 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
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);
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();
Overview
Performs verification of the properties of input values in PL/pgSQL.
Table F.62. 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
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');
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();
Overview
Some functions compatible with DBMS_LOB package.
Table F.63. DBMS_LOB Features
| Feature | Description |
|---|---|
| INSTR | Returns starting index of specified string. |
| SUBSTR | Extracts the substring of string. |
| GETLENGTH | Returns the number of characters in the string. |
Syntax
INSTR(STR TEXT, PATT TEXT, START INTEGER DEFAULT 1, NTH INTEGER DEFAULT 1) RETURNS TEXT
SUBSTR(STR TEXT, AMOUNT INTEGER DEFAULT 32767, START_OFFSET INTEGER DEFAULT 1) RETURNS TEXT
GETLENGTH(STR TEXT) RETURNS INTEGER
Features
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
PERFORM DBMS_LOB.INSTR('Thomas', 'om');
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
PERFORM DBMS_LOB.SUBSTR('Thomas', 'om');
GETLENGTH
The length of the text in bytes or characters as an INTEGER.
NULL is returned if the input str is NULL.
Example
PERFORM DBMS_LOB.GETLENGTH('abc');
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();
Overview
Sends messages to clients such as ltsql from PL/pgSQL.
Features
Table F.64. 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
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);
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();
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.65. 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.66. 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
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.67. 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();
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();
Overview
Generates random numbers in PL/pgSQL.
Table F.68. 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
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.69. 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();
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();
Overview
Provides utilities of PL/pgSQL.
Table F.70. DBMS_UTILITY Features
| Feature | Description |
|---|---|
| FORMAT_CALL_STACK | Returns the current call stack. |
| GET_HASH_VALUE | A hash value based on the input string. |
Syntax
FORMAT_CALL_STACK(fmt text);
FORMAT_CALL_STACK();
GET_HASH_VALUE(name VARCHAR2,base INTEGER,hash_size INTEGER)
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.71. 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);
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();
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.72. 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
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';
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');
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.
Most of the transaction behavior are exactly same, however the below stuff is not.
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.
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.