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.

Back to index of questions


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


Back to top

Back to index of questions