ORA-00932:数据类型不一致:预期 - 获得 CLOB - 从 clob 列获取 XML

Question

我把这个XML存储在CLOB列中：

<SvcData><Status><StatusCode>EXI</StatusCode><StatusDesc></StatusDesc><StatusCodeAlt>000000</StatusCodeAlt><StatusDescAlt>OK</StatusDescAlt><StatusCodeHost></StatusCodeHost><StatusCodeHostSeverity>OK</StatusCodeHostSeverity><StatusCodeError>000000</StatusCodeError></Status><ConnectorResponse><ModuleResponse>
  <statusCode>000000</statusCode>
  <messageError/>
  <concreteReturn class="net.excelsys.delivery.icbs.base.dto.IcbsPreviousDataDTO">
    <previousData class="net.excelsys.delivery.icbs.base.dto.IcbsPreviousDataDTO$HolderDTO">
      <content class="ClientLimitAmountDTO">
        <services>
          <ClientLimitAmountServiceDTO>
            <serviceId>210640</serviceId>
            <serviceName>Pagos Virtuales</serviceName>
            <limitList>
              <LimitTypeDTO>
                <limitName>TRANSACTION</limitName>
                <status>true</status>
                <limitAmount>1000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>DAILY</limitName>
                <status>true</status>
                <limitAmount>100000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>MONTHLY</limitName>
                <status>false</status>
                <limitAmount>-1.00</limitAmount>
              </LimitTypeDTO>
            </limitList>
          </ClientLimitAmountServiceDTO>
          <ClientLimitAmountServiceDTO>
            <serviceId>210733</serviceId>
            <serviceName>Transferencias - Sebra</serviceName>
            <limitList>
              <LimitTypeDTO>
                <limitName>TRANSACTION</limitName>
                <status>true</status>
                <limitAmount>1000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>DAILY</limitName>
                <status>true</status>
                <limitAmount>100000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>MONTHLY</limitName>
                <status>true</status>
                <limitAmount>1000000000.00</limitAmount>
              </LimitTypeDTO>
            </limitList>
          </ClientLimitAmountServiceDTO>
          <ClientLimitAmountServiceDTO>
            <serviceId>210643</serviceId>
            <serviceName>Pagos Virtuales PSE</serviceName>
            <limitList>
              <LimitTypeDTO>
                <limitName>TRANSACTION</limitName>
                <status>true</status>
                <limitAmount>1000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>DAILY</limitName>
                <status>true</status>
                <limitAmount>100000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>MONTHLY</limitName>
                <status>true</status>
                <limitAmount>1000000000.00</limitAmount>
              </LimitTypeDTO>
            </limitList>
          </ClientLimitAmountServiceDTO>
        </services>
      </content>
    </previousData>
  </concreteReturn>
</ModuleResponse></ConnectorResponse></SvcData>

我有这个查询来获取XML列并将其作为普通查询结果读取：

SELECT
  Q.ServiceId,
  Q.ServiceName,
  S.LimitName,
  W.Status,
  X.LimitAmount
FROM
  LTX_ENGINE_LOGGER LEL
  LEFT JOIN xmltable('SvcData/ConnectorResponse/ModuleResponse/concreteReturn/previousData/content/services/ClientLimitAmountServiceDTO'
                      passing LEL.DATA_RECV
                      COLUMNS ServiceId       NUMBER(10)      path 'service',
                              ServiceName     VARCHAR2(1000)  path 'serviceName',
                              LimitNameXML    XmlType         path 'limitList/LimitTypeDTO/limitName',
                              StatusXML       XmlType         Path 'limitList/LimitTypeDTO/status',
                              LimitAmountXML  XmlType         Path 'limitList/LimitTypeDTO/limitAmount'
                      ) Q ON (1=1)
  LEFT JOIN xmltable('/limitName'
                      passing Q.LimitNameXML
                      COLUMNS LimitName       VARCHAR2(1000)  path '.') S ON (1=1)
  LEFT JOIN xmltable('/status'
                      passing Q.statusXML
                      COLUMNS Status VARCHAR2(1000) path '.') W ON (1=1)
  LEFT JOIN xmltable('/limitAmount'
                      passing Q.LimiteMontoXML
                      COLUMNS LimitAmount VARCHAR2(1000) path '.') X ON (1=1);

我有这个错误：

ORA-00932：数据类型不一致：预期 - 获得 CLOB00932. 00000 - "不一致的数据类型：预期的 %s 得到 %s">

我需要获得这种结果：

