Re: plsql prompt for input

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 17 Feb 2004 22:18:13 -0500
Message-ID: <301039415800.20040217221813@gennick.com>

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@gennick.com

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_at_gennick.com and include the word "subscribe" in either the subject or body.

Tuesday, February 17, 2004, 7:03:37 PM, Maryann Atkinson (maryann_30_at_yahoo.com) 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_at_freelists.org
MA> put 'unsubscribe' in the subject line.
MA> --
MA> Archives are at http://www.freelists.org/archives/oracle-l/

MA> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Tue Feb 17 2004 - 21:18:13 CST