oracle ORA-00932：不一致的数据类型：预期数量在INFORMATICA PowerCenter中获得CLOB

ttisahbt 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(104)

我得到下面的错误，而试图在informatica运行会话。

ERROR  10/11/2023 2:56:21 PM  node02  READER_1_1_1  RR_4035  SQL Error [ ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

我在源限定符中添加了以下SQL查询，以便从源中的CLOB提取数据。

select objectid,db_instance_seq, 
REGEXP_SUBSTR(DATA,'\|(RES_GetResData_Public_ScreenPrint.*?)\|(.*?)\|(.*?)\|(.*?)\|',1,column_value,NULL,1) AS TEXT,
REGEXP_SUBSTR(DATA,'\|(RES_GetResData_Public_ScreenPrint.*?)\|(.*?)\|(.*?)\|(.*?)\|',1,column_value,NULL,2) AS SVCFID, 
REGEXP_SUBSTR(DATA,'\|(RES_GetResData_Public_ScreenPrint.*?)\|(.*?)\|(.*?)\|(.*?)\|',1,column_value,NULL,3) AS AX_TYP_CD,
REGEXP_SUBSTR(DATA,'\|(RES_GetResData_Public_ScreenPrint.*?)\|(.*?)\|(.*?)\|(.*?)\|',1,column_value,NULL,4) AS VALUE
from   TEST
       cross join table(
          cast(
           multiset(
             select level
             from   dual  
             where DATA LIKE '%RES_GetResData_Public_ScreenPrint%' 
             connect by level <= regexp_count(
                                   DATA,
                                   '\|(RES_GetResData_Public_ScreenPrint.*?)\|(.*?)\|(.*?)\|(.*?)\|'
                                 )
                   ) 
           as sys.odcinumberlist
         )
       )

oracle

来源：https://stackoverflow.com/questions/77276407/ora-00932-inconsistent-datatypes-expected-number-got-clob-in-informatica-power

1条答案

按热度按时间

v7pvogib1#

除了正则表达式，你可以使用简单的字符串函数（这要快得多）和递归子查询因子子句：

WITH rsqfc (objectid, db_instance_seq, data, spos, dpos1, dpos2, dpos3, epos) AS (
  SELECT objectid,
         db_instance_seq,
         data,
         INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1) + 34, 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1) + 34, 2),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1) + 34, 3),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1) + 34, 4)
  FROM   test
UNION ALL
  SELECT objectid,
         db_instance_seq,
         data,
         INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1) + 34, 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1) + 34, 2),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1) + 34, 3),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1) + 34, 4)
  FROM   rsqfc
  WHERE  spos > 0
  AND    epos > 0
)
SEARCH DEPTH FIRST BY objectid, db_instance_seq SET order_id
SELECT objectid,
       db_instance_seq,
       SUBSTR(data, spos + 1, dpos1 - spos - 1) AS text1,
       SUBSTR(data, dpos1 + 1, dpos2 - dpos1 - 1) AS text2,
       SUBSTR(data, dpos2 + 1, dpos3 - dpos2 - 1) AS text3,
       SUBSTR(data, dpos3 + 1, epos - dpos3 - 1) AS text4
from   rsqfc
WHERE  spos > 0
AND    epos > 0;

其中，对于样本数据：

CREATE TABLE test (objectid, db_instance_seq, data) AS
  SELECT 1,
         1,
         EMPTY_CLOB()
         || '|RES_GetResData_Public_ScreenPrint - AAAAA|BBBB|CCCC|DDDD|' || CHR(10)
         || 'XXXX|RES_GetResData_Public_ScreenPrint - EEEEE|F||H|I|J|K' || CHR(10)
  FROM   DUAL;

输出：
| objectID|数据库_序列号|TEXT1| TEXT2| TEXT3| TEXT4|
| --|--|--|--|--|--|
| 1 | 1 |RES_GetResData_Public_ScreenPrint - AAAAA| BBBB|中交|DDDD|
| 1 | 1 |RES_GetResData_Public_ScreenPrint -EEE| F| * 空 *| H|
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle ORA-00932：不一致的数据类型：预期数量在INFORMATICA PowerCenter中获得CLOB

1条答案

相关问题

热门标签

最新问答