RE: ORA-6502 with cursor and MAX function

As an update, this DOES work if "v_wo" is defined as VARCHAR2(4000). And a DBMS_OUTPUT.PUT_LINE(LENGTH(v_wo)) shows 4000.

I'm still a bit confused as to why it's casting to a length of 4000...

Rich

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

> -----Original Message-----
> From: Jesse, Rich
> Sent: Tuesday, November 19, 2002 9:29 AM
> To: Multiple recipients of list ORACLE-L
> Subject: ORA-6502 with cursor and MAX function
>
>
> 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).