Re: PL/SQL String Manipulation Problem?

Steve Busby wrote:  

<snip>
> strMsg := ' Hello '
> strMsg := strMsg || ' World '
>
> We get a 6502 Oracle error. strMsg is declared as VARCHAR. What am I
> doing wrong, or is this a PL/SQL bug? This is such a common programming
> construct for building long strings, I would think if there is a problem
> someone else would also have had it by now (which leads me to believe it's
> not a bug).
>
> If we use two separate strings, it works fine
> strMsg1 := ' Hello '
> strMsg2 := ' World '
> strMsg3 := strMsg1 || strMsg2
> But it is wasteful to use all the temporary strings we have to use to do
> that.
>
> We are using Oracle's Procedure Builder from the Dev 2000 package.
>
> Thanks in advance for any responses.
>
> Steve

In the current version of Dev 2000, Procedure builder uses PL/SQL version 1. In this vesrion, if a variable is defined as VARCHAR, without a length, then it is taken as VARCHAR(n), where n is the length of the intitial value (or 1 if it is not given an initial value). In the first case, this means that the variable is defined AS IF you had written 'Strmsg VARCHAR(7) := 'Hello';, and when you try and add more characters, it exceeds the length. In the second case, strMsg1 and strMsg2 each have an implied length of 7, but strMsg3 has an implied length of 14.

BTW. At release 2.0 of Dev 2000 Procedure Builder will use version 2 of PL/SQL, and this does not permit definition of VARCHAR or VARCHAR2 fields without an explicit length (probably because too many people hit a problem similar to the above?).

-- 
The above posting represents the personal opinions of the author and is
not to
be taken as official (or unofficial) policy or opinions of his employer.

Does the internet truly herald the arrival of the 'Global Village'?