Oracle-L: RE: ORA-6502 with cursor and MAX function

From: Freeman, Robert <Robert_Freeman_at_csx.com>
Date: Tue, 19 Nov 2002 10:26:11 -0800
Message-ID: <F001.00506EA5.20021119102611@fatcity.com>

Ran this example in 9.2.1.0 and it worked fine.

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

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

--

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: Freeman, Robert
INET: Robert_Freeman_at_csx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------