ServiceID     ServiceName            LimitName     Status     LimitAmount
210640        Pagos Virtuales        TRANSACTION   true       1000000.00
210640        Pagos Virtuales        DAILY         true       100000000.00
210640        Pagos Virtuales        MONTHLY       false      -1.00
210733        Transferencias - Sebra TRANSACTION   true       1000000.00
210733        Transferencias - Sebra DAILY         true       100000000.00
210733        Transferencias - Sebra MONTHLY       true       -1.00
210643        Pagos Virtuales PSE    TRANSACTION   true       1000000.00
210643        Pagos Virtuales PSE    DAILY         true       100000000.00
210643        Pagos Virtuales PSE    MONTHLY       true       -1.00

我该如何解决这个问题？

感谢您的帮助。

Answer 1

工作解决方案。

with LTX_ENGINE_LOGGER
     as (select 
                  '<SvcData><Status><StatusCode>EXI</StatusCode><StatusDesc></StatusDesc><StatusCodeAlt>000000</StatusCodeAlt><StatusDescAlt>OK</StatusDescAlt><StatusCodeHost></StatusCodeHost><StatusCodeHostSeverity>OK</StatusCodeHostSeverity><StatusCodeError>000000</StatusCodeError></Status><ConnectorResponse><ModuleResponse>
  <statusCode>000000</statusCode>
  <messageError/>
  <concreteReturn class="net.excelsys.delivery.icbs.base.dto.IcbsPreviousDataDTO">
    <previousData class="net.excelsys.delivery.icbs.base.dto.IcbsPreviousDataDTO$HolderDTO">
      <content class="ClientLimitAmountDTO">
        <services>
          <ClientLimitAmountServiceDTO>
            <serviceId>210640</serviceId>
            <serviceName>Pagos Virtuales</serviceName>
            <limitList>
              <LimitTypeDTO>
                <limitName>TRANSACTION</limitName>
                <status>true</status>
                <limitAmount>1000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>DAILY</limitName>
                <status>true</status>
                <limitAmount>100000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>MONTHLY</limitName>
                <status>false</status>
                <limitAmount>-1.00</limitAmount>
              </LimitTypeDTO>
            </limitList>
          </ClientLimitAmountServiceDTO>
          <ClientLimitAmountServiceDTO>
            <serviceId>210733</serviceId>
            <serviceName>Transferencias - Sebra</serviceName>
            <limitList>
              <LimitTypeDTO>
                <limitName>TRANSACTION</limitName>
                <status>true</status>
                <limitAmount>1000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>DAILY</limitName>
                <status>true</status>
                <limitAmount>100000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>MONTHLY</limitName>
                <status>true</status>
                <limitAmount>1000000000.00</limitAmount>
              </LimitTypeDTO>
            </limitList>
          </ClientLimitAmountServiceDTO>
          <ClientLimitAmountServiceDTO>
            <serviceId>210643</serviceId>
            <serviceName>Pagos Virtuales PSE</serviceName>
            <limitList>
              <LimitTypeDTO>
                <limitName>TRANSACTION</limitName>
                <status>true</status>
                <limitAmount>1000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>DAILY</limitName>
                <status>true</status>
                <limitAmount>100000000.00</limitAmount>
              </LimitTypeDTO>
              <LimitTypeDTO>
                <limitName>MONTHLY</limitName>
                <status>true</status>
                <limitAmount>1000000000.00</limitAmount>
              </LimitTypeDTO>
            </limitList>
          </ClientLimitAmountServiceDTO>
        </services>
      </content>
    </previousData>
  </concreteReturn>
</ModuleResponse></ConnectorResponse></SvcData>'
                  DATA_RECV
           from dual)
     select q.ServiceId, q.ServiceName, s.*
       from LTX_ENGINE_LOGGER LEL
            left join
            xmltable(
              'SvcData/ConnectorResponse/ModuleResponse/concreteReturn/previousData/content/services/ClientLimitAmountServiceDTO'
              passing xmltype(LEL.DATA_RECV)
              columns ServiceId number(10) path 'serviceId'
                    , ServiceName varchar2(1000) path 'serviceName'
                    , limitList xmltype path 'limitList') Q
              on (1 = 1)
            left join
            xmltable(
              '/limitList/LimitTypeDTO'
              passing Q.limitList
              columns LimitName varchar2(1000) path './limitName'
                    , Status varchar2(1000) path './status'
                    , LimitAmount varchar2(1000) path './limitAmount') S
              on (1 = 1)