ORA-22835: Buffer too small and ORA-25137: Data value out of range-CodePudding

We are using a software that has limited Oracle capabilities. I need to filter through a CLOB field by making sure it has a specific value. Normally, outside of this software I would do something like:

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

However, this isn't supported so I'm attempting to use CAST instead. I've tried many different attempts but so far these are what I found:

The software has a built-in query checker/validator and these are the ones it shows as invalid:

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

However, the validator does accept these:

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

Unfortunately, even though the validator lets these ones go through, when running the query to fetch data, I get ORA-22835: Buffer too small when using VARCHAR or NVARCHAR. And I get ORA-25137: Data value out of range when using CHAR.

Are there other ways I could try to check that my CLOB field has a specific value when filtering the data? If not, how do I fix my current issues?

CodePudding user response：

Using this as sample data

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

You need to use dbms_lob.substr(col,1) to get the first character (from the default offset= 1)

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

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

Note that the default amount (= length) of the substring is 32767 so using only DBMS_LOB.SUBSTR(COL) will return more than you expects.

CAST for CLOB does not cut the string to the casted length, but (as you observes) returns the exception ORA-25137: Data value out of range if the original string is longert that the casted length.

As documented for the CAST statement

CAST does not directly support any of the LOB data types. When you use CAST to convert a CLOB value into a character data type or a BLOB value into the RAW data type, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target data type. If the resulting value is larger than the target type, then the database returns an error.

CodePudding user response：

The error you're getting indicates that Oracle is trying to apply the CAST(t.new_value AS VARCHAR(10)) to a row where new_value has more than 10 characters. That makes sense given your description that new_value is a generic audit field that has values from a large number of different tables with a variety of data lengths. Given that, you'd need to structure the query in a way that forces the optimizer to reduce the set of rows you're applying the cast to down to just those where new_value has just a single character before applying the cast.

Not knowing what sort of scope the software you're using provides for structuring your code, I'm not sure what options you have there. Be aware that depending on how robust you need this, the optimizer has quite a bit of flexibility to choose to apply predicates and functions on the projection in an arbitrary order. So even if you find an approach that works once, it may stop working in the future when statistics change or the database is upgraded and Oracle decides to choose a different plan.