Home » Other » Client Tools » PL/SQL developer Command Prompt accepts input from user only one time (Oracle SQL Developer version 17.2.0.188 build 188.1159)
PL/SQL developer Command Prompt accepts input from user only one time [message #685414] Sat, 01 January 2022 11:44 Go to next message
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 #685415 is a reply to message #685414] Sat, 01 January 2022 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The value is asked when you create the procedure.
After that the procedure is stored inside the database with this specified value and so this one is used each time you execute the procedure.
a stored PL/SQL procedure is executed inside the database and has no contact with the user.

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 messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 68625
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 #685418 is a reply to message #685417] Sat, 01 January 2022 13:26 Go to previous messageGo to next message
SQLholic
Messages: 5
Registered: January 2022
Junior Member
Yes. It works as you said if I use anonymous block. But can't we take the input from the user using the procedure? Thanks for your help.
Re: PL/SQL developer Command Prompt accepts input from user only one time [message #685419 is a reply to message #685418] Sat, 01 January 2022 13:38 Go to previous messageGo to next message
SQLholic
Messages: 5
Registered: January 2022
Junior Member
PLEASE SEE THE ATTACHED JPG FILE. I used the FOR LOOP in the anonymous block to get the user input in every cycle. But it did not wait for my input. It takes the input only at the first time then it prints the next 4 records continuously with same value I entered. Can't we use the SQL PLUS for user interactive methods?
thanks.

[Updated on: Sat, 01 January 2022 13:39]

Report message to a moderator

Re: PL/SQL developer Command Prompt accepts input from user only one time [message #685420 is a reply to message #685419] Sat, 01 January 2022 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The code is executed inside the database and not inside the client.
So the value is taken once by the client which sends the code replacing the variable by the value to the database which executes it.
The database never sees the client tool variable and it's a value given by the user, it just sees the code with this value inside the code.

[Updated on: Sun, 02 January 2022 00:15]

Report message to a moderator

Re: PL/SQL developer Command Prompt accepts input from user only one time [message #685421 is a reply to message #685420] Sat, 01 January 2022 21:57 Go to previous messageGo to next message
SQLholic
Messages: 5
Registered: January 2022
Junior Member
ok. I understood the point. great thanks to your help.
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
John Watson
Messages: 8922
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>
Previous Topic: SQL Plus Format
Next Topic: Client Install
Goto Forum:
  


Current Time: Thu Mar 28 18:15:16 CDT 2024