From Jared.Still@radisys.com Thu, 03 Jan 2002 10:28:55 -0800
From: Jared.Still@radisys.com
Date: Thu, 03 Jan 2002 10:28:55 -0800
Subject: Re: Oracle9i Error within Cursor
Message-ID: <F001.003E6131.20020103095026@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain



Good reason to do it right the the first time.

Jared - ducking and running   :)




                                                                                       
                          
                    Michal Zaschke                                                     
                          
                    <zaschke@suas.       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L@fatcity.com>     
                    cz>                  cc:                                           
                          
                    Sent by:             Subject:     Re: Oracle9i Error within Cursor 
                          
                    root@fatcity.c                                                     
                          
                    om                                                                 
                          
                                                                                       
                          
                                                                                       
                          
                    01/03/02 04:45                                                     
                          
                    AM                                                                 
                          
                    Please respond                                                     
                          
                    to ORACLE-L                                                        
                          
                                                                                       
                          
                                                                                       
                          




Looks like you're rigth about datatypes restriction. If I use the same
example but define COL as VARCHAR2(2) then it runs fine. I have to
rewrite a lot of my code now. Grrrr :-\
Thank you. :-)

Mike

P.S.: Your solution also works.


Stephane Faroult wrote:
>
> Michal Zaschke wrote:
> >
> > 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
> >
>
> It looks like it's more strict about types. col || 'x' cannot be a
> varchar(1) like col alone. I have not tried it but I guess that using
> for instance rtrim(rpad()) would have roughly the same effect as your
> solution - which I prefer.
> --
> Regards,
>
> Stephane Faroult
> Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michal Zaschke
  INET: zaschke@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@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: 
  INET: Jared.Still@radisys.com

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