Re: sqlplus bind variable question

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 1 Oct 2010 09:05:24 -0700 (PDT)
Message-ID: <fb1bd421-e4bd-461f-bc08-1bcae4db9b9f_at_d25g2000yqc.googlegroups.com>

On Sep 29, 3:45 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> On Sep 29, 11:53 am, Chuck <chuckh1958_nos..._at_gmail.com> wrote:
>
>
>
>
>
> > Recently on an SR, Oracle asked me to run a SELECT that included bind
> > variables named :1 and :2. Is it even possible to do that in sqlplus?
> > The only way I know of to make it work is to change the variable names
> > so that they start with a letter (eg :b1 and :b2). Is there a way to do
> > it without changing the names?
>
> > example:
>
> > var 1 varchar2(20)
> > exec :1 := 'abc';
>
> > That doesn't work, but this does...
>
> > var b1 varchar2(20)
> > exec :b1 := 'abc';
> > print b1
>
> You have to work with the one out of the set of valid variable names
> in sqlplus ... 1 is not legal.- Hide quoted text -
>
> - Show quoted text -

Here is an example:

SQL> select * from marktest where fld2 = 1;

FLD1 FLD2 FLD3
---------- ---------- ---------

moe                 1 24-AUG-10


SQL> _at_t98
SQL> set echo on
SQL> variable v1 varchar2(10)

SQL> variable v2 number
SQL> begin
  2 :v2 := 1;
  3 select fld1 into :v1 from marktest
  4 where fld2 = :v2;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> print :v1

V1

---

moe

HTH -- Mark D Powell -- Received on Fri Oct 01 2010 - 11:05:24 CDT