ORA-22835: Слишком маленький буфер и ORA-25137: Значение данных выходит за пределы диапазона

Question 1

Мы используем программное обеспечение с ограниченными возможностями Oracle. Мне нужно отфильтровать поле CLOB, убедившись, что оно имеет определенное значение. Обычно, вне этого программного обеспечения, я бы сделал что-то вроде:

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

Однако это не поддерживается, поэтому я пытаюсь использовать CAST вместо. Я пробовал много разных попыток, но до сих пор это то, что я нашел:

Программное обеспечение имеет встроенную проверку/валидатор запросов, и это те, которые оно показывает как недействительные:

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

Однако валидатор принимает эти:

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

К сожалению, даже несмотря на то, что валидатор пропускает эти данные, при выполнении запроса для извлечения данных я получаю ORA-22835: Buffer too small при использовании VARCHAR или NVARCHAR. И я получаю ORA-25137: Data value out of range при использовании CHAR.

Есть ли другие способы, которыми я мог бы попытаться проверить, что мое поле CLOB имеет определенное значение при фильтрации данных? Если нет, то как мне исправить мои текущие проблемы?

Question 2

Ошибка, которую вы получаете, указывает на то, что Oracle пытается применить CAST(t.new_value AS VARCHAR(10)) в ряд, где new_value содержит более 10 символов. Это имеет смысл, учитывая ваше описание, что new_value это универсальное поле аудита, содержащее значения из большого числа различных таблиц с различной длиной данных. Учитывая это, вам нужно будет структурировать запрос таким образом, чтобы оптимизатор уменьшал набор строк, которые вы применяете cast вплоть до тех, где new_value имеет только один символ перед применением cast.

Не зная, какой объем программного обеспечения, которое вы используете, обеспечивает структурирование вашего кода, я не уверен, какие у вас есть варианты. Имейте в виду, что в зависимости от того, насколько это необходимо, оптимизатор обладает достаточной гибкостью для применения предикатов и функций к проекции в произвольном порядке. Поэтому, даже если вы найдете подход, который сработает один раз, он может перестать работать в будущем, когда статистика изменится или база данных будет обновлена, и Oracle решит выбрать другой план.

Question 3

Используя это в качестве примера данных

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

Вам нужно использовать dbms_lob.substr(col,1) чтобы получить первый символ (по умолчанию offset= 1)

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

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

Обратите внимание, что по умолчанию amount (= длина) подстроки равна 32767 поэтому используйте только DBMS_LOB.SUBSTR(COL) вернется больше, чем вы ожидаете.

CAST для CLOB не обрезает строку до приведенной длины, но (как вы заметили) возвращает исключение ORA-25137: Data value out of range если исходная строка длиннее, чем приведенная длина.

Как документально подтверждено дляCAST заявление

CAST напрямую не поддерживает ни один из типов данных LOB. При использовании ПРИВЕДЕНИЯ для преобразования значения CLOB в символьный тип данных или значения большого двоичного объекта в НЕОБРАБОТАННЫЙ тип данных база данных неявно преобразует значение LOB в символьные или необработанные данные, а затем явно преобразует полученное значение в целевой тип данных. Если результирующее значение больше целевого типа, то база данных возвращает ошибку.

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

Ошибка, которую вы получаете, указывает на то, что Oracle пытается применить CAST(t.new_value AS VARCHAR(10)) в ряд, где new_value содержит более 10 символов. Это имеет смысл, учитывая ваше описание, что new_value это универсальное поле аудита, содержащее значения из большого числа различных таблиц с различной длиной данных. Учитывая это, вам нужно будет структурировать запрос таким образом, чтобы оптимизатор уменьшал набор строк, которые вы применяете cast вплоть до тех, где new_value имеет только один символ перед применением cast.

Не зная, какой объем программного обеспечения, которое вы используете, обеспечивает структурирование вашего кода, я не уверен, какие у вас есть варианты. Имейте в виду, что в зависимости от того, насколько это необходимо, оптимизатор обладает достаточной гибкостью для применения предикатов и функций к проекции в произвольном порядке. Поэтому, даже если вы найдете подход, который сработает один раз, он может перестать работать в будущем, когда статистика изменится или база данных будет обновлена, и Oracle решит выбрать другой план.

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

Используя это в качестве примера данных

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

Вам нужно использовать dbms_lob.substr(col,1) чтобы получить первый символ (по умолчанию offset= 1)

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

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

Обратите внимание, что по умолчанию amount (= длина) подстроки равна 32767 поэтому используйте только DBMS_LOB.SUBSTR(COL) вернется больше, чем вы ожидаете.

CAST для CLOB не обрезает строку до приведенной длины, но (как вы заметили) возвращает исключение ORA-25137: Data value out of range если исходная строка длиннее, чем приведенная длина.

Как документально подтверждено дляCAST заявление

CAST напрямую не поддерживает ни один из типов данных LOB. При использовании ПРИВЕДЕНИЯ для преобразования значения CLOB в символьный тип данных или значения большого двоичного объекта в НЕОБРАБОТАННЫЙ тип данных база данных неявно преобразует значение LOB в символьные или необработанные данные, а затем явно преобразует полученное значение в целевой тип данных. Если результирующее значение больше целевого типа, то база данных возвращает ошибку.

К сожалению, даже после добавления смещения валидатор запросов не распознает DBMS_LOB.SUBSTR() так что я не могу этим воспользоваться. Я думал CAST способен обрезать строку, потому что нам также пришлось использовать обходной путь для TRUNC: CAST(CAST(date_field AS VARCHAR(9)) AS DATE) и это работает. Это избавляет от части времени. Я надеялся, что мы сможем сделать что-то подобное для этого.
Да, это работает для VARCHAR но, к сожалению, не для CLOB Я обновил ответ. @Патрикгрегорио

ORA-22835: Слишком маленький буфер и ORA-25137: Значение данных выходит за пределы диапазона

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории