JSON_TABLE和CLOB > 32k

Question

我正在尝试阅读&解析一些JSON代码。它在大多数情况下都可以工作，除了JSON字符串为>32k，则不导入JSON字符串。我使用下面的代码。我们运行Oracle 12c SE:

INSERT INTO json_in (clob_data)
VALUES (EMPTY_CLOB())
RETURN clob_data INTO l_clob;
l_bfile := BFILENAME('TEMP_DIRECTORY', 'json.txt');
DBMS_LOB.FILEOPEN(l_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADCLOBFROMFILE (
DEST_LOB      => l_clob,
SRC_BFILE     => l_bfile,
AMOUNT        => dbms_lob.lobmaxsize,
DEST_OFFSET   => l_dest_offset,
SRC_OFFSET    => l_src_offset,
BFILE_CSID    => l_bfile_csid,
LANG_CONTEXT  => l_lang_context,
WARNING       => l_warning);
DBMS_LOB.FILECLOSE(l_bfile);
SELECT clob_data 
INTO v_json_string
FROM json_in;
FOR data IN (SELECT cid,
ts_id,
group_id,
c_subject,
c_message
FROM JSON_TABLE(v_json_string FORMAT JSON,'$[*]'
COLUMNS (
cid                  NUMBER          PATH '$.cid',
ts_id                NUMBER          PATH '$.ts_id',
group_id             NUMBER          PATH '$.group_id',
c_subject            VARCHAR2(300)   PATH '$.c_subject',
c_message            VARCHAR2(32000) PATH '$.c_message') LOOP
INSERT INTO commentary (cid,
ts_id,
group_id,
c_subject,
c_message)
VALUES (data.cid,
data.ts_id,
data.group_id,
data.c_subject,
data.c_message);
END LOOP;

好像c_message是>32000，所以，不确定如何让它处理更大的值。

多谢!

Answer 1

使用您已经读取的CLOB

类似于(未经测试):

DECLARE
l_clob         CLOB;
l_bfile        BFILE;
l_dest_offset  INTEGER := 1;
l_src_offset   INTEGER := 1;
l_bfile_csid   INTEGER := DBMS_LOB.DEFAULT_CSID;
l_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
l_warning      INTEGER;
BEGIN
-- INSERT INTO json_in (clob_data)
-- VALUES (EMPTY_CLOB())
-- RETURN clob_data INTO l_clob;
DBMS_LOB.CREATETEMPORARY(l_clob, FALSE);
l_bfile := BFILENAME('TEMP_DIRECTORY', 'json.txt');
DBMS_LOB.FILEOPEN(l_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADCLOBFROMFILE (
DEST_LOB      => l_clob,
SRC_BFILE     => l_bfile,
AMOUNT        => dbms_lob.lobmaxsize,
DEST_OFFSET   => l_dest_offset,
SRC_OFFSET    => l_src_offset,
BFILE_CSID    => l_bfile_csid,
LANG_CONTEXT  => l_lang_context,
WARNING       => l_warning
);
DBMS_LOB.FILECLOSE(l_bfile);
INSERT INTO commentary (
cid,
ts_id,
group_id,
c_subject,
c_message
)
SELECT cid,
ts_id,
group_id,
c_subject,
c_message
FROM   JSON_TABLE(
l_clob FORMAT JSON,
'$[*]'
COLUMNS (
cid                  NUMBER        PATH '$.cid',
ts_id                NUMBER        PATH '$.ts_id',
group_id             NUMBER        PATH '$.group_id',
c_subject            VARCHAR2(300) PATH '$.c_subject',
c_message            CLOB          PATH '$.c_message'
)
);
END;
/