Oracle-L: RE: Read user input inside plsql code

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 13 May 2003 07:41:00 -0800
Message-ID: <F001.005978AE.20030513074100@fatcity.com>

Jacques - Yes you can, and I almost typed in the exact code from the class notes. However, in looking at the request more closely, he is asking if he can prompt the user, then based on some input validation rules either make another prompt or not. I don't think it can be done and even if you could it would be a mess to maintain.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, May 12, 2003 4:47 PM
To: Multiple recipients of list ORACLE-L

Well, you can always do something like this in a SQL*Plus script:

accept userinput char prompt "Enter value " begin

if '&userinput' = '&Y'
then

do something ; else
do somethinge else ; end if ; end ; /

accept userinput char prompt "Enter value " column script_name new_value script_to_run select

   decode ('&userinput', 'Y', 'script1.sql', 
           'N', 'script2.sql', 
           '--dont_run_any_script') as script_name

from dual ;
whenever oserror then continue
@@&script_to_run

> -----Original Message-----
> From: DENNIS WILLIAMS [ mailto:DWILLIAMS_at_LIFETOUCH.COM
<mailto:DWILLIAMS_at_LIFETOUCH.COM> ]
>
> If you have access to the Oracle Education SQL and PL/SQL
> class notes,
> several of the lab exercises provide examples of
> intermingling SQL*Plus and
> PL/SQL code in a single script. However, I don't see how to
> do what you are
> asking. Basically you can prompt for all the variables, then
> run the PL/SQL
> block. And then you can follow with a section of SQL*Plus
> that can either
> print some values or prompt for more questions. The problem
> is that I don't
> think you can make a decision whether to run the following
> SQL*Plus segment.
> What you wish to do is probably a little too sophisticated
> for the tools
> available. And not very easy to document or maintain if you
> did achieve it.
> If you wish to expand your vision to include the Unix ksh,
> then you could
> drive decisions in it and run SQL*Plus and/or PL/SQL scripts
> as needed. Or
> consider another tool like Perl that can do it all so you get
> something that
> is easy to maintain and document.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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
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).

Received on Tue May 13 2003 - 10:41:00 CDT