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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 12 May 2003 13:47:02 -0800
Message-ID: <F001.005972F2.20030512134702@fatcity.com>

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]
>
> 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: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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 Mon May 12 2003 - 16:47:02 CDT