Re: SQL Variable

From: William Robertson <williamr2019_at_googlemail.com>
Date: 28 Apr 2007 15:48:47 -0700
Message-ID: <1177800527.705854.294780@q75g2000hsh.googlegroups.com>

On Apr 28, 11:24 pm, sybra..._at_hccnet.nl wrote:
> On 28 Apr 2007 15:00:22 -0700, William Robertson
>
> <williamr2..._at_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. Received on Sat Apr 28 2007 - 17:48:47 CDT