Re: Oracle9i Error within Cursor

Good reason to do it right the the first time.

Jared - ducking and running :)

                                                                                       
                          
                    Michal Zaschke                                                     
                          
                    <zaschke_at_suas.       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>     
                    cz>                  cc:                                           
                          
                    Sent by:             Subject:     Re: Oracle9i Error within Cursor 
                          
                    root_at_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_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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_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_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).