RE: ORA-6502 with cursor and MAX function

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Tue, 19 Nov 2002 12:24:22 -0800
Message-ID: <F001.00507271.20021119122422@fatcity.com>

Rich,
What is the description of the field workorderno? The error says that your buffer string area is to small. It couls be that the returned number is super long and overflows the buffer. It works okay if the buffer is set to 4000? correct?
Ron

>>> Rich.Jesse_at_qtiworld.com 11/19/02 02:53PM >>> One way to get it to work is to select MAX(RTRIM(workorderno)) instead.  Why
does this work??? I R'd TFM and it says nothing about MAX (not)working on
any particular datatype.

Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex,

WI USA
> -----Original Message-----
> From: Freeman, Robert [mailto:Robert_Freeman_at_csx.com]
> Sent: Tuesday, November 19, 2002 12:26 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: ORA-6502 with cursor and MAX function
>
>
> Ran this example in 9.2.1.0 and it worked fine.
>
> RF
>
> Robert G. Freeman - Oracle OCP
> Oracle Database Architect
> CSX Midtier Database Administration
> Author of several Oracle books you can find on Amazon.com!
>
> Londo Mollari: Ah, arrogance and stupidity all in the same
> package. How
> efficient of you.
>
>
>
>
>
> -----Original Message-----
> Sent: Tuesday, November 19, 2002 10:29 AM
> To: Multiple recipients of list ORACLE-L
>
>
> OK, severe brain fart here. On 8.1.7.4, the following fails:
>
> declare
> v_wo wip.workorderno%TYPE;
> cursor c1 is
> select max(workorderno) wok
> from wip
> where assypartno = '33626';
> begin
> for aa in c1 loop
> v_wo := aa.wok;
> dbms_output.put_line(v_wo);
> end loop;
> end;
>
> ...on the "v_wo := aa.wok" line with "ORA-6502 PL/SQL:
> numeric or value
> error: character string buffer too small". Huh? I've got no
> idea how to
> fix this. It doesn't appear to be the declaration of "v_wo"
> either, as I've
> tried to make it CHAR(1000) and VARCHAR2(2000) to no avail.
> "wip.workorderno" is CHAR(12).
>
> This works fine without the MAX function and also works by
> converting the
> procedure to use an anonymous block instead of a cursor.
> That would be
> fine, except I'm using this to do 10046 trace comparisons of
anonymous
> blocks and implicit cursors. One other possible culprit is our
> CURSOR_SHARING=FORCE, but I've changed the session to EXACT
> with the same
> results. Also, I get the same error in TOAD v7.4 and SQL*Plus.
>
> Anyone? I could use the excuse that I'm getting over a cold
> or flu here but
> this is ridiculous!
>
> TIA,
> Rich

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Nov 19 2002 - 14:24:22 CST