Re: Defaulting the parameters in a sqlplus script.

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 25 May 2004 10:37:11 -0500
Message-ID: <uu0y4cygo.fsf@standardandpoors.com>

On Tue, 25 May 2004, nospam_at_noway.nohow wrote:

> On 20 May 2004 09:02:05 -0500, Galen Boyer
> <galenboyer_at_hotpop.com> wrote:
>

>>On Thu, 20 May 2004, nospam_at_noway.nohow wrote:
>>> On 19 May 2004 10:21:15 -0500, Galen Boyer
>>> <galenboyer_at_hotpop.com> wrote:
>>>
>>>>On Wed, 19 May 2004, micadot{at}altern{dot}org wrote:
>>>>>
>>>>> "Galen Boyer" <galenboyer_at_hotpop.com> a écrit dans le
>>>>> message de news:ulljosfg5.fsf_at_standardandpoors.com...
>>>>>> Is this possible, so that you don't have to pass all
>>>>>> parameters at the command-line? I'm on 9i if there are
>>>>>> any later features for this.
>>>>>>
>>>>>
>>>>> You can use "define" in your login.sql to default the
>>>>> parameter value: define 1="first parameter" define
>>>>> 2="second parameter" ...
>>>>
>>>>Yeah,
>>>>
>>>>I didn't really want to do it that way, but ...
>>>
>>> I'm kind of curious as to why do don't want to do it that
>>> way. If you want certain defaults -- for *anything* -- you
>>> have to set them *somewhere*. How is setting them in
>>> login.sql any different -- and less desireable -- than
>>> setting them somewhere else -- in some other file?
>>
>>I was hoping that a sqlplus script would allow me to not pass
>>in a parameter and still not prompt me for it, by defaulting
>>it.

> 
> I'm confused.  Isn't that exactly what login.sql does for you?
> I must be missing something.

Here is an example of my issue:

Here is my login.sql:

define 1=SETTONOTHING
define 2=SETTONOTHING

Now Login to sql:

sqlplus galen/galen

SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 25 11:25:47 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

What does define say?

SQL> define

    DEFINE _CONNECT_IDENTIFIER = "oralocal" (CHAR)
    DEFINE _SQLPLUS_RELEASE = "902000400" (CHAR)
    DEFINE _EDITOR	       = "Notepad" (CHAR)
    DEFINE _O_VERSION      = "Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production" (CHAR)

    DEFINE _O_RELEASE      = "902000400" (CHAR)
    DEFINE 1	       = "SETTONOTHING" (CHAR)
    DEFINE 2	       = "SETTONOTHING" (CHAR)

Okay, this is what I would expect. Lets now run the following file:

prompt &1
prompt &2

Here I am running it:

[BOYERG4] /cygdrive/c/grers/grersrepository/ccrermf/dbms/bin > sqlplus galen/galen @parmfile

SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 25 11:26:53 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SETTONOTHING
SETTONOTHING Okay, still what I would expect. Now lets pass parameters to that file:

[BOYERG4] /cygdrive/c/grers/grersrepository/ccrermf/dbms/bin > sqlplus galen/galen @parmfile AA

SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 25 11:27:03 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SETTONOTHING
SETTONOTHING See. The scriptname is a parameter itself, so I can't pass the scriptname a parameter.

AA
SETTONOTHING But, even this would be hokey if it worked. The best would be to not have to rely on login.sql and instead have the ability to default the parameters directly in the script so that I wouldn't have even gotten prompted for &2 and only seen 'AA'.

-- 
Galen Boyer

Received on Tue May 25 2004 - 10:37:11 CDT