Re: How to display MIN/MAX values in a cursor

From: Ubiquitous <weberm_at_polaris.net>
Date: Wed, 20 Oct 2004 15:54:06 +0000 (UTC)
Message-ID: <cl61mu$k2u$1@news.utelfla.com>

"Bruce" <someone_at_nc.rr.com> wrote:

> 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.

Apparently that was it. I used LENGTH() in the DBMS_OUTPUT statements and it returned 8003(!), so I used TRIM and now it works fine. Thanks for the help!

 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(v_rcpt_num.init || ' ');
         DBMS_OUTPUT.put(TRIM( ' ' FROM v_rcpt_num.min_num) || ' ');
         DBMS_OUTPUT.put_line(TRIM( ' ' FROM v_rcpt_num.max_num));
      END LOOP;

   END;
END; A A08531 A90129
D D00001 D07428
E E00002 E71560

-- 
======================================================================
ISLAM: Winning the hearts and minds of the world, one bomb at a time.

Received on Wed Oct 20 2004 - 10:54:06 CDT