CODEWITHSUNDEEP
sqloracle-databasecoldfusioncoldfusion-8
justacoder
justacoder

Reputation: 2704

Oracle error: character string buffer too small

I am running a basic query that retrieves rows based on basic conditional clauses, nothing complex. This works fine:

<cfquery name="courses" datasource="banner">
        SELECT *
        FROM tjucatalog
        WHERE (course_status = 'Active')
            AND CONCAT(subject,course_no) IN (#PreserveSingleQuotes(courselist)#)
            AND term IN ('Fall 2012')
            AND ((end_date > #now()#) OR (course_meeting_info IS NOT NULL))
        ORDER BY TYear, TSort, DayNum, start_date, time, title
</cfquery>

However, when I remove the "AND term IN" line from the query, it fails.

<cfquery name="courses" datasource="banner">
        SELECT *
        FROM tjucatalog
        WHERE (course_status = 'Active')
            AND CONCAT(subject,course_no) IN (#PreserveSingleQuotes(courselist)#)
            AND ((end_date > #now()#) OR (course_meeting_info IS NOT NULL))
        ORDER BY TYear, TSort, DayNum, start_date, time, title
</cfquery>

The error I get is: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "BANINST1.TJUCATALOG_PACK", line 519

Is this maybe a view that requires the field 'term' to be included, or is there something else at play here I'm entirely unaware of?

Upvotes: 0

Views: 22737

Answers (2)

justacoder
justacoder

Reputation: 2704

Our Oracle gurus returned and told us they had to change a field type from varchar2 (4000) to CLOB. The lack of the term field as a filter clause was a red herring error. I don't know which field specifically in the query needed to be increased for allowed length, but it works so I'm happy.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191245

This appears to be an error in a package that's being called under the hood, possibly from a view. You're querying against tjucatalog, and it seems plausible that is a view where one of the returned columns is actually a functional call.

It isn't necessarily the term column that's the problem. By removing that condition, more rows will be returned, and the function is being called against a column value from a row that isn't there when the condition is in place. But it could be any column in one of those now-visible rows.

As a simple and contrived example of what might be happening:

create table t42 (id number, foo varchar2(20));

insert into t42 (id, foo) values (1, 'Short');
insert into t42 (id, foo) values (2, 'More than 10');

create package p42 as
function func(p_id in number) return varchar2;
end p42;
/

create package body p42 as
function func(p_id in number) return varchar2 is
    l_bar varchar2(10);
begin
    select foo into l_bar from t42 where id = p_id;
    return l_bar;
end func;
end p42;
/

create view v42 as select id, p42.func(id) as bar from t42;

So we have a table with two rows, one with a foo less than 10 characters, the other more than 10 characters. We have a (silly) package function that takes an id value, looks up foo, and returns it. And a view that uses that function.

This works:

select * from v42 where id = 1;

        ID BAR
---------- --------------------
         1 Short

But removing the condition causes it to fail:

select * from v42;

ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.P42", line 5

Line 5 of my package body is select foo into l_bar from t42 where id = p_id; and the problem is that I've declare l_bar as varchar2(10), which is too small for the foo value for id=2. I should have declared it as varchar2(20), or even better t42.foo%TYPE.

To see what your problematic function is doing, look at the source code, which you can get from the database (if it isn't wrapped) if you don't have it available:

select line, text from all_source
where owner = 'BANINST1'
    and name = 'TJUCATALOG_PACK'
    and type = 'PACKAGE BODY'
order by line;

Upvotes: 3

Related Questions