ORA-22835: Buffer muito pequeno e ORA-25137: valor de Dados fora do intervalo

Question 1

Estamos usando um software que tem limitado o Oracle capacidades. Eu preciso de filtro através de um campo CLOB, certificando-se de que tem um valor específico. Normalmente, fora deste software gostaria de fazer algo como:

DBMS_LOB.SUBSTR(t.new_value) = 'Y'

No entanto, esta não é suportado por isso que eu estou tentando usar CAST em vez disso. Eu tentei muitas tentativas diferentes, mas até agora essas são as que eu encontrei:

O software que tem um built-in consulta verificador/validador e estes são os que ele mostra como inválido:

DBMS_LOB.SUBSTR(t.new_value)
CAST(t.new_value AS VARCHAR2(10))
CAST(t.new_value AS NVARCHAR2(10))

No entanto, o avaliador não aceitar estes:

CAST(t.new_value AS VARCHAR(10))
CAST(t.new_value AS NVARCHAR(10))
CAST(t.new_value AS CHAR(10))

Infelizmente, mesmo que o validador permite que essas pessoas passam, quando executar a consulta para obtenção de dados, recebo ORA-22835: Buffer too small quando utilizar VARCHAR ou NVARCHAR. E eu fico ORA-25137: Data value out of range quando utilizar CHAR.

Há outras maneiras que eu poderia tentar para verificar se a minha CLOB campo tem um valor específico quando a filtragem de dados? Se não, como faço para corrigir meu questões atuais?

Question 2

O erro que você está recebendo indica que a Oracle está tentando aplicar o CAST(t.new_value AS VARCHAR(10)) para uma linha onde new_value tem mais de 10 caracteres. O que faz sentido dado que sua descrição que new_value é um genérico de campo de auditoria que tem valores a partir de um grande número de tabelas diferentes com uma variedade de comprimentos de dados. Dado que, você precisa de estrutura da consulta de uma forma que obriga o otimizador para reduzir o conjunto de linhas que você está aplicando o cast para apenas aqueles onde new_value tem apenas um único caractere antes de aplicar o cast.

Não sabendo que tipo de escopo do software que você está usando oferece para a estruturação de seu código, eu não tenho certeza do que opções você tem lá. Estar ciente de que, dependendo de como robustos que você precisa isso, o otimizador tem um pouco de flexibilidade para escolher aplicar predicados e funções na projeção em uma ordem arbitrária. Assim, mesmo se você encontrar uma abordagem que funciona uma vez, ele pode parar de funcionar no futuro, quando as estatísticas de alteração ou o banco de dados é atualizado e Oracle decide escolher um plano diferente.

Question 3

Usando dados de exemplo

create table tab1(col clob);
insert into tab1(col) values (rpad('x',3000,'y'));

Você precisa usar dbms_lob.substr(col,1) para obter o primeiro caractere (do padrão offset= 1)

select dbms_lob.substr(col,1) from tab1;

DBMS_LOB.SUBSTR(COL,1)
----------------------
x

Observe que o padrão amount (= comprimento) da subseqüência é 32767 assim, usando apenas DBMS_LOB.SUBSTR(COL) vai devolver mais do que você espera.

CAST para CLOB não não cortar a cadeia de caracteres para o modelado de comprimento, mas (como você observa) retorna a exceção ORA-25137: Data value out of range se a seqüência de caracteres original é longert que o modelado de comprimento.

Como documentado para o CAST declaração

ELENCO não oferece suporte diretamente a qualquer um dos tipos de dados LOB. Quando você usar CAST para converter um CLOB valor em um tipo de dados de caractere ou um valor BLOB para o tipo de dados RAW, o banco de dados converte implicitamente o valor LOB de caracteres ou dados brutos e, em seguida, explicitamente, lança-o valor resultante para o tipo de dados de destino. Se o valor resultante for maior que o tipo de destino e, em seguida, o banco de dados retorna um erro.

Justin Cave · Answer 1 · 2021-11-24T16:59:52

O erro que você está recebendo indica que a Oracle está tentando aplicar o CAST(t.new_value AS VARCHAR(10)) para uma linha onde new_value tem mais de 10 caracteres. O que faz sentido dado que sua descrição que new_value é um genérico de campo de auditoria que tem valores a partir de um grande número de tabelas diferentes com uma variedade de comprimentos de dados. Dado que, você precisa de estrutura da consulta de uma forma que obriga o otimizador para reduzir o conjunto de linhas que você está aplicando o cast para apenas aqueles onde new_value tem apenas um único caractere antes de aplicar o cast.

Não sabendo que tipo de escopo do software que você está usando oferece para a estruturação de seu código, eu não tenho certeza do que opções você tem lá. Estar ciente de que, dependendo de como robustos que você precisa isso, o otimizador tem um pouco de flexibilidade para escolher aplicar predicados e funções na projeção em uma ordem arbitrária. Assim, mesmo se você encontrar uma abordagem que funciona uma vez, ele pode parar de funcionar no futuro, quando as estatísticas de alteração ou o banco de dados é atualizado e Oracle decide escolher um plano diferente.

Marmite Bomber · Answer 2 · 2021-11-23T17:06:33

Usando dados de exemplo

create table tab1(col clob);
insert into tab1(col) values (rpad('x',3000,'y'));

Você precisa usar dbms_lob.substr(col,1) para obter o primeiro caractere (do padrão offset= 1)

select dbms_lob.substr(col,1) from tab1;

DBMS_LOB.SUBSTR(COL,1)
----------------------
x

Observe que o padrão amount (= comprimento) da subseqüência é 32767 assim, usando apenas DBMS_LOB.SUBSTR(COL) vai devolver mais do que você espera.

CAST para CLOB não não cortar a cadeia de caracteres para o modelado de comprimento, mas (como você observa) retorna a exceção ORA-25137: Data value out of range se a seqüência de caracteres original é longert que o modelado de comprimento.

Como documentado para o CAST declaração

ELENCO não oferece suporte diretamente a qualquer um dos tipos de dados LOB. Quando você usar CAST para converter um CLOB valor em um tipo de dados de caractere ou um valor BLOB para o tipo de dados RAW, o banco de dados converte implicitamente o valor LOB de caracteres ou dados brutos e, em seguida, explicitamente, lança-o valor resultante para o tipo de dados de destino. Se o valor resultante for maior que o tipo de destino e, em seguida, o banco de dados retorna um erro.

Infelizmente, mesmo depois de adicionar o deslocamento, a consulta validador não reconhece DBMS_LOB.SUBSTR() então eu não posso usar isso. Eu pensei CAST é capaz de cortar a cadeia porque nós também tivemos que usar uma solução alternativa para TRUNC: CAST(CAST(date_field AS VARCHAR(9)) AS DATE) e ele funciona. Ele elimina a parte do tempo. Eu estava esperando que poderia fazer algo semelhante para este.
Sim, isso funciona para VARCHAR mas sinceramente não para CLOB Eu atualizei a resposta. @PatrickGregorio

ORA-22835: Buffer muito pequeno e ORA-25137: valor de Dados fora do intervalo

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria