Re: SQL Variable

On Apr 28, 6:41 pm, sybra..._at_hccnet.nl wrote:
> On 28 Apr 2007 10:20:56 -0700, William Robertson
>
>
>
> <williamr2..._at_googlemail.com> wrote:
> >On Apr 28, 12:03 pm, sybra..._at_hccnet.nl wrote:
> >> On 28 Apr 2007 02:11:49 -0700, William Robertson
>
> >> <williamr2..._at_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? Received on Sat Apr 28 2007 - 17:00:22 CDT