Re: Quick question about variable substitutions

In our last gripping episode antje <nastjenka_at_uswest.net> wrote:
> I am still pretty new to Oracle, and I am having problems finding a
 way
> to prompt the users for variable information. In SQL *Plus you can do
> this by using the ampersand. for example:
>
> select employee_name from employees where start_date='&Start_date';
>
> Unfortunately, this only works in SQL *Plus. I get the error "a non-
> numeric character was found where a numeric was expected" if I try the
> same query in Oracle SQL Worksheet. Is there a way in other
> applications, such as SQL worksheet, to prompt the users to enter in
> certain parameters? I searched this newgroup, and FAQ's and could not
> find anything other than the Ampersand syntax.
>
> Specifics:
> The query that I would be using that would prompt the user would be
> similar to the above example.
>
> Currently using:
> Oracle8i Release 8.1.6.0.0
>
> Thanks for any help you can provide!
>
> Nicole
>
> Sent via Deja.com
> http://www.deja.com/
>

There is the 'accept' syntax:

accept start_date prompt 'Enter starting date: '

which should prompt the user and pause for input prior to the execution of the query. I say should since I cannot get this to stop for input with SQL Worksheet (although I do get the prompt).

There are various and sundry ways to code this, depending upon the application and the base code involved. I am not certain that you can ask for, and populate, such variables through PL/SQL ( although there will, most likely, be someone who will prove me wrong :-) ).

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/