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
<[EMAIL PROTECTED]>
cz> cc:
Sent by: Subject: Re: Oracle9i Error within Cursor
[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
