1

I am using a query to get data from a CLOB column. I need to add a WHERE clause to it to only get the rows where Error = 0 from the CLOB column.

The query I am using atm is:

select lt.T_ID, lt.SUCCESSFUL_TIMESTAMP,
       clob_xml_element_text(lm.XML_DATA, 'year-of-manufacture') "year",
       clob_xml_element_text(lm.XML_DATA, 'make') "make",
       clob_xml_element_text(lm.XML_DATA, 'model') "model",
       clob_xml_element_text(lm2.XML_DATA, 'mt') "odometer"
from tabletest2 lt
join tabletest lm on lt.T_ID = lm.T_ID
LEFT JOIN tabletest lm2 on lm.T_ID = lm2.T_ID and lm2.TYPE_ID = -129
where lm.TYPE_ID = -29
order by lt.SUCCESSFUL_TIMESTAMP ASC;

Output:

T_ID    SUCCESSFUL_TIMESTAMP    year    make    model odometer
167951  01/01/18 00:00:08.541000000     2009    Kia Sportage 2.0 Auto   82396
167979  01/01/18 00:02:42.613000000     2009    Mazda   2 Sport 5 DR 1.5 4AT    98589
174570  01/01/18 00:03:33.738000000     2015    Mercedes-Benz   CLA-Class CLA 250 2.0P/7AT/SL   null
198700  01/01/18 00:03:35.599000000     2008    Mitsubishi  Galant  null

Expected output

Remove the two rows where the odometer is null.

I tried to use:

(existsNode(lm2.XML_DATA,'/mt[Error]') = 0)

It works but when running the query with no limit I get the error:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

UPDATE:

Updating this question with the tables DDL.

CREATE TABLE tabletest
   (
  "TYPE_ID" NUMBER(10,0) NOT NULL ENABLE,
  "T_ID" NUMBER(10,0) NOT NULL ENABLE,
  "XML_DATA" CLOB,
  CONSTRAINT tabletest_pk PRIMARY KEY (T_ID)
   );

   CREATE TABLE tabletest2
    (
   "T_ID" NUMBER(10,0) NOT NULL ENABLE,
   "SUCCESSFUL_TIMESTAMP" TIMESTAMP(3) NOT NULL ENABLE,
   CONSTRAINT tabletest2_t_id
    FOREIGN KEY (T_ID)
    REFERENCES tabletest(T_ID)
    );

    INSERT INTO tabletest ("TYPE_ID", "T_ID", "XML_DATA") VALUES 
    (
      '-29',
      1,
      '<?xml version="1.0" encoding="UTF-8"?>
    <l-ana version="2.9">
       <vehicle>
          <Error>0</Error>
          <year-of-manufacture>1999</year-of-manufacture>
          <make>Toyota</make>
          <model>Hilux</model>
          <mt>235998</mt>
       </vehicle>
    </l-ana>'
    );

    INSERT INTO tabletest ("TYPE_ID", "T_ID", "XML_DATA") VALUES 
    (
      '-29',
      2,
      '<?xml version="1.0" encoding="UTF-8"?>
    <l-ana version="2.9">
       <vehicle>
          <Error>10</Error>
          <year-of-manufacture>1989</year-of-manufacture>
          <make>Toyota</make>
          <model>Hilux</model>
          <mt>535998</mt>
       </vehicle>
    </l-ana>'
    );

The disare output is to only get records where Error = 0.

  • existsNode is deprecated. Don't use it. – Michael Kutz Feb 8 '19 at 20:07
1

Prologue

You are not having problems with a "CLOB column with a WHERE clause". You are having problems processing XML Documents stored in a CLOB field.

This is your third question regarding this task.

I recommend you Read The Fine Manual before you advance on your problem: Oracle XML Developer's Guide

existsNode

existsNode is deprecated. Don't use it.

Instead, use:

WHERE XMLExists( '/mt[Error="0"]' passing xml_data)

PL/SQL Error

I suspect this might be due to a conversion error.

You should start investigating this error by fixing your Schema such that you are storing numbers as NUMBER, dates as DATE, and XML Documents as XMLType. Even if this isn't the case, your Schema would be better for it.

My other thought is: your XML Document has a string but you are attempting to extract a number.

|improve this answer|||||
  • ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause: *Action: Error at Line: 31 Column: 62 – Manuela Feb 9 '19 at 3:56
  • CLOB <> XMLType. You need to explicitly convert the CLOB to XMLType before you use XML functions. If the conversion fails, then you probably don't have text (in a CLOB) that conforms to XML Specifications. If that is the case, you need to fix your schema to prevent bad data from getting in. – Michael Kutz Feb 9 '19 at 14:45
  • I see. and XMLExists('/mt[Error="0"]' passing XMLTYPE(lm2.xml_data)) returns the same error as my original post. ORA-06502, ORA-06512. Am I converting correctly to XMLType? – Manuela Feb 10 '19 at 5:39
  • "Converting correctly?" is the wrong question. "Is the data actually XML data?" Is the correct question. If you had stored the data as XMLType in the first place, you wouldn't have to research that question. – Michael Kutz Feb 10 '19 at 23:50
  • sorry, this is getting confusing. I've updated the question with the table DDL. So you have the query I am using, you have the Table DDL and the data. Please, provide me with an answer with the query I am using so I can mark this question as resolved. – Manuela Feb 12 '19 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.