LightDB23.3新特性Oracle模式 全面支持Oracle to_clob函数

命令行中使用to_clob

CREATE TABLE test_table (
  col_char CHAR(10),
  col_varchar2 VARCHAR2(20),
  col_nchar NCHAR(10),
  col_nvarchar2 NVARCHAR2(20),
  col_clob CLOB,
  col_raw raw(10)
);

INSERT INTO test_table (col_char, col_varchar2, col_nchar, col_nvarchar2, col_clob, col_raw)
VALUES ('ABC', 'Hello', N'中文', N'こんにちは', 'This is a CLOB', 'AB');

INSERT INTO test_table (col_char, col_varchar2, col_nchar, col_nvarchar2, col_clob, col_raw)
VALUES ('ABC', 'Hello', N'中文', N'こんにちは', 'This is a CLOB', '1');

INSERT INTO test_table (col_char, col_varchar2, col_nchar, col_nvarchar2, col_clob, col_raw)
VALUES ('ABC', 'Hello', N'中文', N'こんにちは', 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_nchar) AS clob_nchar,
       TO_CLOB(col_nvarchar2) AS clob_nvarchar2,
       TO_CLOB(col_clob) AS clob_clob,
       TO_CLOB(col_raw) AS clob_nclob
FROM test_table;

drop table test_table;
预期结果：
zhangliang[lightdb@bitone ~/test/to_clob]$ /home/lightdb/stage/lightdb-x/bin/ltsql -d test_oracle -f test1.sql 
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 clob_char  | clob_varchar2 |  clob_nchar  | clob_nvarchar2 |   clob_clob    | clob_nclob 
------------+---------------+--------------+----------------+----------------+------------
 ABC        | Hello         | 中文         | こんにちは     | This is a CLOB | AB
 ABC        | Hello         | 中文         | こんにちは     | This is a CLOB | 01
 ABC        | Hello         | 中文         | こんにちは     | This is a CLOB | 01AB456789
(3 rows)

DROP TABLE





plorasql中使用to_clob
CREATE TABLE test_table (
  col_char CHAR(10),
  col_varchar2 VARCHAR2(20),
  col_nchar NCHAR(10),
  col_nvarchar2 NVARCHAR2(20),
  col_clob CLOB,
  col_raw raw(10)
);

CREATE OR REPLACE PROCEDURE test_to_clob()
AS 
DECLARE
    col_char       clob;
    col_varchar2   clob;
    col_nchar      clob;
    col_nvarchar2  clob; 
    col_clob       clob;
    col_raw        clob;
BEGIN
    INSERT INTO test_table (col_char, col_varchar2, col_nchar, col_nvarchar2, col_clob, col_raw) VALUES ('ABC', 'Hello', N'中文', N'こんにちは', 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_nchar) AS clob_nchar,
         TO_CLOB(col_nvarchar2) AS clob_nvarchar2, TO_CLOB(col_clob) AS clob_clob, TO_CLOB(col_raw) AS clob_nclob
    FROM test_table into col_char, col_varchar2, col_nchar, col_nvarchar2, col_clob, col_raw;
    RAISE '%', col_char || ',' || col_varchar2 || ',' || col_nchar || ',' || col_nvarchar2 || ',' || col_clob || ',' || col_raw;
END;
/

call test_to_clob();

drop table test_table;
drop procedure test_to_clob();

预期结果：
zhangliang[lightdb@bitone ~/test/to_clob]$ /home/lightdb/stage/lightdb-x/bin/ltsql -d test_oracle -f test2.sql 
CREATE TABLE
CREATE PROCEDURE
ltsql:test2.sql:28: ERROR:  ABC       ,Hello,中文        ,こんにちは,This is a CLOB,01AB456789
CONTEXT:  PL/oraSQL function test_to_clob() line 14 at RAISE
DROP TABLE
DROP PROCEDURE


非oracle模式下
CREATE TABLE test_table (
  col_char CHAR(10),
  col_varchar2 VARCHAR2(20),
  col_nchar NCHAR(10),
  col_nvarchar2 NVARCHAR2(20)
);

INSERT INTO test_table (col_char, col_varchar2, col_nchar, col_nvarchar2)
VALUES ('ABC', 'Hello', N'中文', N'こんにちは');

SELECT TO_CLOB(col_char) AS clob_char,
       TO_CLOB(col_varchar2) AS clob_varchar2,
       TO_CLOB(col_nchar) AS clob_nchar,
       TO_CLOB(col_nvarchar2) AS clob_nvarchar2
FROM test_table;

drop table test_table;

预期结果：
ERROR:  function to_clob(character) does not exist
LINE 1: SELECT TO_CLOB(col_char) AS clob_char,
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.