| PL/SQL developer Command Prompt accepts input from user only one time [message #685414] |
Sat, 01 January 2022 11:44  |
 |
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;
/
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  |
|
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;
/
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 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 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. 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 |
John Watson
Messages: 8962
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> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
