PL/SQL developer Command Prompt accepts input from user only one time [message #685414] |
Sat, 01 January 2022 11:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/bb4c67cc81b7804b1ebe4e120a41013d?s=64&d=mm&r=g) |
SQLholic
Messages: 5 Registered: January 2022
|
Junior Member |
|
|
Hi,
When I run the successfully compiled procedure in SQL command prompt mode, It accepts the input from the user only onetime. It displays the result correctly. But if I run the same procedure again, it did not wait to accept the input from the user. Instead it displays the same result (data) it displayed earlier. Really, I am very much confused. Do I need to clear cache ? Please see the code and direct me to the right direction to resolve the issue.
CREATE OR REPLACE PROCEDURE PROC_USER_INPUT_DYNAMIC
AS
USER_INPUT NUMBER;
USER_INPUT_1 NUMBER;
BEGIN
USER_INPUT_1 := &USER_INPUT;
DBMS_OUTPUT.PUT_LINE(USER_INPUT_1);
END;
/
It should accept the input from the user every time I run the procedure. Am I right?
advance thanks to all.
|
|
|
|
Re: PL/SQL developer Command Prompt accepts input from user only one time [message #685416 is a reply to message #685415] |
Sat, 01 January 2022 12:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/bb4c67cc81b7804b1ebe4e120a41013d?s=64&d=mm&r=g) |
SQLholic
Messages: 5 Registered: January 2022
|
Junior Member |
|
|
If so, how can I dynamically take the input from the user? I also tried using anonymous block instead of procedure. same effect. It only accepts the input from the user only one time and displays the earlier result if we run the anonymous block again. If we send the parameters from the front-end application, will the procedure behaves like this?
can you recommend any solution for my requirements?
DECLARE
USER_INPUT NUMBER;
USER_INPUT_1 NUMBER;
BEGIN
USER_INPUT_1 := &USER_INPUT;
DBMS_OUTPUT.PUT_LINE(USER_INPUT_1);
END;
/
NOTE: IF I RUN THE PROCEDURE IN QUERY ANALYZER, IT ASKS THE USER EVERYTIME WHEN I RUN THE PROCEDURE BUT NO OUTPUT SHOWN ON THE RESULT WINDOW. WE HAVE TO RUN THE PROCEDURE ON THE COMMAND PROMPT ONLY TO SEE THE RESULT.
thanks
[Updated on: Sat, 01 January 2022 12:35] Report message to a moderator
|
|
|
Re: PL/SQL developer Command Prompt accepts input from user only one time [message #685417 is a reply to message #685416] |
Sat, 01 January 2022 13:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:. It only accepts the input from the user only one time and displays the earlier result if we run the anonymous block again.
...
IF I RUN THE PROCEDURE IN QUERY ANALYZER, IT ASKS THE USER EVERYTIME
This depends on your client tool.
SQL*Plus asks you each time:
SQL> DECLARE
2 USER_INPUT NUMBER;
3 USER_INPUT_1 NUMBER;
4 BEGIN
5 USER_INPUT_1 := &USER_INPUT;
6 DBMS_OUTPUT.PUT_LINE(USER_INPUT_1);
7 END;
8 /
Enter value for user_input: 45
45
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input: 56
56
PL/SQL procedure successfully completed.
This seems to be the case for your "QUERY ANALYZER".
This does not seem to be the case for "PL/SQL Developer" (I don't use it, maybe there is an option to change this behavior).
Or maybe you have to "undefine" the variable between each call, something like:
SQL> DECLARE
2 USER_INPUT NUMBER;
3 USER_INPUT_1 NUMBER;
4 BEGIN
5 USER_INPUT_1 := &USER_INPUT;
6 DBMS_OUTPUT.PUT_LINE(USER_INPUT_1);
7 END;
8 /
Enter value for user_input: 67
67
PL/SQL procedure successfully completed.
SQL> undef USER_INPUT
SQL> /
Enter value for user_input: 78
78
PL/SQL procedure successfully completed.
Quote:BUT NO OUTPUT SHOWN ON THE RESULT WINDOW.
You may have to activate an option.
In SQL*Plus you have to specify "set serveroutput on" to get the result:
SQL> set serveroutput off
SQL> /
Enter value for user_input: 56
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
Enter value for user_input: 78
78
PL/SQL procedure successfully completed.
[Updated on: Sat, 01 January 2022 13:15] Report message to a moderator
|
|
|
|
|
|
|
Re: PL/SQL developer Command Prompt accepts input from user only one time [message #685422 is a reply to message #685421] |
Sun, 02 January 2022 01:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Does this do what you want, passing the input value as an argument to a parameter?orclz> set serverout on
orclz>
orclz> create or replace procedure p1 (n1 number) as begin
2 dbms_output.put_line(n1);
3 end;
4 /
Procedure created.
orclz>
orclz>
orclz>
orclz> exec p1(&input)
Enter value for input: 1
1
PL/SQL procedure successfully completed.
orclz> exec p1(&input)
Enter value for input: 2
2
PL/SQL procedure successfully completed.
orclz>
|
|
|