Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Variable
On Apr 27, 10:06 am, sybrandb <sybra..._at_gmail.com> wrote:
> On Apr 27, 9:08 am, beerora <beersa.bee..._at_gmail.com> wrote:
>
>
>
> > On Apr 27, 1:48 pm, Arto Viitanen <arto.viita..._at_csc.fi> wrote:
>
> > > beerora wrote:
> > > > Hi,
>
> > > > I've got following error
>
> > > > old 7: strTblName := '&1';
> > > > new 7: strTblName := 'TB_B4000';
> > > > ERROR:
> > > > ORA-01756: quoted string not properly terminated
>
> > > > And my SQL is
>
> > > > declare
> > > > strTblName char;
> > > > s number;
> > > > b date;
> > > > BEGIN
> > > > b:=sysdate;
> > > > strTblName := '&1';
> > > > END;
>
> > > > Pls guide me what mistakes in my SQL.
>
> > > > Thanks.
>
> > > > Cheers,
>
> > > You declared strTblName as char, which takes one char. So when
> > > you assign 'TB_B4000' to it, only the first character is assigned.
> > > And as you have only one ', the string is not terminated.
>
> > > Solution? Declare strTblName as VARCHAR(20) (for example).
>
> > > --
> > > Arto Viitanen, CSC Ltd.
> > > Espoo, Finland- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks Arto.
>
> > After I've changed to varchar or varchar2, still got the same error.
>
> > Cheers- Hide quoted text -
>
> > - Show quoted text -
>
> Can not reproduce.
> Please post an *exact* transcript.
> You shouldn't include TB_B4000 in quotes when you specify it. The
> quotes are already there.
Actually you do need quotes, otherwise the substitution will resolve to
strTblName := TB_B4000;
which in the absence of a variable or (presumably) function named TB_B4000 will give an 'identifier must be declared' error.
I was also puzzled by the Arto's comment, that assigning the value 'TB_B4000' (in single quotes) to a 1-character CHAR string will give the first single quote only. No it won't.
Also (to the OP), why CHAR? I thought that had been rightly banned throughout the civilized galaxy? You'll be using VARRAYs next ;)
Oh and there is no SQL in your example. Received on Sat Apr 28 2007 - 04:11:49 CDT