Re: Optional Prompting for Parameters

EscVector (EscVector_at_gmail.com) wrote:
: On Jun 14, 4:39 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
: > <faceman28..._at_yahoo.com> a =E9crit dans le message de news: 1181852233.99=
: 4342.4..._at_x35g2000prf.googlegroups.com...
: > |I would like to have an SQL script that will prompt for parameters
: > | that are not given.
: > |
: > | If I do
: > |
: > | @test 10
: > |
: > | it will run silently.
: > |
: > | If i do
: > |
: > | @test
: > |
: > | It will respond
: > |
: > | Enter Employee ID:
: > |
: > | How might one do this?
: > |
: >
: > You can't.
: >
: > Regards
: > Michel Cadot

: I agree in part that "you can't".

: It is easy enough to pass input via &1 &2 etc. syntax.
: Getting to prompt if the input is null is the hard part.

: I've tried with a simple ACCEPT &1 PROMPT 'ENTER EMPLOYEE ID: '
: and get double prompted.

: Using a named variable or fine requires circular syntax that assigns
: or binds the passed number value to allow for the prompt and that
: double prompted too.

: My best guess is that an anonymous block and GOTO could work in this
: case, but haven't tried it yet.

Newer sqlplus has && to prompt just the first time. That way you can be prompted by name just the first time.

In all sqlplus you can do

        define var_name=&enter_the_var_name which will prompt you once  with "enter_the_var_name" and then set it to var_name which can then be used multiple time without further prompting.

But it's hard to get the &1 into a variable the way you want because if you don't specify the parameter on the command line then you get prompted for it if you try to use it.

I typically do something like

	prompt 1 is var_name
	define var_name=&1

which will prompt with the cryptic variable 1 if you didn't enter it as a parameter, but the line above says what it's for so you know what to type. Received on Tue Jun 19 2007 - 00:15:16 CDT