Re: query with input variable

Make it easy on yourself and omit the ACCEPT. If you only use the substitution character & once, SQL*Plus will only ask for inpout once. An quick&dirty alternative is to hardcode the value of your variables at the top of the script and modify the script each time you need to...

HTH,
dirkske
<ouaatchane76_at_my-deja.com> wrote in message news:93cvmo$utt$1_at_nnrp1.deja.com...
> when in execute this code i find this error
> SQL> /
> Entrez une valeur pour nume: 2000
> ancien 2: select numetrai from traite where numetrai=&nume
> nouveau 2: select numetrai from traite where numetrai=2000
> accept nume number
> *
> ERREUR à la ligne 1:
> ORA-00900: Instruction SQL invalide
>
> how can i fixe this.
> thanks.
>
> In article <93cm4n$oi6$1_at_wanadoo.fr>,
> "Jérôme PERRET" <jperret_at_freesurf.fr> wrote:
> > Use the accept command
> >
> > sample :
> >
> > prompt 'Enter code :'
> > accept codeclt
> > select desig from client where code=&codeclt
> > /
> >
> > Jerome
> >
> > More about accept :
> >
> > ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format]
> > [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]
> >
> > ACCEPT reads a line of input and stores it in a given user variable.
> >
> > variable
> > is the name of the variable in which you wish to store a
 value.
> > If variable doesn't exist, SQL*Plus creates it.
> >
> > NUM[BER]
> > makes the datatype of variable the datatype NUMBER. If the
> > reply does not match the datatype, ACCEPT gives an error
 message
> > and prompts again.
> >
> > CHAR
> > makes the datatype of variable the datatype CHAR. The
 maximum CHAR
> > length limit is 240 bytes. If a multi-byte character set is
 used,
> > one CHAR may be more than one byte in size.
> >
> > DATE
> > Expects the reply to be a valid DATE format. If the reply
 is not
> > a valid DATE format, ACCEPT gives an error message and
 prompts
> > again. The datatype is CHAR.
> >
> > FOR[MAT]
> > specifies the input format for the reply. If the reply does
 not
> > match the specified format, ACCEPT gives an error message and
> > prompts again for a reply. The format element must be a text
> > constant such as A10 or 9.999.
> >
> > Oracle date formats such as 'dd/mm/yy' are valid when the
> > datatype is DATE. DATE without a specified format defaults
 to
> > the Oracle NLS_DATE_FORMAT of the current session.
> >
> > DEF[AULT]
> > sets the default value if a reply is not given. The reply
 must
> > be in the specified format if defined.
> >
> > PROMPT text
> > displays text on-screen before accepting the value of
 variable
> > from the user.
> >
> > NOPR[OMPT]
> > skips a line and waits for input; prompt not displayed.
> >
> > HIDE
> > suppresses the display as you type the reply.
> > <ouaatchane76_at_my-deja.com> a écrit dans le message :
> > 93ck17$jki$1_at_nnrp1.deja.com...
> > > hello,
> > > how can i write a query with input variable in sqlplus: the user
 will
> > > be able to input values at runtime.
> > > many thanks in advance.
> > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sun Jan 14 2001 - 11:20:55 CET