ORA-22922: nonexistent LOB value in SELECT

Question

I am facing a problem with two different consultations on oracle. Both have the sql snippet REPLACE(dbms_lob.substr(wm_concat(disctinct <COLUNA>)), ',', ', ')

The error that returns is the ORA-22922: nonexistent LOB value.

I looked for the mistake in SOPT and in the SOEN but none of the answers, or questions, gave me any light on the subject. Most of the questions deal with errors in INSERT, but my problem is in SELECT.

Both queries have at least one LEFT OUTER JOIN.

The version used by oracle is 11g.

When I run SQL on PL/SQL no error results, but when using the oracle database access library for C# error occurs.

SQL below is the one used by one of the queries:

SELECT *
  FROM (SELECT ROWNUM NUM_REG, queryResult.*
          FROM (

                SELECT anp.id_klient "C",
                        anp.bruecke_1 "L",
                        anp.cod_conhec "Conhec",
                        anp.charge_ref "Ref. L",
                        anp.valor_cif "Valor CIF",
                        w.nr_doc "Documento",
                        w.datum_we "Dt. E",
                        SUM(q.mng_frei) "Qtd. D",
                        TRUNC(anp.datum_gera) "Dt. PC",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(DISTINCT a8.nr_di),
                                                    empty_clob())),
                                ',',
                                ', ') "DI",
                        REPLACE(dbms_lob.substr(wm_concat(DISTINCT
                                                          TO_CHAR(a8.time_neu,
                                                                  'dd/mm/yyyy'))),
                                ',',
                                ', ') "Dt. C DI",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(DISTINCT a8.nr_da),
                                                    empty_clob())),
                                ',',
                                ', ') "DA",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(DISTINCT ak.bez_1),
                                                    empty_clob())),
                                ',',
                                ', ') "Mercadoria",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(DISTINCT
                                                              w.nr_zoll),
                                                    empty_clob())),
                                ',',
                                ', ') "CP",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(DISTINCT w.laenge),
                                                    empty_clob())),
                                ',',
                                ', ') "T C",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(DISTINCT
                                                              TO_CHAR(a4.time_out,
                                                                      'dd/mm/yyyy')),
                                                    empty_clob())),
                                ',',
                                ', ') "Dt. S",
                        REPLACE(dbms_lob.substr(NVL(wm_concat(distinct
                                                              TO_CHAR(a4.time_end_unload,
                                                                      'dd/mm/yyyy')),
                                                    empty_clob())),
                                ',',
                                ', ') "Dt. D"
                  FROM wms.quanten q
                 INNER JOIN wms.we w
                    ON (w.lager = q.lager AND
                       w.nr_lieferschein = REPLACE(q.charge, 'AG', 'AD') AND
                       w.nr_zoll = TRIM(q.trenn_3))
                 INNER JOIN wms.anliefpos anp
                    ON (anp.lager = q.lager AND anp.id_klient = q.id_klient AND
                       anp.bruecke_1 = REPLACE(q.charge, 'AG', 'AD'))
                 INNER JOIN wms.artikel ak
                    ON (ak.id_artikel = q.id_artikel)
                  LEFT OUTER JOIN wms.desmembr a8
                    ON (a8.lager = anp.lager AND a8.id_klient = anp.id_klient AND
                       a8.lote_ad = anp.bruecke_1 AND
                       a8.typ_process = 'DINACI')
                  LEFT OUTER JOIN wms.contcontrol a4
                    ON (a4.nr_container = w.nr_zoll AND a4.document = w.nr_doc AND
                       a4.lager = w.lager AND a4.stat = 90 AND
                       a4.stat_oc = 00)
                 WHERE q.lager = :estabelecimento
                   AND q.id_klient = :cliente HAVING SUM(q.mng_frei) > 0
                 GROUP BY anp.bruecke_1,
                           anp.cod_conhec,
                           anp.charge_ref,
                           anp.valor_cif,
                           w.nr_doc,
                           w.datum_we,
                           anp.id_klient,
                           anp.lager,
                           anp.datum_gera
                 ORDER BY anp.bruecke_1

                ) queryResult)
 WHERE NUM_REG > :limiteInferior
   AND NUM_REG <= :limiteSuperior

I would like to know why an exception is made and how you could treat it.

Editing
I was able to discover that if we remove the SQL responsible for paging the query the result comes as expected. However, I need the pagination for an eventual query that returns many records.

Initially my limits are respectively lower and higher, 0 and 35.

The query results in a total of 72 results, generating 3 pages.

Issue 2
Screenshot of the error that occurs:

Answer 1

Some of the values like nr_di, nr_da, bez_1, laenge, nr_zoll, time_out, time_end_unload may be returning null and the NVL function is replacing this null value with empty_clob()

Since empty_clob is empty you cannot use it as a parameter for the DBMS_LOB package as explained in the documentation on

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions047.htm

Answer 2

I couldn’t figure out why the error occurred, but after two days trying, I was able to solve my problem.

If anyone knows the reason for the error and has more detailed results will be very welcome.

I have a method that performs paging a SQL any, as per code below:

SELECT *
  FROM (
       SELECT queryResult.*, ROWNUM numero
         FROM (
              {0}
         ) queryResult
  )
 WHERE numero > {1}
   AND numero <= {2} 

Using this code with the SQL "CORE" error occurred. A colleague (Hendrig) told me about some analytical functions of Oracle and helped me, until we managed to arrive at a satisfactory result. It seems a bit of Gambiarra, but it worked.

We modified the SQL from paging to the command below and the result was as expected:

SELECT *
  FROM (
     SELECT queryResult.*, DENSE_RANK() over (order by rownum desc) numero
       FROM (
            {0}
       ) queryResult
)
 WHERE numero BETWEEN {1} AND {2}