Re: plsql prompt for input

Jonathan Gennick <jonathan@xxxxxxxxxxx> · Tue, 17 Feb 2004 22:18:13 -0500

Hello Maryann,

If you can find a copy of my SQL*Plus book, read the two
chapters on scripting. There are some tricks you can play
with SQL*Plus to get it to branch depending on whether a
user response with Y or an N.

You really can't prompt for input from PL/SQL. You attempt
to do this below via &&Response, but it is actually SQL*Plus
that prompts for that variable. The true sequence of events
is:

1. SQL*Plus prompts for EMP_ID  (the ACCEPT command)
2. SQL*Plus prompts for Response  (because of &&Response)
3. SQL*Plus sends the PL/SQL block across the network to the
database
4. The database executes the block.
5. DBMS_OUTPUT places "Employee ID entered is ..." into the
output buffer
6. DBMS_OUTPUT places "Response is..." into the output
buffer
7. PL/SQL block exection ends
8. SQL*Plus reads the buffer and displays the two lines
generated by DBMS_OUTPUT.
9. You may not see any of the output generated by
DBMS_OUTPUT, because it doesn't look like you execute SET
SERVEROUTPUT ON.

Maddening as it may seem, there's no way to see any of the
output generated by DBMS_OUTPUT until the PL/SQL block ends.

You might try something along these lines:

ACCEPT Emp_ID Prompt 'Please Enter the Employee ID > '
ACCEPT Response Prompt 'Is &Emp_ID correct (y/n)?> '
@somefile_&Response

Ok. The key here is to be sure you have two script files,
one named somefile_y.sql, and the other named
somefile_n.sql. The user's y/n response then determines
which of those files is executed. I don't show it, but there
are some things you can do to validate that y/n response,
and make it case-insensitive, if those things are important
to you.

SQL*Plus isn't the world's most robust scripting tool.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, 
or send email to Oracle-article-request@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

Tuesday, February 17, 2004, 7:03:37 PM, Maryann Atkinson (maryann_30@xxxxxxxxx) 
wrote:
MA> I am trying to write a pl/sql script that will prompt the user for an id,
MA> and then somehow I need to re-prompt the user to verify that indeed
MA> this is the correct id.

MA> The one below seems like it should work, except because of buffer issue(I 
MA> guess)
MA> I dont get to see the line that prompts the user if its correct.
MA> Anyone has an idea how it can be improved?

MA> thx
MA> maa

MA> SET VERIFY OFF
MA> ACCEPT Emp_ID Prompt 'Please Enter the Employee ID > '

MA> DECLARE
MA>     Response    VARCHAR2(1);

MA> BEGIN

MA>      DBMS_Output.Put_Line('Employee ID entered is ' || &Emp_ID
MA>                         || '. Is this correct?(Y/N)');

MA>      Response := &&Response;
MA>      DBMS_Output.Put_Line('Response is ' || Response);
MA> END;
MA> /

MA> ----------------------------------------------------------------
MA> Please see the official ORACLE-L FAQ: http://www.orafaq.com
MA> ----------------------------------------------------------------
MA> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
MA> put 'unsubscribe' in the subject line.
MA> --
MA> Archives are at //www.freelists.org/archives/oracle-l/
MA> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
MA> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: