ORA-22922: nonexistent LOB value in SELECT

Question

ORA-22922: nonexistent LOB value in SELECT

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I'm facing a problem with two separate queries in oracle . Both have the sql% share of%

The error that returns is REPLACE(dbms_lob.substr(wm_concat(disctinct <COLUNA>)), ',', ', ') .

I looked for the error in ORA-22922: nonexistent LOB value and SOPT but none of the answers, or questions, gave me any light on the subject. Most of the questions are about errors in SOEN , but my problem is in INSERT .

Both queries have at least SELECT .

The version used of LEFT OUTER JOIN is oracle .

When I run SQL in 11g no error results, but when using the oracle database access library for C # the error occurs.

The 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 the reason why an exception is raised and how I could treat it.

Editing Home I was able to find out that removing the PL/SQL responsible for paging the query results 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 Home Image of the error that occurs:

sql c# oracle oracle11g

asked by anonymous 11.04.2016 / 22:24

2 answers

0

Some of the values such as 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 ()

As empty_clob is empty you can not use it as a parameter for the DBMS_LOB package as explained in the documentation in

link

12.04.2016 / 21:01

How to define a column with an adjustable size inside the container LINQ to SQL with entities that already exist, inheritance and composition

score 0 · Accepted Answer

I could not figure out why the error occurred, but after two days of trying, I was able to solve my problem.

If someone knows the reason for the error and has more detailed results, they are very welcome.

I have a method that performs the pagination of a SQL any, as per the code below:

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

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

We have modified the SQL of pagination for the command below and the result was expected:

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