The Oracle (tm) Users' Co-Operative FAQ
Can Oracle prompt the user for input from a procedure?
|
Author's name: Keith_Jamieson Author's Email: Keih_Jamieson@hotmail.com |
Date written: 22 Sep 2003 Oracle version(s): 9.2.0.1.0 |
|
There have been several questions in the newsgroups requesting how to go about this, and certainly, you cannot do this with pure PLSQL but we can work around the problem. There have been several questions in the newsgroups requesting how to go about this, and certainly, you cannot do this with pure PLSQL but we can work around the problem. |
Firstly, while it is possible to
request a prompt from a user, by calling an external procedure or Java Stored
Procedure, it is a very bad idea, as it can end up leaving a transaction or a
piece of PL/SQL code waiting for ever.
It should never be implemented in a production environment. So why would you want to do this then. Well,
typically to test your procedure, feed in different values and determine
different execution paths.
OK, so how do you do it then. The answer is you call an executable piece of code, eg a JAVA Stored Procedure which calls some Java Code or an external procedure(In whichever 3GL is supported on your system, eg C. The executable will then prompt you. Typically, you could use Java to prompt for a value, return this to the Java Stored Procedure, which would inturn return to the calling PL/SQL module.
Further reading: External procedures, Java Stored Procedures
