RE: Read user input inside plsql code

Ben

   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.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

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

You will need to do any user interaction from within SQLPLUS, then use the results in your PL/SQL block.

Jared

On Monday 12 May 2003 09:17, Kader Ben wrote:
> Hi Listers,
>
> I'm about writing a code to prompt user to enter some
> data and the code do some verification and
> subsequently prompt him for further information.
> Basically the pseudocode looks like the following:
>
>
> define a = Y ;
> define b = N;
> define c = 10;
> accept a prompt 'Enter value of a: ';
>
> BEGIN
> IF('&&a' = 'Y')
> THEN
> DBMS_OUTPUT.PUT_LINE('Enter value of c :');
> ACCEPT c;
> IF &&c = 10 THEN
> -- do some staff
> END IF;
> ELSIF('&&a' = 'N')
> THEN
> -- do other treatment
> END IF;
> END;
> /
>
>
> The problem the ACCEPT nor PROMPT doesn't work inside
> the
> PL/SQL code. Do you have any suggestion to do the code
> interact with user?
>
> Thank you in advance.
>
>
> Ben
>
>
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo.
> http://search.yahoo.com

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