Re: SQL Variable

From: William Robertson <williamr2019_at_googlemail.com>
Date: 28 Apr 2007 10:20:56 -0700
Message-ID: <1177780856.781774.88330@o5g2000hsb.googlegroups.com>

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 Received on Sat Apr 28 2007 - 12:20:56 CDT