Oracle 11G Clob column with where clause 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,
      '&lt;?xml version="1.0" encoding="UTF-8"?&gt;
    &lt;l-ana version="2.9"&gt;
       &lt;vehicle&gt;
          &lt;Error&gt;0&lt;/Error&gt;
          &lt;year-of-manufacture&gt;1999&lt;/year-of-manufacture&gt;
          &lt;make&gt;Toyota&lt;/make&gt;
          &lt;model&gt;Hilux&lt;/model&gt;
          &lt;mt&gt;235998&lt;/mt&gt;
       &lt;/vehicle&gt;
    &lt;/l-ana&gt;'
    );

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


The disare output is to only get records where Error = 0.
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.