Oracle-L: RE: SQL Plus Default Value for &1 Help

From: <Jared.Still_at_radisys.com>
Date: Fri, 18 Oct 2002 14:58:52 -0800
Message-ID: <F001.004EDEB6.20021018145852@fatcity.com>

The problem comes down to this:

If there is a '&' in the script, and there is no value already assigned to it, sqlplus is going to stop processing, and wait for user input.

I've never found a way around this.

It would be nice if there were some facility such as all unix shells have to test for the existence and number of command line variables.

If this is something you must have, you might want to consider using Perl to do this, as it's fairly easy there.

Or use something like expect as a frontend to sqlplus, but I think that's more work than just doing the job in Perl.

Jared

"Post, Ethan" <Ethan.Post_at_ps.net>
10/18/2002 02:04 PM

        To:     Jared.Still_at_radisys.com, ORACLE-L_at_fatcity.com
        cc:     "Robert Fendley (E-mail)" <Robert.Fendley_at_ps.net>
        Subject:        RE: SQL Plus Default Value for &1 Help

Thanks Jared, I have been looking at Steve Adams accept.sql script and seeing if I can tweak that in someway. But I think you are right, this is a
difficult task indeed.

---
--

Script: accept.sql
Purpose: to prompt for a script parameter, but allow a default value
--
Copyright: (c) Ixora Pty Ltd
Author: Steve Adams
--
Synopsis: @accept name prompt default
--

---

accept _value_entered prompt "&2 [&3] "
column _value_returned new_value &1 noprint set termout off
select nvl('&_value_entered', '&3') "_value_returned" from dual; set termout on
undefine 1 2 3 _value_entered
column _value_returned clear

-----Original Message-----

Sent: Friday, October 18, 2002 3:58 PM
To: ORACLE-L_at_fatcity.com
Cc: Ethan.Post_at_ps.net
Importance: High

Ethan,

I think you're stuck here.

There isn't any memory structure in sqlplus that I'm aware of that will allow you to check for the existence of variables passed on the command line, without actually checking the variable itself.

Below is the method I use for passing command line variables to sqlplus, and prompting the user if they don't exist.

column ctest noprint new_value utest
prompt Enter the value:
set feed off echo off term off
select '&1' ctest from dual;
set feed on term on

select '&utest' from dual;

I don't see any way to directly supply a default value in sqlplus, though it
would really be cool if someone else has a way to do so in sqlplus.

This would likely require something like expect, or Perls Telnet module to catch user input, and timeout if not supplied in a timely manner.

Jared

"Post, Ethan" <Ethan.Post_at_ps.net>
Sent by: root_at_fatcity.com
10/18/2002 02:23 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

        cc: 
        Subject:        SQL Plus Default Value for &1 Help

Anyone got an example for the following...

I have a SQL Script that expects &1 passed into it, however, if &1 is not supplied I would like to use a default value and not stop to prompt the user. I am playing around with new_value and select nvl('&1','foo') from dual...etc...and nothing seems to be working.

Thanks,
Ethan Post
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Post, Ethan
INET: Ethan.Post_at_ps.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message