|
|
 |
 |
 |
 |
SQL Variable
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,
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
-----------------------------------------------Reply-----------------------------------------------
On Apr 27, 1:48 pm, Arto Viitanen <arto.viita @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 -
>
Thanks Arto. After I've changed to varchar or varchar2, still got the same error.
Cheers
-----------------------------------------------Reply-----------------------------------------------
On Apr 27, 9:08 am, beerora <beersa.bee @gmail.com> wrote:
> On Apr 27, 1:48 pm, Arto Viitanen <arto.viita @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 -
> >
> Thanks Arto.
> After I've changed to varchar or varchar2, still got the same error.
> Cheers- Hide 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. --
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
On 27 apr, 04:59, beerora <beersa.bee @gmail.com> wrote: > Pls guide me what mistakes in my SQL. > Thanks.
Apart from the issues you have mastering strings, you are not developing a "universal script" to drop and create tables, are you? Not good for the stability of your applications :).
Generally, not the Oracle way of doing business - only when
you want pl/sql to do your model upgrades, but why not use
plain sql, then?
-----------------------------------------------Reply-----------------------------------------------
On Apr 27, 10:06 am, sybrandb <sybra @gmail.com> wrote:
> On Apr 27, 9:08 am, beerora <beersa.bee @gmail.com> wrote: > > On Apr 27, 1:48 pm, Arto Viitanen <arto.viita@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 -
> > >
> > Thanks Arto.
> > After I've changed to varchar or varchar2, still got the same error.
> > Cheers- Hide 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.
-----------------------------------------------Reply-----------------------------------------------
On 28 Apr 2007 02:11:49 -0700, William Robertson <williamr2 @googlemail.com> wrote: >Actually you do need quotes, otherwise the substitution will resolve >to >strTblName := TB_B4000;
Actually he doesn't need quotes as his original code was reading strTblname := '&1'; --
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
On Apr 28, 12:03 pm, sybra @hccnet.nl wrote: > On 28 Apr 2007 02:11:49 -0700, William Robertson > <williamr2@googlemail.com> wrote:
> >Actually you do need quotes, otherwise the substitution will resolve
> >to
> >strTblName := TB_B4000;
> Actually he doesn't need quotes as his original code was reading
> strTblname := '&1';
> --
> Sybrand Bakker
> Senior Oracle DBA
I meant he does need the quotes around &1 in the anonymous non-SQL block, in case there are any others I am missing. SQL> def 1
DEFINE 1 = "TB_B4000" (CHAR)
SQL> r
1 DECLARE
2 strTblName VARCHAR2(30);
3 s NUMBER;
4 b DATE;
5 BEGIN
6 b := sysdate;
7 strTblName := '&1';
8* END;
old 7: strTblName := '&1';
new 7: strTblName := 'TB_B4000';
PL/SQL procedure successfully completed.
SQL> ed
Wrote file /Users/williamr/SQL/afiedt.buf
1 DECLARE
2 strTblName VARCHAR2(30);
3 s NUMBER;
4 b DATE;
5 BEGIN
6 b := sysdate;
7 strTblName := &1;
8* END;
SQL> /
old 7: strTblName := &1;
new 7: strTblName := TB_B4000;
strTblName := TB_B4000;
*
ERROR at line 7:
ORA-06550: line 7, column 19:
PLS-00201: identifier 'TB_B4000' must be declared
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored
-----------------------------------------------Reply-----------------------------------------------
On 28 Apr 2007 10:20:56 -0700, William Robertson
<williamr2 @googlemail.com> wrote: >On Apr 28, 12:03 pm, sybra @hccnet.nl wrote: >> On 28 Apr 2007 02:11:49 -0700, William Robertson >> <williamr2@googlemail.com> wrote:
>> >Actually you do need quotes, otherwise the substitution will resolve
>> >to
>> >strTblName := TB_B4000;
>> Actually he doesn't need quotes as his original code was reading
>> strTblname := '&1';
>> --
>> Sybrand Bakker
>> Senior Oracle DBA
>I meant he does need the quotes around &1 in the anonymous non-SQL
>block, in case there are any others I am missing.
>SQL> def 1
>DEFINE 1 = "TB_B4000" (CHAR)
>SQL> r
> 1 DECLARE
> 2 strTblName VARCHAR2(30);
> 3 s NUMBER;
> 4 b DATE;
> 5 BEGIN
> 6 b := sysdate;
> 7 strTblName := '&1';
> 8* END;
>old 7: strTblName := '&1';
>new 7: strTblName := 'TB_B4000';
>PL/SQL procedure successfully completed.
>SQL> ed
>Wrote file /Users/williamr/SQL/afiedt.buf
> 1 DECLARE
> 2 strTblName VARCHAR2(30);
> 3 s NUMBER;
> 4 b DATE;
> 5 BEGIN
> 6 b := sysdate;
> 7 strTblName := &1;
> 8* END;
>SQL> /
>old 7: strTblName := &1;
>new 7: strTblName := TB_B4000;
> strTblName := TB_B4000;
> *
>ERROR at line 7:
>ORA-06550: line 7, column 19:
>PLS-00201: identifier 'TB_B4000' must be declared
>ORA-06550: line 7, column 5:
>PL/SQL: Statement ignored
His FULL code was declare
strTblName char;
s number;
b date;
BEGIN
b:=sysdate;
strTblName := '&1';
END;
So WHICH quotes does he need?
There was never a DEFINE 1 (which was made up by you).
--
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
On Apr 28, 6:41 pm, sybra @hccnet.nl wrote:
> On 28 Apr 2007 10:20:56 -0700, William Robertson > <williamr2@googlemail.com> wrote:
> >On Apr 28, 12:03 pm, sybra@hccnet.nl wrote:
> >> On 28 Apr 2007 02:11:49 -0700, William Robertson
> >> <williamr2@googlemail.com> wrote:
> >> >Actually you do need quotes, otherwise the substitution will resolve
> >> >to
> >> >strTblName := TB_B4000;
> >> Actually he doesn't need quotes as his original code was reading
> >> strTblname := '&1';
> >> --
> >> Sybrand Bakker
> >> Senior Oracle DBA
> >I meant he does need the quotes around &1 in the anonymous non-SQL
> >block, in case there are any others I am missing.
> >SQL> def 1
> >DEFINE 1 = "TB_B4000" (CHAR)
> >SQL> r
> > 1 DECLARE
> > 2 strTblName VARCHAR2(30);
> > 3 s NUMBER;
> > 4 b DATE;
> > 5 BEGIN
> > 6 b := sysdate;
> > 7 strTblName := '&1';
> > 8* END;
> >old 7: strTblName := '&1';
> >new 7: strTblName := 'TB_B4000';
> >PL/SQL procedure successfully completed.
> >SQL> ed
> >Wrote file /Users/williamr/SQL/afiedt.buf
> > 1 DECLARE
> > 2 strTblName VARCHAR2(30);
> > 3 s NUMBER;
> > 4 b DATE;
> > 5 BEGIN
> > 6 b := sysdate;
> > 7 strTblName := &1;
> > 8* END;
> >SQL> /
> >old 7: strTblName := &1;
> >new 7: strTblName := TB_B4000;
> > strTblName := TB_B4000;
> > *
> >ERROR at line 7:
> >ORA-06550: line 7, column 19:
> >PLS-00201: identifier 'TB_B4000' must be declared
> >ORA-06550: line 7, column 5:
> >PL/SQL: Statement ignored
> His FULL code was
> declare
> strTblName char;
> s number;
> b date;
> BEGIN
> b:=sysdate;
> strTblName := '&1';
> END;
> So WHICH quotes does he need?
> There was never a DEFINE 1 (which was made up by you).
> --
> Sybrand Bakker
> Senior Oracle DBA
I meant the quotes around the quotes around &1 in the anonymous block. Was that not clear? I changed the 1-byte CHAR to a VARCHAR2(30) so that discussion could
proceed past the "character string buffer too small" error to the
issue of quotation marks.
I entered DEFINE 1 at the SQL prompt in order to make it clearer how
&1 was defined.
Just to confirm, are you saying his assignment at line 7 should have
been
strTblName := &1;
i.e. the quotes in the original example were not needed?
-----------------------------------------------Reply-----------------------------------------------
On Apr 28, 11:00 pm, William Robertson <williamr2 @googlemail.com> wrote: > I meant the quotes around the quotes around &1 in the anonymous block.
Curse copy & paste. Please remove duplicated words to form a coherent sentence. What happened to Preview anyway? -----------------------------------------------Reply-----------------------------------------------
On 28 Apr 2007 15:00:22 -0700, William Robertson <williamr2 @googlemail.com> wrote: >Just to confirm, are you saying his assignment at line 7 should have >been >strTblName := &1;
>i.e. the quotes in the original example were not needed?
I'm not sure who the block head is here, but I don't understand why you can't grasp the original code read strTblname := '&1', the OP just ran that code, was prompted for &1 and had to enter TB_B4000 *without* quotes! Your nitpicking at me is fully irrelevant, and either you need better glasses, or more coffee, or all of those. --
-----------------------------------------------Reply-----------------------------------------------
On Apr 28, 11:24 pm, sybra @hccnet.nl wrote:
> On 28 Apr 2007 15:00:22 -0700, William Robertson > <williamr2@googlemail.com> wrote:
> >Just to confirm, are you saying his assignment at line 7 should have
> >been
> >strTblName := &1;
> >i.e. the quotes in the original example were not needed?
> I'm not sure who the block head is here,
> but I don't understand why you can't grasp the original code read
> strTblname := '&1', the OP just ran that code, was prompted for &1 and
> had to enter TB_B4000 *without* quotes!
> Your nitpicking at me is fully irrelevant, and either you need better
> glasses, or more coffee, or all of those.
You said " You shouldn't include TB_B4000 in quotes when you specify it. The quotes are already there." I initially wondered whether you were proposing a change to his code to remove the quotes in line 7. However it seems you didn't mean that, you meant that when prompted for &1 (or when passing it as a command line parameter) he should not include any quotes. I agree, even though he did not do so, as he would have got this (10.2.0.1): SQL> DECLARE
2 strTblName VARCHAR2(30);
3 s NUMBER;
4 b DATE;
5 BEGIN
6 b := sysdate;
7 strTblName := '&1';
8 END;
9 /
Enter value for 1: 'banana'
old 7: strTblName := '&1';
new 7: strTblName := ''banana'';
strTblName := ''banana'';
*
ERROR at line 7:
ORA-06550: line 7, column 21:
PLS-00103: Encountered the symbol "BANANA" when expecting one of the
following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol "* was inserted before "BANANA" to continue.
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc