Release date: 2023-09-30
The CREATE SEQUENCE command supports declaring NOMAXVALUE when creating a sequence, which is compatible with the NOMAXVALUE definition when creating a sequence in Oracle.
See CREATE SEQUENCE.
Example:
create sequence s1 nomaxvalue;
\d+ s1
Sequence "public.s1"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
The CREATE SEQUENCE command supports declaring MAXVALUE when creating a sequence,
which is compatible with the MAXVALUE definition when creating a sequence in Oracle.
See CREATE SEQUENCE.
Example:
create sequence s2 maxvalue 100000;
\d+ s2
Sequence "public.s2"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------+-----------+---------+-------
bigint | 1 | 1 | 100000 | 1 | no | 1
The CREATE TABLE command supports the nocompress/compress keywords when creating partitions.
See CREATE TABLE.
Example:
CREATE TABLE lt_oracle_partition_list1
(
a int,
b float,
c date,
d timestamp,
e varchar2(20)
) PARTITION BY LIST(e)
(
PARTITION p1 VALUES ('0001', '0002', '0003', '0004', '0005') compress,
PARTITION p2 VALUES ('0006', '0007', '0008', '0009') nocompress,
PARTITION p3 VALUES ('0010', '0011')
);
\d+ lt_oracle_partition_list1
Partitioned table "public.lt_oracle_partition_list1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | oracle.date | | | | plain | |
d | timestamp without time zone | | | | plain | |
e | varchar2(20) | | | | extended | |
Partition key: LIST (e)
Partitions: "lt_oracle_partition_list1$p$p1" FOR VALUES IN ('0001', '0002', '0003', '0004', '0005'),
"lt_oracle_partition_list1$p$p2" FOR VALUES IN ('0006', '0007', '0008', '0009'),
"lt_oracle_partition_list1$p$p3" FOR VALUES IN ('0010', '0011')
The CREATE TABLE command supports creating partitions using a list+hash composite partition.
See CREATE TABLE.
Example:
create table lt_lh_partition(a int, b int)
partition by list(a)
subpartition by hash(b)
(
partition l_1 values(1),
partition l_2 values(2)
);
\d+ lt_lh_partition
Partitioned table "public.lt_lh_partition"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
Partition key: LIST (a)
Partitions: "lt_lh_partition$p$l_1" FOR VALUES IN (1), PARTITIONED,
"lt_lh_partition$p$l_2" FOR VALUES IN (2), PARTITIONED
\d+ lt_lh_partition$p$l_1
Partitioned table "public.lt_lh_partition$p$l_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
Partition of: lt_lh_partition FOR VALUES IN (1)
Partition constraint: ((a IS NOT NULL) AND (a = 1))
Partition key: HASH (b)
Partitions: "lt_lh_partition$p$l_1_p0" FOR VALUES WITH (modulus 1, remainder 0)
The CREATE TABLE command supports options such as compress/nocompress and logging/nologging.
Previous versions had order restrictions for these options, but this version allows them to appear in arbitrary order.
See CREATE TABLE.
It is not allowed to have the same table name as a package name when creating a table using CREATE TABLE and when creating a package using CREATE PACKAGE.
The ALTER TABLE command supports adding multiple columns at once by placing the column definitions in parentheses.
See ALTER TABLE.
Example:
CREATE TABLE foo(a int);
ALTER TABLE foo ADD COLUMN a1 int;
ALTER TABLE foo ADD COLUMN a2 int default 3;
ALTER TABLE foo ADD COLUMN b1 int, ADD COLUMN b2 int;
ALTER TABLE foo ADD (c1 int);
ALTER TABLE foo ADD (c2 int default 3);
ALTER TABLE foo ADD (e1 int), ADD e2 int, ADD COLUMN e3 int, ADD (e4 int, e5 int);
begin
execute immediate 'alter table foo add(d int)';
end;
/
The ALTER TABLE command supports the TRUNCATE PARTITION partition_name statement with the update indexes specified.
See ALTER TABLE.
Example:
create table part_update_indexes_range(id int primary key)
PARTITION by range(id)(
partition p1 VALUES LESS THAN (202000) nocompress,
partition p2 VALUES LESS THAN (202001) compress,
partition p3 VALUES LESS THAN (202002)
);
ALTER TABLE part_update_indexes_range TRUNCATE PARTITION p1 update indexes;
The DROP TABLE command supports cascading the deletion of all constraints in the table using the cascade constraints option.
See DROP TABLE.
Example:
CREATE TABLE student(id int, name varchar2(100));
ALTER TABLE student ADD CONSTRAINT uk_student unique(id);
\d student
Table "public.student"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
id | integer | | |
name | varchar2(100) | | |
Indexes:
"uk_student" UNIQUE CONSTRAINT, btree (id)
DROP TABLE student cascade constraints;
A new syntax has been added for creating object types using CREATE TYPE name { AS | IS } OBJECT.
See CREATE TYPE.
Example:
select dbms_output.serveroutput(true);
CREATE TYPE stock AS OBJECT (
stock_no int,
price numeric(16,2),
member procedure dis(prompt varchar)
);
CREATE TYPE BODY stock AS
member procedure dis(prompt varchar) is
begin
dbms_output.put_line(prompt || ':' || self.stock_no || '-' || self.price);
end;
END;
/
DECLARE
s stock := stock(600570, 180.00);
BEGIN
s.dis('current');
END;
/
current:600570-180.00
DO
A new TO_CLOB function has been added, which allows converting data from a text string type to a CLOB type.
See orafce.
Example:
CREATE TABLE testorafce_to_clob (
col_char CHAR(10),
col_varchar2 VARCHAR2(20),
col_varchar VARCHAR(20),
col_nchar NCHAR(10),
col_nvarchar2 NVARCHAR2(20),
col_smallint smallint,
col_integer integer,
col_bigint bigint,
col_decimal decimal,
col_numeric numeric,
col_real real,
col_double double precision,
col_clob CLOB,
col_raw raw(10)
);
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC1', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', 'AB');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC2', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', '1');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC3', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, to_clob('This is a CLOB'), '1AB456789');
SELECT to_clob(col_char) AS clob_char,
to_clob(col_varchar2) AS clob_varchar2,
to_clob(col_varchar) AS col_varchar,
to_clob(col_nchar) AS clob_nchar,
to_clob(col_nvarchar2) AS clob_nvarchar2,
to_clob(col_clob) AS clob_clob,
to_clob(col_smallint) AS col_smallint,
to_clob(col_integer) AS col_integer,
to_clob(col_bigint) AS col_bigint,
to_clob(col_decimal) AS col_decimal,
to_clob(col_numeric) AS col_numeric,
to_clob(col_real) AS col_real,
to_clob(col_double) AS col_double,
to_clob(col_raw) AS clob_nclob
FROM testorafce_to_clob order by col_char asc;
clob_char | clob_varchar2 | col_varchar | clob_nchar | clob_nvarchar2 | clob_clob | col_smallint | col_integer | col_bigint | col_decimal | col_numeric | col_real | col_double | clob_nclob
------------+---------------+-------------+------------+----------------+----------------+--------------+-------------+------------+-------------+-------------+----------+------------+------------
ABC1 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | AB
ABC2 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01
ABC3 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01AB456789
(3 rows)
A new XMLType object type has been added, which supports the XMLType.getClobVal method.
See orafce.
Example:
CREATE TABLE xml_table (id NUMBER, xml_column XMLType );
INSERT INTO xml_table (id, xml_column) VALUES (1, XMLType('<root><name>John</name></root>'));
DECLARE
v_clob CLOB;
BEGIN
SELECT XMLType.getClobVal(xml_column) INTO v_clob FROM xml_table WHERE id = 1;
DBMS_OUTPUT.PUT_LINE('CLOB value: ' || v_clob);
END;
/
CLOB value: <root><name>John</name></root>
DO
DECLARE
xml_data XMLType;
clob_data CLOB;
BEGIN
xml_data := XMLType('<root><name>John Doe</name></root>');
clob_data := xml_data.getClobVal();
DBMS_OUTPUT.PUT_LINE(clob_data);
END;
/
<root><name>John Doe</name></root>
DO
SELECT XMLType.getClobVal(xml_column) from xml_table;
getclobval
--------------------------------
<root><name>John</name></root>
(1 row)
A new GUC parameter lightdb_oracle_sql_mode has been added to support the default conversion of Oracle mode table names to uppercase.
See lightdb_oracle_sql_mode.
Example:
create table std(no int, name varchar2(32));
insert into std values(1, 'zhangsan');
select * from std;
no | name
----+----------
1 | zhangsan
(1 row)
set lightdb_oracle_sql_mode to 'true';
select * from std;
NO | NAME
----+----------
1 | zhangsan
(1 row)
The ltsql command has added support for the Oracle SQL*Plus RENAME old_name TO new_name command, which allows renaming tables.
See RENAME command.
The ltsql command has added support for the Oracle SQL*Plus SPOOL command.
See SPOOL command.
In Oracle mode, support is added for binary_float and binary_double,
both of which follow the IEEE754 standard.
See orafce.
In Oracle mode, '\0' can be inserted normally into char(n) type strings.
Example:
create database oradb with lightdb_ascii_zero_store_value 20 lightdb_syntax_compatible_type 'oracle';
\c oradb
create table t00(a char(1), b char(2));
insert into t00(a,b) values(E'\0',E'\0');
insert into t00(a,b) values(chr(0),chr(0));
select * from t00;
a | b
------+-------
\x14 | \x14
\x14 | \x14
(2 rows)
'\0' is converted to the lightdb_ascii_zero_store_value value for storage.
In Oracle mode, the result of subtracting dates is compatible with Oracle and returns a numeric type.
The col view is compatible with Oracle, and the width field remains compatible with Oracle.
greatest/least are compatible with Oracle. See GREATEST and LEAST.
The following compatibility features have been mainly implemented:
Returns NULL if there is a NULL parameter.
The types of the remaining parameters are forced to convert to the type of the first parameter.
If a NULL parameter is encountered, parsing of the remaining parameters is stopped.
All Oracle functions in orafce are now default safe,
and parallel processing is enabled by default.
pl/sql functions support transaction commit and rollback. See Enhancements for Oracle.
pl/sql functions support inout parameters.
See Declaring Function Parameters.
pl/sql functions support DML statements. See Executing DML in pl/sql.
pl/sql stored procedures support the use of CREATE DOMAIN to create cursor type variables.
Example:
SELECT dbms_output.serveroutput(true);
CREATE TABLE employees(employee_id int, first_name varchar2(100), last_name varchar2(100));
INSERT INTO employees values(1500, 'first15', 'last15');
CREATE DOMAIN mycursor AS refcursor;
CREATE OR REPLACE PROCEDURE get_employee_name AS
employee_cursor mycursor;
v_employee_name VARCHAR2(50);
BEGIN
OPEN employee_cursor FOR SELECT first_name FROM employees WHERE employee_id = 1500;
LOOP
FETCH employee_cursor INTO v_employee_name;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END LOOP;
CLOSE employee_cursor;
END;
/
call get_employee_name ();
Employee Name: first15
CALL
New INSERT IGNORE functionality.
See INSERT for more information.
Example:
create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
create table t(a int primary key, b int);
insert ignore into t(a,b) values(1,1),(1,1),(2,2);
select * from t;
a | b
---+---
1 | 1
2 | 2
(2 rows)
A new collection of JSON functions has been added.
See MySQL Compatible Functions JSON functions for more information. The list of functions is as follows:
JSON_ARRAY
JSON_OBJECT
JSON_EXTRACT
JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_PRETTY
JSON_REMOVE
JSON_INSERT
JSON_REPLACE
JSON_SET
A new parameter lightdb_mysql_lower_case_table_names has been added.
When creating a database using the create database statement,
you can specify this parameter to set whether case-sensitive functionality is enabled.
See CREATE DATABASE for more information.
The varchar type is compatible with boolean type data.
See MySQL Compatible Functions for more information.
A new upgrade check tool called lt_upgrade_check has been added to perform compatibility and exception checks before upgrading,
in order to minimize the possibility of upgrade failures.
See lt_upgrade_check for more information.
Support for using ‘–’ and ‘//’ comments has been added to ECPG(Oracle Pro*c Compatible) code.
See ECPG(Oracle Pro*c Compatible) for more information.
New support for GBK, GB18030-2022 on both server and client sides has passed the national standard certification of the People's Bank of China. See localization for details.
The new behavior of lt_createdb is to propagate to DN nodes by default in a CN distributed environment.
See lt_createdb for more information.
A new GUC parameter called lightdb_cursor_after_commit has been added to control fetch behavior.
When lightdb_cursor_after_commit is set to off,
fetch operations cannot be performed on a result set after a transaction commit. When set to on,
the cursor can continue to be used after a transaction commit.
See lightdb_cursor_after_commit for more information.
Added support for the distinct connect by level syntax.
Added support for the connect_by_isleaf syntax.
See SELECT for more information.
Enhanced support for displaying the compatibility mode of the current service database in ltsql.
Enhanced support for cross-version upgrades in lt_upgrade.
ltloader enhances performance to over 80% of Oracle's sqlldr.
See ltldr for more information.
Enhancements have been made to lt_dump.
See lt_dump for more information.
The enhancements include:
The --lt-exclude-lightdb-objects option supports filtering out built-in users lightdb and lt_probackup.
The --lt-exclude-lightdb-objects option supports exporting ACCESS METHOD.
Both lt_dump and lt_dumpall have added the --lt-target-version option, which allows you to specify the target version for the exported content.
The default template for exporting is now template1.
The create database statement now supports the lightdb_syntax_compatible_type option logic.
Enhanced support for querying fields larger than 64K has been added to mysql_fdw.
ltjdbc supports numeric with Short type.
ltjdbc supports passing Types.CLOB and Types.BLOB to setNull function.
ltjdbc supports using string type for integer values.
ltjdbc supports the function xmlparse.