It's a little known bug (gasp.. did I say that?) in Oracle. MIN or MAX of a
CHAR() field will return a 4000 character data when used in a cursor like
this. The problem is "solved" by upgrading to a 9i version. To verify this,
change your DBMS_OUTPUT to LENGTH() and you'll see it. This appears to only
happen in a cursor as this SELECT in SqlNAV or TOAD will show the 6
character MIN and MAX values without all the spaces padded on.
>
> >| I wrote a quick PL/SQL statement to display the range of values in a
table
> >| but am having trouble with the DBMS_OUTPUT line. It runs fine as a
stand-
> >| alone SELECT statement but not in this. Any ideas on what's wrong?
> >| Thanks in advance!
> >|
> >| BEGIN
> >| DECLARE
> >| CURSOR rcpt_num_cur IS
> >| SELECT SUBSTR(receipt_number, 1, 1) INIT,
> >| MIN(receipt_number) MIN_NUM,
> >| MAX(receipt_number) MAX_NUM
> >| FROM remitter
> >| WHERE SUBSTR(receipt_number, 1, 1) IN ('A', 'D', 'E', 'N')
> >| GROUP BY SUBSTR(receipt_number, 1, 1);
> >| BEGIN
> >| FOR v_rcpt_num IN rcpt_num_cur LOOP
> >| DBMS_OUTPUT.put_line(v_rcpt_num.init || ' ' ||
v_rcpt_num.min_num
> >| || ' ' ||
v_rcpt_num.max_num);
> >| END LOOP;
> >| END;
> >| END;
> >|
> >
> >error messages?
>
> You know, I just realized that a couple seconds ago! Dooh!
>
> I'll have to go back to the office for the exact ORA code, but the
> text described it as some sort of conversion error in the line with
> DBMS_OUTPUT.put_line in it. I narrowed it down to v_rcpt_num.min_num
> and v_rcpt_num.max_num, if that helps. The receipt number field is a
> CHAR(6), so I'm totally baffled.
>
> --
> ======================================================================
> ISLAM: Winning the hearts and minds of the world, one bomb at a time.
>
Received on Sun Oct 17 2004 - 12:16:48 CDT
