| ACCEPT command in PL/SQL Anonymous blocks [message #7795] |
Wed, 09 July 2003 10:05  |
Deepak
Messages: 111
Registered: December 1999
|
Senior Member |
|
|
Hi Everyone,
I have a question about using the ACCEPT command in a stand alone PL/SQL block.
This is what I want to achieve using the ACCEPT command.
First I want users to answer a question like
" DO YOU WANT TO USE PROGRAM XYZ ?"
If the answer is YES, then I want to prompt to the users again with a question
"PLEASE ENTER THE PATIENT NUMBER "
Using the answer to the above, I want to then call a PL/SQL anonymous block to execute a set of procedures.
If the answer is NO, then I want to call a different PL/SQL anonymous block to execute a different set of procedures.
Could this be done. Any help highly appreciated.
Thanks
Deepak
|
|
|
|
| Re: ACCEPT command in PL/SQL Anonymous blocks [message #7879 is a reply to message #7795] |
Mon, 14 July 2003 19:34  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
ACCEPT is a SQL*Plus command and therefore cannot be used in PL/SQL, whether an anonymous PL/SQL block or a stored procedure. However, you can accomplish the functionality that you are looking for using sql scripts that can contain anonymous PL/SQL blocks. For example, the scripts below would prompt for yes or no as desired, if yes is selected prompt for patient number and run a program, or if no is selected run another program. After each run, it will return you to the original yes or no question, until you enter something other than yes or no or nothing, then it will return to the SQL> prompt.
-- beginning of script1.sql
ACCEPT yes_or_no PROMPT 'DO YOU WANT TO USE PROGRAM XYZ? (YES/NO): '
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 SERVEROUTPUT ON VERIFY OFF WRAP ON
SPOOL script2.sql
SELECT DECODE (UPPER ('&yes_or_no'),
'YES', 'START programxyz.sql' || CHR (10) || 'START script1.sql',
'NO' , 'START notprogramxyz.sql' || CHR (10) || 'START script1.sql',
NULL)
FROM DUAL;
SPOOL OFF
START saved_settings.sql
START script2.sql
-- end of script1.sql
-- beginning of programxyz.sql
SET VERIFY OFF
ACCEPT patient_number PROMPT 'PLEASE ENTER THE PATIENT NUMBER: '
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is program xyz.');
DBMS_OUTPUT.PUT_LINE ('The patient number is ' || '&patient_number');
END;
/
-- end of programxyz.sql
-- beginning of notprogramxyz.sql
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is not program xyz.');
END;
/
-- end of notprogramxyz.sql
A sample run, using the above scripts, might look like this:
SQL> START script1
DO YOU WANT TO USE PROGRAM XYZ? (YES/NO): yes
Wrote file saved_settings
START programxyz.sql
START script1.sql
PLEASE ENTER THE PATIENT NUMBER: 1
This is program xyz.
The patient number is 1
DO YOU WANT TO USE PROGRAM XYZ? (YES/NO): no
Wrote file saved_settings
START notprogramxyz.sql
START script1.sql
This is not program xyz.
DO YOU WANT TO USE PROGRAM XYZ? (YES/NO):
Wrote file saved_settings
SQL>
|
|
|
|
| Re: ACCEPT command in PL/SQL Anonymous blocks [message #7913 is a reply to message #7879] |
Wed, 16 July 2003 19:51 |
Deepak
Messages: 111
Registered: December 1999
|
Senior Member |
|
|
Barbara,
Thank you so much for your explainations. I was able to use your ideas in my anonymous PL/SQL block. I was wondering what the "STORE SET saved_settings REPLACE" command does. Thanks once again for your help.
Deepak
|
|
|
|
| Re: ACCEPT command in PL/SQL Anonymous blocks [message #7917 is a reply to message #7913] |
Wed, 16 July 2003 21:27 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Deepak,
In order for the spooling to work properly, various set options, like echo and feedback and so forth have to be changed prior to the spooling. However, unless they are changed back after the spooling, you may get some undesired results. Without knowing what your previous settings were, the simplest method is to save the existing options, then restore them. STORE SET saved_settings REPLACE tells Oracle to store the current settings of all of the environment variables that can be modified using the set command to the file saved_settings.sql, replacing any such existing saved_settings.sql file. Then by starting that saved_settings.sql file, after the spooling, the prior settings are restored. Any time that you change some general setting within a program, it is considered a good practice to put it back the way it was before you started. If you:
SQL> EDIT saved_settings.sql
you will see the file containing all the stored set commands, ready to be re-run.
Barbara
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
