sql - 我的 SQL 查询出现 ORA-00932 和 ORA-22835 错误
问题描述
我有一个 SQL 表:
CREATE TABLE "text_files"
( "FILE_NAME" VARCHAR2(4000 BYTE),
"FILE_CONTENT" CLOB
);
我正在尝试运行以下查询:
WITH rsqfc ( file_content, line, rn, max_rn, nm1, prv, depth ) AS (
SELECT file_content,
REGEXP_SUBSTR( file_content, '.+?(' || CHR(10) || '|$)', 1, 1 ),
1,
REGEXP_COUNT( file_content, '.+?(' || CHR(10) || '|$)' ) - 1,
CASE SUBSTR( file_content, 1, 4 ) WHEN 'NM1*' THEN 1 ELSE 0 END,
CASE SUBSTR( file_content, 1, 4 ) WHEN 'PRV*' THEN 1 ELSE 0 END,
CASE SUBSTR( file_content, 1, 4 ) WHEN 'NM1*' THEN 1 WHEN 'PRV*' THEN 2 ELSE 0 END
FROM text_files
UNION ALL
SELECT file_content,
REGEXP_SUBSTR( file_content, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ),
rn + 1,
max_rn,
CASE SUBSTR( REGEXP_SUBSTR( file_content, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ), 1, 4 ) WHEN 'NM1*' THEN nm1 + 1 ELSE nm1 END,
CASE SUBSTR( REGEXP_SUBSTR( file_content, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ), 1, 4 ) WHEN 'PRV*' THEN prv + 1 ELSE prv END,
CASE SUBSTR( REGEXP_SUBSTR( file_content, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ), 1, 4 ) WHEN 'NM1*' THEN 1 WHEN 'PRV*' THEN 2 ELSE depth END
FROM rsqfc
WHERE rn < max_rn
)
SELECT CASE depth
WHEN 2 THEN '2010A' || CHR( 64 + prv )
WHEN 1 THEN '1000' || CHR( 64 + nm1 )
WHEN 0 THEN '000'
END AS "LOOP",
line
FROM rsqfc;
然而它给了
ORA-00932: 不一致的数据类型: 预期 CLOB 得到 CHAR 错误
我知道我们不能将 CLOB 用于正则表达式和 where 子句。但是其中的内容CLOB
超过 4000 个字符,当我尝试使用VARCHAR2(4000)
字段更改表并复制CLOB
到新字段时,它不会复制。
它给了我
ORA-22835: 缓冲区太小,无法进行 CLOB 到 CHAR 或 BLOB 到 RAW 转换(实际:4436,最大值:4000)
我无法将文本分成多个字段。我需要 1 个字段中的整个文本。
任何帮助,将不胜感激。
解决方案
使用流水线函数:
Oracle 设置 - 测试数据:
CREATE TABLE text_files (
FILE_NAME VARCHAR2(4000 BYTE),
FILE_CONTENT CLOB
);
INSERT INTO text_files VALUES (
'test1',
'ISA*00*AUTHORIZAT*00*SECURITY I*ZZ*000000060000000*ZZ*000000010000000*110705*1132*^*00501*110705001*0*T*:~
GS*HC*00000006*00000001*20110705*113253*110705001*X*005010X222A1~
ST*837*0021*005010X222~
BHT*0019*00*244579*20061015*1023*CH~
NM1*41*2*PREMIER BILLING SERVICE*****46*TGJ23~
PER*IC*JERRY*TE*3055552222*EX*231~
NM1*40*2*KEY INSURANCE COMPANY*****46*66783JJT~
HL*1**20*1~
PRV*BI*PXC*203BF0100Y~
NM1*85*2*BEN KILDARE SERVICE*****XX*9876543210~
N3*234 SEAWAY ST~
N4*MIAMI*FL*33111~'
);
Oracle 设置 - 流水线功能:
CREATE TYPE line_detail AS OBJECT(
line_no NUMBER(38,0),
line CLOB,
loop VARCHAR2(6)
);
/
CREATE TYPE line_detail_array AS TABLE OF line_detail;
/
CREATE FUNCTION get_Line_Detail(
file_content IN CLOB
) RETURN line_detail_array PIPELINED
AS
p_line CLOB;
p_loop VARCHAR2(6) := '000';
p_substr CHAR(4);
p_line_no PLS_INTEGER := 0;
p_line_start PLS_INTEGER := 1;
p_line_end PLS_INTEGER;
p_line_len PLS_INTEGER;
p_length PLS_INTEGER;
p_nm1 PLS_INTEGER := 0;
p_prv PLS_INTEGER := 0;
BEGIN
IF file_content IS NULL THEN
RETURN;
END IF;
p_length := DBMS_LOB.GETLENGTH( file_content );
LOOP
p_line := EMPTY_CLOB();
p_line_no := p_line_no + 1;
p_substr := DBMS_LOB.SUBSTR( file_content, 4, p_line_start );
IF p_substr = 'PRV*' THEN
p_prv := p_prv + 1;
p_loop := '2010A' || CHR( 64 + p_prv );
ELSIF p_substr = 'NM1*' THEN
p_nm1 := p_nm1 + 1;
p_loop := '1000' || CHR( 64 + p_nm1 );
END IF;
p_line_end := DBMS_LOB.INSTR( file_content, CHR(10), p_line_start );
IF p_line_end = 0 THEN
p_line_end := p_length;
END IF;
LOOP
EXIT WHEN p_line_start > p_line_end;
p_line_len := LEAST( 4000, p_line_end - p_line_start + 1 );
p_line := p_line || DBMS_LOB.SUBSTR( file_content, p_line_len, p_line_start );
p_line_start := p_line_start + p_line_len;
END LOOP;
PIPE ROW( line_detail( p_line_no, p_line, p_loop ) );
EXIT WHEN p_line_end >= p_length;
END LOOP;
END;
/
查询:
SELECT c.*
FROM text_files t
CROSS JOIN TABLE( get_Line_Detail( t.file_content ) ) c;
输出:
LINE_NO | 线 | 环形 ------: | :------------------------------------------------ -------------------------------------------------- ---------- | :----- 1 | ISA*00*AUTHORIZAT*00*SECURITY I*ZZ*000000060000000*ZZ*000000010000000*110705*1132*^*00501*110705001*0*T*:~<br> | 000 2 | GS*HC*00000006*00000001*20110705*113253*110705001*X*005010X222A1~<br> | 000 3 | ST*837*0021*005010X222~<br> | 000 4 | BHT*0019*00*244579*20061015*1023*CH~<br> | 000 5 | NM1*41*2*高级计费服务*****46*TGJ23~<br> | 1000A 6 | PER*IC*JERRY*TE*3055552222*EX*231~<br> | 1000A 7 | NM1*40*2*KEY 保险公司*****46*66783JJT~<br> | 1000B 8 | HL*1**20*1~<br> | 1000B 9 | PRV*BI*PXC*203BF0100Y~<br> | 2010AA 10 | NM1*85*2*BEN KILDARE SERVICE*****XX*9876543210~<br> | 1000℃ 11 | N3*234 SEAWAY ST~<br> | 1000℃ 12 | N4*迈阿密*佛罗里达*33111~ | 1000℃
db<>在这里摆弄
推荐阅读
- c++ - own tuple implementation segfaults on gcc while works in clang
- docker - Docker Compose 总是强制构建一个服务
- python - 打印新列错误值的长度与索引的长度不匹配
- azure - Azure ARM 部署 - 缓存的 JSON/参数值
- python - 如何在应用函数上连接总和并将数据帧打印为文件中的表格格式
- ios - 如何获取 iOS 共享扩展以从 Safari 而不是 URL 获取网页内容
- openldap - LMDB:如何有效地存储大值大小
- javascript - What is the correct way to use object destructing in this situation?
- javascript - 是否可以根据 activeRange 更改公式中的单元格引用?
- c - 如何修复函数“_start”中的错误:(.text+0x20):未定义对“main”的引用