Oracle9i Error within Cursor

From: Michal Zaschke <zaschke_at_suas.cz>
Date: Thu, 03 Jan 2002 03:32:43 -0800
Message-ID: <F001.003E5B33.20020103030518@fatcity.com>

Hi all,
  I wrote a script bellow. If it is run on Oracle 8 it runs fine and gives me all the result but on Oracle 9i it returns the error ORA-06502. Can anybody tell me please why?
I have found a workaround - instead of

    select col||'x' colu from tbl union select col colu from tbl; use

    select col||'x' colu from tbl union select col||null colu from tbl; But it doesn't tell me, why the first statement doesn't work on 9i.

Thak you for your responses.

Mike

<<<script>>>
set serveroutput on
create table tbl (col varchar2(1));
insert into tbl values ('a');
insert into tbl values ('b');
select * from tbl;
select col||'x' colu from tbl union select col colu from tbl; declare
  cursor cA is
    select col||'x' colu from tbl union select col colu from tbl; begin
  for vA in cA loop
    dbms_output.put_line(vA.colu);
  end loop;
end;
/
drop table tbl;
<<</script>>>

The error message:

SQLWKS> declare
     2>   cursor cA is
     3>     select col||'x' colu from tbl union select col colu from
tbl;
     4> begin
     5>   for vA in cA loop
     6>     dbms_output.put_line(vA.colu);
     7>   end loop;
     8> end;
     9> /

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 5

--

 Ing. Michal Zaschke           
   DB Administrator             

Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: zaschke_at_suas.cz
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Michal Zaschke
  INET: zaschke_at_suas.cz

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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 Thu Jan 03 2002 - 05:32:43 CST