0

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?

Improve this question
4
  • 1
    From the name, BANINST1.TJUCATALOG_PACK looks like a package, which you can check in all_objects; and one which possibly has a varchar2 column declared too small somewhere - maybe a reference to term but not necessarily. But it isn't clear where it fits into the picture. Is tjucatalog a view? Are you sure the error is coming from this select and not somewhere you're using one of the returned values later? Commented Jul 2, 2012 at 14:57
  • Use ALL_SOURCE to see the code of the package. Commented Jul 2, 2012 at 15:30
  • It seems likely that the AND term in ('Fall 2012') predicate is causing a row to be excluded from the query results. When the predicate is removed and the row is passed to the package or stored procedure BANINST1.TJUCATALOG_PACK the error you've reported occurs. FIgure out what BANINST1.TJUCATALOG_PACK is, dive into it. and look at line 519. Share and enjoy. Commented Jul 2, 2012 at 16:12
  • Thank you everyone. Unfortunately, I don't have access or permission to Oracle. Just the views they provide us. Commented Jul 2, 2012 at 16:22

2 Answers 2

Reset to default
3

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

If 'TJUCATALOG_PACK' is a package (which given the name seems likely) then it would be a good idea to filter on and type = 'PACKAGE BODY'. Although as the errant line is 519 there is probably little chance of confusion between source from the spec and body.
@APC - Thanks, good point. Ordering by line alone without that filer would make it a bit hard to follow.
@APC, I don't believe you've never seen a 519 line package spec :-).
@Ben - they are pretty rare. Very few specs contain so many comments that they reach that size. And any spec which is that big because of the number of methods is very poorly designed and ought to be refactored into several more cohesive packages.
2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.