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" <[EMAIL PROTECTED]>
10/18/2002 02:04 PM
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
cc: "Robert Fendley (E-mail)" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/18/2002 02:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
